Thursday, December 15, 2011

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!

3 comments:

  1. Hi Kishor,

    Please help me out in finding the solution to my problem. This is very
    urgent. I am creating a AX 2009 SSRS report. In the report I have two
    parameters, based on the first parameter which is a drop down , the
    second parameter needs to be filtered with specific values. For eg AccountNum
    is the first parameter which displays all the CustomerId. Based on the
    AccountNum selected, the second parameter should display the
    AppointmentNumber. This is the thing which I have to perform.

    For this issue i have got a solution but i am not able to figure out that solution so i want your help. The solution is :

    1- I have create a DataSet in Visual Studio that is linked to a query in AX that brings all the customer id's. So i create a query in AX to fetch all the customer id's.

    2- For the second parameter, i have to write a data method in Visual Studio that calls a method (and/or custom class) in AX. So, for this i have to create a tmpTable in AX that will be used to store the results of what the info related to a customer id.

    Then, as recommend by some one i have to create a class and 1 static method. The static will have the name of the tmpTable, so i have to return this table back to visual studio.

    For this i have to write public static tmpTable_AppoinemntNumbers(CustNum _custNum)

    The method will receive the customer id as a parameter and with this i have to create a while loop that will insert the values into the tmpTable, then in Visual Studio, the data method will have to return a data table, so it will be like this

    public DataTable getAppointments (string custNum)

    Then, i have to call the new class and assign it to the data table from C# and then set it back to the appointment parameter value in my SSRS report.

    I have perform the first step and created the ax query in ax but i am unable to create the data methods as i didn't have any knowledge regaidng it.
    So please help me as it is very very urgent for me.

    Thanks & Regards,
    Ankita

    ReplyDelete
  2. You can refer following websites...
    1) http://msdn.microsoft.com/en-us/library/dd255197.aspx
    2) http://blog.infotoad.com/post/2009/06/25/Working-with-Cascading-Parameters-in-Reporting-Services.aspx
    3) http://sql-bi-dev.blogspot.in/2010/08/cascading-parameters-in-ssrs-2008.html

    ReplyDelete
    Replies
    1. 4) http://beyondrelational.com/modules/2/blogs/101/posts/13370/ssrs-51-using-parameters-in-ssrs-cascading-parameter-2.aspx
      5) http://www.resultdata.com/adding-cascading-parameters-to-a-ssrs-report/
      6) http://www.msbiguide.com/2012/02/15/adding-cascading-parameters-to-ssrs-reports/

      Delete