Making Report Through sql stored procedue

sharad
sharad Member Posts: 112
edited 2012-05-12 in NAV Three Tier
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.
Sharad Gupta
Navision Technical Consultant & .Net Developer

Comments

  • clausl
    clausl Member Posts: 455
    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
    Claus Lundstrøm | MVP | Senior Product Manager | Continia.com
    I'm blogging here:http://mibuso.com/blogs/clausl and used to blog here: http://blogs.msdn.com/nav
    I'm also offering RDLC Report Training, ping me if you are interested. Thanks to the 700 NAV developers that have now already been at my training. You know you can always call if you have any RDLC report issues :-)
  • Dan77
    Dan77 Member Posts: 17
    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
    Dan77 Member Posts: 17
    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!!!

Categories