Display method on query range

(0) Share

Report

Posted on by TonyAx

570

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

I have the same question (0)

All responses (7)

Answers (0)

André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
TonyAx 570 on at

Like (0)

Report

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;

    }

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

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?

Was this reply helpful? Yes No
TonyAx 570 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Mark Boy2 506 on at

Like (0)

Report

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

Was this reply helpful? Yes No