Application reports in SSRS

I’ve been researching SQL Server Reporting Services. Our goal is to let users of our application view reports created by a business administrator. The application uses Active Directory via forms authentication. It has its own custom authorization.

There are two ways to create a report in SSRS. One way is to use Visual Studio. The second is to use the Report Builder on line in the Report Manger site. I’ll use Visual Studio to get started. I’m using SQL Server 2008 R2 and Visual Studio 2008.

Create a Report Server Project
imageFirst create a Report Server project. I’m using the Report Server Project Wizard in the Business Intelligence Projects section. If you don’t see Business Intelligence Projects, please be sure to install SQL Server Reporting Services on your development box.

Create a new data source by setting up a connection string. Since we are creating application reports, we connect to the development application database. You can hit the “Edit” button to help you out, or just copy the connection string from the web.config file of your application.

image My application uses a specific SQL account to access the database. This is not the end-user’s account. I entered these credentials while creating the connection string. The username and password are not visible in the generated connection string, but I can see that they’ve been stored if I hit the “Credentials” button.

Next the wizard prompts you to create a query. Define a query that selects and groups the data the way that it will be displayed on the report. The report can do little more than place the data in the right boxes. You’ll need the query to do the heavy lifting.image

Then, you create your report. I selected a “Matrix” report, which allows me put data on both rows and columns. You might also know this as a “crosstab” report. My query has four columns, which is the ideal number for a matrix report. I added the highest-level concept to the “Page” box, the grouping concepts to “Rows” and “Columns”, and the lowest-level value to “Details”. Avoid putting more than one column in these boxes, because that will turn a simple matrix report into a complex tree.

Finally, give the URL of your reporting server. You can find this URL by running “Microsoft SQL Server 2008 R2”, “Configuration Tools”, “Reporting Services Configuration Manager”. Click on “Web Service URL” and select the hyperlink.

Deploy the report
You now have a project containing a single .rdl file. This is a report definition that SSRS can run. You can run the report now by pressing F5. For others to run the report, it will need to reside on the report server.

image By default, the project is not deployable. Select “Build”, “Configuration Manager” to change this. Check the box under “Deploy” for your report project.

image To deploy the project, select “Build”, “Deploy Solution”. The .rdl file will be copied to the reporting server. You can now see the report on line. Go back to the Reporting Services Configuration Manager, but this time choose the “Report Manager URL” section. Click the hyperlink to open the report manager in the browser.

The report was deployed to a folder that you named in the wizard. Mine is in IONReports. Click on the folder, and then click on the report to view it.

 

You probably want other people to be able to access the report. Right now, it is probably deployed to SSRS running on localhost. To deploy it to a remote server, first ensure that SSRS is installed on the target machine. Then you can run the Reporting Services Configuration Manager on that server to find the Web Service URL.image

Right click on the report server project in Visual Studio and choose “Properties”. This brings up a property page where you can edit the settings that you configured using the wizard. Enter the remote server’s Web Service URL in the TargetServerURL. You can now deploy the report to a server where others can run it.

image Once the report is deployed, others can edit it on line using the Report Manager. Instead of clicking to run the report, pull down the menu. Choose “Edit in Report Builder”. The report builder is not quite as slick as the Visual Studio wizard, but it gets the job done. The connection, query, and report layout that you created in Visual Studio are all editable from within the report builder. You just might have to use text instead of a visual interface.

A business admin can edit and create reports using this interface. It may be easier for everyone if a developer or DBA designs the queries to start with. A developer would most likely be using a Visual Studio report server project, whereas the DBA would probably create a view. But once they have help getting started, the business admin can take over.

Next steps
Remember, the goal is to access reports from within an application. The admin uses the Report Manager, but the end user needs a simpler, branded interface. We’ll tackle that in the next article.

Leave a Reply

You must be logged in to post a comment.