Solved

Crystal report XI linking table and stored procedure

Posted on 2012-04-09
19
657 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
  • 10
  • 4
  • 4
  • +1
19 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 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 100

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
 

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 100

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 34

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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 100

Expert Comment

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

mlmcc
0
 
LVL 34

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 34

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 34

Accepted Solution

by:
James0628 earned 250 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 115
C# SQL BULK INSERT CLASS 5 35
Recurring Excel Timelime for Veeam 2 34
SQL JOIN + SUBQUERY? 3 13
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now