Crystal Report 8.5 Sub-Report question

shilsum
shilsum used Ask the Experts™
on
I am quite new to Crystal Reports hence this simple question.

I am trying to create a report for a call center that would display the following for a given agent:
1.) Number of cases created during a date range (CASE_TABLE.CREATED_DATE)
2.) Number of cases closed during the same date range (CASE_TABLE.CLOSED_DATE)
3.) Number of cases that are outstanding till the date of report.

I created 3 reports
1.) Count of Case_Created for cases created- prompts for agent, created_from_date, created_to_date
2.) Count of Case_Closed for cases closed- prompts for agent, closed_from_date, closed_to_date
3.) Count of Case_Outstanding for cases open- prompts for agent.

Each of them individually run fine.

I created a new report using Case_Created as primary report and Case_Closed & Case_Outstanding as sub-reports with proper linkages.

When I try running the report, it prompts me for prompt values 3 times - once for Case_Created, once for Case_Closed and once for Case_Outstanding though the values for each are same.

How do I get rid of the 2 extra prompts from appearing and instead use the values specified in the first prompt for running the sub-reports?

(I was able to get over this using a single query with Unions and then grouping the data as per my needs. Yet I would still like to know if there is a way to get over this.)

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
when linking the sub-report did you link its parameter field to a data field in the parent report?
Ummm ... I could help you out with your subreport question but I believe I would serve you better by giving you an easier way to produce your report.

Maybe you can adapt your existing report but I'll give you instructions for starting from scratch.

1.  Create your report and include the tables your need.

2.  Create your date parameters, say {?FromDate} and {?ToDate}

3.  Put in the formula:
// Created before the end
{CASE_TABLE.CREATED_DATE} <= {?ToDate}
and
(
// Closed after the beginning
{CASE_TABLE.CLOSED_DATE} >= {?FromDate}
OR
// Still Open
{CASE_TABLE.CLOSED_DATE} = Date(0,0,0)
)

Note:  This will ensure that this report will include all of the records you are after.  I have assumed that you are using the report option of receiving NULLS as default values.

4.  Group by agent

5.  Create a Running Total NumOpen and set it to; count Call ID, count where {CASE_TABLE.CREATED_DATE} >= {?FromDate} and reset upon change of Agent group.

6.  Create similar Running Totals of NumClosed and NumOpen and set them up similar except your want to count where ({CASE_TABLE.CLOSED_DATE} >= {?FromDate} AND {CASE_TABLE.CLOSED_DATE} <= {?ToDate})  and the next formula - ({CASE_TABLE.CLOSED_DATE} = Date(0,0,0) OR {CASE_TABLE.CLOSED_DATE} > {?FromDate}), respectively.

7.  Place the running totals in the group footer and supress the details.


Let me know if you need more help.


This question has been classified abandoned. I will make a recommendation to the moderators on its resolution in a week or two. I appreciate any comments that would help me to make a recommendation.


Unless it is clear to me that the question has been answered I will recommend delete. It is possible that a Grade less than A will be given if no expert makes a case for an A grade. It is assumed that any participant not responding to this request is no longer interested in its final disposition.


If the user does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp

Pat K
EE Cleanup Volunteer

I believe that I must have answered this one.

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: gawilson2000 {http:#7480765}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

mnye
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial