Creating groups excluding values and summing

[pmella2000]

Wise Folk,

I am having difficulty creating a single report that includes the following:

1- Displaying all billing values and totals for a sales rep
2- Displaying only values with contract number with A's (contract number field is Str)
3- Showing only values with contract number with B's & C's
4- Showing all others
5- Displaying Total for A, B & C's, and all other's in a summary

It should look like:

All CTS
Joe 600
Phil 900
Ann 600
ttl 2100

Only A's
Joe 300
Phil 433
Ann 944
ttl 1644

B & C's
Joe 444
Phil 200
Ann 300
ttl 944

All Other's
Joe 100
Phil 200
Ann 300
TTl 600


Joe's gtotal of a, gtoal of b&C, gtotal all
Phil's gtotal of a, gtoal of b&C, gtotal all
Ann's gtotal of a, gtoal of b&C, gtotal all

I have tried to achieve this with subreports but I am unable to figure out how to display the final sumarized totals. Maybe this can be accomplished with subtotals?

I am new to sub-totals...

Thanks
Pmella

 

[lbass]

I think you could do this by inserting a crosstab in the report header that uses sales rep as the row and sum of {table.billing} as the summary.

Then create a formula in the formula editor:

if instr({table.contractno},"A") > 0 then "A" else
if instr({table.contractno},"B") > 0 or
instr({table.contractno},"C") > 0 then "B or C" else
"All Others"

Not sure what your contract number looks like, so there might be a better way, but still this formula should work. Insert a group on this formula, and then a group on sales rep, and then insert summaries on the billings.

Finally, place another crosstab in the report footer, using the above formula as the column, sales rep as the row, and billings as the summary field.

-LB

 

[pmella2000]

lbass, many thanks for your response to my post.

I've was unable to get the cross tab to produce the results that I required. I am on short assignment and I did not have the time to impliment your suggestion. Instead, I created one report with several sub reports. In the last subreport which included subset of the summary totals, I used all running totals.

Regards,
pmella2000