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
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
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
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:
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..
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