Reports – Steve Novoselac's Blog

You can whip out reports in SSRS (SQL Server Reporting Services) very quickly with the report builders (2.0 and 3.0 are money).

But what should you remember to do each time, or information to get?

Where does the data come from (GL, Sales, etc) – we could use a cube or datawarehouse, or staging, or Other system, etc.
Report Name (on Report Manager)
Short description of the purpose – to appear under report name on report manager
Should it go into an existing folder, or a new folder, or the user’s folder?
Who should have access?
How should it be consumed? Email (and what, excel, pdf, web archive, etc), To a file share, User Ran?
What parameters should be available? a. What are the defaults?
Can we get a rough mockup of how it should look? a. Can we get an existing report (if avail, crystal, or excel, etc)
Does it need to print on a 8×11 page?
How often are you going to run it? (Hourly,Daily, Weekly, Monthly, ad-hoc)
Is it going to be informational, or used to export data and manipulate? (if export – is there something else we can do?)
If tabular data, does it need to be sortable?
Do snapshots need to be taken? a. How many do we save? b. How often to take them? c. Do you need ability to delete them?

you get the idea, the list could go on and on. So creating that report is simple, but actually getting and doing all the things necessary to get it done “correctly” is more time consuming.

What other things can you think of for the list?

Last week I was in a heated discussion about Business Intelligence, and it came up that “Business Intelligence is just writing reports, what is the difference from just writing some reports?”

I tend to disagree. Writing reports is, well, writing reports. You can write reports off of tons of data, and yeah, probably get some good info, but usually it ends up in disparate information.

I have blogged about this before, probably a few times, but this is usually what happens in organizations:

1) Some app or system is set up where data is created in some kind of data store, usually a database

2) People want to see the data in some type of report

3) Developers start cranking out reports right off the source system, stopping the bleeding for the current need for more reporting.

4) System keeps growing, more data is created/collected.

5) Reports off source system start slowing down, timing out, many different reports end up for the same thing and don’t match, end users complain.

6) Developers get clever, start moving and summarizing data on some kind of interval, all with custom code, custom design. They write some reports off the summary data to once again stop the bleeding. Some reports are still off the source data. Reports don’t match. End users complain

7) More data is collected, more users want reports, and want them with different views, grouped by this, sorted by that, add this column, remove this column, etc.

8) Developers are taking the heat. Their reports aren’t matching, they are running slow, etc. They can’t keep up with all the report requests. They decide to create a custom reporting engine on top of their summary data that allows end users to create their one reports using some quasi query language and drag and drop columns/grouping. This stops the bleeding for a while.

9) Finally someone in the organization realizes that the developers are reinventing the wheel. This is where Business Intelligence comes in. The source data is left untouched, the BI group creates and ETL to have some kind of data warehouse design and structure, using out of the box tools. (SQL Server Management Studio is and out of the box tool – you use that to write queries. SQL Server Integration/Analysis/Reporting services are out of the box tools, you use them to create ETL’s, Cubes, and Reports).

10) End users can now write reports using Excel off the OLAP Cube, or they use the Report Builder with Reporting Services to create reports off a UDM (Universal Data Model), or the BI devs create reports in Reporting Services, all off the same source data. One version of the truth, less custom reporting.

11) Developers are left to develop on the source system turning requirements into code/features, not focus on reports, and the BI group is focused on getting all the data turned into information.

Of course this is a perfect scenario described above. Nothing is ever as simple as that, but we can hope. Also I am biased towards the MSFT toolset, but there are other toolsets out there that would solve the same problem.

All I know is that I have seen everywhere I go pretty much the same thing happen from #1 to #8 above. In some cases you never see #9-#11 and you end up in a world of hurt.

In the end though, Business Intelligence just isn’t “writing reports”, far from it.