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!"

More on the Future of BI with Microsoft, Dynamics AX and Project Gemini

In the past I mentioned Project Gemini, which will have a large impact on new features, and abilities for doing BI reporting, analytics in regard to Microsoft stack, including Dynamics AX.

The following is a great video that really goes into project Gemini, and covers what Gemini is.
Ch. 9 Project Gemini Video

From the details of the Video:
"Need to make timely business decisions without having to use complicated and sluggish analytical applications? Love to use Excel? Project Gemini is an Excel 2010 add-in that allows you to create powerful analyses by quickly manipulating millions of rows of data into a single Excel workbook and utilize Microsoft Office 2010 to share and collaborate on your insights with your team."

And..
"For all you spreadsheet jockeys out there, this should prove extraordinarliy useful to you when you need to crunch and analyze lots of data effeciently. Wow. 1,000,000 rows in Excel without causing a hang."

So this whole 'BI for the Masses' will start to have some real meaning behind the term, once Gemini becomes a reality.

Another great resource on this, can be found at the following link.:
Project "Gemini": Build powerful analytical applications



"You can combine native Excel 2010 functionality with Gemini’s in-memory engine to allow users to interactively explore and perform calculations on large data sets. In addition, you can easily streamline the process of integrating data from multiple sources – including corporate databases, spreadsheets, reports, and data feeds.

Share and collaborate with confidence by easily publishing your analysis to SharePoint 2010 and have other users enjoy the same slicer and fast-query capabilities when working on your Excel Services reports."

So now, with Office 2010, and the next release of Dynamics AX 2011, Analytics and Ad-hoc ability to really offer BI to masses will have a dramtic face lift and new offerings. This includes collaboration making use of SharePoint, which is already a big part of the Dynamics AX platform.

This is going to add to, the already existing abilities of BI options with Dynamics AX, and will add to also the ability to talior Role Centers and add meaningful data points and reports / abilities to those users that need those Dashboard or Role Centers.

That's all for now, but check back soon as more to come.




"Visit the Dynamics AX Community Page today!"

Dynamics AX 2009 - Calling X++ logic from SSRS and C#

Recently I wrote the following blog entry:
Calling a Custom Dynamics AX 2009 SSRS report and passing parameters - From X++

This was to address a set of questions from a reader, where he stated it was not clear about how to call a report from X++ and send in parameter data. The above post, shows you exactly how to do that.

This is the blog entry, in which those comments where left.:
Dynamics AX 2009 - Reporting Possibilites

The next thing in the readers comments, was making use of display methods, or methods in general from X++. This also can be done, an there is great examples of this through some of the out of the box Report's, and also the new Environmental Dashboard that came out during the spring.

I am going to pull from the environmental dashboard, and show an example of this being used.

So if we open our reports project, we will see something similar as follows, and notice the report, has a section for data methods.



Looking at these data methods that are part of the EnergyCost report, we see we have this one, with some parameters / variables that need to be sent in.

On double clicking of the method, takes us to the Business Logic section, and we see our C# code here, along with the .Net Business Connector Assembly, and AxaptaWrapper Object being used.



As you can see we have access to make calls to static methods, and normal non-static business logic. So this could easily be changed and made use of as a template for working with X++ methods, that we need access to for our report purposes.

Now moving back to the report design, I have highlighted the field in the report design, which happens to be a Matrix Cell, that makes use of this datamethod. The expression where this data method is called, is the following.:



=Cdbl(GetEnergyCost(Fields!SourceProcessId.Value, Fields!DestinationProcessId.Value, Fields!SubstanceId.Value, Parameters!EnergyCost_Filter.Value))

So we can see our datamethod being called, to fill this field, and see how the parameters are being sent in. Even one being referenced from the parameters that are sent in to this report.

This is a really good example, that can be expanded upon, and used as a template for such custom needs of datamethods, and access X++ business logic and display methods for a Dynamics AX 2009 SSRS Report.

I hope this helps, and look for more soon. See you next time!



Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.





"Visit the Dynamics AX Community Page today!"

Calling a Custom Dynamics AX 2009 SSRS report and passing parameters - From X++

Recently, I have been doing post about making use of Custom SSRS reports with Dynamics AX 2009.

The most recent post, was the following.: Dynamics AX 2009 - Reporting Possibilites

In that post there was a comment left by someone talking about some of the limits that still exist with the custom SSRS reports.

As I point out there are pro's and con's with the custom SSRS reports for Dynamics AX 2009, however one of the things the reader pointed out was passing parameters to the custom SSRS report.

So I thought it would be worth while to do a post on exactly how to do this.

In this example I have created a custom SSRS report for Dynamics AX 2009 instance.



In this you will see I have a Query, that was used for my report, and also a report library I created using Visual Studio 2008 Dynamics AX 2009 Reporting template.

The query, has a Range of SalesId, and in doing that, this was auto added to the report parameters, in the VS2008 projects. (see below image)



This is the name of the parameter in the report def., and therefore important to note for our exercise.

So the task was to take and pass values to reports from X++. I took and create a job that does exactly that. It takes and call my custom reports, output menu item, and pass it a value for the SalesId parameter. The code follows.:


MenuFunction CustRptMI;
Args Args;
;

CustRptMI = new MenuFunction(menuItemOutputStr(srsCustomRpt),MenuItemType::Output)
Args = new Args();
Args.parm("qryCustomSSRS_SalesId=*SO-100004*");
CustRptMI.run(Args);
CustRptMI.wait();


Notice the name of the parameter is exactly the name it is in the VS2008 project for the report def. 'qryCustomSSRS_SalesId'. Then simply and = and then . You can add multiple parameters here with commas, and there you have the report being called, ran, and from X++, parameters being passed and the report generated. (See image below)



So as you can see, you can make use of X++ to call custom SSRS reports, pass in paramaters, and do this pretty simply. You just have to know the exactly parameters names, and you can go from there.

Check back soon, as more post are coming!




"Visit the Dynamics AX Community Page today!"