AX 2009 SSRS Reporting Using Store Procedures

Finance and Operations/AX Developer Forum
1

Hi All

I am starting to create more ‘complex’ SSRS reports now, and am finding that the Query Buffer on the server is being hit as the queries are too large.

As opposed to increasing the buffer, the recommendation has been made to try Store Procedures.

Now, I have tried to hunt online for a complete step-by-step for this but the information I am getting is in dribs and drabs.

Can anyone please list step by step how to create the store procedure and where this is done, how this is then used in Visual Studio as the datasource, the creation of the report is OK I am fine with creating the report, then please explain if the process for the report deployment is different?

Thanks [:)]

2

I’m in the same boat right now. I’m on the brink of figuring out how to call the SP from an AX data provider class which is where I’m stuck. However, I’m able to point you in the right direction though until I settle the rest.

Stored procedures are contained on the database side and written in SQL. You can use Microsoft SQL Server Management Studio for development. You will need a good knowledge of the table structure how the data joins on the back-end. Google “writing stored procedures” for a quick tutorial. When I figure out how to return the results to a report, I’ll post my steps (Although I’m using AX 2012).

3

Alright, got mine working. I documented it in the link below. Note I didn’t go overboard with the details so I’m sorry if it is lacking.

http://wiki.lukewyatt.us/index.php?title=Documentation:Microsoft_Dynamics_AX/Calling_Stored_Procedures_In_SSRS

4

Cool and thanks for the reply - I will have a look through your guide and attempt the process.

5

No problem. Let me know if you have any snags and I’ll explain in more depth.

6

SQL 2008 R2 > Your Database > go to ‘Programability’ node > Right click Stored procedure > New Stored procedure

select all > delete evry thing > paste below code

CREATE PROCEDURE sp_testprocedure
AS

SELECT PURCHID FROM PURCHTABLE

Click execute.

Your stored procedure will be appearing when you refresh the database.

Here is the sample code to call the stored procedure from ax

static void storedprocedure(Args _args)
{

LogInProperty Lp = new LogInProperty();
OdbcConnection myConnection;
Statement myStatement;
ResultSet myResult;
;

LP.setServer(“Your Data base server name”);
LP.setDatabase(“Data base name”);

try
{
myConnection = new OdbcConnection(LP);
}
catch
{
info(“Check username/password.”);
return;
}

myStatement = myConnection.createStatement();
myResult = myStatement.executeQuery(‘EXEC [sp_testprocedure]’);//stored procedure name
while (myResult.next())
{
print myResult.getString(1);
}
pause;

}

7

Thanks a ton Luke!!! :slight_smile:

The document is Just Wondeful.

8

No problem, glad you like it [:)]

9

Hi Luke,

I am newbie of SSRS and X++ programmer. May I ask if a parameter is exist in SQL stored procedure, like

exec sp_findCustAccountBalance @CustAccount

How can I prepare a parameter in Ax class so it can receive a parameter in SSRS then pass the parameter to stored procedure?

Very Thx!!

Jay

10

Hey Jay,

In my document, the SQL command is stored in a string:

sql = “EXEC STOREDPROCEDURENAME”;

This can be concatenated dynamically:

string parm = “2012”;

sql = "EXEC STOREDPROCEDURENAME " + parm;

I haven’t had a need to do this yet, but to point you in (hopefully) the right direction, I would be to figure out how to get data from a custom form to this.