Gallery

Introducing SQL reports

Posted on 6 Nov 2008 at 15:48

End users' reporting requirements are ever-changing. Simon Jones takes you through the process of building a report model to answer every demand.

Once you've checked every entity and attribute and are satisfied that they're all logically named and have their properties correctly specified, you can deploy the model to the report server. Right-click on the root of the project in Solution Explorer and choose "Properties" from the menu, and you'll see the configuration properties, where you set the Target Server URL and the folder names for the model and data sources. The target Server URL should be something like http://ServerName/ReportServer, where ServerName is the name of your SQL Server, and if you have different-named instances of SQL Server on that box, put the instance name after a dollar sign on the end of the URL. Click OK to save the configuration parameters, right-click the root of the project again and choose "Deploy" from the menu, whereupon Visual Studio will build your project and upload it to the server.

There's no way to try out your model inside Visual Studio, so to test it you have to open your internet browser and navigate to the report server's website. This is usually at http://ServerName/Reports, but as before you'll need to add "$InstanceName" if you have named instances. You should see a Report Builder button on the grey bar towards the top of the page, and clicking this will check if you already have the Report Builder application installed, and download it from the server if you haven't - at about 6MB it doesn't take long. Once it's loaded, you'll see the Getting Started task pane on the right-hand side where you can create a new report based on any model that's been published to the server, or open an existing report.

As well as choosing the model you want to work with, you also have to specify the layout of the report: this can be a table, a matrix or a chart. Table reports are the simplest, with columns and rows of data. Matrix reports are akin to CrossTabs in Excel, with lookup data in the column and row labels and summary data in the middle. Having chosen the model and report layout, you'll see the entities and attributes in the model in the Explorer pane on the left and the report design surface fills the rest of the window. Creating a report is simply a matter of dragging and dropping entities and attributes onto the design surface. If you drag an entity onto the design surface, you'll get either the entity's default detail attributes or its default aggregate attributes, depending on the context. Other attributes can be dragged individually to the design surface.

You can sort and group data, format it with fonts, colours and lines, add titles and other textboxes, and set filter parameters that are either fixed or for which the user is prompted when the report is run. You can run the report right there in the Report Builder to test the layout and make sure everything is working properly. Once you're happy with a report you can export it as a PDF document, Excel workbook, as XML data or more, or you can save its definition to the report server, either in your personal My Reports folder or, with the right permissions, to some other folder from where other users will be able to run it. Once the report definition is saved, the report can be run whenever you want with just a couple of clicks, and can be scheduled to run at regular intervals with the results emailed to you in any of the supported formats.