Custom Aggregate Function - SSRS Report, AX 2012

(0) Share

Report

Posted on by Nehal Mistry

I have an SSRS report which basically takes data from a table and displays it in a table format.

What I want to do now is add a custom aggregate function, something more complex than SUM() or AVG(). I have an inquiry form within AX and I have accomplished this exact thing by using a display method and looping through the query. How would I do something similar on a report. Please assist me. I'm using AX 2012 R2.

Thanks,

Nehal

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Nehal

If the SSRS Report datasource is a Query, you can use the Display Field in the same way you use on AX rich client.

If the SSRS Report datasource is a Data Provider, you have to create the proper fields on the temporary table and set the value on DP class.

Was this reply helpful? Yes No
Nehal Mistry on at

Like (0)

Report

Hi Denis,

Thanks for replying.

The DataSource is a query.

Please clarify. In the rich client, the display method is on the form and I am looping through all the records in that display method to come up with the aggregate value. Where would I put this method for the report. Can I put this in the query itself?

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Nehal

You have to create the Display on Table side involve in the query.

Then, on text report control you have to set the proper datasource and choose a field or a Display field.

Was this reply helpful? Yes No
Nehal Mistry on at

Like (0)

Report

Yes, I do remember seeing a way to access the table display method from the report, however it probably won't work since it will be for just for a single record. I need someway to access all records that are shown in the report. Also, I don't want the values to be shown on each line, but only at the end of the report.

Thanks.

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Nehal

You can use a SSRS Data Method and execute a specific query that return a Datatable.

This Datatable can become a Dataset that can be use on your report Design.

Take a look at www.youtube.com/watch

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi Nehal,

You can create Expression in SSRS report it self.

As your requirement is to show the value in footer once you all records are displayed

so create a expression at report footer level

As you said your required calculation is complex..

it will be good if you explain your calculation requirement so that i can take a look and suggest what could be the expression and how to write it.

Please provide more information if you like.

Was this reply helpful? Yes No
Verified answer

Nehal Mistry on at

Like (0)

Report

Denis,

Thanks, I did previously see that video. Perhaps that could have worked for me but it would be nice if it's based on a query.

Nitesh,

Thanks. Yes I realize I did not specify enough details. I found that I could use a display method for each line, and then use SUM() to find the totals based on the line values. I'm sure I could have also used an expression value on the line rather than a display method. Overall, I did find a way to accomplish what I wanted.

I also found that I needed to use a PrecisionDesign to put the aggregate value at the bottom of the report. This didn't seem possible with the AutoDesign. This was fine by me.

Was this reply helpful? Yes No