Passing Parameters in Reporting Services Reports
While preparing a detailed overview of the functionality of MS Datazen and Pyramid Analytics, we decided to post a small article on solving a local problem for SQL Server Reporting Services.
Recently, when creating reports in SSRS, we encountered the following problem:
you need to create two related reports so that you can go from the main report to the auxiliary report, and from the auxiliary report return to the main one with the transfer of parameters.
Limitations:
The main report is initially loaded with the default parameter values, and all possible values are limited to the set from the query.
For greater clarity, we give a diagram of the transfer of parameters: the main report (Main Report) has the parameter Year ( Year ) with the default value of 2013 and possible values — the history for the previous 2 years of 2011-2013, and the auxiliary report (Detalisation).
')
First, we tried to solve the problem on the forehead: create the parameter Year in the Main Report with the default value CurrentYear (now we will not go into details from which query I get this value and from which query the valid values) and with valid values from the Years set. When switching from Main Report to Detalisation, the action is configured as a transition to a report with the Year parameter. The return from the Detalisation report to the Main Report is also configured as an action with a transition to the report with the Year parameter. But here Reporting does not work - the Year parameter already has a list of valid values, specified by the set.
And it occurred to the following solution of this problem: to create an additional parameter for which the set of acceptable values will not be strictly specified, and to transfer the parameter value to it when returning from the auxiliary report.
The following is a step-by-step description of this solution.
1. For demonstration we will create the following project:
a. DataSource: DataSource;
b. DataSets: CurrentYear, Years;
c. Reports: Main Report, Detalisation.
2. In the Main Report create the parameter Year with the default value from the set CurrentYear
And without valid values
3. Next, create the @YearMain parameter with the default value = Year
And valid values from the Years set
4. In the report we create an element, when clicked, which will go to the auxiliary report. We made a text box. In the properties, we configure the Action (Action) as a transition to the report (Go To Report) Detalisation with passing the parameter @YearDetalisation = @YearMain
Work with queries in the Main Report report is carried out with the @YearMain parameter
5. Now go to the Detalisation report. Create the @YearDetalisation parameter with no default values and no valid values.
6. Create an element for the transition - let it again be a text field. We configure the Action as a transition to the Main Report report with the transfer of the parameter Year = @YearDetalisation
It's time to see what happened. When loading the main report, the current year was loaded by default. In the Year parameter, we see 2013. In a real report, this parameter will be hidden for the user.
In the @YearMain parameter, only an established set of values is provided for selection:
Select 2012 and rebuild the report (View Report or View Report button). As you can see, the value of the Year parameter has not changed, and the value of the @YearMain parameter, with which the work is being done in the report itself, has changed:
Let's go to the auxiliary report:
Now back to the main report:
Hooray, the value of the year is preserved, as it was required.
If this task concerns the transmission of a parameter with several values (multivalue), then it is necessary to do this as follows. In the main report, create a parameter with the ability to have multiple values. When setting up an action, you must specify = Join (Parameters! Years.Value, “,”) to go to the auxiliary report. In the auxiliary report, the corresponding parameter will already be text, with the only possible value. When returning to the main report, when passing a parameter, you must specify = Split (Parameters! Years.Value, “,”).
I hope someone will find this task useful and will help in life.
In the next article we will look at the solutions of Pyramid Analytics and Datazen (we will go through all the stages, from connecting to a cube and other sources to creating a report)
"Pyramid" is considered by Microsoft as a solution for large businesses from the corporate segment and Datazen, a solution for small and medium businesses.
By the way, at the moment we are actively looking for people for very interesting and large BI projects (links to vacancies below):
1. BI Analyst
2. BI Architect
Recently, when creating reports in SSRS, we encountered the following problem:
you need to create two related reports so that you can go from the main report to the auxiliary report, and from the auxiliary report return to the main one with the transfer of parameters.
Limitations:
The main report is initially loaded with the default parameter values, and all possible values are limited to the set from the query.
For greater clarity, we give a diagram of the transfer of parameters: the main report (Main Report) has the parameter Year ( Year ) with the default value of 2013 and possible values — the history for the previous 2 years of 2011-2013, and the auxiliary report (Detalisation).
')
First, we tried to solve the problem on the forehead: create the parameter Year in the Main Report with the default value CurrentYear (now we will not go into details from which query I get this value and from which query the valid values) and with valid values from the Years set. When switching from Main Report to Detalisation, the action is configured as a transition to a report with the Year parameter. The return from the Detalisation report to the Main Report is also configured as an action with a transition to the report with the Year parameter. But here Reporting does not work - the Year parameter already has a list of valid values, specified by the set.
And it occurred to the following solution of this problem: to create an additional parameter for which the set of acceptable values will not be strictly specified, and to transfer the parameter value to it when returning from the auxiliary report.
The following is a step-by-step description of this solution.
1. For demonstration we will create the following project:
a. DataSource: DataSource;
b. DataSets: CurrentYear, Years;
c. Reports: Main Report, Detalisation.
2. In the Main Report create the parameter Year with the default value from the set CurrentYear
And without valid values
3. Next, create the @YearMain parameter with the default value = Year
And valid values from the Years set
4. In the report we create an element, when clicked, which will go to the auxiliary report. We made a text box. In the properties, we configure the Action (Action) as a transition to the report (Go To Report) Detalisation with passing the parameter @YearDetalisation = @YearMain
Work with queries in the Main Report report is carried out with the @YearMain parameter
5. Now go to the Detalisation report. Create the @YearDetalisation parameter with no default values and no valid values.
6. Create an element for the transition - let it again be a text field. We configure the Action as a transition to the Main Report report with the transfer of the parameter Year = @YearDetalisation
It's time to see what happened. When loading the main report, the current year was loaded by default. In the Year parameter, we see 2013. In a real report, this parameter will be hidden for the user.
In the @YearMain parameter, only an established set of values is provided for selection:
Select 2012 and rebuild the report (View Report or View Report button). As you can see, the value of the Year parameter has not changed, and the value of the @YearMain parameter, with which the work is being done in the report itself, has changed:
Let's go to the auxiliary report:
Now back to the main report:
Hooray, the value of the year is preserved, as it was required.
If this task concerns the transmission of a parameter with several values (multivalue), then it is necessary to do this as follows. In the main report, create a parameter with the ability to have multiple values. When setting up an action, you must specify = Join (Parameters! Years.Value, “,”) to go to the auxiliary report. In the auxiliary report, the corresponding parameter will already be text, with the only possible value. When returning to the main report, when passing a parameter, you must specify = Split (Parameters! Years.Value, “,”).
I hope someone will find this task useful and will help in life.
In the next article we will look at the solutions of Pyramid Analytics and Datazen (we will go through all the stages, from connecting to a cube and other sources to creating a report)
"Pyramid" is considered by Microsoft as a solution for large businesses from the corporate segment and Datazen, a solution for small and medium businesses.
By the way, at the moment we are actively looking for people for very interesting and large BI projects (links to vacancies below):
1. BI Analyst
2. BI Architect
Source: https://habr.com/ru/post/260531/
All Articles