Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Display method on quer...
Microsoft Dynamics AX (Archived)

Display method on query range

(0) ShareShare
ReportReport
Posted on by TonyAx 562

Hi Guys,

I have a display method in Vend Trans Table displayDiscountDate() which gives the cash discount date for the vendor transactions. I want to give this display method in Query range in ssrs report. I tried by giving it but  can't be done, because queries are run on SQL Server while display methods are defined in Dynamics AX.  Can you please advise on this. I heard about sysqueryrangeUtil class but not sure how to use that. Please help me

        query = new Query();

        qbdsvendTrans = query.addDataSource(tableNum(vendTrans), tableStr(vendTrans));

        qbdsvendTrans.addRange(fieldNum(VendTrans, displayDiscountDate())).value(queryRange(fromdate,todate));

*This post is locked for comments

  • Suggested answer
    Mark Boy2 Profile Picture
    Mark Boy2 490 on at
    RE: Display method on query range

    You can move the logic about display method to your report .. and  Create a variable to receive it

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Display method on query range

    As we already noticed, Cash discount date is calculated with quite a complex logic.

    There is no way around that.

    And coming up with such a new filter requirement when your report development is already done is quite difficult. Because you might need to change your report dataset from VendTrans to a temp table.

    My suggestion is that you try to come up with some rule regarding VendTrans.TransDate, that you can use to limit your initial dataset.  (for example: criteria dates from the user +- 30 days. But you need to discuss this with your customer, I can't tell you what is the correct criteria for you). Then, iterate this dataset, call the CashDiscDate display method and discard records where the value doesn't match the criteria from the user.

    Also please estimate the cost for this, and ask if it's more valuable to your customer than the cost.

  • TonyAx Profile Picture
    TonyAx 562 on at
    RE: Display method on query range

    My Requirement is to create a ssrs report which display the vendor cash discount with Transaction. Report development is completed. Intially i did with Trans date In contract class they want from date and todate which should pull value from Cashdiscount date field which is a display method in VendTrans Table. I am looking for workaround to solve this.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Display method on query range

    Unfortunately I can't spend time to do the actual development for you. But you are on the right track.

    You need to add CustVendTransOpen and CustVendSettlement in your query, and utilize the same logic that you see in CustVendTransDetails\firstCashDiscDate (and the other methods that are called from there).

    As you can see it's quite a complex task, so you might also ask if resolving this requirement adds more value than it costs.

    One possibility for your report is that you don't attempt to filter by "displayDiscountDate" during query execution time, instead you run this method for all fetched records.Then, pass only the records that pass your check into a temp table that you will use as a data source for your report.

    But since this has a high performance price, you can't do it if your query returns a lot of records. You need to test it yourself to verify if this is a valid approach for your customer scenario and their data volumes.

    Maybe you could share your business requirement and we can discuss if there are easier ways to solve it?

  • TonyAx Profile Picture
    TonyAx 562 on at
    RE: Display method on query range

    Thanks Andre and Nikolaos for your reply. I have this display method code but not sure how i have to use this in my query. Can you please help me on this.

     display CashDiscdate displayDiscountDate()

        {

            CustVendTransDetails custVendTransDetails = new CustVendTransDetails(this);

            return custVendTransDetails.firstCashDiscDate();

        }

     CashDiscdate firstCashDiscDate()

        {

            CashDiscdate cashDiscdate;

     

            this.fetchCustVendTransOpenSum();

     

            if (custVendTransOpenSum.CashDiscDate)

            {

                cashDiscdate = custVendTransOpenSum.CashDiscDate;

            }

            else

            {

                this.fetchCustVendSettlementSum();

                cashDiscdate = custVendSettlementSum.CashDiscDate;

            }

            return cashDiscdate;

        }

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Display method on query range

    Display methods can't be used in query ranges or select statements. Display methods are used to show some data based on a record that you already have. This means they can be executed after you have already retrieved your data. Because of this, you can't use them to filter out data.

    So, as Andre already said, you need to look into the display method, see what logic is in it and then try to figure out if you can implement similar logic into your query.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,269 Super User 2024 Season 2 on at
    RE: Display method on query range

    Hi Tony,

    You cannot add ranges on display methods. You can try to follow the logic in this display method to extend the query with the correct table and fields to be able to do filtering. If it contains some conditional logic, then you can consider creating a view with a computed column where you can add the field using T-SQL.

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,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans