Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Pro best practices for saving or reusing reports, queries, store procedures etc. for use at...


SSRS Pro best practices for saving or reusing reports, queries, store procedures etc. for use at multiple companies

Author
Message
asheppardwork
asheppardwork
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 162
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.
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2172 Visits: 12483
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?
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44968 Visits: 39862
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.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/

Dwain Camps has a good article on how to generate non-uniform random but constrained data at the following URL.
http://www.sqlservercentral.com/articles/SQL+Uniform+Random+Numbers/91103/

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


/**********************************************************************************************************************
Purpose:
Create a voluminous test table with various types of highly randomized data.

--Jeff Moden
**********************************************************************************************************************/
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL
DROP TABLE dbo.JBMTest
;
--===== 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
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))
+ 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
;
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90
;




--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
asheppardwork
asheppardwork
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 162
Thank you very much for the insight.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search