Cross- Tab Total field 1

[gilsonr]

I have created a report which just contains a Cross-tab. The problem is that the values are OK, and the column totals are OK, its just the row total that are not correct.

Any suggestions would be appreciated.

 

[ShortyA]

Are you able to provide more info eg Crystal Reports Version, database type, how you are connecting, the data that is being shown and what you would expect ? This will help forum members assist you..

ShortyA

 

[gilsonr]

ShortyA

Thanks for the response. Iam using Crytal version 9.2.0.448, and I am connecting to an Oracle Database via Microsoft ODBC for Oracle.

In the cross tab the summarised field values are correct, but the Grand Totals for the Rows & Columns dont add up to the correct values.

 

[ShortyA]

What type of summary are you using in the crosstab eg count, distinctcount, sum etc.. ?

When using distinctcount the row totals don't always add up to the grand total as a record may appear once in two groups but would only be counted once in the grand total.

For example:
Car Customer
Ford Fred Smith
Renault James Scott
Mercedes Fred Smith

The crosstab would show 1 Ford, 1 Renault and 1 Mercedes. However the grand total would show a distinctcount of 2 as there are only two customers (assuming it was a distinctcount on customer).

ShortyA

 

[gilsonr]

ShortyA

Thanks for the response, you have summarised my problem exactly. The Column & Row Grand Totals are distinct counts, how do I amend the x-tab so that the totals add up.

 

[ShortyA]

If they are distinct counts this could be difficult. Is there a specific reason why a normal count isn't used ? Selectin from the Database menu "Select Distinct Records" may alleviate the issue of duplicates or checking the database relationship may be another alternative.

If none of these are appropriate you could create a manual crosstab but this can get a little high maintenance (see

http://www.kenhamay.com

as he has some excellent articles about crosstabs including creating running totals from crosstab values (which would help your issue).

ShortyA

 

[gilsonr]

ShortyA

Thanks for the help, I see what you mean about creating a manual x-tab , its a bit more involved.

I have created the rows with no problembut Iam having trouble with the conditional formula that creates the column values.

Tried the following if-then-else, but its not quite right

if ToNumber ({@Status})=86 then DistinctCount ({CAMT_PROMO_HISTORY.CONTACT_URN}) else 0

Any suggestions.

 

[lbass]

If you need a distinctcount, then you should probably be using running totals. You would need a separate running total for each group level and each column (which I assume is based on {@status}. In the running total, choose distinctcount of {CAMT_PROMO_HISTORY.CONTACT_URN}, evaluate based on a formula:

{@status} = "86" //for the "86" column

Reset on change of group.

-LB

 

[gilsonr]

Ibass

Thanks for the, it seems that Im going in the right direction. I have created a Running Total Formula as you suggested for a couple of the columns in the xtab. The columns are populated with values but unfortunatley they are not the correct counts.

Essentailly what I am trying to create is a xtab of the number of customers {CAMT_PROMO_HISTORY.CONTACT_URN}
by the Telesales type (@Affininty) and Response Code (@status)

The two formulas that I have created are substrings of the Telesales Type & Response code

 

[lbass]

So your row field is {@affinity} and you want your column field to be {@status}, right? For a manual crosstab, you would insert a group on {@affinity} and then insert a running totals as I described.

This should work correctly, and if it doesn't then I think you should supply sample data at the detail level that might look something like this:
Status RT - 86 RT - 93
GrpHdr - High affinity
Detail - 86 1 0
Detail - 93 1 1
Detail - 86 1 1
GrpFtr 1 1
Medium affinity
Detail 86 1 0
Detail 02 1 0
Detail 93 1 1
Detail 93 1 1
GrpFtr 1 1
Low affinity
Detail 78 0 0
Detail 20 0 0
Detail 20 0 0
Detail 93 0 1
GrpFtr 0 1

I don't think we can really help unless we can better understand your data. In my example, you would suppress the group header and details and just display the group footer results. Please create an actual sample for us and confirm that you are using running totals as described placed in the group footer. If you are using something different, you need to share exactly what you are doing.

-LB