How to group/subtotal when the fields differ from record to record

[ewarr]

Select statement:

{AgdataView.Distributor_IC} in
["0216501890000",
"0091965930000",
"7939627130000"]

or

{AgdataView.Reseller_IC} in
["0216501890000",
"0091965930000",
"7939627130000"]

Each record in the database has both a Distributor_IC and Reseller_IC. The report needs to subtotal on every record found. I need the SQL to determine if the IC# belongs to a Distributor or Reseller, then group by company name (Distributor Name or Reseller Name) and then by product name and subtotal by company name.

I have 2 grouping formulas for use in my group expert: Distributor Group and Reseller Group.

Group Expert is setup like this:
Distributor Group
-Reseller Group
--Product Name

This isn't working correctly. Any ideas? Thanks.

 

[Turkbear]

Hi,
What formulas are you using for your groups?
Since each record can be in 2 groups the formula will be difficult..



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

 

[lbass]

Since one record can appear in only one group, you either have to use a subreport in the report footer for one of the categories, or you could use a command as your datasource where you use a union all to generate two sets of records, as in:

select 'Distributor' as type, AgdataView.Distributor_IC as IC, AgdataView.otherfield
from AgdataView
where AgdataView.Distributor_IC in ("0216501890000",
"0091965930000","7939627130000")
union all
select 'Reseller' as type, AgdataView.Reseller_IC as IC, AgdataView.otherfield
from AgdataView
where AgdataView.Reseller_IC in ("0216501890000",
"0091965930000","7939627130000")

The syntax and punctuation depend upon your datasource, but once you get the command working, you can insert a group on {command.type} and then your data will display as desired.

-LB