Link to home
Start Free TrialLog in
Avatar of imstac73
imstac73

asked on

Crystal report XI linking table and stored procedure

I have a Crystal report in which I am linking a table with a stored procedure that has input parameters.  My report is not returning any data for my stored procedure.  If I create a report with just my stored procedure on it then it works fine. However, when I add my table and do a left outer join from my table to my stored procedure I do not get any data returned.

Is there some issue with having a stored procedure and a table linked in a Crystal Report?
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of imstac73
imstac73

ASKER

I'm linking in the database expert.
You may have to use a COMMAND as the source in that way the data will be retrieved from the table and the SP at the same time.

What is the purpose of the table?

mlmcc
Do you have a good reference link for using a command?  Never used this method before.  The report pulls from several different tables and stored procedures; it is a very comprehensive report.
A command is basically a stored procedure but written in Crystal.  Generally you can take the SQL from your SP and paste it in place.

I haven't seen any tutorials on Commands

mlmcc
In the Database Expert, you might also want to try editing the link---try "reverse link" or a right outer join instead.
I can't do this because the stored procedure may not have a record for everything.
There is probably some reason that you're not doing this, but, FWIW, I would add the table to the stored procedure, rather than try to link the SP output to a table in CR.  As you can see, there are inherent problems in trying to link them in CR, and it would probably be much more efficient to do everything in the SP.  If you don't want to change that SP (maybe it's used by other things and you don't want to change the data for those other things), then you could create a new SP.

 As far as a CR Command goes, as mlmcc said, it's just a query that you create in CR and it's stored in the report, instead of on the server.  When you're looking at the datasource in the Database Expert, there is an "Add Command" option.  Note that if you have a Command EXEC a SP (as opposed to using the SP itself as a datasource), I'm guessing that you'll need to manually create the parameters for the SP in the Command editor.

 James
Yeah, I can't add the table to the stored procedure.  I have 5 regular tables and 4 stored procedures that go into this report.
Okay so I'm trying the Add Command feature again.  I copy and paste my stored procedure information into the sql command window in CR and add my parameter I'm using in the parameters list.  When I click Okay it says that a value has not been given.  Not sure what I'm doing wrong.

Can someone that has used the Command feature with parameters point me in the right direction on this? I've attached a screen shot.

Thanks,
4-10-2012-1-42-24-PM.png
Here is the command I've written.  It works fine in SQL but when I put this in the command window in Crystal and run it after I key in the value of my parameter it gives me an error that there is incorrect syntax near ORDER.

SELECT    dbo.APPCNS.WCONO, dbo.APPCNS.WJBNO, dbo.APPCNS.WJCDI, dbo.APPCNS.WJCCT, dbo.APPCNS.WAMCN, dbo.APPCNS.WAMCG, dbo.APPCNS.WDTEN, CONVERT(char, dbo.appcns.wdten, 112) as Date
               
FROM         dbo.APPCNS

where CONVERT(char, dbo.appcns.wdten, 112) <= {?enddate}
ORDER BY dbo.APPCNS.WCONO, dbo.APPCNS.WJBNO, dbo.APPCNS.WJCDI
How did you add the parameter to the COMMAND?

mlmcc
Since you're converting wdten to a string and comparing that to the parameter, I'm guessing that you made the parameter a string.  If so, you need to put it in single quotes:

where CONVERT(char, dbo.appcns.wdten, 112) <= '{?enddate}'

 Of course if you're doing a string comparison, you have to make sure that the date/datetime that you enter in the parameter is in the same format you're using for the CONVERT.


 If the parameter is actually a date/datetime, then:

 1) Why convert wdten to a string?

 2) As mlmcc asked, how did you add the parameter?  You need to create it in the Command editor, which it looks like you were doing from the screenshot you posted earlier.  FWIW, I think you can insert the parameter in the query by double-clicking on the parameter name on the right.

 James
wdten is a decimal data type.  So I got the Command working with just that in the CR; however when I add a regular table and link it the data does not pull.  I've decided since this report is so complex that I'm just going to have stored procedures that populate tables with the data I need and then use those tables in CR.  I'll just have to set up a web interface to allow the uses to run the stored procedures.
> ... however when I add a regular table and link it the data does not pull.

 Are you adding a table to the Command, or adding a table to the report and trying to link that table with the Command?  If you're trying to link a table with the Command, you may be running into a similar problem to when you were trying to link a table with a stored procedure.  Why not just use the table in the Command?


 Or, if you can create new stored procedures (to fill tables for the report), why not just use one of my earlier suggestions and create a new SP that's a copy of the old one, and change the copy to include the table that you were trying to link in the report, and use that SP as the datasource for the report.  If a SP can fill a table with the data needed by the report, it should be able to just output the data directly to the report.

 James
James,
There are several transaction tables involved at different detail levels and when I try to link them as is I get duplication of records.
Thanks to everyone for their suggestions but I have decided to go with having the stored procedures populate tables and then use the tables in the report.

Giving the points to mlmcc since they were the first responder.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Decided to go with a web interface to populate tables with stored procedures to be used in report.