Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Calling a Stored Procedure from Crystal Report

    I've tried to find this documented on the Internet and found Crystal Reports User's Guid online documentation (582 pages of it!) It looks like it will be helpful for my other questions, but what I'm trying to do right now is call a stored procedure from my crystal report. So in Field Explorer under Database Fields my stored procedure is there with the three columns it selects. How do I put these three columns on my report? I try dragging them to Section 3 details and they're there but when I preview the report all I get are the headers, not the records. Is there something else I need to do? It requires an input parameter which I am not getting prompted for - maybe that is my problem, without an input parameter it can't successfully run the stored procedure, so how do I make it prompt me?

    Thanks for helping me with my first Crystal Report!

  2. #2
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by M Mock
    I've tried to find this documented on the Internet and found Crystal Reports User's Guid online documentation (582 pages of it!) It looks like it will be helpful for my other questions, but what I'm trying to do right now is call a stored procedure from my crystal report. So in Field Explorer under Database Fields my stored procedure is there with the three columns it selects. How do I put these three columns on my report? I try dragging them to Section 3 details and they're there but when I preview the report all I get are the headers, not the records. Is there something else I need to do? It requires an input parameter which I am not getting prompted for - maybe that is my problem, without an input parameter it can't successfully run the stored procedure, so how do I make it prompt me?

    Thanks for helping me with my first Crystal Report!
    This seems like a question for a Crystal Reports forum. When you do find the right forum to post to, it would surely be very helpful to mention the version of Crystal and the front-end you are using (I'm guessing the designer, but it could be the Crystal Web server you're talking about, etc).

    Older versions of the Crystal Report designer had some hiccops when interfacing to Stored Procedures (that is, as opposed to Views and Tables that it has no problems with). I don't know about the newer ones since I only have versions 8 and 9, but I do use Stored Procedures successfully and can tell you that getting the prompts to pass to the SP isn't obvious (beyond what I'm prepared to describe here), particularly if you're using subreports. The Crystal web site has a wealth of sample code, but their search engine sucks (it never seems to find something unless you know exact titles to search for) and I'd suggest using Google Advanced Search and restrict results to their site only - works great.

    I would suggest defining the SP with all the input parameters you want then use the Crystal Report wizard to auto-build a new report, and therefore the interface. You can do that with a sample report to explore what it did and try implementing that into your current report, or just start from scratch.

    Also beware - they don't handle passing dates to SPs properly, so you have to enter it as a text field and do a conversion within the stored procedure.

    I'll go out on a limb and assume that you are either using a date parameter, or you will need to in the near future. Here's some code for passing the date:

    Code:
    CREATE PROCEDURE sp_MySP (@strAsOfDate varchar(8)) AS    --use format 'YYYYMMDD' to represent the date as a string.
    
    declare @AsOfDate datetime
    set @AsOfDate = CONVERT(DATETIME, @strAsOfDate, 112)
    
    /*
    my query ... WHERE MyDateColumn <= @AsOfDate
    */
    GO
    Last edited by vich; 08-07-07 at 19:06.

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    vich,
    Yes, I know I am probably in the wrong place. I should've realized there would be a forum for Crystal but I started in a db forum since that's what I am used to.
    I am using vb.net/Visual Studio 2005 and I created a crystal reports project in my solution where my app resides that needs to display the report. I realized re-reading my post that a reader would have no idea what environment I was using!
    I've written the stored procedure successfully, I just need to know how to call it. I wrote the same code in a query, I guess it's called a Command by Crystal, and it works but eventually it should be moved to a stored procedure for performance. Thank you for the advice on dates, as I will need to report on a range such as this week, Sunday 8/5 to Saturday 8/11 or last year, etc.
    Right now I can run my report directly in VS, but I want to have a button one one of my Windows Forms to run the report. So I am asking around about how to do that. My app is a Windows App using Web Services.
    Thanks. I am as confused as I sound.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •