Dynamic Column RDLs

When creating reports in RDL there may be cases where you want to represent data in a table, but table column are chosen by the user at the runtime. Assume you have no clue about how many columns will the user choose for the table and what kind of data will it represent.

Normal tables in reports require statically laid columns with statically defined expressions to populate data in them. Matrix comes handy in situations like these. I have explained how to use a matrix to create a table with dynamically populated columns using Business Intelligent Studio that comes with SQL Server Reporting Service (SSRS).

To use this feature database query should result in the following format.

Employee ID (emp_id)	Column Name (col_name)	Column Value (col_value)
1	Name	John
1	Age	25
1	Designation	Software Engineer
2	Name	Smith
2	Age	31
2	Designation	UI Designer

1. Add a ‘Matrix’ from the toolbox to the canvas
2. Set the necessary basic elements in the report. Such as Data Source, Data Set, Parameters, etc.
3. In the newly added matrix insert a ‘Row Group’, and a ‘Column Group’ as follows,

• Row Group: rows should be group by employee id. In this case ‘col_name’. Hence no 2 employees can have same employee id. If it happens to be, then the initial employee records get overwrite by the second employee who has the same id value.
• Column Group: columns should be group by column name. In this case ‘col_name’. Hence no 2 columns can have the same name.
• Cell value should be the data value that satisfies both Column and Row Group conditions. In this case ‘col_value’.

Employee ID <- This is a text box	col_name <- Column Name field
emp_id<- Employee ID field	col_value <- Column Value field

Now when this RDL is deployed and viewed, it will output a table as follows,

Employee ID	Name	Age	Designation
1	John	25	Software Engineer
2	Smith	31	UI Designer

Hence no knowledge is required on number of columns or their content when creating this kind of tables.

SSRS Reports Generation

Creating a New RDL

SQL Server Reporting Service (SSRS) require its reports to be generated in Report Definition Language (RDL). Package also contains a Visual Studio Environment to Develop RDLs, named Business Intelligent Studio.

Shown below is a report in Business Intelligent Studio.

Visual area of the report is the canvas. All items on it will be shown in the final published report when viewed. tabular, matrix, charts, labels, images can be drag and dropped to the canvas from the Toolbox (shown vertically on the very left edge in the above image). once the report layout is created you can bind data to it. For any functional report there are few main parts to configure. They are,

•  Data Source  - which defines the connection string and the credentials to the database.
• Data Set – which defines the query to execute on the ‘Data Source’ to fetch  data. ‘Fields’ are the returned data attributes for the Data Set.
• Parameters – these are user inputs that may required by the Data Set queries for the report.
• Chart, tabular, matrix, label items to be shown in the report.

Adding a New Report Item

1. Create a new Report Project.
2. In the solution Explorer do the following changes.
3. Add a shared data source to access the database.
4. Right click on ‘Reports’ folder and add a new report Item.
5. If ‘Report Data’ View is not visible in the workbench, go to ‘View’ menu and select ‘Report Data’.

Setting up Report Data Source and the Data Sets

1. In the ‘Report Data’ select ‘New -> Data Source’ and point the new data source to the existing shared data source ‘MATRIX24X7DS’.
2. In the same manner in ‘Report Data’ select ‘New -> Data Set’. In creation the ‘Data Set’ you have to specify the database stored procedure (sp) or the direct query relevant to this dataset. 

Parameters for a Report

Parameters are the only method of getting user input for the report. When adding data set queries the user inputs required for the query to execute are automatically get added under ‘Parameters’ section. we can add new parameters, modify existing ones or even remove them as required.

1. To add a new Parameter, in the ‘Report Data’ select ‘New –> Parameter’ and follow the wizard.
2. To modify right click on an existing parameter and choose ‘Properties’
3. To delete parameter right click and then select ‘Delete’

There are varies methods of taking user inputs using parameters.

1. If required a date as a input in the wizard select type to be ‘Date’
2. If you want the user to select from know list of values in the wizard add label-value pairs in the ‘Available Values’ section that will display a dropdown list in the runtime.
3. If you want to set a default value to a parameter set it in the ‘Default Value’ section of the wizard.
4. If you don’t want the user to see a parameter mark it as ‘Hidden’ in the wizard.
5. If the data to available values should be obtain from the database select ‘By Query’ in the ‘Available Values’ section.
6. If you want the user to provide no input select ‘Allow null’ option.
7. If you want user to select more than one option from the all available values, select ‘Allow multiple inputs’.
8. If you want parameters to show available values base on multiple values selected from a previous parameter by the user, set the dependent parameter ‘Available Values’ to previous parameter. set Label to ‘<prev_param>.label’ and Value to ‘<prev_param>.value’

Adding Charts

 

Column Chart

1. Add a column chart from the toolbox, select chart type column and hit ‘Ok’.
2. In chart properties set ‘DataSetName’
3. Click inside the chart again. Something similar to above image should appear.
4. Moving mouse over top right corner of all the 3 drop fields will make the selection button appear in blue as for the ‘Drop category field’ in the above image.
5. In ‘Drop category fields’ click on the selection button and select the data field required for the x-Axis of the chart.
6. In ‘Drop data fields’ click on the selection button and select the field that was selected for the category field. It will be shown as ‘Count(category_field)’.
7. Double click on the ‘Chart Title’ and two ‘Axis Titles’, and give the relevant names for them.
8. Drag and resize the chart as required.
9. Go to the ‘Solution Explorer’ right click the report and select ‘Build’.

Bar Chart

Creating a bar chart is identical to column chart except the fact that chart type should be ‘Bar’ and hence will not be discuss again.

 

Pie Chart

Creating a pie chart is identical to column chart except the fact that chart type should be ‘Pie’ and hence will not be discuss again.

 

Line Chart

Creating a line chart is identical to column chart except the fact that chart type should be ‘Line’ and hence will not be discuss again.

 

Table

1. Add a ‘Table’ instead of a chart to the canvas.
2. Select the table and go to the property window of the table. Set the properties ‘DataSetName’.
3. Type the column names in the header row.
4. Select data set fields by clicking the top right corner blue icon and then the required field name in all the columns in the detail row.
5. Leave the footer row empty or remove. It’s again a static row like the header row.
6. Set borders, font style and cell background color by right clicking and selecting ‘Properties’ for cells.

 

Matrix Table

Matrix can be used populate dynamic columns when the creator has no prior knowledge on number of columns. To use this feature database query should result in the following format.

Agent ID	Column Name	Column Value
A	Col_1	Val_1
A	Col_2	Val_2
A	Col_3	Val_3
B	Col_1	Val_4
B	Col_2	Val_5
B	Col_3	Val_6

1. Add a ‘Matrix’ from the toolbox to the canvas
2. Set Matrix’s ‘DataSetName’ in the property plane.
3. Insert a ‘Row Group’, and a ‘Column Group’ as followed

Agent ID <- This is a text box	Col <- Column Name field
Id <- Agent ID field	Val <- Column Value field

Now when this RDL is deployed and viewed, it will output a table as follows,

Agent ID	Col_1	Col_2	Col_3
A	Val_1	Val_2	Val_3
B	Val_4	Val_5	Val_6

 

Multiple Series Charts

1. Add any chart from the toolbox In the above case it’s a line chart.
2. Set chart’s ‘DataSetName’ property.
3. Select a x-axis field from ‘Drop category fields’ top right corner selection button.
4. Select a group by field from ‘Drop series fields’ same as above.
5. Select a data field from ‘Drop data fields’ same as above.
6. Set x-axis title and y-axis title.

Microsoft SQL Server Reporting Service (SSRS)

Overview

SQL Server Reporting Service (SSRS) is a server based reporting platform introduced by Microsoft. It’s reliable, comprehensive reporting platform distributed with Microsoft SQL Server since the introduction of ‘SQL Server 2000’ in year 2004.

Same as its competitors such as ‘Crystal Reports’, ‘IBM Cognos’, etc. SSRS also provides attractive features in the areas of data representation, report management and report viewing. SSRS is fully compatible with SQL Server, SharePoint  and uses Visual Studio environment. Yet it can be used with many other commercial and open source DBMSs as mentioned in the features section below.

There are 3 main stages in SSRS report life cycle. They are,

1. Report Creation – Report is defined using the ‘Report Definition Language’ (RDL) which is a XML based markup language. ‘Business Intelligent Studio’ which uses Visual Studio environment can be used to generate reports.

2. Report Deployment – Once the reports are developed they are deployed in a central location in the Reporting Server.

3. Report Viewing – Once successfully deployed, reports can be viewed through any web connection, expose them through a web service, or publish them directly to a SharePoint site. These published reports can be exported to many formats like PDF, Excel, TIFF, XML or print or even subscribe to by uses. Subscriptions are delivered as Emails or Publish to FTP servers.

More information regarding SSRS architecture can be found at SSRS home page in MSDN, http://msdn.microsoft.com/en-us/library/ms159106.aspx.

SSRS can be used to produce reports in a business enterprise or can be customized and integrated to custom applications which has a SQL Server based back-end. For whatever the requirement it’s always advantageous to analyze pros and cons of using SSRS to meet your reporting requirement.

Features of SSRS

• It’s fully compatible with SQL Server Database, SharePoint servers and Visual Studio development environment.
• Supports relational, multidimensional, XML or custom data sources such as Microsoft SQL Server, OLE DB, Microsoft SQL Analysis Service, Oracle, ODBC, XML, Report Server Model, SAP NetWeaver BI, Hyperion Essbase, TERADATA.
• Data can be represented in tabular, matrix, charts, images, labels and many other forms.
• DB logics can be loosely coupled from the business logics that are used to compose the report by calling the Database stored procedures from the RDL.
• Generated report can be viewed using web and exported to many formats including PDF, Excel, Word, TIFF, HTML, MHTML, CVS, XML, etc.
• Users can subscribe to reports by configure the SSRS to deliver them in a scheduled time or in intervals as Emails or as FTP file shares.
• SSRS expose many of its functionalities such as getting report parameters, getting the report output, subscribing to a report, deleting a report, building a report and many more by a Web Service to the outside that can be used to integrate the SSRS features to your custom applications.
• SSRS provides drill down and parameterized reports.
• Business Intelligent Studio provides drag and drop features for easy report generations.
• Anyone with a fair knowledge can develop a custom rendering extension to process the RDL and generate reports.
• Exported report XMLs can be easily transformed in to any desirable format with the use of a simple XSLT file.
• SSRS is continuously supported and enhanced by Microsoft since year 2004. Hence has a fairly good documentation support with MSDN.

Weaknesses of SSRS

• It doesn’t expose all its functions such as drill down facility through its web service. List of all exposes methods can be found at http://msdn.microsoft.com/en-us/library/ms155071.aspx
• If you are generating and showing reports through SSRS other than directly retrieving raw data from the database it may include additional delays.
• From SSRS 2005 to 2008 there is slight difference of XML output, and the subscription xml generated by the reporting server that looses direct backward compatibility. Since they are not huge changes, those can be incorporated while development. This will matter only if you use SSRS as the backend of your custom application.