Grouping Question 2

[TXP007]

I'm using CRXI.

I have 1 SQL server table.

Sales (invoice_num, customer_num, customer_region, sale_price)

I want a sales summary report by customer_region, but I want to show all regions, even when there aren't any sales for a given region.

Is there a way I can define my groups (regions) and report spaces or zeoes for those groups that don't have any data?

Thank you for your time,
TXP007

 

[Turkbear]

Hi,
If a region that has no sales is not listed in the table, you will not be able to show it, obviously..

If the region will be there even if no sales, then just group on customer_region and create your summary in the group header ( or footer) - If sale_price is a nummeric field then a formula like

if IsNull(sale_price) then 0

in the detail section should give you a 'summable' number for any group with no sales.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kskid]

If you have a customer table, then you can left outer join the sales table to the customer table and group by the {customer.customer_region}

 

[jmd0252]

Great Idea about the left outer join. A lot depends on the design of not only the sales table, but also the "master", tables, if the customer region is in the customer table,, maybe there is a sales region master table of all regions, etc, etc. Depends on the information contained in the database tables. Kudos to the left outer join..