I have written a query as mentioned below:
WITH
SET [kpi_study] AS
{[study].[study].[BHC June12]}
SET [geographic] AS
{[territory.market_hierarchy].[state].[HP]}
SET [brand] AS
{[brand.brand_hierarchy].[brand].[Gold Flake (Unspecified)]}
SET [edu12] AS
IIF
(
'All' = 'All'
,[education].[education].MEMBERS
,[education].[education].[All]
)
SELECT
NON EMPTY
{[Measures].[tom]} ON COLUMNS
FROM [funnel_analysis]
WHERE
{[kpi_study]*[geographic]*[brand]};
Result:Tom:4.19
Instead of All if i am passing(SSC/HSC) any other value always getting same value.Can any help me where i am doing wrong.it means IIF function is not working proper.
You have the following:
SET [edu12] AS
IIF
(
'All' = 'All'
,[education].[education].MEMBERS
,[education].[education].[All]
)
So I'm guessing All are placeholders ?! - and you say when you change one of them nothing happens.
This is because in your select you're not actually using the SET called [edu12] so why would anything change?
SELECT
NON EMPTY
{[Measures].[tom]} ON COLUMNS
FROM [funnel_analysis]
WHERE
{[kpi_study]*[geographic]*[brand]};
Is [edu12] supposed to be on rows?