Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

(0) Share

Report

Posted on by Bryan Elias

280

Hi,

I'm am trying identify slow moving items that we currently have in stock. I noticed that the detailed sales report in theory should allow this, as it has all the filters i'm looking for (On Hand, Last Sold, Store ID), but once i use the "Last Sold" filter the report comes up blank. I'm running the report in headquarters in a multi-store environment. I know a custom report is probably not necessary, but does anyone have any idea why the filter is not working as it should?

Thanks in advance,

Bryan

*This post is locked for comments

All responses (6)

Answers (1)

Spencer McCandless 2,085 on at

Like (0)

Report

RE: Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

No problem. Happy to help. It looks like you've got it, but feel free to post here or email if you run into any more trouble.
Bryan Elias 280 on at

Like (0)

Report

RE: Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

Actually,

Seems like i've gotten it to work now by adding the criteria in at the top of the report as well. Thank you so much Spencer, it's extremely appreciated!
Bryan Elias 280 on at

Like (0)

Report

RE: Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

Thanks again. Now that i know the last sold filter should be "Itemdynamic.LastSold" as opposed to "item.LastSold" I did something similar with the Store Snapshot Quantity report . I'll paste the report below. It seems to be working well as i can look at each individual store and identify slow moving products that are still in stock, but ideally i'd like to be able to add department/category/supplier to further narrow down the report. I initially added them all but i got bound errors. I'm not really sure how these report languages work, i just tend to build them by trial and error so don't know how to fix that.

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

Begin ReportSummary

ReportType = reporttypeItems

ReportTitle = "Last Sold Quantitiy List"

PageOrientation = pageorientationPortrait

OutLineMode = True

Groups = 1

GroupDescription = "Total"

DisplayLogo = True

LogoFileName = "MyLogo.bmp"

ProcedureCall = ""

TablesQueried = <BEGIN>

FROM ItemDynamic WITH(NOLOCK)

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

LEFT JOIN Store WITH(NOLOCK) ON ItemDynamic.StoreID = Store.ID

<END>

SelCriteria = ""

GroupBy = ""

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 ---//

//--- Columns ---//

Begin Column

FieldName = "Store.Name"

DrillDownFieldName = "Store.Name"

DrillDownReportName = ""

Title = "Store"

VBDataType = vbString

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1700

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.ID"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store ID"

VBDataType = vbLong

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 870

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.StoreCode"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store Code"

VBDataType = vbString

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1095

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.Region"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store Region"

VBDataType = vbString

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1335

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.City"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store City"

VBDataType = vbString

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1020

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.State"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store State"

VBDataType = vbString

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1155

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.Zip"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store Zip"

VBDataType = vbString

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 945

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Store.Country"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Store Country"

VBDataType = vbString

Formula = ""

ColHidden = True

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1560

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Item.ItemLookUpCode"

DrillDownFieldName = "Item.ItemLookUpCode"

DrillDownReportName = ""

Title = "LookUp Code"

VBDataType = vbString

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1245

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Item.Description"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Description"

VBDataType = vbString

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1600

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotTime"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "SnapShot Date"

VBDataType = vbDate

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1395

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotQuantity"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Quantity"

VBDataType = vbDouble

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1000

GroupMethod = groupmethodSum

ColFormat = "#.##"

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotQuantityCommitted"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Committed"

VBDataType = vbDouble

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1200

GroupMethod = groupmethodSum

ColFormat = "#.##"

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotReorderPoint"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Reorder Pt"

VBDataType = vbDouble

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1100

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "ItemDynamic.SnapShotRestockLevel"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Restock Lvl"

VBDataType = vbDouble

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 1100

GroupMethod = groupmethodNone

ColFormat = ""

End Column

Begin Column

FieldName = "Itemdynamic.LastSold"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "Last Sold"

VBDataType = vbDate

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 900

GroupMethod = groupmethodNone

ColFormat = ""

End Column
Verified answer

Spencer McCandless 2,085 on at

Like (0)

Report

RE: Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

Well, fixing the issue with the field itself is beyond me, and would really be up to Microsoft, but I should be able to help you make a report to at least get the info you're looking for.

You could replicate the functionality the detailed sales report is supposed to have by using a prequery to create a view with the correct values and join it to the tables queried by the report. This wouldn't be too difficult, and I can post the report if you like, but it will contain a separate entry for each time each item was sold up to and including the last sold date, which may be kind of overkill if you just want a list of items that haven't sold in a while. Here's a quick report that will just list items with the last time they were sold at each store. Let me know if you need any help importing the report.

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

Begin ReportSummary
   ReportType = reporttypeSales
   ReportTitle = "Last Sold Report"
   PageOrientation = pageorientationLandscape
   WordWrap = False
   ShowDateTimePicker = False
   OutLineMode = True
   Groups = 1
   GroupDescription = ""
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   PreQuery1 = ""
   PreQuery2 = ""
   TablesQueried = <BEGIN>

      FROM        ITEM WITH (NOLOCK)
      LEFT JOIN   Department WITH(NOLOCK)
                  ON Item.DepartmentID = Department.ID
      LEFT JOIN   Category WITH(NOLOCK)
                  ON Item.CategoryID = Category.ID
      LEFT JOIN   Supplier WITH(NOLOCK)
                  ON Item.SupplierID = Supplier.ID
      LEFT JOIN   ItemDynamic WITH(NOLOCK)
                  ON Item.ID = Itemdynamic.ItemID
      LEFT JOIN   Store WITH(NOLOCK)
                  ON ItemDynamic.StoreID = Store.ID
<END>
   SelCriteria = ""
   GroupBy = ""
   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 = "Generated On <Report Date>"
   Font = "Arial"
   FontBold = True
   FontSize = 10
   Color = "Black"
End TitleRow

//--- Filters ---//

//--- Columns ---//

Begin Column
   FieldName = "Store.Name"
   DrillDownFieldName = "Store.Name"
   DrillDownReportName = ""
   Title = "Store Name"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1800
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.ID"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store ID"
   VBDataType = vbLong
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 870
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.StoreCode"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store Code"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1095
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.Region"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store Region"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1335
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.City"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store City"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1020
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.State"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store State"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1155
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.Zip"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store Zip"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 945
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Store.Country"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Store Country"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1560
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Department.Name"
   DrillDownFieldName = "Department.Name"
   DrillDownReportName = ""
   Title = "Department"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2205
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "Category.Name"
   DrillDownFieldName = "Category.Name"
   DrillDownReportName = ""
   Title = "Category"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1515
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "Supplier.SupplierName"
   DrillDownFieldName = "Supplier.SupplierName"
   DrillDownReportName = ""
   Title = "Supplier"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 825
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "Item.ItemLookupCode"
   DrillDownFieldName = "Item.ItemLookupCode"
   DrillDownReportName = ""
   Title = "Item"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1380
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "Item.BinLocation"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Bin Location"
   VBDataType = vbString
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1140
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "Item.Description"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Description"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 2115
   GroupMethod = groupmethodNone
   ColFormat = ""
   ColAlignment = flexAlignLeftCenter
End Column

Begin Column
   FieldName = "Item.Quantity"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "On Hand"
   VBDataType = vbDouble
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 810
   GroupMethod = groupmethodNone
   ColFormat = "#.#"
End Column

Begin Column
   FieldName = "Item.Price"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Price"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 555
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.PriceA"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Price A"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 720
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.PriceB"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "PriceB"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 660
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.PriceC"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Price C"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 720
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.SalePrice"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Sale Price"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 945
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.SaleStartDate"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Sale Starts"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1020
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.SaleEndDate"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Sale Ends"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 930
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.MSRP"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "MSRP"
   VBDataType = vbCurrency
   Formula = ""
   ColHidden = True
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 945
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Itemdynamic.LastSold"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Last Sold"
   VBDataType = vbDate
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 900
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column
Bryan Elias 280 on at

Like (0)

Report

RE: Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

Thanks very much Spencer,

I think you may be on to something. After running the query it seems that every sku is listed on our system on the results tab with a corrected value. How can I go about correcting this?
Spencer McCandless 2,085 on at

Like (0)

Report

RE: Last Sold Custom Report/ Last Sold Filter not working in Detailed Sales

I can't promise this is the problem, but I've found that for whatever reason the item.lastsold field (which the detailed sales report filters by) in Headquarters isn't always automatically updated with the largest value in the itemdynamic.lastsold field (which holds individual store data) for that itemid. If you're comfortable running queries in HQ Administrator, you could run something like:

select item.itemlookupcode, item.description, item.lastsold as wrongvalue, item2.correctvalue from item with (NOLOCK) join (select itemid, max(lastsold) as correctvalue from itemdynamic with (NOLOCK) group by itemid) as item2 on item.id = item2.itemid where item.lastsold <> item2.correctvalue

to see if this is the case in your environment. If anything comes up, then this may be the issue.