grouping on main table and alias

[dunlop1975]

I am trying to make a report that will forcast all items that will be used on a given day. The items used are stored on 2 seperate tables in the database. Main and secondary (there is always a main but not always a secondary). I am trying to group on InvDesc and then I will list the details of which procedure it is for. Using an alias does not allow me to group on that field, I can split it into a subreport but then I would only be able to list the items in 2 parts. I've tried to link both tables to the Inventory table as an experiment (that went wrong...). Anyone have any ideas? Thx




Request Main Procedure Inventory
------- (inner Join) --------------- ---------
Req# ------------------>Req # (inner join)
| Inv#----------------->Inv#
| InvDesc
|
|
|
| Sec Procedure Inventory(alias)
|(left Outer join) ------------- ----------------
----------------------->Req# (inner join)
Inv#------------------>Inv#
InvDesc

 

[RTag]

Can you publish the actual SQL ?
The shown schema will return 2 columns InvDesc.
Are you using a subquery for Sec Procedure - Inventory ?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

I would use a command for this in order to "merge" the fields of the two tables into one set of fields, like this:

select 'Main'as Procedure, Request.Req#, Inventory.Inv#, Inventory.InvDesc
from ((Request
inner join Main on
Request.Req#=Main.Req#
)
inner join Inventory on
Main.Inv#=Inventory.Inv#
)
union all
select 'Sec'as Procedure, Request.Req#, Inventory.Inv#, Inventory.InvDesc
from ((Request
left outer join Sec on
Request.Req#=Sec.Req#
)
left outer join Inventory on
Sec.Inv#=Inventory.Inv#
)

Then you can group on {Command.InvDesc} and use {Command.Procedure} to distinguish Main and Sec Procedures.

-LB

 

[dunlop1975]

I'mbeing called away on a different issue so will not be able to tackle this until the end of the week but thank you!

I have never used a Union in CR and now see how this could save me a headache on a different report as well. I'll report back when I have results