Developing Reports for Excel

One of the most popular popular formats for SSRS to export to is excel, especially for large data dumps or for further data processing, or just because it is so familiar to business users. Here are some things to consider when developing with this output in mind.

SSRS – Parameter Sets

When a report has multiple parameters, it can be handy for the user to be able to shortcut their way to making a selection in each of them, particularly when there’s a certain combination of parameter values which they frequently run. This is where the concept of a parameter set comes in.

SSRS Parameters

Most reports benefit from being parameterised to allow the user to decide what filtering to apply to the data, or which fields they’d like to see, or some other behaviour or visualisation option. I’m going to show you a really simple example of grabbing some parameter options from the database, presenting them to the user, and sending the selection to the stored procedure to run the report data.

SSRS Show/Hide Drilldowns

Drilldowns are often asked for to get an initial high-level view of data, then delve deeper into any interesting areas. One way to achieve this is to use the ‘Action’ property of an object to link through to another report, pre-populating parameters if necessary. Another is to set up a matrix with several levels of hierarchy and allow users to show the branches of the tree they are interested in. This is the technique I’ll be showing you in this article.

Basic T-SQL

In this article I’m going to outline the basic T-SQL commands for those new to the language, and as a reference for others.

Lead & Lag Functions

A reporting requirement I’ve seen come up several times is the ability to compare a field value from one record with the value in the same field but a previous record, or else some other requirement to get the preceding or proceeding record.

Data Warehouse – Dimensional Model

This is the first in my series of ‘Basics’ articles, introducing the simple concepts needed for creating a reporting system. In this post I will be outlining the format of the data warehouse.

Exporting Excel Formulas

Sometimes, a customer will request for an SSRS excel export to contain some formulas rather than literal values. Usually this is when they require a ‘workbook’ rather than a normal report. i.e. they can make changes to detailed values and see what the calculated results are, rather than a read-only high-level information summary.

Headers & Footers

Headers & footers are a great place to put report metadata, especially for high-level summaries designed for PDF or other similar document formats.

Using Subreports

Subreports are a powerful feature of SSRS. I use them in all my reports to separate out the different tasks that need to be done.