Grouping and Counting

building reports desktopintelligence
1

This is a second request—I have spent a day and a half trying to solve this.

I have a series of formulas that gets me to a final result in the Bus Obj User application. Now I want to count all the like values from that final result. Example of values in the “Final Result” 110
110
234
99
456
456
110

I need to see the data grouped and counted as in the example below 110 3
234 1
99 1
456 2

The only result I get in the count field are 1’s. I tried, as suggested, to use the count(count(finalresult)), but it also rendered only 1’s. I have tried formatting the table to “avoid duplicate row aggregation”, but it still counts each like duplicate value as “1”

All I am getting for each value, even when it repeats such as 110 is a 1 in the “count” field. I have tried to make a variable that counts the “final result”, tried to use the count function from splice and dice, etc.

I thought the problem may be that my “final result” is a measure object and the COUNT function only counts unique value for dimension and detail objects. So I made converted the results back to strings defined as dimensions but to no avail

Question: How can I group and count my “Final Result” field?

If anyone can help me dig my way out of this, I would be very grateful.

–Beverly

Listserv Archives (BOB member since 2002-06-25)

2

A sneaky way to do it would be the following:

Add a column which contains the number 1 in each row.

Code numberfield
110 1
110 1
234 1
99 1
456 1
456 1
110 1

Then your final query will look like this:

select code, sum(numberfield)
from table
group by code

Hope this helps.

Kathy Vazquez

Listserv Archives (BOB member since 2002-06-25)