Expert SSRS with AX Tip from Michael Stashwick
One of the things I like to do, from time to time, is have guest bloggers appear here. This is to help bring more and more relevant content to you, the readers of this blog.
A new person, to the blogging ring this time is a fellow peer at Sunrise Technologies, that goes by the name of Michael Stashwick. He is a Senior Technical Architect, like myself, and have a lot of passion for AX and Technology.
One area that Michael seems to focus on a lot is BI and AX. I have wrote a lot over the years about BI, from concepts, to specific technical howto's. Today falls into that howto or technical tip area.
Without getting to much into the debate of how SSRS should be used with Dynamics AX, Michael choose for his first guest post to focus on using native SSRS reports with AX.
This is not the Dynamics AX SSRS Visual Studio focused reports, but rather trying to take and offer best of both worlds. So with that, I will give the "blogging stick" over to Michael.:
---
A common debate in every Dynamics AX implementation is, “What tool should I use for custom reporting?” For many years, the battle was between using native MorphX reporting, and SQL Server Reporting Services (SSRS), a widely used tool that can be used against any SQL database. In AX 2009, another contender emerged, AX 2009 SRS, which is a hybrid of the previous two choices.
AX 2009 SRS reports is a SQL reporting option that can access Dynamics AX business logic, as well as inherit AX security--and run natively in Dynamics AX, which is one of the best arguments as this reduces end user confusion in their search for data. Unfortunately, AX 2009 SRS reports void the great functionality found in Report Manager (report subscriptions, caching, file share, e-mailing, et al), which is one of the reasons to go down the road of SQL reporting in the first place.
With a little ingenuity, SSRS reports can appear to run natively in a Dynamics AX 2009 environment, which not only provides the most professional, native aesthetic, but in my opinion, make SSRS the best reporting option available.
First, open Visual Studio and create a new Dynamics AX Reporting project. Give the project a meaningful name, one that will ultimately be the same file name as the SSRS report.
Next, deploy the blank project to Dynamics. This will create an AX 2009 Report Library in the AOT.
This is the aforementioned ingenuity. Report Libraries reference files [only] in the Dynamics folder in Report Manager. The file name is the only connection between the deployed report and library.
Now all we have to do is create a SSRS report in Business Intelligence Development Studio (BIDS) with the same Report Definition Language (RDL) filename as the Report Library, which is ExampleSSRS in this case.
Create a new SSRS report in BIDS, or preferred application. Remember that the SSRS report project can be any meaningful name, but the report file itself must match the name of the Report Library.
Upload the RDL to the Dynamics folder in Report Manager. If the proper target server URL is setup in the Report Server Project in BIDS, it can also be deployed to Report Manager from there.
Next, in AX, create an output menu object. Make sure that the object type is SQLReportLibraryReport, and that the object is the name of the Report Library.
Now, the menu object can be placed in an AX menu, or called via x++. Parameters can still be passed through the URL in an Args object, albeit rendering format, report values, or command parameters (refer to URL Access on MSDN MSDN - SSRS URL Access), just disclude the portion of the report server and name.
The following example runs the report, renders it in HTML, leaves the toolbar visible, but hides any parameters:
When the report is run, it will appear natively in an AX 2009 window, and no one will know the report was actually written in SQL.
---
Well I would like to thank Michael for taking the time and sharing this nice tip. I hope that this is just the first from Michael, and we hear about many more tips and tricks when it comes to BI / Reporting related topics for Dynamics AX.
Here is Michael's Bio:
Michael Stashwick is a Senior Technical Architect at Sunrise Technologies. He has been working with Dynamics AX since version 3.0 in tandem with many Business Intelligence projects focusing on SSRS and SSAS for Dynamics AX.
Michael has a bachelor's degree in Business Information Technology with focuses on Decision Support Systems and Operations Management from Virginia Polytechnic Institute and State University. He is also a Microsoft Certified Business Management Solutions Professional.
That's it for now. Make sure to follow me on twitter.: DynamicsERP on Twitter For next week, as I bring you live coverage of the Microsoft Dynamics AX 2011 Technical Conference. (#DAXCONF). I will also be creating a lot of new blog post, and guest columns throughout the Dynamics Community sites, based on the contect from next week. See you then!
"Visit the Dynamics AX Community Page today!"
A new person, to the blogging ring this time is a fellow peer at Sunrise Technologies, that goes by the name of Michael Stashwick. He is a Senior Technical Architect, like myself, and have a lot of passion for AX and Technology.
One area that Michael seems to focus on a lot is BI and AX. I have wrote a lot over the years about BI, from concepts, to specific technical howto's. Today falls into that howto or technical tip area.
Without getting to much into the debate of how SSRS should be used with Dynamics AX, Michael choose for his first guest post to focus on using native SSRS reports with AX.
This is not the Dynamics AX SSRS Visual Studio focused reports, but rather trying to take and offer best of both worlds. So with that, I will give the "blogging stick" over to Michael.:
A common debate in every Dynamics AX implementation is, “What tool should I use for custom reporting?” For many years, the battle was between using native MorphX reporting, and SQL Server Reporting Services (SSRS), a widely used tool that can be used against any SQL database. In AX 2009, another contender emerged, AX 2009 SRS, which is a hybrid of the previous two choices.
AX 2009 SRS reports is a SQL reporting option that can access Dynamics AX business logic, as well as inherit AX security--and run natively in Dynamics AX, which is one of the best arguments as this reduces end user confusion in their search for data. Unfortunately, AX 2009 SRS reports void the great functionality found in Report Manager (report subscriptions, caching, file share, e-mailing, et al), which is one of the reasons to go down the road of SQL reporting in the first place.
With a little ingenuity, SSRS reports can appear to run natively in a Dynamics AX 2009 environment, which not only provides the most professional, native aesthetic, but in my opinion, make SSRS the best reporting option available.
First, open Visual Studio and create a new Dynamics AX Reporting project. Give the project a meaningful name, one that will ultimately be the same file name as the SSRS report.
Next, deploy the blank project to Dynamics. This will create an AX 2009 Report Library in the AOT.
This is the aforementioned ingenuity. Report Libraries reference files [only] in the Dynamics folder in Report Manager. The file name is the only connection between the deployed report and library.
Now all we have to do is create a SSRS report in Business Intelligence Development Studio (BIDS) with the same Report Definition Language (RDL) filename as the Report Library, which is ExampleSSRS in this case.
Create a new SSRS report in BIDS, or preferred application. Remember that the SSRS report project can be any meaningful name, but the report file itself must match the name of the Report Library.
Upload the RDL to the Dynamics folder in Report Manager. If the proper target server URL is setup in the Report Server Project in BIDS, it can also be deployed to Report Manager from there.
Next, in AX, create an output menu object. Make sure that the object type is SQLReportLibraryReport, and that the object is the name of the Report Library.
Now, the menu object can be placed in an AX menu, or called via x++. Parameters can still be passed through the URL in an Args object, albeit rendering format, report values, or command parameters (refer to URL Access on MSDN MSDN - SSRS URL Access), just disclude the portion of the report server and name.
The following example runs the report, renders it in HTML, leaves the toolbar visible, but hides any parameters:
When the report is run, it will appear natively in an AX 2009 window, and no one will know the report was actually written in SQL.
Well I would like to thank Michael for taking the time and sharing this nice tip. I hope that this is just the first from Michael, and we hear about many more tips and tricks when it comes to BI / Reporting related topics for Dynamics AX.
Here is Michael's Bio:
Michael Stashwick is a Senior Technical Architect at Sunrise Technologies. He has been working with Dynamics AX since version 3.0 in tandem with many Business Intelligence projects focusing on SSRS and SSAS for Dynamics AX.
Michael has a bachelor's degree in Business Information Technology with focuses on Decision Support Systems and Operations Management from Virginia Polytechnic Institute and State University. He is also a Microsoft Certified Business Management Solutions Professional.
That's it for now. Make sure to follow me on twitter.: DynamicsERP on Twitter For next week, as I bring you live coverage of the Microsoft Dynamics AX 2011 Technical Conference. (#DAXCONF). I will also be creating a lot of new blog post, and guest columns throughout the Dynamics Community sites, based on the contect from next week. See you then!
Labels: BI, Custom SSRS Report, DAXCONF, Dynamics AX, Dynamics AX 2009, Dynamics AX 2011, Guest Blog Post, Michael Stashwich, Microsoft, Reporting, SSRS
posted by brandon | 3:01 PM
5 Comments:
That's a really interesting tip..Thanks. Brandon Please continue your guest columns. This is a nice start...
Hi there.
This is really a great one...
Fantastic..
Naushad.
Brandon..
Great one...
Correct me if I'm wrong, but you won't be saving the actual report definition back to the AOT this way. How would you save the latest version of the report back to the AOT? One of the points of using the custom Dynamics AX Model template is the "Save to AOD". This feels like a hack that will bite you when the SSRS content needs to be rebuilt from the AOT.
Can we send a ssrs report direct to printer instead of showing it on Screen. Is it possible in AX 2009?
Post a Comment
<< Home