Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Looking for a Report t...
Microsoft Dynamics RMS (Archived)

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

(0) ShareShare
ReportReport
Posted on by Rick Kirkman 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

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,431 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,503 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans