Passing parameters from one report to another report via the URL can be a bit tricky, esepecially when it comes to multi value parameters. Below you will find an example of how to do this for single and multi value parameters.
For example, your report server is at http://myserver/reportserver and the report you want to pass the parameter(s) to is in \MyFolder\MyReport and the parameter name is myparam1.
If it is a single value parameter you will generate the URL as
=http://myserver/reportserver/Pages/ReportViewer.aspx?%2fMyFolder%2fMyReport&rs:Command=Render&myparam1=" & parameters!myparam.value
This is quite obvious and not too tricky but how would you pass multi value parameters? Some people have tried to do it like this:
=http://myserver/reportserver/Pages/ReportViewer.aspx?%2fMyFolder%2fMyReport&rs:Command=Render&myparam1=" & Join(parameters!myparam.value,",")
Unfortunately this won't work, the correct method is:
=http://myserver/reportserver/Pages/ReportViewer.aspx?%2fMyFolder%2fMyReport&rs:Command=Render&myparam1=" & Join(parameters!myparam.value,"&myparam=,")
In other words, for every value of the parameter you have to pass &myparam=value to the URL.
For more info on URL access click http://msdn.microsoft.com/en-us/library/ms152835.aspx
Welcome to my Business Intelligence blog. I will keep this blog mainly to remind myself of pitfalls and gotcha's I have run into while working in the BI industry but everyone is welcome to follow it of course.
Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts
Monday, 11 January 2010
Monday, 4 January 2010
SSRS - conditional formatting for lazy people
Let's say you create a report and you want to apply conditional formatting, in this case just a conditional colour of the font, there's a couple of ways to do this. Let's consider the below (mini) report.
We'll have to:
The alternative is to use the value of the textbox instead of the value of the dataset field to determine if the color of the text needs to be red or black.
We'll have to:
- Select the textbox containing the value =Fields!Revenue_KPI.Value
- Edit Color property to say =IIF(Fields!Revenue_KPI.Value<0,"Red","Black")
- Select the textbox containing the value =Fields!Volume_KPI.Value
- Edit Color property to say =IIF(=Fields!Volume_KPI.Value<0,"Red","Black")
The alternative is to use the value of the textbox instead of the value of the dataset field to determine if the color of the text needs to be red or black.
- Select all the textboxes you want to format
- Edit Color property to say =IIF(Me.Value<0,"Red","Black")
SSRS - adding parameters to a dataset with an Oracle data source
When you are creating a SQL Server Reporting Services (SSRS) report and you want to add a dataset that connects to an Oracle dataset using OLE DB and you want to add parameters to the PL/SQL query, you might make the mistake of using the same syntax as in T-SQL which is @param. The PL/SQL version is :param.
For example, in T-SQL we would write:
SELECT id, name
FROM
myTable
WHERE
cond = @param
while in PL/SQL we would write:
SELECT id, name
For example, in T-SQL we would write:
SELECT id, name
FROM
myTable
WHERE
cond = @param
while in PL/SQL we would write:
SELECT id, name
FROM
myTable
WHERE
cond = :param
Also, don't forget to pass the report parameter variable value to the dataset parameter on the dataset properties tab aptly called "Parameters". See below for the screen shot in 2008.
Also, don't forget to pass the report parameter variable value to the dataset parameter on the dataset properties tab aptly called "Parameters". See below for the screen shot in 2008.
Subscribe to:
Posts (Atom)

