Question Status

Verified
Nehal Mistry asked a question on 24 Mar 2014 10:35 AM

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

Reply
Denis Macchinetti responded on 24 Mar 2014 10:55 AM

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.

Thanks & Regards

Denis Macchinetti

Senior Technical Architect

Reply
Nehal Mistry responded on 24 Mar 2014 11:02 AM

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?

Reply
Denis Macchinetti responded on 24 Mar 2014 11:06 AM

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.

Thanks & Regards

Denis Macchinetti

Senior Technical Architect

Reply
Nehal Mistry responded on 24 Mar 2014 11:23 AM

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.

Reply
Denis Macchinetti responded on 24 Mar 2014 2:33 PM

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

Thanks & Regards

Denis Macchinetti

Senior Technical Architect

Reply
Suggested Answer
Nitesh Ranjan responded on 24 Mar 2014 5:48 PM

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.


Please update with your feedback.

Regards, NITESH RANAJN (PMP) | Dynamics AX Consultant

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)

Reply
Verified Answer
Nehal Mistry responded on 3 Apr 2014 1:20 PM

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.

Reply
Verified Answer
Nehal Mistry responded on 3 Apr 2014 1:20 PM

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.

Reply
Suggested Answer
Nitesh Ranjan responded on 24 Mar 2014 5:48 PM

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.


Please update with your feedback.

Regards, NITESH RANAJN (PMP) | Dynamics AX Consultant

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)

Reply