fredag den 30. september 2011

Parameterized SSRS reports sourced from OLAP cube with a dimension having set a default member

Imagine you develop SSRS 2008 reports based on SSAS olap cubes and face the following situation:
a default member on a dimension, let's say the Date dimension.
The report uses a parameter and it's default member is based on it ( typically the last date where the system has a non empty measure).
User picks up the default member for the date dim. and another date - in order to see the aggregated results - but only the results for the default member are displayed.

The relevant part of the query - I do take into account what the designer generates - looks like :
 SELECT NON EMPTY { [Measures.[...] } ON COLUMNS, NON EMPTY
{... } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM FROM ( SELECT ( STRTOSET(@LocalDateCalendar, CONSTRAINED) ) ON COLUMNS FROM [MISDBCC]))
WHERE ( IIF( STRTOSET(@LocalDateCalendar, CONSTRAINED).Count = 1, STRTOSET(@LocalDateCalendar, CONSTRAINED), [Local Date].[Calendar].currentmember )  ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
( I omitted other parameters , etc. )

The query includes both days but the cube retrieves only the results for the default member.
When I eliminate the default member on the dimension the results are right.
My conclusion was : There is a cube// dim. bug and I worked on the following work-around :

Delete the default member for the dimension, create different roles and create the default member only for one role, as I still need it for some reports.
Then use different connections for the reports data sources depending on the roles (Roles= directive).
And last but not least at the report level I created data sets for default member for the Date parameter involved , based on the previous logic for defining the default member at dimension level. ( Tail(Exists (...) construction .

If you were struggling with this kind of issue before or just like to reproduce and investigate it, than I'd like to get your feedback.

Ingen kommentarer:

Send en kommentar