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 1Service 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
COUNT(GENDER_DESC) OVER (ORDER BY (SERVICE_NAM), (GENDER_DESC))
Where GENDER_DESC is "Gender Description"
Any help will be greatly appreciated