SQL 2005 Reporting Services

[MoeSSRS]

I created a report to track the whole inventory in stock area. It seems I am getting all the results I want but I am missing records when the quanity on hand is 0. Let me now how to get those records. Shall I write any condition expression or anything else. I have these fields as follows on the report. Appreciate your quick response and any help and suggestions.

1) Stock Area
2) Stock Location
3) Part#
4) Part Desc
5) Qty On Hand
6) Unit Cost

 

[RiverGuy]

Can you post your source query?

 

[MoeSSRS]

Here is the SQL Script: You will see lots of fields and tables in this script but the only fields I am using right now is STKAREA,STKLOC,PRTNO,PRTDESC,QTY,BILLCST and the tables are as follows: PRTAREA,PRTLOC,PRTCAT,PRTLTSK. The rest is there in case if I need. Thanks and let me know.


SELECT INVENTORY.PRTAREA.STKAREA, INVENTORY.PRTLOC.STKLOC, INVENTORY.PRTCAT.PRTNO, INVENTORY.PRTCAT.PRTDESC,
INVENTORY.PRTLOT.UNITCST, INVENTORY.PRTLOT.QTYUSED, INVENTORY.PRTLOT.QTYRECD, INVENTORY.PRTSTK.LSTISSQTY,
INVENTORY.PRTSTK.LSTRECDQTY, INVENTORY.PRTSTK.REORDLEV, INVENTORY.PRTSTK.CSTMETHOD, INVENTORY.PRTSTK.ORDLEVFAC,
INVENTORY.PRTSTK.QTYPERUOI, INVENTORY.PRTSTK.REORDQTY, INVENTORY.PRTSTK.AVERAGECST, INVENTORY.PRTSTK.QTYPERHT,
INVENTORY.PRTCAT.LSTPURDATE, INVENTORY.PRTLOT.RECDDTTM, INVENTORY.PRTSTK.LSTISSDATE, INVENTORY.PRTLOT.ADDDTTM,
INVENTORY.PRTLTSK.QTY, INVENTORY.PRTCAT.BILLCST
FROM INVENTORY.PRTLOC INNER JOIN
INVENTORY.PRTSTK ON INVENTORY.PRTLOC.STKLOCKEY = INVENTORY.PRTSTK.STKLOCKEY INNER JOIN
INVENTORY.PRTCAT ON INVENTORY.PRTSTK.PRTKEY = INVENTORY.PRTCAT.PRTKEY INNER JOIN
INVENTORY.PRTLOT ON INVENTORY.PRTCAT.PRTKEY = INVENTORY.PRTLOT.PRTKEY INNER JOIN
INVENTORY.PRTAREA ON INVENTORY.PRTLOC.STKAREAKEY = INVENTORY.PRTAREA.STKAREAKEY AND
INVENTORY.PRTSTK.STKAREAKEY = INVENTORY.PRTAREA.STKAREAKEY INNER JOIN
INVENTORY.PRTLTSK ON INVENTORY.PRTSTK.STKKEY = INVENTORY.PRTLTSK.STKKEY AND
INVENTORY.PRTLOT.STKLOTKEY = INVENTORY.PRTLTSK.STKLOTKEY
GROUP BY INVENTORY.PRTAREA.STKAREA, INVENTORY.PRTCAT.PRTNO, INVENTORY.PRTCAT.PRTDESC, INVENTORY.PRTLOT.UNITCST,
INVENTORY.PRTLOT.QTYUSED, INVENTORY.PRTLOT.QTYRECD, INVENTORY.PRTSTK.LSTISSQTY, INVENTORY.PRTSTK.LSTRECDQTY,
INVENTORY.PRTSTK.REORDLEV, INVENTORY.PRTSTK.CSTMETHOD, INVENTORY.PRTSTK.ORDLEVFAC, INVENTORY.PRTSTK.QTYPERUOI,
INVENTORY.PRTSTK.REORDQTY, INVENTORY.PRTSTK.AVERAGECST, INVENTORY.PRTSTK.QTYPERHT, INVENTORY.PRTCAT.LSTPURDATE,
INVENTORY.PRTLOT.RECDDTTM, INVENTORY.PRTSTK.LSTISSDATE, INVENTORY.PRTLOT.ADDDTTM, INVENTORY.PRTLOC.STKLOC,
INVENTORY.PRTLTSK.QTY, INVENTORY.PRTCAT.BILLCST
HAVING (INVENTORY.PRTAREA.STKAREA = @STKAREA)

 

[RiverGuy]

You are using all INNER JOINS. That means a match must be found in each table to be returned.

If the table that has the Qty On Hand column does not have records for parts when there is 0 on hand, then it won't be picked up. If this is the case, you need to start with your table which has your list of Parts. Then outer join your table to get Qty On Hand. If a NULL is returned for Qty On Hand, you case use the ISNULL function (QtyOnHand = ISNULL(QtyOnHand, 0), and default a NULL to 0.

 

[MoeSSRS]

Thanks and let me know try this and let you know soon. Thanks and talk to you later.

 

[RiverGuy]

Good luck!

 

[MoeSSRS]

This one I have to put this code in the report layout text field section for the quantity. Let me know is this correct.

QtyOnHand = ISNULL(QtyOnHand, 0), and default a NULL to 0

 

[RiverGuy]

No, I was giving you a description of what to do.

I'm not sure what column corresponds to your "Quantity On Hand" figure.

Looking at your query, let's say its the INVENTORY.PRTLTSK.QTY column. Instead of having INVENTORY.PRTLTSK.QTY in your SELECT list, you would have the following: QTY = ISNULL(INVENTORY.PRTLTSK.QTY, 0). This would replace the output of your QTY column with a 0 if QTY was NULL.

 

[MoeSSRS]

What I was aksing is that on my Sql Report I have 3 tabs one is the Data tab where you could see the tables and SQL Script, The Layout tab shows the design, grouping, fields names, the 3rd one is the preview. When I click on the Layout Tab and click on the QTY field and this expression on the properties. Do you think is this works or you want me to put on the script itself on the Data tab. Let me know thanks.

 

[RiverGuy]

In the Data tab. You're going to have to re-do your whole query anyways if you need to change your JOIN type.

 

[MoeSSRS]

Ok let me create a new script and see how it works. Thanks and I will let you know tomorrow and thanks for the great help. Talk to you later.

 

[MoeSSRS]

The SQL Script I send it to you before is lots of fields we are not using. Here is the new Script below.Right now I took out the fields I dont need it and keep only tables I needed. I am using these four main tables and other tables are just for links. Here is the tables below:
1) PRTAREA (Fields coming from this table is STKAREA)
2) PRTLOC (Fields coming from this table is STKLOC)
3) PRTCAT (Fields coming from this table is PRTNO,PRTDESC,BILLCST
4) PRTLTSK(Fields coming from this table is QTY)

Here is the new script below. Take a look at it and let me know where and what I have put on the script to show me 0. Everything is coming out right except not showing the whole row if its 0 QTY on the QTY field.Just a questions can I also write an expression within the QTY field with the IIF statement. Let me know what is the best way to resolve this issue. Thanks and appreciated.Do you do Webex so that I could show you what I am doing here. Thanks and let me know.


SELECT INVENTORY.PRTAREA.STKAREA, INVENTORY.PRTLOC.STKLOC, INVENTORY.PRTCAT.PRTNO, INVENTORY.PRTCAT.PRTDESC,
INVENTORY.PRTLOT.UNITCST, INVENTORY.PRTLOT.QTYUSED, INVENTORY.PRTLOT.QTYRECD, INVENTORY.PRTLOT.RECDDTTM,
INVENTORY.PRTLTSK.QTY, INVENTORY.PRTCAT.BILLCST
FROM INVENTORY.PRTLOC INNER JOIN
INVENTORY.PRTSTK ON INVENTORY.PRTLOC.STKLOCKEY = INVENTORY.PRTSTK.STKLOCKEY INNER JOIN
INVENTORY.PRTCAT ON INVENTORY.PRTSTK.PRTKEY = INVENTORY.PRTCAT.PRTKEY INNER JOIN
INVENTORY.PRTLOT ON INVENTORY.PRTCAT.PRTKEY = INVENTORY.PRTLOT.PRTKEY INNER JOIN
INVENTORY.PRTAREA ON INVENTORY.PRTLOC.STKAREAKEY = INVENTORY.PRTAREA.STKAREAKEY AND
INVENTORY.PRTSTK.STKAREAKEY = INVENTORY.PRTAREA.STKAREAKEY INNER JOIN
INVENTORY.PRTLTSK ON INVENTORY.PRTSTK.STKKEY = INVENTORY.PRTLTSK.STKKEY AND
INVENTORY.PRTLOT.STKLOTKEY = INVENTORY.PRTLTSK.STKLOTKEY
GROUP BY INVENTORY.PRTAREA.STKAREA, INVENTORY.PRTCAT.PRTNO, INVENTORY.PRTCAT.PRTDESC, INVENTORY.PRTLOT.UNITCST,
INVENTORY.PRTLOT.QTYUSED, INVENTORY.PRTLOT.QTYRECD, INVENTORY.PRTLOT.RECDDTTM, INVENTORY.PRTLOC.STKLOC,
INVENTORY.PRTLTSK.QTY, INVENTORY.PRTCAT.BILLCST
HAVING (INVENTORY.PRTAREA.STKAREA = @STKAREA)