Summarize totals 1

[emax6]

I have a report as Follow

1 - Salesperson
2 --- Product Category
3 ------ Total $amount product sold for these category-for this salesperson


How can I calculate the total $ value sold for one specific category to be able to calculate the percentage sold by a specific salesperson.
Thanks

Crystal Xi
SQL2000

 

[ricolame]

you are trying to figure out the % of the total sales sold by the person or ... ?

 

[emax6]

yes, therefore I need to be able to calculate the total sale by category.

 

[lbass]

It sounds like you want to calculate the sales by a specific salesperson as a percentage of a specific category (across all salespeople). If there are only a few departments, you could create formulas like this:

//{@prodcategA}:
if {table.prodcateg} = "A" then {table.sales}

//{@prodcategB}:
if {table.prodcateg} = "A" then {table.sales}

You could then write a percentage formula for each product category like this:

//{@percentA}:
sum({@prodcategA},{table.salesperson}) % sum({@prodcategA})

//{@percentB}:
sum({@prodcategB},{table.salesperson}) % sum({@prodcategB})

You could then format each to suppress if zero, and then move the formulas on top of each other so that only one result appears per product category in the same position each time.

If there are a lot of product categories, you could either write your report using a command as the datasource where you have the command return the report level sum by product category so that you can use it in a formula to calculate percent within salesperson groups, or you could insert a subreport in the product category group that is linked on product category (but NOT on salesperson) that returns a shared variable which sums the total sales for that category which can then be used in the percent calculation.

-LB

 

[emax6]

Thanks,
There is around 150 categories, therefore I am going to go with the subreport solution.
But, I do not understand what you mean by 'using a command as a data source'-Do you mean a query using the category as a parameter

 

[lbass]

In 9.0 and above, in the database expert, before choosing a table, you might have the option of "Add command". This is a place where you can write a SQL query directly to use as your datasource or to use in combination with tables. You might first try going to "add command" and entering:

Select sum(table.`sales`) as TotalforCateg, table.`prodcateg`
From `table`table
Group by table.`prodcateg`

Then link this to your main report table on product category. You could then write a formula:

sum({table.sales},{table.salesperson}) % {command.TotalforCateg}

Place this in the group header or footer for Product Category. This is actually the simplest solution.

-LB

 

[emax6]

Yes,this look a lot more familiar to me.
I will try this tomorrow.
Thanks

 

[emax6]

In the query==> Select sum(table.`sales`) as TotalforCateg, table.`prodcateg`
From `table`table Group by table.`prodcateg` <==

For each group I need to only select the categories associated to each record by adding a ==> HAVING table..`prodcateg`= CategorieNb;

where CategorieNb would be equal to the value of each group.
How can I include this parameter value in the query?

 

[lbass]

I'm not following. Do you need to use more than one table in the command? If so, please identify the specific tables and fields.

If your concern is that not all product categories will be used in the report, i.e., you are using a parameter in the main report to limit the product categories, that would not affect the command. The command just returns a sum per product category.

-LB

 

[emax6]

Actually, I just found the way to do it. I do not have to add a specific category in the query, and the query return the correct number. This is handle by the report.
Thanks anyway.