Cross Tab Summary

[SR24]

My client who is using CR 8.5 has asked me to produce a cross tab report which has a summary that calculates a percentage in the middle of the cross tab. I'm not an expert with cross tabs so I'm wondering if this is even possible using a standard cross tab report or if a manual cross tab would need to be used. The report would be laid out as follows:

Sept-06 Oct-06 Etc
Total Accounts 9039
Result O 3141
Result Y 3294
Result C 0
Completion Rate 71%
Result D 345
Result N 1078
Result T 1181
Result L 0
Incompletion Rate 29%

The formula for the Completion Rate would be the sum of Results O-Y divided by Total Accts and Incompletion Rate would be the sum of Result D-L divided by Total Accts. Anyone have an idea if this can be done using a cross tab?

Thanks!

 

[lbass]

Please identify the row fields and the fields used for the various results (which are summary fields?). If you are using formulas, display the contents of the formulas.

-LB

 

[SR24]

I have a single field in the row section of the cross tab named User_02. This field contains the various results, ie O, Y, etc for each Loan number. I am doing a count of Loan number in the summarized Fields section. Date, grouped by month, is in the column field. With this set up counts for each result are correct, but I'm not sure how to insert a summary in the middle which will calculate a percentage of the total Accts for the sum of O, Y, & C and another at the bottom to calculate the percent of total accounts for the sum of D, N, T & L.

Initially I created this report in designer by having Group 1 on Date for each month. Group 2 I inserted a formula in the details to identify whether the result code was a completion code or incompletion code. Group 3 on User_02. The report worked perfect except they wanted the data laid out by month horizontally instead of vertically.

 

[lbass]

If you need that exact layout, then I think you are better off with a manual crosstab. You could add an outer group on a formula like {@Rate}:

if {table.user_02} in ["O","Y","C"] then
"Completion Rate" else "Incompletion Rate"

Then drag the groupname into the group footer. (Your group #2 will be on {table.user_02}. Then create a formula for each month like {@Sep06}:

if month({table.date}) = 9 and
year({table.date}) = 2006 then 1

Insert summaries on each formula at the group #2 level and then for the completion rates, use:

sum({@Sep06},{@Rate}) % sum({@Sep06})

-LB

 

[SR24]

Thanks LB. I figured this would be a manual crosstab because of the layout they've requested. Your @Rate formula is what I used in my initial report with the vertical layout for Group 2. I wanted to try to use a crosstab so the column headings would change dynamically going forward as opposed to hard coding them. Can you dynamically change the column headings (months) in a manual crosstab based on a parameter or some value?

I also read a few posts that you commented on that discussed formatting the details section using "format with multiple columns". I've never used this. Is that something that may work for my situation?

 

[lbass]

You could use formulas like the following where {?start} is a date parameter representing the start of a period:


//{@Month1}:
if {table.date} in dateserial(year({?start}),month({?start}), 1) to dateserial(year({?start}),month({?start})+1, 1)-1 then 1

//{@Month2}:
if {table.date} in dateserial(year({?start}),month({?start})+1, 1) to dateserial(year({?start}),month({?start})+2, 1)-1 then 1

//etc.

For the column labels, use formulas also like:

//{@month1label}:
totext(dateserial(year({?start}),month({?start}), 1),"MMM-YY")

-LB