Skip to Content
0
Nov 01, 2018 at 07:35 PM

Two calculated measures in a crosstab

45 Views

Hi Experts,

I am developing a report in SAP Lumira discovery. Backend Data source Universe (unx) built on Oracle data base.

I need to show a Dimension; Service name, two measures, Service Count and Gender Count and Gender Dimension with all possible values(Male, Female, Not Shown) in column containing gender count .

I am using a crosstab.

Desired Crosstab should be like this, showing correct values.

Service NameService CountMaleFemaleNot ShownA212B531C441

Gender count shows correct value if "Service Count" is not added in the query. If Service Count is included in the query all Gender count is 1

Service NameMaleFemaleNot ShownA111B11C11

Please note, Service Count in the above table has not been included but Service Count is present in the query hence causing all 1s in Gender Count

Both counts have been calculated at the universe level

Gender count

COUNT(GENDER_DESC) OVER (ORDER BY (SERVICE_NAM), (GENDER_DESC))

Where GENDER_DESC is "Gender Description"

Service count

count(.SERVICE_ID)

Any help will be greatly appreciated