Post reply

Binding two separate SSRS parameter values to the same SSAS dataset

  • February 3, 2011 at 12:13 pm

    #244703

    Hi there

    I need to build a report that allows users to choose two months for month on month comparisions (so two columns would appear on the report, for example, January and February). The data source is an SSAS cube.

    I got this working by having one multi-valued parameter on the report, bound to the CalendarMonth attribute in my time dimension. The user ticks two different months in the parameter list and the two columns appear on the report side by side. However, there is nothing stopping the user from selecting a third column which would mess up some other logic I have in my report. So basically i want to allow them to pick only two months (doesn't matter which) to compare.

    I assume I need two parameters (Month1, Month2) for example, and I need to bind both of these to my dataset somehow.

    Any assistance would be greatly appreciated!!

    Thanks

    Doodles

  • February 13, 2011 at 4:16 pm

    #1286211

    Hi Doodles

    Indeed you need 2 report-parameters.. However you need to override automatic MDX creation by SSRS.

    SSRS Subcubes, when you use two parameters having the same dimensionallity you end up with an empty set.

    What should you do:

    copy the settings of the current month parameter into a new parameter 'month2' ..it should use the same dataset as original one

    disable allow multiple values in both ones. (and you should turn off the all-member selection option but that is for another discussion).

    Open de dataset query .. and go into MDX mode (press design mode)..

    Something like this shows up

    SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Geography].[Country].[Country].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DeliveryDateMonthofYear, CONSTRAINED) ) ON COLUMNS FROM [Adventure Works]) WHERE ( IIF( STRTOSET(@DeliveryDateMonthofYear, CONSTRAINED).Count = 1, STRTOSET(@DeliveryDateMonthofYear, CONSTRAINED), [Delivery Date].[Month of Year].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    (Above is based on the AdventureWorks cube).

    And looks for 1 parameter (month)

    Change the MDX into something like this..

    SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Geography].[Country].[Country].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT

    {

    (STRTOMEMBER(@DeliveryDateMonthofYear1, CONSTRAINED)) --parameter1

    , (STRTOMEMBER(@DeliveryDateMonthofYear2, CONSTRAINED)) --parameter2

    } [/i] ON COLUMNS FROM [Adventure Works])

    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    That's all folks

    Regards Kees.

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

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