Hi,
I have a cross tab report which have 2 nested columns Month and Week. I am desplaying 4 months data on the cross tab and it also shows the week numbers along with them.Eg:-
--------------------------
Month | Week | measure1 |
--------------------------
But when I execute the report, it shows only the weeks on the first month and then only the month name for the other 3 months and the mesure1 displays data for the first weeks of those 3 months
Can any body tell me what the issue is ? i want to show the week numbers for the other months as well
Thanks in advance
Hi,
What kind of package are you using - dimensional or relational?
How are the weeks defined - 1 to 52, or 1, 2, 3, 4 within each month, or 200901, 200902 etc or something else?
MF.
Thanks for your reply.
I am using a dimensional package (cube created using transformer)... Weeks are defined as 200901, 200902 ....
OK. How did you bring the months in to the crosstab - by selecting individual month members or by dragging in the Month level? Same question for the weeks.
If you try building a crosstab in Analysis Studio with Month and Week nested in the rows, what result do you get there?
MF.
I have used the month level in the cross tab(same for week level too). I also have a filter on week level. The same method works perfectly with list report, i have the same report in list
If i remove the filter on the corsstab query, it is showing all the weeks.
Quote from: UseCog on 06 Jan 2010 04:48:01 AMI also have a filter on week level... If i remove the filter on the corsstab query, it is showing all the weeks.
Aha. What kind of filter - a detail filter in the query? This is probably not a good idea, as detail filters are really for relational reporting not dimensional.
Look instead at using slicer filters or query calculations using the filter() function.
Can you specify how your filter is currently defined? If so, we may be able to suggest an appropriate dimensional alternative.
MF.
yes... i have a detailed filter on the query. The good part is that the same method is working in many cross tab reports and list reports. I cannot use a slicer as this filter is a dynamically calculated based on the expression. I think it would be a greater amount of rework, if i think about using filter()
Now we are converting one list report to cross tab report, and this problem occurs.
The filters are based on a start and end week ang both of these are calculated expression
So... can you post up the filter expression?
MF.
My detailed filter is
[Week] between [Start Week] and [Last week]
where,
[start week] = firstChild (prevMember(prevMember(prevMember(parent(?prompt week?)))))
[Last week] = lastSibling (?prompt week?)
ie, I am filtering for the weeks in current month and those in the last 3 months, based on the week selected by the user. I was forced to use those expressions as some of the other functions are not giving the exact result
Thanks,
Try the following:
1. Delete the detail filter.
2. Replace the Month level in the row headings with a Query Calculation called Month. Define the following expression:
lastPeriods(4,parent([Your week level]->?prompt week?))
3. Replace the Week level in the row headings with a Query Calculation called Week. Define the following expression:
descendants(lastPeriods(4,parent([Your week level]->?prompt week?)), [Your week level])
The report should prompt for week as before, then display the months and weeks correctly as per your original intent (for the detail filter you were trying to use). In addition, this should work far more efficiently than the detail filter.
Best regards,
MF.
Kudos... This has worked perfectly
Excellent! It's worth considering making the same mod to any other dimensional crosstab reports currently using the detail filter too - if nothing else, it will make them more efficient.
MF.
Thanks for that suggestion.
But how do you use the same method if you want to filter on some dimension, but is not used on the cross tab?
Depends... Maybe a slicer filter, perhaps a tuple function?
Can you show us an example of what you're currently doing? That will help in suggesting a solution.
Thanks,
MF.