Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

Posted on by 2

We need to adjust some of the ItemLookup codes to reflect "Item is Taxable" and I can't find a qrp report that shows this information so we have a list to work from. Anyone know the field name to add this to an existing report, or offer any help?

 

Thanks, Rick

*This post is locked for comments

  • Rick Kirkman Profile Picture
    Rick Kirkman 2 on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Fernando... Brilliant! This was exactly what I needed! This column, added to the previous report you suggested, gave me a list of items with the "ITEM IS TAXABLE" box unchecked. Works both ways. Thank you!

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Rick - I don't have access right now to a store ops database, but I think if you add this column to the report I sent you, you can filter by taxable / non-taxable. I am almost positive the column is TAXABLE, but you can confirm this by running sp_columns item to view all the columns in the item table. Hope this helps.

    Begin Column

      FieldName = "Item.Taxable"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Taxable"

      VBDataType = vbBoolean

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1057

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignCenterCenter

    End Column

  • Rick Kirkman Profile Picture
    Rick Kirkman 2 on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Using version 2.0.1004. I will try this and post back if it works. Thanks!

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    or SELECT i.ItemLookupCode, i.Description, i.ExtendedDescription FROM Item i WHERE i.Taxable = 1

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Make a Query on DBase of RMS

    I dont know what version u have but in Version 2 u can obtain all items that pay Tax

    just running this

    SELECT i.ItemLookupCode, i.Description, i.ExtendedDescription FROM Item i WHERE i.TaxID<>0

  • Rick Kirkman Profile Picture
    Rick Kirkman 2 on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Thanks Jeff and Fernando - two good responses, but I'm not sure if I'm explaining what I need correctly. In the attached screenshot, under Department and Category, the checkbox for "Item is Taxable". That's what I need to create a report on, if that box is checked, or not. Am I missing something in the information you've provided, as to how to call this out in a report?

    Thanks again.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Rick - see if this works for you; will also show what tax assignment the item has. Cheers

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

    Begin ReportSummary

      ReportType = reporttypeItems

      ReportTitle = "Store Operations Item Tax Assignment"

      PageOrientation = pageorientationLandscape

      OutLineMode = True

      Groups = 1

      GroupDescription = ""

      DisplayLogo = True

      LogoFileName = "MyLogo.bmp"

      ProcedureCall = ""

      TablesQueried = <BEGIN>

         FROM  Item WITH(NOLOCK)

         LEFT JOIN   ItemTax WITH(NOLOCK)

                     ON Item.TaxID = ItemTax.ID

         LEFT JOIN Tax WITH(NOLOCK)

    ON ItemTax.TaxID01 = Tax.ID

         LEFT JOIN Department WITH(NOLOCK)

    ON Item.DepartmentID = Department.ID

         LEFT JOIN Category WITH(NOLOCK)

    ON Item.CategoryID = Category.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 = "ItemTax.Description"

      DrillDownFieldName = "ItemTax.Description"

      DrillDownReportName = ""

      Title = "Item Tax"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1365

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "ItemTax.Code"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Item Tax Code"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1365

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Tax.Description"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Sales Tax Desc"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1365

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Tax.Percentage"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Tax %"

      VBDataType = vbDouble

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1000

      GroupMethod = groupmethodNone

      ColFormat = "#.###"

    End Column

    Begin Column

      FieldName = "Department.Name"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Department"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1560

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Category.Name"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Category"

      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 = "Item.Cost"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Cost"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1100

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Item.Price"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Price"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1100

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Item.Quantity"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "On Hand"

      VBDataType = vbDouble

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 800

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Item.DateCreated"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Date Created"

      VBDataType = vbDate

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1000

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "Item.LastSold"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Last Sold"

      VBDataType = vbDate

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1000

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

  • Jeff @ Check Point Software Profile Picture
    Jeff @ Check Point ... 13,380 on at
    RE: Looking for a Report that shows Items with "Item is Taxable" checked or unchecked.

    Rick,

    Couple of different ways to see this.

    HQ Manager | Maintenance mode | Wizards | 320 Adjust Item Sales Tax | Item Filter button | Filter on Tax <> <Not Assigned> for items with Tax or = <Not Assigned> for items without tax

    OR

    Item Snapshot List report | Filter on Tax <> <Not Assigned>

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,912 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,355 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans