In a previous article I showed you how to create slicers in Reporting Services (SSRS), but the report still used the classic SSRS parameters pane to allow you to select the year and month for which you want to display data. In this article I will show you how you can avoid using the parameter pane altogether, and embed parameter selection inside reports.

This technique is particularly useful when developing reports for mobile BI on tablets and smartphones using SSRS 2012 SP1 and above. In this article I am going to presume that you already have a reasonable knowledge of basic SSRS and that you can build reports already, but that you may not yet have ventured into enhancing the SSRS interface. If you need further information then please consult my book, Business Intelligence with SQL Server Reporting Services – Apress – February 2015.

The source files required to follow this article are available in the attached .zip files (see the Resources section at the bottom). The data is in SQL Server 2012 format. Consequently you will need a version of SQL Server 2012 to restore it..

The Scenario

Suppose that you have a report that displays a chart of sales to date. Users have expressed a clear opinion that the Reporting Services user interface is too boring and clunky, and they are tired of clicking on the "View Report" button - or even forgetting to click it. They complain that changing the choice of month in Excel, or even (horror of horrors) a rival reporting tool, updates a chart or table without needing any further effort. Consequently they want to have this level of interactivity in Reporting Services reports.

No problem! In this example we will replace the year and month parameters with an interactive and visual selection of the year and date. A single click (or tap if you are using a touch-sensitive screen) will update the report and refresh all the data.

To see where we are going, an example of a year and month selector is given in the image below. In the case of this report the contents are deliberately simple, so that we can concentrate on the interactive date selector. This report will, in fact, display no data. This is to enable it to become a reusable “widget” that you can embed in other reports.. However, as you can see, the choice of year and month is visible in the report. What is more, if you look at the sample file (DateSelector.rdl) the parameters are not visible at the top of the report as they would be in a standard SSRS report.

The Source Data

This report actually requires seven datasets. The datasets are:

• DateWidgetYear - This is a shared dataset that returns the last three years from the source sales data. This is based on the stored procedure Code.pr_WD_DateWidget.
• DateWidgetColorScheme - This is also a shared dataset. Using the stored procedure Code.pr_WD_DateWidgetColorScheme it sets the colors used in the custom date selector. Setting the colors like this lets you redefine the colors from a single piece of code – ensuring that a suite of reports using this widget can be updated in a few seconds.
• A dummy dataset using the dataset Code.pr_WD_DateWidgetDummy - this shared dataset is needed for the tables that contain the year and month elements in the date selector to prevent SSRS returning an error.
• ReportingYear - This shared dataset returns all the years for sales in the database.
• ReportingMonth - This shared dataset returns all the months for sales in the database.
• CurrentMonth – This shared dataset returns the current month.
• CurrentYear - This shared dataset returns the current year.

The reason that all these datasets are shared is simple. We will probably want to reuse our year and month selector in several reports. So it is more logical to design this widget as a reusable element, and that also means ensuring that the data it is based on can be reapplied easily. This means using shared datasets that we can develop once, then re-apply in a suite of reports.

The datasets that are used are shown in the attached code block:

-- Code.pr_ReportingYear
-- This stored procedure returns all the years for sales in the database
CREATE procedure Code.pr_ReportingYear
AS
SELECTReportingYear
FROMData.CarSalesData
GROUP BYReportingYear
ORDER BYReportingYear
-- Code.pr_ReportingMonth
-- This stored procedure returns all the months for sales in the database
CREATE procedure Code.pr_ReportingMonth
AS
SELECTReportingMonth
FROMData.CarSalesData
GROUP BYReportingMonth
ORDER BYReportingMonth

-- Code.pr_CurrentMonth
-- This stored procedure returns the current month
CREATE procedure Code.pr_CurrentMonth
AS
SELECT MONTH(GETDATE()) AS CurrentMonth

-- Code.pr_CurrentYear
-- This stored procedure returns the current year
CREATE procedure Code.pr_CurrentYear
AS

SELECT YEAR(GETDATE()) AS CurrentYear

-- This stored procedure returns the last three years where there are sales in the database
-- Code.pr_WD_DateWidget
CREATE PROCEDURE Code.pr_WD_DateWidget
AS
WITH YEAR_CTE
AS
(
SELECT TOP (3)
YEAR(InvoiceDate) AS theYear, ROW_NUMBER() OVER (ORDER BY YEAR(InvoiceDate)) AS ID
FROM Data.CarSalesData
GROUP BY YEAR(InvoiceDate)
ORDER BY YEAR(InvoiceDate)
)
SELECT
(SELECT theYear FROM YEAR_CTE WHERE ID = 1) AS Year1
,(SELECT theYear FROM YEAR_CTE WHERE ID = 2) AS Year2
,(SELECT theYear FROM YEAR_CTE WHERE ID = 3) AS Year3

-- Code.pr_WD_DateWidgetColorScheme
CREATE  PROCEDURE Code.pr_WD_DateWidgetColorScheme
AS
SELECT
 'DimGray' AS BckGrdSelected1
,'WhiteSmoke' AS BckGrdNonSelected1
,'Black' AS BckGrdSelected2
,'LightGrey' AS BckGrdNonSelected2
,'White' AS TextSelected1
,'Black' AS TextNonSelected1
-- the dummy dataset
CREATE  PROCEDURE Code.pr_WD_DateWidgetDummy
AS
SELECT ‘’

Building the Report

Now that we have prepared our data it is time to create the report itself. Here is how:

1. Create a new SSRS report named Tablet_YearAndMonthSelector.rdl.
2. Add a Data Source that points to the CarSales_Reports_SSC database that you have restored from the backup in the zip file that accompanies this article. Name it CarSales_Reports.
3. Create the following seven shared datasets:
   • CurrentMonth using the stored procedure - Code.pr_CurrentMonth.
   • CurrentYear using the stored procedure - Code.pr_CurrentYear.
   • ReportingYear using the stored procedure - Code.pr_ReportingYear.
   • ReportingMonth using the stored procedure - Code.pr_ReportingMonth.
   • DateWidgetYear (using the dataset DateWidgetYear.rsd).
   • ColorScheme (using the dataset DateWidgetColorScheme.rsd).
   • Dummy (using the dataset Dummy).
4. Add these seven shared datasets to the report, using the shared dataset name as the report dataset name.
5. Add a table to the report and delete its header row. Ensure that it has three columns, and configure it to use the dataset DateWidgetYear. This will be the year selector table
6. Set all the text box borders to LightGray, 1 point.
7. Set each of the text boxes to 1 inch wide, and apply the following fields:
   • Leftmost column: Year1
   • Center column: Year2
   • Rightmost column: Year3
8. Click on the leftmost text box and set the BackgroundColor property to the following expression: =IIF(Parameters!ReportingYear.Value = Fields!Year1.Value,First(Fields!BckGrdSelected1.Value, "ColorScheme"),First(Fields!BckGrdNonSelected1.Value, "ColorScheme"))
9. Set the Color property for this text box to the following expression: =IIF(Parameters!ReportingYear.Value = Fields!Year1.Value,First(Fields!TextSelected1.Value, "ColorScheme"),First(Fields!TextNonSelected1.Value, "ColorScheme"))
10. Carry out steps 8 and 9 for the other two text boxes in this table, only replace Year1 in the expression with Year2 for the center text box and with Year3 for the rightmost text box.
11. Add a table to the report and delete its header row. Ensure that it has 12 columns, and configure it to use the dummy dataset. This will be the month selector table.
12. Set all the text box borders to LightGray, 1 point.
13. Enter the texts Jan through Dec in the twelve text boxes to display the twelve months of the year.
14. Click on the leftmost text box and set the BackgroundColor property to the following expression: =IIF(Parameters!ReportingMonth.Value = 1,First(Fields!BckGrdSelected2.Value, "ColorScheme"),First(Fields!BckGrdNonSelected2.Value, "ColorScheme"))
15. Set the Color property for this text box to the following expression: =IIF(Parameters!ReportingMonth.Value = 1,First(Fields!TextSelected1.Value, "ColorScheme"),First(Fields!TextNonSelected1.Value, "ColorScheme"))
16. Do the same for the other eleven text boxes, only replace Value = 1 in the expression with Value = 2 (for February), Value = 3 (for March) etc.
17. Right-click on the Year1 text box in the upper table, and select Text Box Properties. Click on Action on the left.
18. Select Go to report as the enabled action.
19. In the Specify a report popup do not select the name of the report, but enter =Globals!ReportName as the report to jump to. The dialog will display [&ReportName].
20. Click twice on the Add button and define the two following parameters as expressions (so click on the Fx button to display the Expression dialog):
    • Name: ReportingYear - Value: =Fields!Year1.Value. The dialog will show [Year1] after you click OK in the Expression dialog.
    • Name: ReportingMonth - Value: =Parameters!ReportingYear.Value. The dialog will display [@ReportingMonth] after you click OK in the Expression dialog
21. Set actions for the two other text boxes in the table containing the years. They must be identical to those that you set for Year 1 with the only differences being that you will set the ReportingYear value to be Year2 for the center text box and Year3 for the rightmost text box.
22. Set actions for all the twelve text boxes in the month selector table. This is done as for the years, except that the ReportingMonth values are 1 (for January), 2 (for February) etc. These are not expressions, and are hard-coded.
23. The ReportingYear parameter is set - for the textboxes for all the 12 months - to the expression =Parameters!ReportingYear.Value which will show as [@ReportingYear] in the Text Box Properties dialog. The widget should look like the following figure:

The Report Data pane should look something like this:

You can now view the report and clicking on any of the years will set the ReportingYear parameter to the contents of the selected text box. Equally, clicking on any month will set the ReportingMonth parameter to the value that you clicked (or tapped). The report will then be refreshed, and any other objects in the report that use these parameters in their queries or filters will be updated to display the relevant information. Specifically the stored procedure pr_wd_dateWidgetColorScheme will modify the colors in the year and month tables to reflect the selected elements.

How it Works

What happens here is that we have one table that displays the last three years, and another table that displays a hard-coded list of months. If you click - or tap - on a year or a month in either of the tables, the text box's action property is triggered. This property sets the year parameter (if you click on a text box in the year table) or the month parameter (if you click on a text box in the month table) - and then refreshes the report, by jumping to itself as the destination report.

This is, in effect, a kind of "postback" for the report, which ends up refreshing itself, only with a changed parameter.

Hints and Tips

In step 20 you can, alternatively, click the Fx (function) button and then expand Built-in Fields and select ReportName, instead of entering [&ReportName] or =Globals!ReportName.

When it comes to selecting the parameter values (as in step 21) you have three options:

1. Type in the value as the dialog will display it. So if you know the parameter name, you can type in [@ReportingYear], for example.
2. Type in the expression as it would appear in the Expressions dialog - =Parameters!ReportingYear.Value, for instance.
3. Click on the function button (Fx) and select a parameter where one exists. So, for instance, instead of typing [@ReportingYear], you can display the Expression dialog, expand Parameters as the Category and then select a parameter, such as ReportingYear (in this example). The dialog will display =Parameters!ReportingYear.Value, but once you click OK only [@ReportingYear]will be displayed in the Text Box Properties dialog.

In an attempt to make the report a little more dynamic I set the years to display only the last three years for which there are data. You can simplify the process and hard-code the years if you prefer. However the extra effort to have greater automation seems trivial, so I preferred to show how it can be done

Some interface elements are bound to be reused over and over again. Date parameter selection is a case in point. If you find that you are reusing an interface element such as this, then it is probably worth spending some time to perfect it, and then save it as a reusable "widget" in a separate report. You can then copy and paste it into any report which requires it. You should also probably define any datasets a widget used as shared data sets, which you can then add to new reports in a couple of clicks

This report can now become the basis for other reports as it contains not only the widget itself, but all the required datasets for displaying and selecting the year and month for your reports using the CarSales_Reports database

Adam is the author of Business Intelligence with SQL Server Reporting Services – Apress, February 2015, which you can purchase at Amazon