?
Solved

Crystal report XI linking table and stored procedure

Posted on 2012-04-09
19
Medium Priority
?
889 Views
Last Modified: 2012-04-12
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?
0
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 4
  • +1
19 Comments
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 37824177
There may be since the stored procedure is run before the report is opened.

Are you using a COMMAND or are you linking in the database expert?

mlmcc
0
 

Author Comment

by:imstac73
ID: 37824203
I'm linking in the database expert.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37824214
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:imstac73
ID: 37824221
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.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37824267
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
0
 
LVL 9

Expert Comment

by:joshbula
ID: 37824689
In the Database Expert, you might also want to try editing the link---try "reverse link" or a right outer join instead.
0
 

Author Comment

by:imstac73
ID: 37824982
I can't do this because the stored procedure may not have a record for everything.
0
 
LVL 35

Expert Comment

by:James0628
ID: 37826331
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
0
 

Author Comment

by:imstac73
ID: 37827810
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.
0
 

Author Comment

by:imstac73
ID: 37829254
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
0
 

Author Comment

by:imstac73
ID: 37829517
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
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37830952
How did you add the parameter to the COMMAND?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 37831184
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
0
 

Author Comment

by:imstac73
ID: 37832578
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.
0
 
LVL 35

Expert Comment

by:James0628
ID: 37833482
> ... 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
0
 

Author Comment

by:imstac73
ID: 37833801
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.
0
 

Author Comment

by:imstac73
ID: 37835060
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.
0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 37836077
FWIW, rather than have stored procedures that fill some tables with the data needed by the report, and then have the users manually run those procedures before they run the report, I would think that you could have a stored procedure that created some temp tables or table variables (depending on how much data you have), and then used those to produce the data for the report, and the report would use that SP as the datasource.  Then you wouldn't need the extra step of running the SP(s) first.

 Just throwing that out there.  Whatever works for you.

 James
0
 

Author Closing Comment

by:imstac73
ID: 37839944
Decided to go with a web interface to populate tables with stored procedures to be used in report.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question