Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: ninjamayo on 09 Jun 2016 11:15:17 AM

Title: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 09 Jun 2016 11:15:17 AM
I have a complicated crosstab that essentially has a number of columns that I am reporting on. The last column brings back a variable number of entries and under that column I have another calculated column like in my example below:

                           FIRST COUNT | SECOND COUNT |                   TYPE ID
SEGMENT                                                                   TYPE COUNT | PERCENT COUNT

I can easily calculate TYPE COUNT because its a simple calculation however my PERCENT COUNT formula needs the COUNT for the specific TYPE ID / OVERALL COUNT for that segment. The OVERALL COUNT I get in my calculations is always the one for the TYPE ID so I cannot get the overall one. I tried using different solving orders but that didnt work. Any ideas how to solve this?
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: MFGF on 10 Jun 2016 03:12:53 AM
Quote from: ninjamayo on 09 Jun 2016 11:15:17 AM
I have a complicated crosstab that essentially has a number of columns that I am reporting on. The last column brings back a variable number of entries and under that column I have another calculated column like in my example below:

                           FIRST COUNT | SECOND COUNT |                   TYPE ID
SEGMENT                                                                   TYPE COUNT | PERCENT COUNT

I can easily calculate TYPE COUNT because its a simple calculation however my PERCENT COUNT formula needs the COUNT for the specific TYPE ID / OVERALL COUNT for that segment. The OVERALL COUNT I get in my calculations is always the one for the TYPE ID so I cannot get the overall one. I tried using different solving orders but that didnt work. Any ideas how to solve this?

Dimensional or relational package?

MF.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 10 Jun 2016 04:52:58 AM
Dimensional (I think) :)
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: MFGF on 10 Jun 2016 05:55:59 AM
Quote from: ninjamayo on 10 Jun 2016 04:52:58 AM
Dimensional (I think) :)

In that case, getting the overall count is probably going to involve using the completeTuple() function. Are the counts measures, or are they calculated?

MF.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 10 Jun 2016 06:34:20 AM
The counts are calculated on other crosstab columns.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: MFGF on 10 Jun 2016 06:45:19 AM
Quote from: ninjamayo on 10 Jun 2016 06:34:20 AM
The counts are calculated on other crosstab columns.

Can you share how?

MF.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 10 Jun 2016 06:51:26 AM
Yes, definitely. Sorry I didnt make that clear.

So for example FIRST COUNT is Count(FIRST) for that particular SEGMENT. So under my TYPE ID my PERCENT COUNT is TYPE COUNT / FIRST COUNT for that particular SEGMENT.

I tried the completeTuple() but I get the count for all the segments. So nearly there with your help :).

Thanks so much.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: MFGF on 10 Jun 2016 07:15:43 AM
Quote from: ninjamayo on 10 Jun 2016 06:51:26 AM
Yes, definitely. Sorry I didnt make that clear.

So for example FIRST COUNT is Count(FIRST) for that particular SEGMENT. So under my TYPE ID my PERCENT COUNT is TYPE COUNT / FIRST COUNT for that particular SEGMENT.

I tried the completeTuple() but I get the count for all the segments. So nearly there with your help :).

Thanks so much.

Sorry - can you be a little more specific about the syntax? What exact syntax are you using for your count? What expression did you use in your completeTuple() calculation? Have you referenced currentMember([the hierarchy your Segment members belong to]) within the completeTuple?

MF.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 10 Jun 2016 07:26:57 AM
completeTuple([FIRST]) but I dont know how to use the currentMember. I 've been trying to do completeTuple([FIRST], [SEGMENT]) but it doesn't seem to work.

Any ideas?
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 10 Jun 2016 08:29:24 AM
Aha, I think I got it.

I used Total([FIRST] FOR [SEGMENT]) and that seemed to do the job.

Thanks anyway!
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: MFGF on 10 Jun 2016 09:27:08 AM
Quote from: ninjamayo on 10 Jun 2016 07:26:57 AM
completeTuple([FIRST]) but I dont know how to use the currentMember. I 've been trying to do completeTuple([FIRST], [SEGMENT]) but it doesn't seem to work.

Any ideas?

Yep. [SEGMENT] is a set not a member, so you can't use it as the argument of a tuple. Your set of [SEGMENT] members belong to a hierarchy within your package. You can isolate the current member being used using the currentMember() function, so you get

completeTuple(currentMember([your hierarchy]), [FIRST])

This is the way to do things with a dimensional source.

Quote from: ninjamayo on 10 Jun 2016 08:29:24 AM
Aha, I think I got it.

I used Total([FIRST] FOR [SEGMENT]) and that seemed to do the job.

Thanks anyway!

Ouch!! No - don't do that! total([something] FOR [something else]) is a relational summary, not a dimensional summary. It's not the way to do it with a dimensional package.

MF.
Title: Re: Calculating a new column in crosstab with 2nd order solving
Post by: ninjamayo on 13 Jun 2016 04:03:45 AM
Hi,

just saw your comment. So why Total([FIRST] FOR [SEGMENT]) is not a good idea? It seems to work at the moment so is there a problem in terms of performance?
Text only | Text with Images