How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

(0) Share

Report

Posted on by Don C.

Hi,

I'm trying to create a custom QRP report in RMS displaying the below columns:

Item Look Up
Description
Store
Supplier
Supplier Cost
Total Units Sold
Stock On Hand Qty

The main filter for the report is "Time" field in the transaction table, my main problem is that filtering the date will only result to items in itemdynamic table that has been sold in the transaction table. Is it possible to display all rows in itemdynamic table and show zero (0) in total units sold column if it's not been sold in the date filter criteria? Please find below the query I'm using for the report. Please note that I'm using a view to combine Transaction and TransactionEntry table to simplify the query.

TablesQueried = "FROM dbo.ItemDynamic WITH ( NOLOCK ) INNER JOIN ITEM WITH ( NOLOCK ) ON ItemDynamic.ItemID = ITEM.ID INNER JOIN Department ON Item.Departmentid = Department.id INNER JOIN Category ON category.Departmentid = item.Departmentid and category.id = item.categoryid INNER JOIN Store WITH ( NOLOCK ) ON [ItemDynamic].StoreID = Store.ID LEFT JOIN dbo.Supplier WITH ( NOLOCK ) ON ITEM.SupplierID = Supplier.ID LEFT JOIN SupplierList WITH(NOLOCK) ON Supplier.ID = SupplierList.SupplierID and Item.ID = SupplierList.ItemID LEFT JOIN VIEW_TRANSACTION_TRANSACTIONENTRY vTE WITH (NOLOCK) ON VTE.StoreID = Store.ID AND vTE.ItemID = ITEM.ID"

Thanks in advance.

*This post is locked for comments

All responses (10)

Answers (0)

Spencer McCandless 2,085 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Hey Don, glad you got it worked out. Just to clarify, I meant to include the "OR Datesold = '01/01/1900'" in the filter area rather than the selection criteria area, either by setting it manually each time it's run or by defining it in that section of the report. RMS seems to treat an OR operator in the filter as implying everything before it and everything after it as being in parentheses. So, you would have to put something like "Datesold Between '01/01/2012' and '01/31/2012' AND Department = 'Vitamins' OR Datesold = '01/01/1900' AND Department = 'Vitamins'" to get the data you want. Which would be a pain to set every single time the report was run, so if the hardcoded date calculation suits your needs, that's definitely the better solution.
Suggested answer

Don C. on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Thanks guys for all your help, I've tried Spencer's suggestion but as previously mentioned, this will still not produce the correct result because RMS doesn'provide a way to Group the criteria using parenthesis. So with the above suggestion, the SQL query result will become:

Date Sold >= '11/01/2012' and Date Sold <= '11/30/2012' or Date Sold = '01/01/1900' and etc..

This will still return items only sold within the specified range. The below criteria is the correct one and should return the desired result:

((Date Sold >= '11/01/2012' and Date Sold <= '11/30/2012') or Date Sold = '01/01/1900) and etc...'

Unfortunately, I'm not aware if this is possbile with RMS, so as an alternative solution offered to the client, I have hard-coded the date range to always return the total unit sold for 2 weeks and removing the "Date sold" as filter.

Here's the code:

//--- Report Summary --- //

Begin ReportSummary

ReportType = reporttypeItems

ReportTitle = "Range Ordering Report"

PageOrientation = pageorientationPortrait

WordWrap = False

ShowDateTimePicker = False

OutLineMode = True

Groups = 1

GroupDescription = "Total"

DisplayLogo = True

LogoFileName = "MyLogo.bmp"

ProcedureCall = ""

PreQuery1 = <BEGIN>

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewTransactionEntry') DROP VIEW ViewTransactionEntry

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewPORange') DROP VIEW ViewPORange

<END>

PreQuery2 = <BEGIN>

CREATE VIEW [dbo].[ViewTransactionEntry] AS

SELECT T.StoreID ,

TE.ItemID ,

SUM(TE.Quantity) AS UnitSold

FROM dbo.[Transaction] T

INNER JOIN dbo.TransactionEntry TE ON T.TransactionNumber = TE.TransactionNumber

AND T.StoreID = TE.StoreID

WHERE

T.Time >= DATEADD(dd, DATEDIFF(dd, 14, GETDATE()), 0) AND T.Time <= GETDATE() --This is the hardcoded value

GROUP BY TE.ItemID, T.StoreID

[GO]

CREATE VIEW [dbo].[ViewPORange] AS

SELECT PO.StoreID ,

POE.ItemID ,

SUM(CASE WHEN ( QuantityOrdered - QuantityReceivedToDate ) > 0

THEN ( QuantityOrdered - QuantityReceivedToDate )

ELSE 0

END) AS OnOrder

FROM PurchaseOrderEntry POE

INNER JOIN PurchaseOrder PO ON POE.PurchaseOrderID = PO.ID

AND POE.StoreID = PO.StoreID

WHERE (PO.POType <> 3 OR PO.POType <> 5)

AND PO.Status <> 2 GROUP BY PO.Storeid, POE.Itemid

<END>

TablesQueried = <BEGIN>

FROM ItemDynamic WITH ( NOLOCK )

INNER JOIN Item WITH ( NOLOCK ) ON ItemDynamic.ItemID = Item.ID

INNER JOIN Department ON Item.Departmentid = Department.id

INNER JOIN Category ON category.Departmentid = Item.Departmentid and category.id = Item.categoryid

INNER JOIN Store WITH ( NOLOCK ) ON [ItemDynamic].StoreID = Store.ID

LEFT JOIN Supplier WITH ( NOLOCK ) ON Item.SupplierID = Supplier.ID

LEFT JOIN SupplierList WITH(NOLOCK) ON Supplier.ID = SupplierList.SupplierID and Item.ID = SupplierList.ItemID

LEFT JOIN ViewPORange VPO WITH (NOLOCK) ON Store.ID = VPO.StoreID AND Item.ID = VPO.ItemID

LEFT JOIN ViewTransactionEntry VTE ON VTE.StoreID = ItemDynamic.StoreID AND VTE.ItemID = ItemDynamic.ItemID

<END>

SelCriteria = ""

GroupBy = "Store.ID, Item.ID"

SortOrder = ""

End ReportSummary

//--- Title Rows ---//

Begin TitleRow

Text = "<Store Name>"

Font = "Arial"

FontBold = True

FontSize = 16

Color = "Blue"

End TitleRow

Begin TitleRow

Text = "<Report Title>"

Font = "Arial"

FontBold = True

FontSize = 12

Color = "Black"

End TitleRow

Begin TitleRow

Text = "As Of: <Report Date>"

Font = "Arial"

FontBold = True

FontSize = 10

Color = "Black"

End TitleRow

//--- Filters ---//

Begin Filter

FieldName = "Item.BinLocation"

FilterOp = reportfilteropLesserEqual

FilterLoLim = "2"

FilterHilim = ""

FilterNegated = False

FilterConnector = reportfilterbooleanconAND

End Filter

//--- Columns ---//

Begin Column

FieldName = "Item.BinLocation"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Bin Location"

VBDataType = vbString

Formula = "MAX(Item.BinLocation)"

ColHidden = True

ColNotDisplayable = True

FilterDisabled = False

ColWidth = 1440

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Item.ItemLookUpCode"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Item Look Up Code"

VBDataType = vbString

Formula = "MAX(Item.ItemLookUpCode)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1440

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Item.Description"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Description"

VBDataType = vbString

Formula = "MAX(Item.Description)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 2535

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.ID"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Shop ID"

VBDataType = vbLong

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 870

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.Name"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Shop Name"

VBDataType = vbString

Formula = "MAX(Store.Name)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 2730

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Category.name"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Category"

VBDataType = vbString

Formula = "MAX(Category.name)"

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 2535

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Department.name"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Department"

VBDataType = vbString

Formula = "MAX(Department.name)"

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 2535

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Supplier.Code"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Supplier Code"

VBDataType = vbString

Formula = "MAX(Supplier.Code)"

ColHidden = True

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 1410

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Supplier.SupplierName"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Supplier"

VBDataType = vbString

Formula = "MAX(Supplier.SupplierName)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 2730

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "SupplierList.Cost"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Supplier Cost"

VBDataType = vbCurrency

Formula = "ISNULL(MAX(SupplierList.Cost),0)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 1275

GroupMethod = groupmethodNone

ColFormat = "#,###.##"

End Column

Begin Column

FieldName = "VTE.UnitSold"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Total units sold"

VBDataType = vbDouble

Formula = "ISNULL(MAX(VTE.UnitSold),0)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 1275

GroupMethod = groupmethodSum

ColFormat = "#.##"

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotQuantity"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "SOH qty"

VBDataType = vbDouble

Formula = "ISNULL(MAX(ItemDynamic.SnapShotQuantity),0)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 1005

GroupMethod = groupmethodSum

ColFormat = "#.##"

End Column

Begin Column

FieldName = "On_Order"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Stock on Order"

VBDataType = vbDouble

Formula = "ISNULL(MAX(VPO.OnOrder),0)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = True

ColWidth = 1200

GroupMethod = groupmethodSum

ColFormat = "#.##"

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotQuantityCommitted"

DrillDownFieldName = ""

DrillDownReportName = ""

StoreIDFieldName = ""

Title = "Units Committed"

VBDataType = vbDouble

Formula = "ISNULL(MAX(ItemDynamic.SnapShotQuantityCommitted),0)"

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1300

GroupMethod = groupmethodSum

ColFormat = "#.##"

End Column
Spencer McCandless 2,085 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

It's an ugly solution, but you could use the prequery to create a view out of the values you're already pulling unioned with a list of all items with a static 0 in the quantity sold column and some unique arbitrary date in the transaction time column. Set the view as the table queried, then include an "OR Date Sold = '01/01/1900'" (or whatever) in your filters when running the report.

It's definitely a pain, but it will get you the data until Jeff or someone provides a better solution. Although at some point it starts to make more sense to just pull the info using Administrator every time.
Jeff @ Check Point ... 13,380 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Danny,

The only thing there is is the Customization Guide, looking at and understanding the existing reports and lots o' practice!
Spencer McCandless 2,085 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Well, I've put my finger on part of the problem with the way you were going about it, which I think it what you were trying to say in your first reply.

The [transaction].time column is going to contain null values for anything that has never sold and values outside the specified date range for those that have sold but not within the specified date range. Neither of these are going to show up when date sold is filtered for.

I feel like the solution could be creating a separate view that sums the sales and joining it to the item or item dynamic table, but as was mentioned in the other thread, I don't think there's any way of to use supplied filters in constructing a view, so I'm not sure how to go about setting the dates to sum sales for. I'll think on this a bit more and post if I come up with anything.
Community Member Microsoft Employee on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Jeff

I like your saying "My dilemma, should I teach the man to fish or should I give the man a fish?"

Not directly related to this question but to making some Active Reports in general.

I would like to do more RMS Active Reports QRP, unfortunately their is very little information regarding making Active Reports besides the customization guide.

So I would like a fishing pole and some tackle "Active Report manual or guide" - so I could do some fishing.

Danny
Jeff @ Check Point ... 13,380 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

My dilemma, should I teach the man to fish or should I give the man a fish?

You are looking at the problem the wrong way. If an item hasn't been sold, it won't ever be in the transaction table. You should be looking at the item table first and then grab your other info from the appropriate tables.

All you are doing is the same as the Item Movement report does now. It doesn't show all unsold items either
Spencer McCandless 2,085 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Hm. Have you tried executing the query through Headquarters Administrator to see if it is pulling all of the information you want it to? If it's not pulling entries with null values for the transaction and transaction entry tables, we'll know the issue is in the construction of the query. If it does, then the issue is with the report itself, and we can focus on figuring out why it isn't displaying all results.
Don C. on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Thanks Spencer, I've tried your suggestion but the problem is still there. The reason I think is because the date filter will limit the query to items which was only sold within the date range. Is it possible to group the filters with parenthesis to include null dates? What I'm trying to achieve is to have a WHERE statement in SQL like the following:

WHERE (([Transaction].Time Is NULL) OR ([Transaction].Time >= '01/01/2012' AND [Transaction].Time <= '01/31/2012')) AND ETC...(Additional filters)

I tried adding the "Transaction.Time IS NULL" IN Selcriteria but the result is incorrect because the filters are not grouped in parenthesis.
Spencer McCandless 2,085 on at

Like (0)

Report

Re: How can I create a report from ItemDynamic Table and Transactions table with date filter in Transaction.Time field while displaying all items in Itemdynamics even if it's not been sold?

Hi. I haven't had the chance to sort through the logic of your query and see if the problem is there, but have you tried putting "ISNULL(vTE.quantitysold, 0)" or something similar in the formula area of the quantity sold column, so that it will replace null values with 0 for the purpose of determining how many units of each item were sold?