Microsoft has released Microsoft Dynamics GP 2010 with almost 200 SQL Server Reporting Services (“SSRS” or “SRS”) reports. They are accessible from the home page as a
Microsoft released SQL Reporting Services back in 2004 as a free download to SQL Server 2000 that allowed Web-based reporting using the SQL Server platform. Report definitions are stored in two SQL Server databases, ReportServer and ReportServerTempDB, and displayed in Internet Explorer using Web services. Given its name and storage technology, one common misnomer of SRS is that it can only report on data stored in SQL Server. This is incorrect since SRS can report on virtually any data source, as long as an ODBC driver exists for the data to be output. This includes, of course, SQL Server, but it also encompasses Microsoft Excel files, Microsoft Access databases, text files, Oracle, and just about any other data storage technology that exists. If an ODBC driver exists, SRS can retrieve and display the data in textual format or with any number of included charts and graphs.
For anyone that has used the Access report writer tool, the SRS interface should appear similar. In fact, reports written in Access can be imported into the SRS Integrated Development Environment (“IDE”) and converted to the SRS report definition language (“RDL”). The data sources would need to be changed since the reports are being pulled out of the Access environment into SRS, but the general layout of the report, which many users say is one of the more difficult aspects of report writing, would remain. Defining a data source in SRS follows the now-standard Microsoft method of encapsulating connection information by prompting the user for the ODBC driver and credential information. The DSN attached to the ODBC driver must only exist on the server, thereby giving SRS and the related reports a minimal footprint on the client machines (more later).
SQL Reporting Services operates under three principles: data sources, data sets, and presentation. The most granular level of definition with an SRS report is the data source, which defines the location of requested data. Data sets, on the other hand, are tied to data sources and define the data that will be pulled. These are synonymous with queries. The presentation or rendering of an SRS report pulls data from the data sets and lays it out on the report as tables, lists, and free-form formats. Multiple data sets can be sorted and grouped on one report, allowing heterogeneous data to be displayed in a homogeneous fashion. Adding to the power of SRS is the ability to have dynamic parameters—having one or more parameters define the possible values of one or more other parameters—displayed to the end user to drive the data retrieval. Parameters themselves can be tied to data sets, giving the users lists of values for a given entry. An example of this is a data set that pulls a list of customers from a data source and displays these as a drop-down list to the user. We could then tie a dynamic parameter to this list such that selecting a customer will fill another parameter with a list of possible invoices for the selected customer. Selecting from the list of invoices—a parameter tied to another data source—will populate a report displaying the invoice in a tabular format in a Web page, thereby displaying the power of SRS.
Reports written in SRS are delivered via two Internet Information Services (“IIS”) sites: ReportServer and
Finally, SRS is much more than a simple reporting engine. Programmatic logic can be incorporated into reports so that launching a report will invoke other activities. An example of this is to update a “times printed” column in a SQL database every time a report is printed. By nature, SRS is an extensible technology via .NET assemblies or SQL Server stored procedures. Given this extensibility, entire applications can be written solely using SRS and deployed to the Report Manager Web site to drive business processes, such as automatically running a data migration/integration from one SQL Server to another SQL Server. E-mail can be sent automatically whenever reports are printed and, since SRS is a Web application, hyperlinks can be included in the published reports to make the reports truly dynamic and data driven. Additionally, sub-reports can be added to published reports and all of these components working in concert provide a rich reporting and BI experience.
Crestwood has been working with SQL Reporting Services since it was released in 2004 and has written hundreds of reports for Dynamics GP and CRM. Please contact us at [email protected] for more information about using this tool.
Written by William Schroeder, Practice Manager, at your