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.
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.
Thanks & Regards
Respect the provided answers from other community members. Don't provide or do not copy the same answer to the same thread. Additional insights are welcome!
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?
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.
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.
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
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.
If this post helps you, Please verify this answer and earn Thank you Badge for yourself (Follow this link for more details https://community.dynamics.com/braggerbadges/badge/48.aspx)
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.
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.