| Discoverer Newbie - Help! [message #135183] |
Tue, 30 August 2005 05:59  |
cbyrne
Messages: 2
Registered: August 2005
|
Junior Member |
|
|
Hi All,
I'm new to discoverer, although an old hand at SQL. I'm trying to run a report in Discoverer and can't my head around how to do something, maybe someone can help.
Basically I need to be able to tell from a set of rows, what percentage of them satisfy a particular condition.
So, I can set up a worksheet that returns all the rows (basically like a select * with no conditions set). I can then add a condition to the worksheet which checks for a particular value in a field and filters the rows returned.
How though, can I get discoverer to basically return a count of the number of rows in the select * and a count of the rows which match the condition (and ideally one as a percentage of the other)- in the one query?
Once I can achieve this I then need to do something like a 'group by' report, where I can return % of rows that match the condition grouped by another field.
Apologies if this is a stupid query or doens't make sense - its a bit more complicated than this but I'm trying to break it down to its simplest part as if I can get the basics I should be ok.
Thanks in advance for any help!
|
|
|
|
| Re: Discoverer Newbie - Help! [message #136046 is a reply to message #135183] |
Mon, 05 September 2005 10:50  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi,
Did you try a decode (or case when)?
That way, you have a calculation which results in for example "1" if the row meets the condition and 0 if it doesn't.
Like: case when <some-condition> then 1 else 0 end
Regards,
Sabine
|
|
|
|
| Re: Discoverer Newbie - Help! [message #136047 is a reply to message #136046] |
Mon, 05 September 2005 11:01 |
cbyrne
Messages: 2
Registered: August 2005
|
Junior Member |
|
|
Hi Sabine,
thanks for the response,
yes I do have something like this setup whereby its setting a particular field to either 'Y' or 'N' depending on whether the condition is met or not. The problem is the amount of rows which are returned is huge so at the moment I am having to export the data to excel and then manually having to filter the results to see how many rows have 'Y' in them and how many have 'N'. This export is taking a lot of time across a network and I'm presuming (or hoping) there was some way to do this in discoverer, i.e. just have one row returned that says Number Y=986777, Number N=21998982,
thanks for your help.
|
|
|
|
| Re: Discoverer Newbie - Help! [message #137462 is a reply to message #136047] |
Thu, 15 September 2005 05:42 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Yes you can do that. Eliminate all the "significant" columns from your worksheet and only keep a count on your calculation:
- create the calculation that gives Yes or No
- create another calculation like 'count(calucation1)'
Only select those two.
Is that what you mean?
Regards,
Sabine
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
