If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

how to use case when using members

Started by NewtonsCannon, 17 Apr 2012 01:58:54 PM

Previous topic - Next topic

NewtonsCannon

I am very new to this.
I want to give a value for select locations. These locations do not have any prior year data.

so

Case
    When   'this is where I have tried many times to some how reference the location.
               [31 MESQUITE] = '31 MESQUITE'
         also
               [Sales Delivered (SALD)].[Locations].[Locations].[RA1] = '31 MESQUITE'

No luck.

So if you could tell me how I can test a member or one specific value, that would be great.               

NewtonsCannon

Hope fully more clear;
Case
When Caption( [Locations] ) = '31' then 1.5
else
[SETTING MATERIAL SALES PRIOR YEAR]/[TILE N STONE SALES PRIOR YEAR] 
end
.  This is but one of many ways that I have tried to test locations for a value of 31.

Thanks.

NewtonsCannon

I have tried
CurrentMember([Locations]) = '31'

Caption(CurrentMember([Locations]))

Many other ones too.

cognostechie

Is this from a Cube or DMR ?

If it's a cube, you would have attributes called 'Short Name' and 'Long Name' which are character alternatives of the MUN.

You can use -

If
(Location - Long Name = '31')
then (<something>)
else (<something else>)
End

You can also use If then Else with MUNs but I don't remember right now how to do that.

NewtonsCannon

Hello;
It is from a cube.  I tried this and its no good for me.
Case
When  [Sales Delivered (SALD)].[Locations].[Locations].[Locations].[Locations - Short Name] = '31' then 1.5
else
[SETTING MATERIAL SALES PRIOR YEAR]/[TILE N STONE SALES PRIOR YEAR]
End


the error code, Initially, in data source type(s) '', the function 'source' is not supported in 'TabularOperationProvider', which i have looked into google, talks about multiple joins of which I dont have.

How about a different way.

The goal is to put hard coded values for 2 locations into the percent cell. If a location is not one of these 2, then just use the calculation.  Any thoughts on how to do this>

Thanks, Eric

cognostechie

I just did it in a report and it works.

See attached screens.

NewtonsCannon

Perfect sir.  My main problem was not using the () to group up what i wanted.  Again.  Thanks a ton.