Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSRS Pro best practices for saving or reusing reports, queries, store procedures etc. for use at multiple companies Expand / Collapse
Posted Friday, December 20, 2013 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 19, 2015 3:12 PM
Points: 39, Visits: 154
I have a question that I haven't seen put out there and I don't know if it is because it would never be done or is just a unspoken law.

In the last decade, I have had a few different positions at different companies. Some I left for better opportunities, others due to layoffs, and others due to contracts ending.

As a report designer, query writer, programmer (C# mainly), and all around techie guy; I find myself sometimes coming to a new company who wants a report done in much the same way as a previous employer.

Usually, I start from scratch building the report to the new specs; but, on several occasions I use the same techniques as before but possibly forgot what I did to get around a problem. At that point, I do what I normally do, go research the best way to tackle a problem, apply it, and move on.

However, it strikes me that I could have been saving my work all along and have it for reference later. The first obstacle that comes to mind is that I don't want to use the employer data or expose any part of their business to security risks and I don't want to breach any non-compete or other such industry agreement about data etc.

How do you keep track of the nifty problems you've solved and how do you store them for later application in other projects without infringing on the concerns above? Do you make a dummy database that mirrors the one you work on daily? Do you setup mock reports/SPs/code etc. that mimic what you've done?

What I'd like to have eventually is a tidy stable of reports that I've done along with other types of projects in a place that I can use them in current work and show them off as examples of work.
Post #1525162
Posted Saturday, December 21, 2013 6:07 PM


Group: General Forum Members
Last Login: Today @ 3:58 PM
Points: 1,511, Visits: 8,715
Here's one idea... definitely don't take any data that could be construed as belonging to your employer, but if you develop a solution, there's no reason you can't keep a database of things like that. Especially stored procedures, functions etc, or a general design.

Create a database on your personal computer and if you're allowed (check first), script out the structure of the tables you need and the stored procedures, etc. Then fill them with fake data. Jeff Moden has a routine somewhere around here that generates something like a million rows of data (look for "JBMTest"). Modify that to insert the proper data types into your tables.

Then you can create stored procedures and reports based on that. I did one for fake patient data where I found the top 200 most frequent first names and last names, and then did a top values/cross join to populate a table with fake people. Birthdates are easy - generate a random number and use DATEADD() to make it look reasonably realistic. (If you have The Guru's Guide to T-SQL, the author gives a bunch of examples of creating stored procedures to crank out fake data.) Then write all your stored procedures etc for your reports and away you go. Sure, the data is fake, but who cares?
Post #1525305
Posted Saturday, December 21, 2013 10:54 PM



Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 38,559, Visits: 35,567
Here are a couple of articles for generating gobs of data in a random but highly constrained (you know what the ranges of values will be) fashion.

Dwain Camps has a good article on how to generate non-uniform random but constrained data at the following URL.

And, no need to search for "JBMTest". I've included one of the many different renditions here.

Create a voluminous test table with various types of highly randomized data.

--Jeff Moden
--===== Conditionally drop the test table to make reruns easier
--===== Create and populate a 1,000,000 row test table.
-- "SomeID" has a range of 1 to 1,000,000 unique numbers
-- "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers
-- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times
-- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates"
-- "SomeName" contains random characters at random lengths from 2 to 20 characters
SELECT TOP 1000000
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))
SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding
SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2)
INTO dbo.JBMTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

Helpful Links:
How to post code problems
How to post performance problems
Post #1525317
Posted Tuesday, December 24, 2013 8:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 19, 2015 3:12 PM
Points: 39, Visits: 154
Thank you very much for the insight.
Post #1525760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse