Hope somebody can help!!

 

I have 2 tables.

 

MANUFACTURER  & TRANSACTIONS

 

they are linked as MANUFACTUER (Primary) >left outer join TRANSACTIONS

 

I want to build a report that shows sales by customer, by manufacturer.

 

but I want it as a template so it always shows the same manufacturer, regardless of which customer is specified.

 

When i specify a TRANSACTIONS customer in the select expert, it removes brands from the grouping.

 

ie;

 

Customer: JOHN SMITH

 

BRAND 1      10 units

BRAND 2      11 units

BRAND 3      20 units

BRAND 4      15 units

BRAND 5       9 units

BRAND 6       30 units

 

the problem I am having is, I group by manufacturer, and if the specified customer hasnt purchased brand 2, 3 & 5

 

it shows this:

 

There will be 3 paramters:

{TRNSTK.CUSTOMER} startswith {?Customer}
{TRNSTK.PERIOD} = {?month}
{TRNSTK.YEAR} = {?Year}

Yes, more than 1 customer, BUT, we wont be displaying all the individual customers seperately. simply amalgamating the customers units / values.

so for example we have customers JANE1 to JANE9, we would usually just put {TRNSTK.CUSTOMER} startswith "JANE"

Yes, I have a finite number of brands.

There are about 100, but we are only going to be using about 25, the rest can be catagorized ito "other"

basically move your select criteria to a formula field

 

if {TRNSTK.CUSTOMER} startswith {?Customer} and
{TRNSTK.PERIOD} = {?month} and
{TRNSTK.YEAR} = {?Year}and

then {TRNSTK.Units} else 0

//guessed at the units name

 

now you can insert a SUM of this formula field at the grouplevel 1 to get total units per Brand

If you want 25 that show to be dynamic based on volume you can use TOP N group options.

Other wise you will have to write a formula for your 20-25 that you want to show and groupng the rest under 'other'. This would replace your field to group on for group 1 (Brand)

You can go the route of a command or stored proc and imbed your parameters there.

basically for that you would run a query that groups all of your brands into one table, run an inner query (using the params at the query level) to select sales rows that meet your condition and outer join the two together.

The trick  is to get the limited sales data first and then do the outer join to the master list of brands, other wise your select criteria can turn your outer join back into an inner join.