Former Member

### Month by Month Summary Sheet

Hi

Every month a margin report is produced and I would like to create a summary sheet to pull the results of the summaries below to go in a summary sheet in the format shown below.

How can I achieve that?

Thank you.

capture.jpg (108.1 kB)
10|10000 characters needed characters exceeded

• Former Member
May 30, 2017 at 06:57 AM

Hi Rony,

I used cross tab for this requirement, hope you will be fine using the same :)

Source date (used excel):

Cross Tab:

I selected 'Summarize Horizontally' and 'Show Summarized Field Labels' from cross-tab 'Summarized field labels' option.

Output achieved

Crystal:

Excel:

Hope it works for you :)

Thanks,

Gulam Mustafa

10|10000 characters needed characters exceeded
• Former Member
May 30, 2017 at 09:22 AM

Hi Gulam

Thank you for your answer. I may have to go into more details about how my report is set up to ascertain the effectiveness of this approach...

1. Some of the "summary" fields in my report are obtained through formulas and other through direct summaries. In the case of the fields whose results I want to pull into a summary sheet, the results are all obtained through formulas. Examples:

a) Total Bottles/Cases. The result here is obtained through a concatenation of two formulas:

ToText({@total_cases_less_samples_less_third_party_stock},0) & " Bottles" & "/"

&{@concatenation_of_total_whole_part_and_total_decimal}

b) Total Sales. Here the result is obtained through another formula:

Sum ({'Sales_Data_'.Total Sale}) - Sum ({@if_third_party_stock})

c) Total Cost. Here the result is also obtained through another formula, as well:

Sum ({@total_cost}) - {@sum_of_total_cost_of_samples_and_tps}

d) The results for the other fields are also obtained through formulas which are built upon other formulas in order to get the aimed result like in the case "a" above.

2. Two data sources are linked with a left outter joint to obtain the values in my formulas and summaries;

I thought of cross tab, but I can't see how it would suit the complexity of my report.