Charts & Forumla Field - Different Grouping Need to Report

[OzWolf]

This may be impossible to do, but someone might have an idea.

We currently have a sub-report with a manual cross-tab with the following groupings:

- Product Group
- Product Type
- Bill Period

A business need has arisen for a chart in the same sub-report that shows the total charge / total quantity per bill period for each product group.

Obviously, if I try to create a formula field grouped on bill period for use by the chart, the chart contains the values for the last product type only and not for the product group as a whole.

Is there anyway I can create the chart I want? Or will I have to go back to them and say find another place for the chart?

Cheers.

 

[lbass]

Actually, you should be able to create an advanced layout chart to be placed in the group header or footer for the product group, where you use the billing period as your "on change of" field and the sum of your formula:

{table.qty}*{table.charge}

...as your "show value" field.

-LB

 

[OzWolf]

Actually, what they wanted was something along the lines of:

SUM({table.chg},{table.bill_period}) / SUM({table.qty},{table.bill_period})

I tried all types of ways to fix it (including an automatic cross-tab). None worked.

Thankfully, I've just finished convincing them that the graph provided useless and meaningless info, so I dodged the issue

 

[lbass]

But I think you would get the same result by using sum as a summary and a formula as the field to sum:

{table.chg}/{table.qty}

-LB

 

[OzWolf]

Yeah, but the sum of averages doesn't give you an overall average.

For example, say for a specific bill period you have:

Prod Type A Avg = 300/10 = $30
Prod Type B Avg = 200/20 = $10

That gives $40 as a sum when the reality is 500/30 = $16.66

I also need to put a grouping in the formula field, otherwise the graph will be full of identical bars as Crystal calculates the average charge across the entire dataset, not just for the bill period it is analysing at that time.

The level of headache I have given myself trying to solve this lends credence that it can't be done. If I wasn't already trying to modify a view-on-demand sub-report, I'd use a sub-report to solve it.