This discussion is archived
4 Replies Latest reply: Apr 30, 2012 12:59 PM by BIPuser RSS

Counting columns using distinct values of other column

933151 Newbie
Currently Being Moderated
Hi everyone,


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 :)
  • 1. Re: Counting columns using distinct values of other column
    Avinash_Varma Expert
    Currently Being Moderated
    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.
  • 2. Re: Counting columns using distinct values of other column
    933151 Newbie
    Currently Being Moderated
    Hi varma,

    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.
  • 3. Re: Counting columns using distinct values of other column
    Avinash_Varma Expert
    Currently Being Moderated
    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 : datlaavinash@gmail.com

    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.
  • 4. Re: Counting columns using distinct values of other column
    BIPuser Guru
    Currently Being Moderated
    Try using this:

    <?count(xdoxslt:distinct_values(element_name))?>

    In your case, use this: <?count(xdoxslt:distinct_values(YN[.='YES']))?> and do the same by for count where YN = 'NO'

    Thanks,
    Bipuser

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points