Hallo everyone,
in my fact table, I have this:
School Sex Quantity
----------------------------------------------
School_A M 11
School_A F 17
School_B M 293
School_B F 288
...
School_Z F 23
In my Top-10-Report, I have this report:
School Female Sum
--------------------------------------------
School_K 34843 34843
School_Z 2739 2739
School_J 2298 2298
School_P 1998 1998
School_S 986 986
...
School_B 288 288
I would like to have a report like this:
School Female Female+Male
------------------------------------------------------
School_K 34843 59483
School_Z 2739 xxxx
School_J 2298 xxxx
School_P 1998 xxxx
School_S 986 xxxx
...
School_B 288 xxxx
This ist not easy, because Cognos will filter automatically my column Female+Male to only Female, because I have choosed Female as dimension filter :-[. How do I create a calculated measure to let Cognos produce the column Female+Male, which is not considered to the filter Female? Thank you for you help.
Regards,
Ratna :)
solution 1:
- remove the filter;
- create 3 query calcs
--------[quantity_F] --> if( sex = 'F') then ([Quantity]) else (0); (set rollup aggregate function to "Total")
--------[quantity_M]--> if( sex = 'M') then ([Quantity]) else (0); (set rollup aggregate function to "Total")
--------[quantity_FM] --> [quantity_F]+[quantity_M]
solution 2;
- create a new query with filter for Male;
- Join both querries by school
- perform your quantity calcs in the joined query;
hope this helps...
Hallo CognosAdmn,
thank you for the quick reply. Since actually my fact table Looks like this:
School Sex Quantity
----------------------------------------------
School_A M 11
School_A F 17
School A Transex 3
School_B M 293
School_B F 288
School B Transex 2
...
School_Z F 23
School Z Transex 1
I have built the Report, where the user can choose which sexuality he interests in it:
If the user choose female, reports should look like:
School Female Female+Male+Trans
----------------------------------------------------------
School_K 34843 59483
School_Z 2739 xxxx
School_J 2298 xxxx
School_P 1998 xxxx
School_S 986 xxxx
...
School_B 288 xxxx
If the user choose Trans, then:
School Trans Female+Male+Trans
----------------------------------------------------------
School_K 34843 59483
School_Z 2739 xxxx
School_J 2298 xxxx
School_P 1998 xxxx
School_S 986 xxxx
...
School_B 288 xxxx
I prefer the solution 2 you suggested; I somehow have to create a calculated measure, which includes male, female, and trans, but I can not remove the filter I provided for the user to choose. Maybe the solution 2 you suggested is just the right way to solve the Problem, but I havent understood how you meant by Joining the queries. In fact, I have only a measure query(quantity), and I have a filter for sexuality(if user chooses 'male', then 'male' will appear in the Report. If user chooses 'male' and 'female', then both will appear in the Report as above).
Thank you for you reply.
Regards,
Ratna
There may be more efficient way to build this report....
but I might build a separate query (in Query Explorer) for each Sex Type;
Query1 = "Male" with folllowing data items: School, Sex_M, Quantity_M and filter: sex = M
Query2 = "Female" with folllowing data items: School, Sex_F, Quantity_F and filter: sex = F
Query3 = "Trans" with folllowing data items: School, Sex_T, Quantity_T and filter: sex = T
Query4 = Join Query1 and Query2 with 1:1 relationship by School; Query4 will now have: School, Sex_M, Sex_F, Quantity_M, Quantity_F
Query5 = Join Query3 and Query4 with 1:1 relationship by School; Query5 will now have: School, Sex_M, Sex_F, Quantity_M, Quantity_F, Sex_T, Quantity_T
In Query5 build a custom calculation: Quantity_M+Quantity_F+Quantity_T
Now you could use Conditional Block to format the report using Value Prompt with Statis Values for Female, Male and Trans;
Hope this helps....
Hallo Cognos Admn,
thank you for the reply. I tried your suggestion by joining queries, but it gave me the error message in report studio:
The function 'ces_currentmeasure' not supported
Is there something I can do to fix this?
Thank you.
Regards,
Ratna :)
hmmm....do you have any summarized data items, aggregated data items or total data items items in your query1, query2 or query3?
if you do, you may need to remove them from querries 1-3.
you can do the summary functions in your Query5 (final query after joins).
Silly question... are you using a relational package or a dimensional package?
MF.