Print Page - Crosstab: show value indipendent from rows and column item

Title: Crosstab: show value indipendent from rows and column item
Post by: g.congnos on 18 Dec 2013 08:57:32 AM

Hi,

I have a crosstab with rows 1 and 2 and column A and B.

The value under the column B depend on the value under the column A but not form rows 1 and 2 items.

A B
1 2

B value should be:

if (A > 3) then (B) else (C calculated independently from rows 1 and 2)

any suggestions?

Thanks

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: MFGF on 18 Dec 2013 11:25:06 AM

Quote from: g.congnos on 18 Dec 2013 08:57:32 AM
Hi,

I have a crosstab with rows 1 and 2 and column A and B.

The value under the column B depend on the value under the column A but not form rows 1 and 2 items.

A B
1 2

B value should be:

if (A > 3) then (B) else (C calculated independently from rows 1 and 2)

any suggestions?

Thanks

Sorry - that doesn't seem to make sense. When you refer to "A" in "if (A > 3) are you referring to the value in cell A1? A2? The summary for the A column? The caption (label) for the A column?

Are you using a relational package or a dimensional package?

MF.

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: g.congnos on 19 Dec 2013 02:21:16 AM

e.g.

        Age   Salary
Surname1 Name1
Surname2 Name2

if (Age > 30 ) then (Salary of Surname-Name) Else ( AVG(salary))

I'm using a dimensional package

Thanks

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: MFGF on 19 Dec 2013 03:17:41 AM

Add a new column as a query calculation (called AvgSalary) with the expression average([Salary] within set [Your set of people in the rows])

Then add another new column as a query calculation with the expression if (tuple(currentMember([Your people hierarchy]),[Age]) < 30) then ([Salary]) else ([AvgSalary])

Does this give you what you need?

MF.

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: g.congnos on 19 Dec 2013 05:12:20 AM

I got the following error

Invalid coercion from 'string' to 'hierarchy' for '[Surname]' in 'if (tuple(currentMember([Surname],[Name]),[Age]) < 30) then ([Salary]) else ([AvgSalary])'.

do you have any suggestions to solve it?

Thanks

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: MFGF on 19 Dec 2013 05:29:14 AM

Hi,

Yep - you used [Surname],[Name] in the currentMember() function instead of the hierarchy the surname/name comes from.

Drag in the hierarchy object instead - in my example I tried to show this with [Your people hierarchy]

You also seem to be missing a close bracket for the currentMember() function - compare what you coded with my example

MF.

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: g.congnos on 19 Dec 2013 08:33:11 AM

Hi,

I'm sorry, i haven't got it. I'm a beginner.

what would you write instead of [Surname],[Name] ?

Thanks

Title: Re: Crosstab: show value indipendent from rows and column item
Post by: MFGF on 19 Dec 2013 09:00:15 AM

In your package you have dimensions. Within each dimension is at least one hierarchy. Grab the hierarchy your name info comes from and drop it into the currentMember() function.

(https://image.ibb.co/cu94ua/Hierarchy_zpsa040022b.png)

In the image above you can see the icon to look for

MF.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: g.congnos on 18 Dec 2013 08:57:32 AM