Can I use a single parameter across multiple/mixed datasets?

Question

Can I use a single parameter across multiple/mixed datasets?

Ed Leddy

SSC Rookie

Points: 26
More actions
August 21, 2013 at 5:07 pm

#278928

Hi all,
I'm creating a test dashboard that uses two data sources - one is a table on SQL Server 2012 and the other is a data cube on that same server. I'd like to be able to have a user enter the SKU once as a parameter in the parameters window and have that input used in both datasets. Is this possible + are there any tricks for this sort of thing?
I had the SKU parameter working for a single dataset (the one pulling from a table) and tried to add a second dataset for the cube and edit the MDX so that it uses the same parameter name from the first dataset, but this is just kicking back a processing error..
Thanks for reading!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Stan Bice Grasshopper Points: 13 More actions · Answer 1

Yes, you can easily do this. Under the Parameters folder, within the Report Designer, create a new parameter (right-click the Parameters folder and select Add Parameter). This parameter will be the common parameter used by each dataset. Create each dataset with a parameter that will be used in the query or stored procedure. In the Dataset Properties dialog box, select Parameters. Change the Parameter Value, of the dataset parameter, and select the common parameter, that you created under the Parameters folder of the Report Designer. If the parameters for each dataset show under the Parameters folder, just delete them. Hope this helps.

Carnegie Grasshopper Points: 21 More actions · Answer 2

Ditto... to what Stan posted. Create a parameter in the "Reports" collection to share among all datasets. Please note that there is also a separate and distinct parameter collection for each dataset in a report.

Cheers,

Carnegie

happycat59 One Orange Chip Points: 29451 More actions · Answer 3

If you are using the same report parameter for SQL datasets and also for Analysis Services datasets, you will need to be aware of the differences in the syntax of the 2 languages used. Whilst you can use the same parameter in any reporting services dataset, but you may need to use an expression to make the value of the parameter syntactically correct for the language

In the simplest form a SQL Select using a parameter could look something like

SELECT ColumnList... from dbo.Sometable Where ColumnName = @ColumnParameter

The equivalent MDX may well need to be an expression that looks something like

='SELECT { [dimname].[levelname].members } on columns,

{Measure.MyMeasure } on rows

from MyCube

WHERE ([dimname2].[dimlevel2].[' + @ColumnParameter + ']) '

A small disclaimer here: the above 2 pieces of code are entirely from memory and are simply intended to demonstrate the differences in the usage of a report parameter within a dataset.

Ed Leddy SSC Rookie Points: 26 More actions · Answer 4

Stan, Carnegie, happycat59 -

Thanks for taking the time to read + reply. This all did the trick - my (first) dashboard is starting to come together! I have a few other challenges ahead but I am going to RTFM before posting again. 🙂

Thanks again for your time,

Ed