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!"
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
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!"
Labels: BI, Code Example, Custom SSRS Report, Dynamics AX 2009, SSRS, X++
posted by brandon | 4:44 PM
15 Comments:
Great post, thank you!
Naturally question - how about multiple parameters?
Vic
Thanks Brandon,
I think the last posts was directly mainly toward me :), Thanks Brandon for clarifiying too many issues, really it's so great information. But I've one request, acutally it's two request the first one, where I can found these information, I've searched the internet, partnersource, and the MCT MOC download center, but no result, could u direct me to source of this information and if any available textbook, pdf, or usefull resources. The other request I know it's hard for you and ofcourse for me too becuase we all love Dynamics AX, but for being objective, unbiased and not subjective, could you tell about the cons of the SSRS with AX. Really appreciate your knowledge and helping us.
Mahmoud Anass
@Anonymous : the post mention that you have to use commas to deal with multiple parameters...
Hi,
This is a very useful articule.
However, I find that I cannot pass multiple parameters even though I seperate it with ",". I tried other seperator like " ", ".", "\" but none of them are working.
Any ideas on how to pass multiple parameters?
Thanks.
Great post Brandon! Thank you so much!
If you want to pass multipler parameters comma doesn't work on your menuitems and ssrs side.
you can do like this,
Args.parm("&qryCustomSSRS_SalesId=SO-100004" + "&Parameter2=XXXX" + "&Parameter3=YYYY");
It works every condition
best codings
Mr.FX
Mr. FX, and others having issues with using Comma's... this works fine from the example I created, calling the menu item through code and making use of comma's.
I have not tried the way Mr.FX states, but if that works, that good to know!
thanks everyone!
-Brandon
May I know how to pass the value from AX to SSRS if the parameter type is Multiple Values (list of items with checkboxes)
param1=a,b,c,f,e does not work.....
May I know how to pass value from AX to SSRS if the parameter is having Multiple values?
if the menuitem of the SSRS Report is set to Run On = Server and if you call it from X++, the system crashes.
Based on the above code, if you changed the query of the report, when you call the report, it doesnt refresh
For example:
---------------------
You have a query :
Main Table : LedgerTable
Child Table : Dimensions
Link: LedgerTable.Dimension[1] = Dimensions.num
Join Type : Inner Join
Run the report FIRST time
-----------------------------------
then change the Join Type : Outer Join
Run the report SECOND time, THIRD time and so on, the report doesnt refresh if you are calling X++ EVEN the Application Pool in IIS is restarted.
-----------------------------------
However, when the report is calling from the menuitem button, restart the applicatin pool and refresh/run the report twice, you will get the updated query result.
So the question now is : How to pass the parameter values to the menuitem using the menuitem button (Note: with multiple parameters)
Hi Brandon
I'm new to this blog, but have been in the AX world for many years.
SSRS is fairly new to me though, this info around passing parameters to an SSRS report from AX is very usefull, I was wondering if you have expirience in doing that to a SSRS report taht run in the role center, since that is not called from x++ code, I guess that would be different ?
Thanks
Carsten
How would you do the same thing from Enterprise Portal?
Hi,
I have a SSRS report developed in BI and want to call this report in AX 2009.But problem is how to call this report??
Thanks
Hi,
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. Can you
let me know how to achieve this scenario. Is this scenario possible in
AX
2009 SSRS?
Thanks & regards,
Ankita
Post a Comment
<< Home