Group By Date Range Parameter

[dleewms]

I am using CRXI. I need a report with several counts per day for each day within a range. There will be three columns one for open, closed and pending tickets. I need to group by each day in the user defined date range. For example, if my user enters a beginning date of 4/16/07 and an ending date of 4/19/07, I would have 12 entries; one for each of the three columns for four days. The problem is that I can't group by a date range parameter, neither can I get a formula to return range values. Is there a way to do this within CR or will I have to create a temp table within my DBMS?

Thanks,
DLee

 

[synapsevampire]

You can get formulas to return range values, check out:

faq767-5684

However grouping is based on the data, as with SQL and basically every tool wich used grouping, not variables or parameters, it's based on your data.

So that said, use Report->Selection Formula->Record and place the criteria in there to limit the rows returned:

{table.date} = {?MyDateRangeParameter}

Then just group by the date,and then subgroup by what field comprises the open, closed and pending tickets.

I suggest that for future reports you post example data and expected output rather than stating what you want or need to do until you become familiar with what data is and Crystal Reports.

-k

 

[dleewms]

Thank you for your reply. However, I used the selection formula when there was only one field (case.opendate) that determined which records should be returned. That does not apply for my new report. I need a count per day of cases that were opened, closed and still pending at the end of the day. The cases could have been opened on any date but would be included in the closed column if they were closed on that day. I will use conditional running totals to summarize the counts for each column. Below is sample output.

OPEN CLOSED PENDING
04/16/07 65 43 131
04/17/07 53 59 125
04/18/07 77 68 134
04/19/07 43 63 114

 

[synapsevampire]

My apologies, it sounds as though you do know Crystal a bit, and a conditional Running Total sounds appropriate.

However you should still be limiting the rows returned by the report.

(
{table.closed} in {?MyDateRange}
or
{table.pending} in {?MyDateRange}
)

-k

 

[dleewms]

Thank you!

 

[Luca5h]

Hi DLee,

I need a report almost exactly the same as yours, but am having some problems.

My table ([TICKET]) has [creationdate] and [closeddate] fields, and i can use isnull(closeddate) to differentiate between 'open' and 'closed' records.

I already had the following selection formula in place:

(
{ticket.creationdate} in {?MyDateRange}
or
{ticket.closeddate} in {?MyDateRange}
)

But can't figure out how you manage to get the report to return totals for each of the dates stored in {?MyDateRange}. You suggest using conditional running totals to do this, but where in the Running Total Field editor are you specifying your date range parameter?

Any help much appreciated!

Lucas
(Crystal Newbie)

 

[Luca5h]

Sorry, forgot to mention, i'm using CRXI with SQL2000.

Thanks,

Lucas

 

[dleewms]

Lucas,

I wound up not using CR to total my tickets per date. Had to do it all in a stored procedure.

Are you grouping by any field, perhaps your creation date? If so, you can group on your creation date and have a new group for each day. You can then do a count per day.

If not, you'll have to have a field that acts as a calendar and use that as your group by field.

Let me know if I can do anything else to assist you.

DLee

 

[Luca5h]

Hi Dlee,

Thanks for the quick response.

I originally tried grouping by creationdate, but as in your case, there are records that have a closeddate within the date range, but have creationdates outside the range, so grouping by creationdate results in rows well outside my range.

I'll keep trying!

Thanks you,

Lucas