Posts Tagged ‘parameter’

SSRS #52 – Setting Dynamic Default Parameters in MDX Dataset

May 30, 2011 Leave a comment

In my previous blog post, SSAS #21 – Steps to create a SSRS report with MDX accessing a Cube, I blogged the out-of-box features in SSRS to create a report with parameters accessing an Analysis Services database.

5 report parameters were automatically created.

When I preview the report, the Year Number shows the following available values, which include 0 (zero).



There are two things I’d like to change for the Year Number parameter.

  1. Remove the value 0 (zero) from the available list.
  2. Set a dynamic default value to the current year. 
    Here is what I’d like to achieve;



Step 1 – Show Hidden Datasets

In my BIDS, the default is to hide all the MDX datasets that are automatically created. To show all the MDS datasets, right-click on the Analysis Services data source, and select Show Hidden Datasets (make sure the check mark is showing).

Now, all 5 MDX datasets are showing under the Analysis Services data source.


Step 2 – Add a Filter function for Year Number.

In order to remove the 0 from the list, I will need to use the Filter function.

To save some typing, I copied the automatically created MDX query to SSMS, and modified it with a Filter function.


This is the final MDX query with a Filter function.


In the Query Designer, I pasted my new MDX query, and tested it.


Step 3 – Set a dynamic default value to parameter Year Number

I’ll achieve this with Reporting Services expressions. However, with MDX datasets, I’ll need to use a combination of regular RS expressions and MDX expressions.

First, get to the Report Parameter Properties window.


On the Report Parameter Properties window, go to the Default Values tab. Check “Specify values”. Then click Add, and the function button image .


In the Expression editor, type this:

="[DimDate].[Year Number].&["+CSTR(Year(Today))+"]"


You will need to replace [DimDate] with your own date/time dimension. [Year Number] is the one of the attributes in [DimDate]. You will need to replace it with your own too.

However, the Today function, the Year function, and the CSTR function are all regular RS functions.

You are done. Now preview your report, you will see that value 0 is removed from the available list, and 2011 is provided as the default value for parameter Year Number.

SSIS – Pass a list of values to a data flow source

March 13, 2011 1 comment

I wrote a blog post SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow a few days ago.

But when do we really need the Foreach Loop Container? I find that I do not use it very often. The reason is that I can only pass one at a time the value of the variable to the data source of the data flow inside the Foreach Loop Container. I don’t have a benchmark number of how much overhead that adds to my run time, but if I have 100 values to pass, I start to feel uneasy.


I have a list of status IDs I maintain on the SQL server side. Status IDs is one of the dimensions for my fact table. I want to limit the number of records I bring down from our DB2 data warehouse using the list of the Status IDs.

1) Can I maintain the status ID dimension table on DB2? Yes, I can. But I prefer not to do that because by default all our development is supposed to be on SQL server.

2) Can I use MERGE in the data flow, merging my source from DB2 with this Status ID table on SQL Server? Not really. My query on the DB2 side uses several temp tables due to the complexity the query. Without physically staging those temp data, I cannot really use MERGE.

3) Can I create a package variable to hold a dynamic SQL query with a parameter? No. The Status IDs I need to use is not just one value, but a list of many values.

4) How about hard coding those Status IDs in my DB2 query. No. Thanks. No hard coding in this case.

Here is the solution I came up with, and so far it is working perfectly for me.

Step 1: create a SQL table to hold those Status IDs on the SQL Server side

Step 2: in SSIS package, create an Execute SQL Task to read in the Status ID values, and concatenate all the rows into a comma-delimited string, and store the string as a single value into a user defined variable

Step 3: create a user defined function on DB2 to convert the list of the Status IDs into rows. (see my next blog post on how I did that)

Step 4: create a data flow with a parameterized data source, and pass the user defined variable into the data source as the parameter.

More to come on this…

%d bloggers like this: