Graphing Percentages 1

[james75]

I have a Grand Total which represents GP%(Gross Profit) for a whole region which is a formula field. I want to be able to show the regions GP% per Month on a line graph. When I do this all I get a flat line with the last value shown in the grand total field across all months.

I know I'm doing something wrong here particulary since the Grand Total field I believe only calculates once at the end of the report printing cycle. But how do I get this grand total to appear on the graph by month?

Thanks

 

[lbass]

So you have used a date field in the "On Change Of" section and set it to "monthly" and you still get a flat line for GP%? The formula used for calculating the Grand Total has to be used at the group level, where you are grouping on a date field and setting it for "monthly". Can you post the Grand Total formula and give us some information on your data fields?

-LB

 

[vijayjoga]

You can do it in two ways, either you create a group on date for each month or create a formula for month and in chart make onchange this formula.

good luck
balaji

 

[james75]

Ibass that's correct. My date field is at the Country Level which is the top level group since this is a drill-down report. My Grand Total's are in the Report Footer which would make sense why it's not graphing them by month. However this still leaves me with a slight delemer as I still need the grand totals to be displayed on the graph by month. Perhaps create a new subsection and make the grand totals appear there??

Anyway the Grand Total formula for GP% looks like this:

Sum ({@Actual GP})%Sum ({@USD Sales})

Both the above mentioned fields are number summary fields USD is obviously US Dollars.

Thanks

 

[lbass]

I'm assuming that what you want to show is the percentage of gross profits for each month based on the total sales for each month--and NOT the percentage of each month's profits compared to the grand total of sales for all months. Let me know if I'm wrong about this.

To get the gross profits per month based on sales per month, group on your date field {table.date}, and set it to "monthly". Create a formula {@monthlyGP%}:

sum({@Actual GP},{table.date},"monthly&quot%sum({@USD Sales},{table.date},"monthly&quot

Insert the chart, choose line, advanced layout, enter {table.date} as your "On Change Of" field, and then select it. Choose "Order" and select "chart values shown for each month." Choose {@monthlyGP%} as your summary field, and check "don't summarize values."

This should give you the graph you're looking for. Let me know if you run into any more issues.

-LB

 

[james75]

I tried this but CR returns an error message saying it can't create a summary / running total field. Yes I do want to want to show the GP percentage of sales for all products in all countries. Currently the GP% is calculated by a different field with the same formula at each level and doesn't calculate as a summary from the details section up (if this helps).
I'm also using CR version 8.5.

 

[lbass]

When you say:

"Currently the GP% is calculated by a different field with the same formula at each level and doesn't calculate as a summary from the details section up (if this helps)."

What do you mean by "a different field with the same formula"? Are you saying that the detail fields used to calculate GP% in your formula are replaced by different fields for the calculation at the group level? I think you need to provide the exact formulas for us.

When you say my suggested formula caused an error--did you group on {table.date}(monthly) before creating the formula? Did you add in the "monthly" into the formula? If you didn't do one of these, you would get the error you described.

-LB