Have you ever noticed that a list report and a chart report behave differently when it comes to querying. I found that out fairly recently. Let me give you an example of how differently the two work. Assume you are to display for each selected Month the Top Products by Sales along with the monthly % contribution towards the sales. Ideally you would drag Product, Month, Sales, Rank(Sales for Month), Total(Sales for Month) - This is the total sales for the top products, % Contribution being - Sales / Total (Sales for Month) - basically how much did this Product contribution towards the total sales. I have a rank filter - rank < = 5 with after auto aggregation property set.
Now when I drag Product, Month, and % Contribution into my Chart report and List report, I see that the values are different. The list report displays the expected value while the chart report doesn't. On closer look I notice that the chart report doesn't apply the rank prior to the calculation of Total (Sales for Month). This means that my chart is calculating the total sales across all products for the month rather than Total sales for the Top 5 products.
Now that is weird when the List and chart behave differently. To work around this, I had to create a subquery which gets me the Product, Month, Sales and Rank with filter Rank < = 5. On top of this query I have another query that does the % contribution calculation. Now the list and chart work perfectly.
In retrospection I think the chart was working as expected, the rank filter was getting applied after aggregation. It was the list that wasn't working fine even though it returned the data I required.
The point though that I would like to make here is that not always lists and charts work the same way or fire the same query.
Reader Tips:
shiv shankar
Using solve order would have solved the issue.
Posts
4 comments:
did you try using solve order. I guess that would have solved it for you
That's a nice tip. Didn't know Solve Order worked with Charts..Thanks shiv shankar
Will solve order work in DMR model(cube published in FM)??
i investigated that in crosstab the calculations happen inorder:
outer columns,inner columns outer rows and then inner rows.
i face an issue with the performance of Reportnet while rendering a CROSSTAB, i'm using DMR model where as cube is created and imported into Framework manager.
My crosstab report (which contains YTD(13 months rollin) as columns and measures across rows is taking 25mins to retrun the output, but when i swapped rows and columns its was giving me the output in a min, i knew that less no. of rows in a crosstab would run the report fast, so i have divided the crosstad into several crosstabs, but since i need dynamic prompting for 13months rolling having prompts from report page i had an issue going for tabular reference and changing the Query item property Aggregation to None.
The tabular Query is running fast in a minute but the crosstab is taking 25mins, and when swapped its too fast,
could anyone give me the reason why is producing a crosstab so slow? i applied solve order but still its slow...
and how swapping rows and columns increasing the performance, is it because of the reason i mentioned above ie; order os calculations is outer most column,inner most and then rows?
Question here: How are you framing the dynamic 13-months rolling filter? If this is a detail filter then that could be one cause for your issue as its a complete no-no to use detail filters against dimensional models.
What has me stumped in your case is the fact that your crosstab is fast when you swap rows and columns.
Lists and Cross-tabs work different in that Cognos creates virtual cubes before it displays cross tabs.
Did you try looking up the MDX query Cognos generates when you swap rows and columns and try comparing that with the one generated when you don't?
Let's see if anyone else has any other suggestions to help.
Post a Comment