21 February 2017

SSRS Dynamic Parametrization

With Recourse to Master Data Services

In Business Intelligence projects, it is sometimes necessary to develop substantial amounts of SSRS reports with the same structure, changing the dimensions and/or the measures to use. For these situations, taking into consideration the reports maintenance efforts and performance, it has been developed a solution for this problem.

MDS Model and Entities Development

In order to ensure the dynamism of SSRS queries produced in MDX through report parameterization it is necessary to develop a model and its respective entities. For this example, it has been developed the “Article” model with the following entities:

  •  “Dimension”: it has two attributes (name and code), being composed by multiple dimensions used in reports tables and charts;
  • ”Measure”: it has two attributes (name and code), being composed by multiple measures used in reports tables and charts;
  • “Report”: this entity will ensure the interactivity with the previous two, being composed by the following attributes:
  • Name: of report/dimensions/metrics;
  • Code: automatically generated;
  • ReportOperationalCode: report number (e.g.: “A 1, A 1.1, A 1.1.1,…”);
  • ParentReport: this report is dependent from the “code” attribute, so you can identify which is reports parent-report in question, having multiple functionalities;
  • Filter_Dimension: entity “Dimension” dependent attribute, which allows to select the dimension of certain reports;
  • Filter_Measure: entity “Measure” dependent attribute, which allows to select the measure of certain reports;
  • Filter_Dimension_Rows: very similar attribute to the “Filter_Dimension” attribute, not limited however by the dependence of the “Dimension” entity, as it may have multiple functionalities (e.g.: insert a MDX measure filter on the report; need to have a second dynamic dimension on report).

 

MDS Entities Fill In

For the “Report” entity to be properly fulfilled its necessary that the “Dimension” and “Measure” entities have its attributes also fulfilled. For this example, you have the following needed information for the conclusion of the report:

Developing an SSRS Report

Like any other report its necessary to configure the data source, both the SSAS cube as to MDS.

Dataset and Parameters Development

MDS Dataset Development

Regardless of the report to generate, we know that it is necessary to extract information from the MDS “Report” entity. For such, it makes sense to create a dataset with the following query:

As you can see, its automatically generated the “@ReportID” parameter, that corresponds to the entity id attribute that will fed the parameter, allowing, in turn, the reading of the scale and measuring of a particular report. Later, through the Report Data tab, you can set the parameter being assigned the name “Report”, without having a default value.

Dataset and Parameters Development

With the ultimate purpose of enabling the user to view the report he wants without having the technical knowledge of his dimension and measure, it’s necessary to create a dynamic MDX query with SSRS parameters.

For such, it has been created the “DsTabela” dataset, connected to the SSAS cube and composed by the following query:

As you can see, the following parameters are required:

MétricaRelatório: this parameter obtains information directly from the MDS, being the “Filter_Measure” attribute his source, having as default value the “Filter_Measure_Code” field;

 

 

DimensãoRelatório: this parameter obtains information directly from the MDS, being the “Filter_Dimension” attribute his source, having as default value the “Filter_Dimension_Code” field;

 

 

Data: being relevant for this example having an annual analysis, this parameter is composed by a concrete value.

 

 

Although there is the need to create the parameters, only the “Report” parameter is visible to the user, because when entering the code, it automatically creates a reading of the report dimension and measure through the MDX query.

Regarding the fields of the query, they must be added to the “Fields” tab because the source of the fields is hard-coded. As such, in order to be able to take a reading of the “Fields” tab, all the required dimensions and measures with a code are inserted, such as:

<?xmlversion=”1.0″ encoding=”utf-8″?><Field xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xsi:type=”Level” UniqueName=”[Customer].[Customer Geography].[Country]” />

Report Development

As mentioned above, the structure for the multiple reports is the same, however the dimensions and metrics change. Thus, for this example it has been developed a model that shows results accordingly the selected report. Here are some examples:

As can be seen by many examples, depending on the choice of the report, we see the following:

  • The subtitle changes for the user to see the used dimension and measures;
  • The first Tablix column changes the title and the filed accordingly the chosen report;
  • The graphs change the analysis and the respective subtitle.

This possibility of developing SSRS reports reveals a greater efficiency as well a greater ease in maintenance of those. This is due to the fact of only one query being executed and, in the event of being needed, add new dimensions and measures becomes an easy and fast process, since it’s only necessary to add another dimension and/or measure on the MDS entities, as well as a field in the SSRS dataset.

 .
.
.
.
.
.
.
     Sofia Margalha
         Consultant
Blog