Finally, some reporting scenarios
I am a SQL database bod. I have unfettered access to the servers under my watch, I understand the data in them. I can create SQL at will and see the results in various grids. I can paste my results into reporting tools and view the results. This is not very flexible, and potentially, a very slow way of getting information circulated. It is very useful in developing reports, but there is a long cycle of generating the reports - for the person who wants to see them.
- Many people have experience of Excel - which provides a useful interactive mechanism for creating reports. There are a few ways Excel can be used, as follows:
- Preconfigure an Excel document with macros - A user connects to data using one of the provisioning scenarios. The skill is to produce the macro so the reports can be viewed with basic knowledge of connecting to an external data source. System Integration Manager (SIM) can even put the data in the spreadsheet for you.
- Import data tables - Connecting to external data table using the Excel menu command can let you load up your spreadsheet and start processing.
- Import data into pivot tables and pivot charts - These are very powerful tools. You need processed data, such as from data stores, data warehouse or SQL server integration services (SSIS) to make best use of these. You can quickly create summary reports using filters and aggregations (Sum, Average, Max) and “dice and splice” on dates/time, rooms/locations, students/courses/departments so it’s a matter of planning your data extracts.
SSRS: SQL Server Reporting Services
This is a module of the SQL Server, which needs installing and configuring. This is a useful tool as you can use it to access various data provisions. In the simplest form, you can select tables to base your reports on, or develop SQL views and use SSIS data cubes.
As part of Visual Studio, you can manage the production of reports. The SSRS interface allows you to control the access, and it allows for subscriptions ether by registering or by generated list. There is also a product called Report Builder, which enables you to create one- off reports in an ad-hoc manner.
These tend to be focused on Key Performance Indicators, and often have a pleasing interface with lots of bells and whistles. Drill down to underlying data. Nothing SSRS can’t do, but usually much more pleasing. Underlying this is the provisioning of data. They usually need processed data, as outlined.
Sometimes all you require is a simple file of data to pass onto someone or for a system to consume, for example staff/hours for payroll.