SQL reporting Services issue

armela20 · 2008-01-10

Hi there.
I am creating a new report in SQL and I need to know the qty on hand and the qty outstanding on a sales order.

I have the following query, however it is not giving me the correct results.
It seems like the grouping is not working the way I want it an it generates bad results for the above quantities.

SELECT [CRONUS USA, Inc_$Item].No_, SUM([CRONUS USA, Inc_$Item Ledger Entry].[Remaining Quantity]) AS EXPR1,
SUM([CRONUS USA, Inc_$Sales Line].[Outstanding Quantity]) AS EXPR2
FROM [CRONUS USA, Inc_$Item] INNER JOIN
[CRONUS USA, Inc_$Sales Line] ON [CRONUS USA, Inc_$Item].No_ = [CRONUS USA, Inc_$Sales Line].No_ LEFT OUTER JOIN
[CRONUS USA, Inc_$Item Ledger Entry] ON [CRONUS USA, Inc_$Item].No_ = [CRONUS USA, Inc_$Item Ledger Entry].[Item No_]
GROUP BY [CRONUS USA, Inc_$Item].No_

It seems like it is adding the sums up according to the number of Sales Lines and item Ledger entries that I have.

Can someone please help ??

Thanks.

ara3n · 2008-01-11

This should do it.

SELECT CRONUS USA, Inc_$Item.No_,  isnull(SUM([CRONUS USA, Inc_$Item Ledger Entry].[Remaining Quantity]),0)  AS EXPR1, 
                         isnull(SUM([CRONUS USA, Inc_$Sales Line].[Outstanding Quantity]),0)        AS EXPR2
FROM  CRONUS USA, Inc_$Item LEFT OUTER JOIN
               [CRONUS USA, Inc_$Sales Line] ON CRONUS USA, Inc_$Item.No_ = [CRONUS USA, Inc_$Sales Line].No_ LEFT OUTER JOIN
               [CRONUS USA, Inc_$Item Ledger Entry] ON CRONUS USA, Inc_$Item.No_ = [CRONUS USA, Inc_$Item Ledger Entry].[Item No_]
GROUP BY CRONUS USA, Inc_$Item.No_
having (SUM([CRONUS USA, Inc_$Item Ledger Entry].[Remaining Quantity]) > 0) OR
(SUM([CRONUS USA, Inc_$Sales Line].[Outstanding Quantity]) > 0)

armela20 · 2008-01-11

That still did not work.
It is producing the same results that I was getting before.
Here is an example.
Item No. 1000, qty on Hand = 32 (2 open Item ledger Entries) and qty on Sales order 104 (5 open Orders)

WHat I am getting with the above query or my query is this

Item no. 1000, qty on hand 160 (32*5 open sales orders), qty on Sales Order 108 (104 *2 open Ledger entries).
I hope this will make more sense to whoever is reading this.
My undestanding is that if you use the group by it should work the way I want it, but it is not.

Any other ideas ????

Thanks

ara3n · 2008-01-11

Could you give me an example on how you want to see the results?

Qty in ILE is

Item No.     Qty  
A                   10
A                   12
B                    03
B                    04

Qty in Sales Order

Item No.     Qty  
A                   5
A                   2
B                    3
B                    4

How do you want to see the results?

Item      QtySales   QtyILE
A              7                 22
B               7                 7

armela20 · 2008-01-11

I want to see the following.

Item No. Qty on ILE, QTY on Sales Order

1000 32.00 104.
1001 33 0.00
1002 0.00 37.00

ara3n · 2008-01-11

This query does exactly what you are asking.

SELECT [CRONUS USA, Inc_$Item.No_],  isnull(SUM([CRONUS USA, Inc_$Item Ledger Entry].[Remaining Quantity]),0)  AS EXPR1,
                         isnull(SUM([CRONUS USA, Inc_$Sales Line].[Outstanding Quantity]),0)        AS EXPR2
FROM  CRONUS USA, Inc_$Item LEFT OUTER JOIN
               [CRONUS USA, Inc_$Sales Line] ON CRONUS USA, Inc_$Item.No_ = [CRONUS USA, Inc_$Sales Line].No_ LEFT OUTER JOIN
               [CRONUS USA, Inc_$Item Ledger Entry] ON CRONUS USA, Inc_$Item.No_ = [CRONUS USA, Inc_$Item Ledger Entry].[Item No_]
GROUP BY CRONUS USA, Inc_$Item.No_
having (SUM([CRONUS USA, Inc_$Item Ledger Entry].[Remaining Quantity]) > 0) OR
(SUM([CRONUS USA, Inc_$Sales Line].[Outstanding Quantity]) > 0)

armela20 · 2008-01-11

ara3n,

I appreacite all of your info, however that query is not working form me.

Item No. Qty on ILE Qty on Sales Order
1000 160.00000000000000000000 208.00000000000000000000
1001 0.00000000000000000000 3.00000000000000000000

Here is the exact result I get when I run.
I only have 32 on hand, but I have 5 open sales orders, hence it is calculating my qty on ILE as 32 * 5.
The same with the qty on Sales order. I have 104, but I have 2 open ledger entries, hence it is calculating my qty on Sales Order as 104 * 2.
I copied and pasted your query to my sql reporting query area.
Thanks

ara3n · 2008-01-12

I looked at the numbers and you are right. I've modified the query and this should give you the results.

Select [CRONUS USA, Inc_$Item].No_, ISNULL(QtyinILE, 0) as QtyinILE ,isnull(QtyinSales,0)   as QtyinSales
FROM  [CRONUS USA, Inc_$Item] LEFT OUTER JOIN
(
SELECT [Item No_] as ItemNo, SUM([Remaining Quantity]) AS QtyinILE
FROM  [CRONUS USA, Inc_$Item Ledger Entry]
GROUP BY [Item No_]
) AS ILE ON [CRONUS USA, Inc_$Item].No_ = ILE.ItemNo
LEFT OUTER JOIN
(Select [No_] as SalesItemNo, SUM([Outstanding Quantity]) AS QtyinSales 
FROM [CRONUS USA, Inc_$Sales Line] 
where [Type] = 2
GROUP BY [No_] 
) AS Sales ON [CRONUS USA, Inc_$Item].No_ = Sales.SalesItemNo 
where (QtyinILE > 0) OR (QtyinSales > 0)

armela20 · 2008-01-14

That worked.
Thank you so much for your help.

ara3n · 2008-01-14

you are welcome.
Also to improve performance you should set a where clause for ILE only sum where Open = 1.

Also on qty on sales line you should set a where clause where "Document type" = 1 so that you only see orders.

Howdy, Stranger!

Quick Links

Categories

SQL reporting Services issue

Answers