Calculation Problem between Main and Subreport

[MiagiSan]

Hello,

I am using Crystal 10 and I am having a bit of a problem with a report. I will try to explain as best as possible. I am sorry for the length of this posting.

The Main Report has a number of Cross Tabs in the header area and a section that shows me the following.

Total number of cases in Period
% reported by phone
% reported by Email
% reported by Chat
% remaining Open

On the same header area I have subreports that rollup the same information YTD.

When I run a report for January 1st - today, the numbers in the main report do not match the numbers in the YTD report. (YTD numbers are correct)

for example the main report will show 91% reported by phone
and the YTD subreport will show 90% reported by phone.(Again, YTD numbers are correct)

The formulas in the main and subreports are identical; I first do a count of the cases.

if {PS_RC_SOURCE_TBL.RC_SOURCE}="PHONE" then 1 else 0

Then another formula to add to the report header area.
(Sum({@Phone})/Count({PS_RC_CASE.CASE_ID}))*100

Here is the record selection for the sub report

if({PS_BO.BO_NAME} Like {?Pm-?Company} or {PS_BO.BO_NAME} = {?Pm-?Company})then {PS_RC_CASE.ROW_ADDED_DTTM} in YearToDate

Here is the record selection for the main report.

if ({PS_BO.BO_NAME} like {?Company}or {PS_BO.BO_NAME} ={?Company}) then {PS_RC_CASE.ROW_ADDED_DTTM} in {?Period}

The only thing I can think of is that in the main report details section I am displaying information on each case for the period selected. The cases may have more than one solution so to avoid duplicates, I created the following Group Selection formula to show me a case if there is no solution or show only the newest solution.

isnull({PS_RC_RESOLUT_VW.CASE_ID})or{PS_RC_SOLUTION.ROW_ADDED_DTTM} = maximum({PS_RC_SOLUTION.ROW_ADDED_DTTM},{PS_RC_CASE.CASE_ID})

It seems like the Main report summary is counting cases more than once (For any case that has more than one solution) I am just not sure how to fix this.

I tried adding distictcount to the phone formula but the percentage just went to 100.

(Sum({@Phone})/DISTINCTCount({PS_RC_CASE.CASE_ID}))*100

I appreciate the help, I hope I have given enough information.

R

 

[lbass]

A few comments:

1-Since you have different record selection formulas, you would expect different results in the main and subreport.

2-If you use "like" you should be using a wildcard, so I think the formulas should be written like this:

{PS_BO.BO_NAME} like "*"+{?Company}+"*" and
PS_RC_CASE.ROW_ADDED_DTTM} in {?Period}

3-Group selection only affects the displayed results--non-group selected records still contribute to inserted summaries--and group selection doesn't always affect crosstabs--it depends upon the crosstab design. Generally when using group selection, you need to use running totals, which will just pick up on the displayed records.

-LB

 

[MiagiSan]

Thank you lbass, I will give that a try. Have a great weekend.

R