Hi all,
I have a problem with a calculation item based on the cubes in Cognos (Report Studio)
I have an metric that is formed with: METRIC = METRIC1 - METRIC2
- METRIC 1 = metric based on the prompt
- METRIC 2 = metrica based on the prompt - 12 month
How can I do to get the difference? Necessarily have to create 2 queries?
Please help me? :(
Quote from: Aidanna on 24 May 2013 10:23:04 AM
Hi all,
I have a problem with a calculation item based on the cubes in Cognos (Report Studio)
I have an metric that is formed with: METRIC = METRIC1 - METRIC2
- METRIC 1 = metric based on the prompt
- METRIC 2 = metrica based on the prompt - 12 month
How can I do to get the difference? Necessarily have to create 2 queries?
Please help me? :(
Can you be a little clearer on what you mean by "metric based on the prompt"? Do you mean the users can select which metric to use? Or do you mean that they are prompted for some dimensional information which is used as context for a specified measure?
Generally if you want to choose which measure to use, you can do this with a query calculation which builds up the MUN of the measure - driven by a prompt macro.
If you want to use a single measure with two different contexts, then you could use tuple() functions to get the measure value for the prompted member and the prompted member in the current year and the prompted member in the previous year.
Cheers!
MF.
Hi MF,
thanks for reply.
Yes, I want to use a single measure with two different contexts, based on the choice of the prompt.
So I have to retrieve the values and make a difference
Regards
Nadia
Hi,
Trying to make the UNION of two queries that have the same number of elements in the same order.
Trying to put some of the fields in a new crosstab queries union, throwing out the report the following error:
XQE-PLN-0001
Internal error. Query Service is not able to plan the query
Thanks
N.
Hi,
This is a dimensional report, so you don't need multiple queries and unions. You should simply be able to use the tuple() function in your expression to return the desired measure intersections, and the lag() function to return the month 12 months prior to this member
eg for your expression you could code
tuple([Your measure], [Whatever month member you prompted for]) - tuple([Your measure], lag([Whatever month member you prompted for], 12))
Cheers!
MF.