Making Report Through sql stored procedue

sharad

Hello Frnz,

I want to make a Report from Sql Stored Procedure. I've done this by using ADO automation.
But it takes much time to load Report. I just want to make a crystal report bind with stored procedure and called from navision with updated data every time and with filtering capability.

I don't have any idea how to do this?
Please Help me and explain if Possible to make report like this way?


THANKS IN ADVANCE.

clausl

It is possible to do this but I would use SQL Reporting Services and not Crystal Reports.

You will need to do the following:

1. Setup your Store Procedure in the database where it is required in SQL Management Studio. Make the query go directly to the tables in the SQL server. This is of course not reccommend since you will shortcut NAV Business logic when you go directly to the table itself, but in many cases this would OK.
2. Now go to the Report Server and create a new report. Setup the dataset of your report to point to your Store Procedures.
3. Design your report based on the fields you collect in your Store Procedure. Designing the report can be done in i.e. Report Builder.
4. Publish your report to the Report Server.
5. And then run it.

/Claus Lundstrøm

Dan77

clausl wrote:

It is possible to do this but I would use SQL Reporting Services and not Crystal Reports.

You will need to do the following:

1. Setup your Store Procedure in the database where it is required in SQL Management Studio. Make the query go directly to the tables in the SQL server. This is of course not reccommend since you will shortcut NAV Business logic when you go directly to the table itself, but in many cases this would OK.
2. Now go to the Report Server and create a new report. Setup the dataset of your report to point to your Store Procedures.
3. Design your report based on the fields you collect in your Store Procedure. Designing the report can be done in i.e. Report Builder.
4. Publish your report to the Report Server.
5. And then run it.

/Claus Lundstrøm

Exactly the steps I followed. The tricky part is really to get the stored proc right. It takes time and can be painful, but defo worth it!

Dan77

I add that you can also query web services from RS, which is in total respect of the business logic.
I am eager to see what we will get with queries and odata in Nav 7!!!