Nested CASE

Hello,

I've got a SP that selects the best price from a table that has all info collected into it. Selecting the price is easy, I use COALESCE.

But I want to have a column next to it that contains which price that was choosen. I used CASE and nested it... worked fine until I reached the 10th level, there is a limit there.

"Case expressions may only be nested to level 10."

I'm sure som people will puke when they see this code and I'm open to suggestions on how to do it in another way. I can always do it in two queries, but it should be possible to do it in one.

I was looking at IF, THEN, ELSE, but I don't find any way to use it in a query, just to determine WHICH query will be run.

Here is my SP (how can I get it in a nice grey area like som people post?):

CREATE PROCEDURE dbo.ProcCOST_SET_TC AS

/* Empty TC table */
truncate table dbo.COST_TC

/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract' ELSE
Case SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract' ELSE
Case SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO' ELSE
Case SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt' ELSE
Case SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other' ELSE
Case SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC' ELSE
Case SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
Case SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order' ELSE
Case SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt' ELSE
Case SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC' ELSE
Case [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1' ELSE
'NO DATA' END END END END END END END END END END END
FROM COST_AllInfo
GO

---

Suggestions (don't hit me to hard)?Use the [ code] and [ /code] markers before and after your code so that vBulletin will treat it as code. I've put a space after the opening bracket so that they'll be visible in my posting, you must remove the spaces for them to take affect.

Turn them all into one CASE, something like (untested, of course):CREATE PROCEDURE dbo.ProcCOST_SET_TC AS

/* Empty TC table */
truncate table dbo.COST_TC

/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract'
WHEN SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract'
WHEN SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO'
WHEN SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt'
WHEN SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other'
WHEN SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC'
WHEN SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
WHEN SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order'
WHEN SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt'
WHEN SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC'
WHEN [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1'
ELSE 'NO DATA' END
FROM COST_AllInfo

RETURN
GO-PatP|||This CASE is tested and works to my satisfaction... Looks a lot cleaner too than my first atempt.

CASE
WHEN isnull(VPA_average_USD,0) > 0 THEN 'VPA'
WHEN isnull(SITEINPUT_sitecontract_usd,0) > 0 THEN 'SITEINPUT Site Contract'
WHEN isnull(SITEINPUT_lastPO_usd,0) > 0 THEN 'SITEINPUT Last PO'
WHEN isnull(SITEINPUT_lastreceipt_usd,0) > 0 THEN 'SITEINPUT Last Receipt'
WHEN isnull(SITEINPUT_other_usd,0) > 0 THEN 'SITEINPUT Other'
WHEN isnull(SITEINPUT_wac_usd,0) > 0 THEN 'SITEINPUT WAC'
WHEN isnull(SYSTEM_Min_ContractPrice_usd,0) > 0 THEN 'Min Contract Price'
WHEN isnull(SYSTEM_Min_OpenOrder_usd,0) > 0 THEN 'Min Open Order'
WHEN isnull(SYSTEM_Last_Receipt_usd,0) > 0 THEN 'Last Receipt'
WHEN isnull(SYSTEM_Min_WAC_usd,0) > 0 THEN 'Min WAC'
ELSE 'NO DATA'
END

Thanks for pointing me in the right direction!|||How do you figure your COALESCE statement is going to return the best price? I don't know what "best price" means (lowest? highest? best for whom?), but I'm sure COALESCE doesn't know either. It just returns the first non-null value from your parameter list. So Contract_usd will be returned because it is the first value, not the "best" value.|||The "Best Price" is order by priority, not by value. So the order of the columns in the COALESCE determines the priority order.

The first priority is "VPA_average_USD", if there is one, use it no matter what the value is. (I see I used the actual column name instead of "Contract_usd" I used in my first post)

The last priority is the price used last quarter.

Need urgent MDX help on using Aggreate funtion in SSAS 2005

Hi,

I need a functionality similar to Named Member in ProClarity in SSAS 2005.

In Proclarity I can define a named memeber by selecting a few levels of a dimension. The underlying MDX uses the Aggregate function something like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once the named member is selected the calculated measures show the aggreated (sum) results based on the named member and the underlying selected levels in the aggreate function.

How can I do something like this in SSAS 2005. I dont want a named set. And I tired created a calculated member (which i knew will not work but still gave a shot)

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

I also tried specifying the measure in the second parameter

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }

, [Measures].[Orders Received Local])

Is it possible to use the aggreate function dynamically as its used in ProClarity?

Thanks in advance for help

The aggregate function should work fine, can you tell us exactly what error you are getting. One thing about the aggregate function is that you would need to create the calculated member on a dimension other than the measures dimension.

Something like the following should report on a dynamically created aggregate member:

Code Snippet

WITH

MEMBER [Channel].[Channel].[AggTest]

as Aggregate({

[Channel].[Channel].[CON - Contractor]

, [Channel].[Channel].[DIS - Distributor]

, [Channel].[Channel].[END - End-User]

, [Channel].[Channel].[GRP - Group]

, [Channel].[Channel].[OEM - OEM]

, [Channel].[Channel].[PLA - Private Label]

, [Channel].[Channel].[SER - Service Provider]

, [Channel].[Channel].[SYS - System builder]

})

SELECT

{[Channel].[Channel].[AggTest]} ON COLUMNS

FROM <Cube>

|||Thanks. yes it worked this way. i made the mistake of creating it on the measures dimension. after changing the dimension to Channel it works fine.

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }, [Measures].Orders Received Local)

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.