A while back I posted a tutorial on creating and deploying a report using SSRS. I wanted to go back to this example, see if anything had changed in SQL Server 2014 and try to dig a little deeper into the product. All my examples in both posts are working in Native mode (as opposed to Sharepoint mode).
A useful book I purchased was Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide by Reza Rad. It’s a good overview of the various BI technologies used in SQL Server, I definitely recommend it.
SSRS is installed from the SQL Server installation media (I’m using SQL Server 2014 Developer Edition). Once installed, I needed to open the Reporting Services Configuration Manager. Once connected to the correct Reporting Service instance, I went to the Web Service URL tab, then clicked ‘Apply’ to accept the default settings (I also did the same with the Report Manager URL tab).
From here, we’ll need to create databases to be used by the Reporting service. This can be done from the ‘Database’ tab, then ‘Change Database’.
Create a new report server database => Connect to the database server.
This will create two databases: ReportServer and ReportServerTempDB(Although the ReportServer name can be changed during setup). ReportServer stores the report definitions, data sources, security setup data, etc. The TempDB stores session information and cached data.
I use Visual Studio to create reports. You can download Data Tools for Visual Studio(which takes the places of BIDS). There is still a wizard that will guide you through the report creation, although after the report is created you may want to go back to the designer to tweak it.
In my original example, I passed a stored procedure with hard-coded parameter values as query text (which is the only available option in the wizard). In VS, you can right-click on the dataset, select ‘Dataset properties’ and go to the ‘Query’ tab. We can then set the Query Type to text, table or stored procedure. Once we set the type to stored procedure, we can run the report and we’ll see input boxes to enter the parameter values.
One thing that tripped me up was after I updated the dataset, ran the report, and I wasn’t able to set the parameter values. The report project still compiles, but there will be a warning that the dataset wasn’t updated (which I didn’t notice at first). As the default, the report project has the OverwriteDataSources and OverwriteDatasets properties set to false, so these need to be set to true so that these components can be updated.
In the designer, we also have row group and column group sections where we can group values for a specified value together. In my example, I set the SalesDate as a row group, so all records with the same sales date will be listed together in the report. We can also enable drill-down capability, where all the values for each date are collapsed and we can expand them.
We can also enable sorting by selecting the details row and selecting ‘Tablix Properties’. From here, we can select a column or columns and set the sort order.
The Wizard will prompt for one of two report types, Tabular or Matrix. Tabular allows us to specify the columns (a typical report setup) and Matrix allows us to define both the columns and row headers.
Once the report has been displayed, there are several options available for exporting the report: XML, CSV, TIFF, PDF, Excel, Word and MHTML. When I first tried exporting to Excel, there was no data exported, since all of the drill-down sales dates were collapsed. Once I opened everything in the report, I was able to export the data to a spreadsheet.
Expressions and Code:
One of the powerful things in SSRS is expressions, which allows the user to write code (using a VBA like code syntax) to create expressions. We can use these expressions to set the background color of a text box, for example, or to create calculated fields. We can right-click in a text box in the designer and select ‘Expression’ to open the Expression Builder window. Some of the functions we can use are IIF(immediate if) or a switch statement.
For more complicated scenarios, we can also write code and create function. From the report properties, we can select the ‘Code’ tab to access the code window. If a function is created here (say a GetSalesType function) we can call Code.GetSalesType in an expression to run this code.
Along with the Reporting Services Configuration Manager that was referred to in the Setup section, there are some web based applications used to manage reports. The Report Server was setup in the Configuration Manager under Web Service. This can be used to view reports.
The Report Manager can be used for configuration and administration.
SSRS uses roles to manage access for reports.
The Report Manager can be used to control access to reports: There are several built-in roles: Browser(view reports), Content Manager, My Reports(give a user admin permission on their own directory), Publisher, Report Builder.
Anyone who belongs to BUILTIN/Administrators will be an admin for SSRS as well.
From the ‘New Role Assignment’ page, users or groups can be assigned to these roles. Security can be set for a directory or even for a specific item as well.
In addition to the book I mentioned, SQL Server Central has a Stairways to SSRS series that goes over the basics of SSRS.