Counting Dilemma...

[jdemmi]

Does anyone have any creative ideas for the following issue? I'm stuck with 2 ideas, neither of which seem palatable to me.

CRXI - Oracle 10.x

I have a report request where a user wants to see a number of "counts" by month, by quarter and by year. This is not the issue as I have already created a parameter for month/quarter/year which is passed to a formula for the report grouping.

This concept works beautifully if I am just counting records by "record date"

The dilemma comes in when they want additional record counts, like records opened, closed, and past due in the same period. Since I am grouping by record date those other counts become dependent on the record date as well. Which is, obviously, incorrect.

So, more specifically, they have the need the following counts...

Initiated -- records created in the month, quarter or year specified
Opened -- records in an opened state (not closed) in the month, quarter or year specified
Closed -- records closed in the month, quarter or year specified
Overdue -- records closed after their due date for the month, quarter or year specified

How can I count these values without grouping on record date, date created, date closed, or due date?

I thought about building a "calendar" table and adding it to the report, but that would still require a join to the table holding the records which need to be counted. It seems messy.

I also thought about creating a subreport for each count and then passing the values back to the main report. But...I'd still need to group by an arbitrary date....

I am sure I have done a poor job of explaining this issue. So, please, let me know what else I can tell you that will help you help me.

Thanks in advance...

-- Jason
"It's Just Ones and Zeros

 

[Madawc]

Have you looked at Crosstabs? They let you show the same data with a different sort of grouping.

Failing that, you could do it with running totals using relevant tests to determine what needs to be counted. Maybe a 'Mock Crosstab' - use SEARCH if you need details.




Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[IanWaterman]

This is very difficult. The only solution I found was to use a command and group data in the database.

It was done by using a Cross Join query to a dummy table holding the dates.

I would post your query in the MS SQL developer forum if you do not know how to write Cross join queries.

Ian

 

[IanWaterman]

Actually found thread where I got some help to do this

http://www.tek-tips.com/viewthread.cfm?qid=1600351

Ian

 

[lbass]

Please identify the date fields you are working with. Also provide your selection formula.

My guess is that you can add running totals to provide the counts.

-LB