Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Report Help - View, Ta...
Microsoft Dynamics RMS (Archived)

Report Help - View, TableQueried or SelCriteria?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Would like to create a custom QRP report, where I would put the query enclosed below. Would it go in the TablesQueried or SelCriteria section of the report or would I have to create a view. Any help would be appreciated.

Thanks

Danny

SQL Query used:

select itemlookupcode, description, departmentid, categoryid, quantity, inactive from item where convert(varchar, lastupdated, 111) < '2010/12/31' and lastsold <> '' and quantity <= 0 and departmentid = 7 and not exists(select transactionentry.itemid from transactionentry, [transaction]  where transactionentry.transactionnumber = [transaction].transactionnumber and  item.id = transactionentry.itemid and convert(varchar, [transaction].time, 111) > '2010/12/31')

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Report Help - View, TableQueried or SelCriteria?

    Hi Danny,

    Try to look at the RMS Standard Report (Items - Item Movement Report.qrp) as a sample template.  It uses SQL Query to create a VIEW.

    Hope it helps.

    Hotrod

  • Suggested answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: Report Help - View, TableQueried or SelCriteria?

    Hello, there are many things you need to know more before writing your own report.

    Please be reminded to these:

    1. TablesQueried is where you put your from clause

    2. SelCriteria is where you put your where clause

    3. When specifying a column to be used, make sure the column exist from the given FROM CLAUSE.

    TablesQueried = "From Item"

    SelCriteria =<BEGIN>convert(varchar, lastupdated, 111) < '2010/12/31' and lastsold <> '' and quantity <= 0 and departmentid = 7 and not exists(select transactionentry.itemid from transactionentry, [transaction]  where transactionentry.transactionnumber = [transaction].transactionnumber and  item.id = transactionentry.itemid and convert(varchar, [transaction].time, 111) > '2010/12/31')

    <END>

     

     

    then you can specify columns with this format:

     

    Begin Column
    FieldName = "Itemlookupcode"
    DrillDownFieldName = ""
    DrillDownReportName = ""
    Title = "Item Code"
    VBDataType = vbString
    Formula = ""
    ColHidden = False
    ColNotDisplayable = False
    FilterDisabled = False
    ColWidth = 1365
    GroupMethod = groupmethodNone
    ColFormat = ""
    End Column

     

    Note: 

    FieldName  is the column name of your "FROM CLAUSE"

    DrillDownFieldName are unique only to some RMS table columns such as Department, Category, supplier etc. Check the manual.

    VBDataType, from the word itself, the data type. Either string as vbString, money as vbCurrency, number as vbInteger etc etc.

    Formula, only used when casting,  calculating, converting,  calling an sql function etc.

     

    Hope this helps.

     

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans