Summarising & Analysing Printed Data 1

We are using Crystal 8.5 with Oracle 9 databases. I have a report that contains details of quotes we have made and which the business has been won or lost. For each type we have a set of reasons. There are up to 19 different reasons (although this can increase in the future). The records are grouped by sales person, date entered, quote number, line number & release number. Each of the quotes has a reason code and these codes cannot be grouped together due to the earlier groupings.

What I am trying to do is create a summary at the end of the report which lists the distinct reasons with the number of quotes which used each reason and a percentage of the total number of quotes which used each reason ie:

Reason              No of Quotes     %
REPEAT BUSINESS         30           60%
ABLE TO MEET SPEC       10           20%
ABLE TO MEET LEAT TIME  10           20%
TOTAL                   50          100%

The reasons pulled through are dynamic ie there may be 2 different reasons or there may be 15 different reasons dependent upon the dates selected to report on and I do not want to 'hard code' the reasons in the report as additional reasons may be added at a later date.

I had thought about creating a 2 dimensional array into which was added the reason and the second element was updated with an incrementing count whilst printing the records but I cannot see how to a) create the array with the distinct reasons or b) search the array to find the correct element to update the count as I go through each line in the report.

Any suggestions as to how to go about this will be much appreciated.

Thanks in advance.

Regards
Richard Chambers.

 

[lbass]

You could place a crosstab in the report footer using reason as the row, with no column field, and count of quote number as the summary field. To get a percentage in the crosstab, see thread149-769395. The downside is that the presentation of the summary fields would be vertical.

If you had 9.0, you wouldn't need this workaround.

To use arrays, try something like the following:

//{@init} to be placed in the report header:
whileprintingrecords;
numbervar array y:= 0;
redim preserve y[1000];
numbervar counter := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x;
numbervar array y;
numbervar counter;
stringvar results := "";

if instr(x,{table.reason}) = 0 then x := x + {table.reason} +", ";

for counter := 1 to ubound(split(x,", "))-1 do(
if split(x,", ")[counter] = {table.reason} then
y[counter] := y[counter] + 1 else
y[counter] := y[counter];
results := results + split(x,", ")[counter] + ":" +
space(25-(len(split(x,", ")[counter] + totext(y[counter],0,"")))) +
totext(y[counter],0,"") + space(6) +
totext(y[counter] % count({table.reason}),0,"") + "%" + chr(13));

//{@display} to be placed in the report footer:
whileprintingrecords;
stringvar results;

Substitute your field name for {table.reason}. You must right click on {@display} and go to format->field->common->and check "Can Grow". The formula is set up to display with aligned columns if you use a non-proportional font. Try it with Courier to see what I mean.

-LB

 

[McMint]

Thanks LB. Excellent suggestions and the code is superb. I had not even thought of a Cross Tab. A pity the percentage is not a bit easier <grin>. I have looked at the thread you suggested but still cannot see how I could apply it to this situation. Sorry for being a bit thick!!

One thing I forgot to mention was the fact that there are multiple lines to the quote. I have used a distinct count on the quote number to give me the number of quotes for each reason within the cross tab. I have a formula field in the report itself which gives me the total number of quotes but how do I do the calculation in the cross tab to give me the percentage? Then to add insult to injury I would like to sort the cross tab on the percentage so that the greatest percentage is first etc.

The array code works brilliantly but for the multiple lines for each quote. Also how would you sort the array before displaying it?

Thanks again for you help so far.

Regards
Richard Chambers

 

[lbass]

You could rewrite the display formulas, one for each element, so that you could position them manually, but that's not a great solution.

I came up with a crosstab solution that uses SQL expressions, but I'm not sure how this would work with duplicate data. Can you explain the duplication? Do you have multiple tables that you are linking? It might help if you could show some sample detail level data, so we could see how the data is repeating.

Here is the crosstab solution, in case you can adapt it (I honestly am not sure how to change it to account for duplicate data though):

Create two SQL expressions:

1 - {%count}:

(select count(AKA.`Reason`) from Table AKA where
AKA.`Reason` = Table.`Reason`)

2 - {%percent}:

(select count(AKA.`Reason`) from Table AKA where
AKA.`Reason` = Table.`Reason` ) /(select count(AKA.`Reason`) from Table AKA)*100

In each of these formulas, substitute your table name for "Table" and your exact field name for "Reason". Leave "AKA" as is, since it is an alias table name.

Insert a crosstab, and add {table.reason} as your row field, and {%count} and {%percent} as your summary fields. Do NOT change the type of summary while in the crosstab expert, as this will result in a Dr. Watson (this happened to me repeatedly). Exit the crosstab and then right click on each summary ->change the summary operation->maximum. (The maximum of the SQL expressions are the same as the expressions themselves, but you have to have some kind of summary in a crosstab.) Then go to format->crosstab->customize style tab and check "Suppress column totals", since you are not looking for maximums on the report level.

Next click on the upper left corner of the crosstab in order to select it. Then go to report->topN/Group Sort and choose topN, where N is greater than the maximum number of reasons you think you will ever have. Choose "%percent" as your topN field. This will sort the crosstab in descending order by this field.

-LB

 

[McMint]

Hi LBass,

I can see where you are coming from but Crystal 8.5 will not allow count() in the SQL Expression Builder. :{

(select count(AKA.REASON_DESCRIPTION)
from WM_QUOTE_WON_LOST\\ AKA
where AKA.REASON_DESCRIPTION = WM_QUOTE_WON_LOST\\.REASON_DESCRIPTION)

I get an ora-00911:invalid character error and the cursor sits at the start of count. Also count is not in the list of functions that can be used.

The data is in the form

Quote No, Line No, Quantity Part No, Won/Lost, Reason
123       1        10,000   123      Won       Good Price
123       2        20,000   123      Won       Good Price
123       3        30,000   123      Won       Good Price
124       1        5,000    xyz      Lost      Too Expensive
124       2        10,000   xyz      Lost      Too Expensive

Here we have 2 quotes, 1 with 3 lines and 1 with 2 lines. They want the quantities to appear on the report so the data acutally looks like above,ie multiple lines per quote. Hence the use of distinct count on the quote number in the summarized field for each reason. This gives me the number for each reason.

I think we will have to leave it with just the count numbers without the percent. Thanks for your efforts though. They are much appreciated.

Regards
Richard Chambers.

 

[lbass]

I built this in 8.0. Often the location of the cursor in SQL expressions does not indicate the location of the problem. I think your expression should read:

(select count(AKA.`REASON_DESCRIPTION`)
from WM_QUOTE_WON_LOST AKA
where AKA.`REASON_DESCRIPTION` = WM_QUOTE_WON_LOST.`REASON_DESCRIPTION`)

...which removes the slashes and adds the single quotes around the field names. However, the syntax can vary based on datasource, etc., but I think it's worth a try.

-LB

 

[sdebo2]

Here is an idea.

You said the report is grouped by Sales Person, and the data given looks like it is sorted by Quote Number. If you create another group on Quote number (you can hide the header and footer if you like), and then move the {@accum} formula, from lbass’s code to build the array, from the detail to the Quote Number footer, this should give you a distinct count by Quote Number. This assumes that there is only one sales person per quote.