Adding a measure

[ratnalein]

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

[CognosAdmn]

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

[ratnalein]

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

[CognosAdmn]

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

[ratnalein]

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

[CognosAdmn]

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

[MFGF]

Silly question... are you using a relational package or a dimensional package?

MF.