SSAS with SSRS drilldown

1
votes

I have a SSRS report that displays totals per weekending for last 10 weeks per clinician. The report has week ending as column group and clinician as row groups. But my second report which is the details drilldown report on week ending should display all the days in that week and display data. 


Clinician   Week1   Week2   Week3
--------------------------------------
Clinician1   10      25      35
Clinician2   23      40      112
.....
.....
.....

in the above sample each one of the totals under week1, week2 or week3 is a drilldown to details report. This drill down works but when i need to show all the weekdays even if there is no data for that clinician. Right now i can only able to show those days in a particular week that has data. Please any help will be greatly appreciated.

thanks

EDIT:

SELECT { [Measures].[Count of ID] } ON COLUMNS, { ([Data].[ClinicianName].[ClinicianName].ALLMEMBERS * [Data].[FacilityName].[FacilityName].ALLMEMBERS * [Data].[FacilityID].[FacilityID].ALLMEMBERS * [Data].[WeekEnd].[WeekEnd].ALLMEMBERS * [Data].[FullWeekName].[FullWeekName].ALLMEMBERS * [Data].[DateNumber].[DateNumber].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DataFacilityID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DataClinicianID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DataWeekEnd, CONSTRAINED) ) ON COLUMNS FROM [Model]))) WHERE ( IIF( STRTOSET(@DataClinicianID, CONSTRAINED).Count = 1, STRTOSET(@DataClinicianID, CONSTRAINED), [Data].[ClinicianID].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
2
reporting-services

2 Answers

0
votes

If you are using the MDX query builder in SSRS, there's a button to "show empty cells"... enter image description here

...or if you are manually constructing the MDX, just remove the NonEmpty function...

0
votes

i actually end up using linked server with stored proc and it works like a charm and the performance is good because i basically filter the data from cube.