Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to create Reports ...
Microsoft Dynamics AX (Archived)

How to create Reports using SQL code

(0) ShareShare
ReportReport
Posted on by mtrapnell 85

I know SQL really well but am new to AX reporting.  We need a number of reports that pull data using many different tables.  I can pull the data using left/outer joins in SQL but cannot figure out how to pull it in AX 2012R2 using Queries or RDP Classes.  We need to use AX functionality in order to keep property user security over the reports (otherwise I would use an Excel ODBC connection.)

Is there a way to use SQL queries to create secure SSRS reports (or maybe an Excel Add-In?)  Can I somehow embed them in an RDP Class?

Thank you very much for your help with this!  

*This post is locked for comments

  • Suggested answer
    Kumar Gaurav @ MS Profile Picture
    Kumar Gaurav @ MS on at
    RE: How to create Reports using SQL code

    If you are planning to utilize ax security then using a direct sql code doesn't seems to be a good idea, you can definitely create an outer join in AX though.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to create Reports using SQL code

    Yes, you can do it using RDP Class.

    I hope you understand how RDP class work with SSRS Report, this class basically returns a dataset to the SSRS report,

    So what you can do is

    Create a Report Data Provider class in AX and extend it to SRSDataProviderBase

    In processReport() method write you logic

    as part of your logic

    create SQL connection Object

    and execute the SQL string as per you SQL Query

    this SQL connection execute will return ResultSet

    loop through the ResultSet and insert it into temporary table which RDP call will return to the SSRS report.

    Please verify and let me know if you need more help to implement your logic.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to create Reports using SQL code

    For the first question -> Yes you can pull data from AX using Queries or RDP class.

    Using queries,

    1. Define a AOT Query

    2. In Visual Studio, in the Dataset map this query

    Using RDP,

    There are lot of articles floating in the internet on creating a Report using a DataContract and Controller.

    For your second question,

    You could try using the Document services. There are two types, one is Vanilla document service which allows you to access data from Excel. Another is Odata (only the protocol format is different, but still a web service)

    Good article from Murray Fife for Odata Document services using Excel : dynamicsaxtipoftheday.com/.../publish-queries-as-odata-document-sources-for-users-to-query-in-excel

    I would suggest to take a look at this book. www.packtpub.com/.../book.

    Regards,

    Saravanan L | Enterprise Architect | MCT | My Blog

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,820 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,514 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans