Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: sergiord2016 on 18 Mar 2017 03:56:57 AM

Title: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 18 Mar 2017 03:56:57 AM
Hi ,

I have a crosstab with one member as a column , other member as another column and a third column with the auto calculated column  member1-member2

This works for each case where the member 1 and meber 2 has values , but for example if the member1 has the value 10 and the member2 has no values the result of the calculated column is empty string.

I tried changing the proporties of ecach member , asigning 0 for empty string values but the result is 0 and not 10. It seems that if the member2 has no values the substract operation does not work.

Please anyone can help me?

Thank you in advance.
Title: Re: member1-member2 does not work when member2 has no values
Post by: New_Guy on 20 Mar 2017 12:38:57 PM
Hi,

Which cubes are you using? we have TM1 cubes and we never had this issues. I tried with transformer cube and I am having the same issue too.
Tried using tuples too but still the same result.

Good luck
New Guy
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 20 Mar 2017 12:53:48 PM
Hi

How can i verify the type of cube?

Title: Re: member1-member2 does not work when member2 has no values
Post by: New_Guy on 20 Mar 2017 01:21:44 PM
Hi,
You should be able to see [PC]  or [TM] before the member. PC is for transformer power cubes and TM is for TM1 cubes.
Rgards
New Guy

[XXXX].[XXXXX].[XXXXX].[XXXXXX]->:[PC].[@MEMBER].[XXXXXX]
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 21 Mar 2017 04:15:55 AM
[BBDD_T].[SERVICIO].[AGRUPA SERVICIOS].[SERVICIO]->[all].[M].[MIR]

This is an example ...
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 21 Mar 2017 09:37:17 AM
any opinion?

i thought it had an easy solution...

i want to substract two members even when one member is null substract as it was zero value.

how can i do this please???
Title: Re: member1-member2 does not work when member2 has no values
Post by: New_Guy on 21 Mar 2017 01:15:52 PM
Check the below link.
https://www.linkedin.com/pulse/calculations-null-values-cognos-report-studio-advanced-craig-eskdale (https://www.linkedin.com/pulse/calculations-null-values-cognos-report-studio-advanced-craig-eskdale)
Good luck
New Guy
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 21 Mar 2017 02:12:48 PM
That should work.

I will probe and tell you , thanks!
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 22 Mar 2017 05:04:00 AM
HI ,

It does not work , it shows an error , i translate from spanish:

The argument in position 2 of the coalesce of data element DIF_ACUM_VER , is a `member` expession that can not be converted to a value expression


coalesce( [ACUMULADO AÑO ACTUAL VER] - [ACUMULADO AÑO ANTERIOR VER];[ACUMULADO AÑO ACTUAL VER] ; -[ACUMULADO AÑO ANTERIOR VER])

Do you know whats the preoblem here?
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 23 Mar 2017 05:32:45 AM
please help!
Title: Re: member1-member2 does not work when member2 has no values
Post by: Deep750 on 23 Mar 2017 07:03:00 AM
Coalesce is a relational function, don't hink you can use it against cube.

Try to make a CASE or IF-THEN-ELSE statement.
Something like
if(member1 is null)
THEN (member2)
else if (member2 is null)
then (member1)
else (member1-member2)
Title: Re: member1-member2 does not work when member2 has no values
Post by: sergiord2016 on 23 Mar 2017 09:22:16 AM
Yes!

That works!!!

Thank you very very much
Text only | Text with Images