Building multiple measures from one measure

[ratnalein]

Hallo everyone,

I have only one measure in my data source.  I would like to create a report having more than one measure columns:

So, in my datasource:

Dim_01 Status  Quantity_of_People
--------- --------- --------------------------
 
I would like to create a report in report studio:

Dim_01  Dead_People  Alive_People
---------   ----------------   ---------------

Dead_People is just a filtered Quantity_of_People (with the status Element = 'dead')

I need somehow the measure 'Alive_People' in the same report.  Technically it would be just to filter the Quantity_of_People with the status element = 'alive'.  But how can I produce this?

For information:
I produced Dead_People by just filtering the Quantity_of_Population in "Query Explorer"

Thank you very much.

Regards,

Ratna

[blom0344]

create Buckets using CASE:

TOTAL(CASE WHEN [STATUS] = 'Alive' THEN [MEASURE] ELSE 0 END)

You can define as many as you want all based on just 1 measure

[ratnalein]

Hallo blom0344,

thank you for your quick reply... mhh, would you please tell me how to create generally a bucket?  what do you mean by bucket?

Thank you very much.

Regards,

Ratna 

[blom0344]

I just showed you by example..  'Bucket'  is just a coined phrase. Using one measure you can define whole sets of calculated measures you can use side by side in a list container.  In ETL terms it is often used and coined  as  'flatten'; pivot;  denormalize. 
The difference here is that by nesting the CASE within the aggregate you can dictate what data would go in a certain 'bucket' .

Another example :

Just by chance I hired 26 people who just by chance have surnames  ranging from  A to Z.  The 26 names are stored in a table , just 1 field.
Now , in my report I want a list with all 26 side by side:

Code Select Expand


SELECT
max(CASE WHEN [NAME] LIKE 'A%' THEN [NAME] ELSE NULL END) AS  A_person,
max(CASE WHEN [NAME] LIKE 'B%' THEN [NAME] ELSE NULL END) AS  B_person,
max(CASE WHEN [NAME] LIKE 'C%' THEN [NAME] ELSE NULL END) AS  C_person,
.......................................................................
.......................................................................
max(CASE WHEN [NAME] LIKE 'Z%' THEN [NAME] ELSE NULL END) AS  Z_person
FROM ..............


yields 26 buckets with the names in individual items..

[ratnalein]

Hallo blom0344,

Thank you very much for the nice illustration, it helped me to understand.  I tried it by creating a new data element with the particular definition, it worked very well. Now I have the columns depending and summarized from dimension elements I want.

Regards,

Ratna