I have this column YN in the report that computes for the number of times YES appears in a column in the table I am querying. However, this is grouped by TYPE. Also, I have this column called SEQUENCE which contains numbers which should be distinct in the group. Now my SQL query looks like this:
SEQUENCE TYPE YN
1 A YES
1 A NO
1 B NO
2 A NO
3 C YES
3 C NO
Now I want to display my report like this:
TYPE Y (count) N (count)
A 1 0
B 0 1
C 1 0
Any ideas on how I can implement this on my rtf template?
Thanks a lot :)
hi you can do it in rtf template .
by using logic some thing like this <?count(YN[.='YES'])?>
will the type a ,b, c will be constant for all the input's then you can make the first column as constant and do the caluclation part for the rest of 2 column .
may i know the version you are using bi pub 10g or 11g.
Thank you for your response.
The thing is, I need to group the count by TYPE. And if there is at least one row in SEQUENCE that has a 'YES' value, it will automatically be counted on the Y column of the report. But my existing code counts ALL of the number of times Y appears in the column. For every SEQUENCE, I only need one number for them, that's why I'm asking if it can be done using distinct values..
Btw I'm using 11g..
I hope it's clear, thank you for your help.
can you please send me your template and xml and required result to my email i can try at my end . can you please give the expected result in email like table you have shown here with exact count what you need to get.
my email id : firstname.lastname@example.org
if you were using 11g and using package method to get the results i would suggest you to do the calculations part in sql level on the final result what you are having now using some case statement like that because as best practice we should avoid calculations as much as possible at template level. i use to do like that . i think your requirement can be done.