Need help with percentage formula in cross tab report

[tdiboll]

This is my formula:

IF SUM ({p21_sales_history_view.extended_price},{p21_sales_history_view.customer_name}) = 0 THEN 0 ELSE SUM ({@GROSS PROFIT DOLLARS},{p21_sales_history_view.customer_name})/SUM ({p21_sales_history_view.extended_price},{p21_sales_history_view.company_name})*100

I am trying to do a crosstab report showing all of our sales by users for each month. For each month, I am reporting the extended price, cogs, profit, and gp %. When I create the formula above to get my gp %, I get the following error message: Summary/Running total field could not be created.

Does anyone have a clue what I need to do to get the formula calculate my gp%?

thanks
Tracey

Crystal Reports 8.5

 

[rosemaryl]

Dear Tracey,

You cannot have percentages in a crosstab in Crystal 8.5.

You will have to go to a manual crosstab report.

regards,
ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[tdiboll]

what about higher versions of crystal?

 

[lbass]

It looks like you might have two issues here--how to do the crosstab and how to get your gp% formula to work. What is the content of {@GrossProfitDollars}? If this already contains a summary, you won't be able to use it in your gp% formula.

-LB

 

[tdiboll]

Here is the formula for the @GrossProfitDollars: {p21_sales_history_view.extended_price}-{p21_sales_history_view.cogs_amount}

 

[lbass]

You should be able to create the formula as long as you have inserted a group on{p21_sales_history_view.company_name}.

To use the {@gp%} in the crosstab, you will need to use SQL Expressions, or as Ro suggested, create a manual crosstab. To use the SQL Expressions approach, create the following two SQL Expressions:

//{%GrossProfit}:
(select sum(AKA.`extended_price`- AKA.`cogs_amount`) from p21_sales_history_view AKA where AKA.`customer_name` = p21_sales_history_view.`customer_name` and
{fn MONTH(AKA.`Date`)}={fn MONTH(p21_sales_history_view.`Date`)} and
{fn YEAR(AKA.`Date`)} = {fn YEAR(p21_sales_history_view.`Date`)})

//{%ExtPrice}:
(select sum(AKA.`extended_price`) from p21_sales_history_view AKA where AKA.`customer_name` = p21_sales_history_view.`customer_name` and
{fn MONTH(AKA.`Date`)}={fn MONTH(p21_sales_history_view.`Date`)} and
{fn YEAR(AKA.`Date`)} = {fn YEAR(p21_sales_history_view.`Date`)})

In each of these formulas, you need to replace `Date` in every instance with `the-field-name-for-your-date-field`. If you have used the correct field names in your posts, then you shouldn't have to change anything else. But please note that the exact punctuation in the expression can vary by datasource (although the above works for me using the Xtreme database with an ODBC connection). You can test what satisfies your database by double-clicking on a field name while in the SQL Expression editor.

Next create a formula {@gp%}:
if {%ext price} = 0 then 0 else
{%gross profit} % {%ext price}

Then insert the crosstab in the report header and use {table.date} as your column->group options->print on change of month. Use {p21_sales_history_view.customer_name} as the row field, and add {@gp%} as one of your summary fields, and then change the summary to "Maximum" of {@gp%}. Since {@gp%} will have the same value in all detail fields as in the group footer of the main report, a maximum will capture the correct value.

-LB