I want to count the number of data rows that make up each aggregate value in a report. How can I do this in the report itself? Is it possible? Anything I put a report count on results in the value of “1”.
I want a 3 column table that looks like this
dimension value ---------- measure value ------------ count of rows
I know I can do this in SQL but I want to do it in the report itself.
I want to count the number of data rows that make up each aggregate value in a report. How can I do this in the report itself? Is it possible? Anything I put a report count on results in the value of “1”.
I want a 3 column table that looks like this
dimension value ---------- measure value ------------ count of rows
Create an object with a definition of count(*).
You can do this one of two ways – either leave it simply as count (*), which won’t parse because it’s not related to any particular table. But you’ll be able to use it with any table you have an object for.
The other way is to relate the count(*) to a particular table using the “Tables” button when you create the object. That way it will always count the items in that table.
The other key is that you need to put a sum( ) around your measure value so that the aggregation takes place on the database, not in business objects.
The SQL it will generate would be something like this:
Select dimension, sum(measure), count (*)
from table
group by dimension
That way it gives you a count of how many records went into the aggregation. This won’t exactly work if you’re going against an aggregate table – it will give you the number of rows that went into each row of the “group by” clause, not how many rows are from your most detailed level.
This gets tricky with contexts and aggregate awareness, but it seems to work for simple universes. Can anyone come up with a better solution?
Create a Dimension variable and define it as the equivalent of your Measure variable. Simply put, your new variable will convert the Measure object into a Dimension variable.
With that in place, for your “count of rows” column, define a formula or variable =Count()
Cheers,
Luis Gonzalez
From: Mills, S. Greg [SMTP:S-GREG.MILLS@USA.CONOCO.COM]
I want to count the number of data rows that make up each aggregate value in a report. How can I do this in the report itself? Is it possible? Anything I put a report count on results in the value of “1”.
I want a 3 column table that looks like this
dimension value ---------- measure value ------------ count of rows
I know I can do this in SQL but I want to do it in the report itself.