Hey all,
I need to create a report(could be a crosstab or a list...doesn't matter since it's a cube) with the only non-measure column being driven by the day level of 2 Date Dimensions(fiscal and calendar)...both the dimensions have year, month, week and day as levels. Based on the selection of calendar or fiscal dimension, this non-measure column needs to be populated with the appropriate day level from one of the two dimensions. The 30(or 31) days need to show up when a month is selected
So, I created a value prompt with static choices set to day levels of both the date dimensions. Then I added a data item to the query with the usual #prompt('[day]','token'). I dragged the data item as a column and then the measures as additional columns. I also created tree prompts for the 2 date dimesions
now, when the calendar prompt is used and I select say June, the 30 days of June shows up perfectly. However, if the fiscal dimension is chosen, then only those days that have a non-zero measures shows up which is not the requirement. It's almost like a zero suppression when the fiscal prompt is being used to drive the report.
i tried to do a report from scratch, as well as modify an existing one, but it didn't work. The cube also has a selection prompt to choose between two cubes since the data source connection string for the cube has been modfied that way for migration purpose.
Any idea what could be wrong?Beats me why it works for one and won't work for the other.
Solved
The fiscal dimension did not have "always include" at the day level