Boyan Penev on Microsoft BI

When constructing a Reporting Services report we have the option of using pictures to illustrate a concept – and Edit button can look like a pencil, traffic lights usually are presented with small icons, a print button can show a small printer. While in PerformancePoint we must use small pictures to show these, in Reporting Services we can also use alternative fonts in the Microsoft fonts library. Standard Microsoft options are the Wingdings and Webdings font families. Using them we can construct horizontal bar graphs, traffic lights and various other icons. The positives with doing that are: seamless exporting to PDF and Excel, easy change of an icon throughout all reports – both of its appearance and colour and easy synchronisation of colours between various report icons.

Examples of report items using Wingdings are:

Horizontal Bar Graph:

Traffic Lights:

Combination of icons, traffic lights and trend arrows:

All of the examples above use Wingdings and Wingdings 2. The definition of the characters, fonts and their colours are stored in control tables, thus they are easily modified without altering the reports.

The first example shows a dynamically generated bar graph where the length of the character string corresponds to the numbers displayed to its left. The largest number fills up the entire table cell, and as it gets smaller, the number of Webdings characters proportionally reduces.

The second and the third reports utilise Wingdings and Wingdings 2 to display colour-blind friendly traffic lights.

The third report fragment illustrates a combination of traffic lights, risk trend arrows and edit characters (pencils icons), all of which use conformed colours, easily synchronised and easily interchangeable. The colour of the upwards trend arrow, in example, is exactly the same as the one used for the red traffic light.

There are a few considerations when using these fonts.

Firstly, only Wingdings and Webdings come with a standard Windows installation, while Wingdings 2 and Wingdings 3 are packaged with Microsoft Office. This is important as the report users need to have all report fonts installed on their computers so that their browser can render them. Also, if we want to allow for printing and exporting the reports to PDF or Excel the fonts must be installed on the Reporting Services server instance as well. Provided we can guarantee this set-up all of the described functionality is seamless for the report users.

SSRS fonts, Reporting Services

A Reporting Services report generally exposes content to end-users but some interactivity can be provided by embedding asp pages to write to the reporting back-end. While for complex user interaction where user input is required (e.g. comments) asp pages is the only way to go, there is another area of simple functionality (in example):

• saving default values for selected parameters
• locking of functionality by a power user
• deleting data from a fact table

or in general – if users do not need to enter any input, but rather trigger some action in the back-end, we can use stored procedures without writing any .NET code.

Firstly, let’s assume that we have already created a report called “Balance Sheet”, which has two parameters – Business_Unit and Period. To keep the case simple, I will assume that the parameter is driven by a stored procedure in a relational back-end.

We first need a place to store the default parameter values – we can simply create a Ctl_User Preference table:

CREATE TABLE Ctl_User_Preferences (
User_Id nvarchar(50),
Default_Business_Unit nvarchar(50)
)

The default parameter value is retrieved from that table by a stored procedure which takes the a User Id as a parameter and returns the Business Unit. Then it is used in the Default Value for the report parameter in Reporting Services.

Now we can investigate the process of populating the table. We place a textbox showing “Save Preferences” text on our Balance Sheet report which links to another report, confirming the user action and writing the selected value of the Business_Unit parameter to the Ctl_User_Preferences table.

We have to create the “confirmation” report first (e.g. “User Preferences Confirmation”). It has to have two parameters – Business_Unit and another, hidden, dummy parameter which we can call Save_Parameter. The Save_Parameter parameter must be able to accept Null values. Then, we create a stored procedure which accepts @User_Name and @Business_Unit parameters and updates or inserts the two values in the Ctl_User_Preferences table. Next we link @Business_Unit to the Business_Unit report parameter and the @User_Name parameter to User!UserID.Value. We link the stored procedure to the hidden parameter by using it for getting its default value (Null as it does not return anything). After completing these steps we end up with a report which “writes” a Business_Unit in the Ctl_User_Preferences table when opened.

Next, we have to link this report to our main Balance Sheet report. I have described how we can link through the use of javascript to a pop-up report in the Advanced javascript in Reporting Services article. Using this technique we can create a textbox in the Balance Sheet report which spawns a pop-up which contains the User Preferences Confirmation report and also passes to it the Business_Unit parameter.

To summarise the events flow, when a user clicks on the textbox a new pop-up window containing the confirmation report opens and while rendering it, Reporting Services calls the stored procedure which saves the Business Unit parameter to the Ctl_User_Parameters table. Next time the user opens the Balance Sheet report, the Business Unit parameter will default to the saved value as its default value is retrieved from the Ctl table.

We can also use this technique to lock/unlock certain functionality, such as changing the reporting period, or even deleting data in our fact tables by placing links in a table or a matrix next to measures and passing their coordinates to the “confirmation” report.

This out of the box functionality in Reporting services can be quite powerful for all sorts of quick tweaks to our reports with comparatively small development effort, thus being quite worthwhile implementing.

SSRS interactivity, Reporting Services

Often our requirements demand from us to use javascript in reporting services to navigate between reports. There are various reasons for using javascript, among others:

SSRS javascript, Reporting Services