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.

søndag den 18. september 2011

Navigate and select from cascading parameters SSRS reports

We all need to share information. That is why we upload strategic reports to Sharepoint to quickly disseminate insightful information across the enterprise.
We implement digital web part pages that display cascading parameters SSRS reports.
But do all users know how to navigate , select in the drop-down list boxes or even print the reports ?
Hope that the following video will help the users:
http://www.youtube.com/watch?v=8wU-kQBER94

torsdag den 8. september 2011

Print a report from a Sharepoint Web application

Some of my end users are interested in printing reports from Sharepoint.
I recomended them to use the following step-by-step approach :

  • Click on Report Library on the breadcrumb navigation
  • Select the report item you want to print from the report library
  • Select Actions -> Export on the status line and choose Acrobat( PDF) or Word or another save format.
  • Choose Open or Save and use the print features that the program you saved the report provides.

The technical users may perhaps have a look at the Microsoft site :
http://technet.microsoft.com/en-us/library/bb326212(SQL.90).aspx