I have a really simple package based on one data table and have created a calculated query item based around two other query items - this is what it looks like ''aggregate([Business].[GI Sales Funnel].[Hot Leads Quoted] )/aggregate( [Business].[GI Sales Funnel].[Hot Leads Sent])''.
This is being used in a cross tab in report studio, with Week and Year aggregation required (I have both available as query items) but I cannot get it aggregate correctly at both week and year level. I have tried what seems like every possible option on regular and semi aggregation both in FM and Report Studio but cannot get it to do both. I have rebuilt the same calculated query in Report Studio and it works fine, and the one above made in FM works fine in query studio but not report studio. Can anyone help ? - I know I have a work around but ideally would like re-use calculations made in FM in all the studios.
Hi there,
Thats always a trick part of using crosstabs
Check out the following documents
http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html
http://www.ibm.com/developerworks/data/library/cognos/page128.html
You'd really need to provide more details about what the database is and also if you've created a DMR model. I'd recommend creating a DMR model if you want to develop a complex crosstab across multiple time dimension levels. Just makes is easy to work with as a crosstab in Cognos is really a mini cube
Cheers
Peter