Orders containing products
Is it possible to create a report, that can provide me with the JobNumbers of Orders that contain one or multiple products/services using filters?
My data can look like this:
Job# 1111
- OrderDetail1 - Sweeping
Job # 2222
- OrderDetail1 - Striping
- OrderDetail2 - Striping
- OrderDetail3 - Striping
Job # 3333
- OrderDetail1 - Asphalt
- OrderDetail2 - Striping
- OrderDetail3 - Sweeping
Here is what I started with, but I am getting redundant JobNumber records due to multiple matching ActivityTypes (Services) on each order.
If I filter to see all Striping Jobs, I will see multiple records for one Job where I only want to see one record per JobNumber:
JobNumber | Activity Type
2222 | Striping
2222 | Striping
2222 | Striping
3333 | Striping
[CODE]
SELECT
JobNumber,
MastOps.ActivityType AS ActivityType
FROM dbo.[OrderDtl] OrdDtl
LEFT JOIN dbo.MASTOPS MastOps ON OrdDtl.MastOpsIdno = MastOps.MastOpsIdno
WHERE @FILTER_T:ActivityType@
[/CODE]
Ben
Re: Orders containing products
[CODE]SELECT
[B]distinct[/B] JobNumber,
MastOps.ActivityType AS ActivityType
FROM dbo.[OrderDtl] OrdDtl
LEFT JOIN dbo.MASTOPS MastOps ON OrdDtl.MastOpsIdno = MastOps.MastOpsIdno
WHERE @FILTER_T:ActivityType@[/CODE]
Re: Orders containing products
OK, that is giving me the expected result but I noticed that I may have not specified properly.
Using the same example, when I filter for two values (Asphalt & Striping) I only want to see one JobNumber but it shows it multiple times.
[COLOR=#333333]
Job# 1111[/COLOR]
[COLOR=#333333]- OrderDetail1 - Sweeping[/COLOR]
[COLOR=#333333]Job # 2222[/COLOR]
[COLOR=#333333]- OrderDetail1 - Striping[/COLOR]
[COLOR=#333333]- OrderDetail2 - Striping[/COLOR]
[COLOR=#333333]- OrderDetail3 - Striping[/COLOR]
[COLOR=#333333]Job # 3333[/COLOR]
[COLOR=#333333]- OrderDetail1 - Asphalt[/COLOR]
[COLOR=#333333]- OrderDetail2 - Striping[/COLOR]
[COLOR=#333333]- OrderDetail3 - Sweeping
[/COLOR]
Results in:
[COLOR=#333333]JobNumber | Activity Type[/COLOR]
[COLOR=#333333]2222 | Striping[/COLOR]
[COLOR=#333333]2222 | Striping[/COLOR]
[COLOR=#333333]2222 | Striping
[/COLOR]3333 | Asphalt
[COLOR=#333333]3333 | Striping
Ben[/COLOR]
Re: Orders containing products
Do a grouping on job number. If you experiment with Dynamic AI design tool then you can see the SQL that it generates to achieve the result. You'll want to make the details in their own level.
-Bob