AX 2012: Add Report Parameter to SSRS Query Report

Posted on

In this post we’ll learn how to add report parameter and show it in the report dialog for a Query based SSRS report. Please follow the development steps below to achieve it:

1. Create an AOT Query MAKCustTable.
2. Drag CustTable table to the Data Sources node of the query.
3. Set Dynamic property to Yes on the Fields node to add all the fields available in the table quickly.
4. Keep the following fields only and remove others. Then set Dynamic property to No.

Untitled

5. Open Visual Studio to create a new Project of type Report Model using Microsoft Dynamics AX installed template.
6. Add report to the project MAKParametersReport.
7. Add Dataset to the report and choose the query MAKCustTable we have just created.

Untitled

8. Drag the dataset MAKCustTable to the Designs node. This will create an AutoDesign layout and Table control with fields added.

Untitled

9. Now Build and Deploy the report from Visual Studio.
10. After successful deployment, you should find MAKParametersReport in the AOT > SSRS Reports
11. Create an output menu item for the newly created SSRS report.
12. Open the report using menu item.

Untitled

13. By default, Customer account is the only report parameter in the dialog.
14. Now to add more report parameters, update the AOT query MAKCustTable to have the following ranges added:

Untitled

15. Back in Visual Studio, refresh the dataset.

Untitled

16. Save, Build and Deploy the report again from Visual Studio.
17. To see the changes, open the report using the existing output menu item.

Untitled


For your information, the MAKCustTable_DynamicParameter collectively holds all the ranges defined in the Dataset’s query. Make sure its properties are set as shown here:

Untitled

Advertisements

4 thoughts on “AX 2012: Add Report Parameter to SSRS Query Report

    Shreedhara said:
    February 22, 2015 at 11:08 am

    Dear Muhammad,

    Thanks for the information,

    I just want to know instead of select can user directly enter their values in the parameter fields.

    Regards
    Shreedhara

      muhammadanaskhan responded:
      February 22, 2015 at 6:34 pm

      Hi Shreedhara,

      Yes. User can enter values directly into the fields. Make sure to validate the values before running the report in the UI Builder class.

    Sukrut said:
    April 15, 2015 at 1:40 am

    HI Muhammad, I added some ranges in my query but those are not reflecting on dialog . I did same steps which are mentioned in the blog but not able to see ranges on Dialog. I did SSRS service restart , Deleted Usage data , SRSReportQuery table nothing worked .. If you know any other option please let me know . Thanks

      muhammadanaskhan responded:
      April 15, 2015 at 3:58 pm

      Hi Sukrut,

      Is your report gets data from AOT query or from RDP class? I would like to know the design of your report to resolve the issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s