Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Adding second SQL to the report Post Reply Post New Topic
Author Message
kirandb
Groupie
Groupie
Avatar

Joined: 10 Jun 2009
Location: United States
Online Status: Offline
Posts: 69
Quote kirandb Replybullet Topic: Adding second SQL to the report
    Posted: 27 Sep 2010 at 9:00am
I have a report which is calling a stored procedure. I have to display a specific field on the report which is obtained by a simple SQL taking in one of the parameters of the report. Please help me in this regard as i am trying to add the SQL in the database expert, i am getting the following error
 
Failed to retrieve data from database
Details: ADO Error Code: 0x
Source Provider
Description : No value given for one or more parameters.
 
I am using the SQL as

select descr from table where cube = ?CUBE_ID

 
Any suggestion on how to add this field by querying the database.
 
Thanks,
Kiran 
share your knowledge
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Sep 2010 at 10:41am
if you created a SQL sp that is using the variable 'cube_id' then when you make this sp your data source it will create a crystal parameter called ?cube_id.
Assuming your sp is using the param in its where clause you do not have to do anything in the crystal select statement. the sp will do what you wanted.
IP IP Logged
kirandb
Groupie
Groupie
Avatar

Joined: 10 Jun 2009
Location: United States
Online Status: Offline
Posts: 69
Quote kirandb Replybullet Posted: 27 Sep 2010 at 10:51am
The SP use the parameter CUBE_ID but it does not fetch the value that is required to be displayed in the report. The description for the CUBE_ID needs to be fetched by querying the database table again. Can there be two datasources, one the SP and the other one is a SQL.
This is an Actuate report being converted into Crystal. Just wondering if this can be done since i am new to crystal.
 
Thanks,
Kiran
share your knowledge
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Sep 2010 at 11:03am
hmmm.
I rarely use SPs as my data source and my deploymenty process won't let me use SP params so I may not be the right person to answer but I thinkyou may have a chicken - egg issue here.
If I understand you correctly yuo want to use a dynamic param with a LOV from a table and then pass the value to a param in a SP. In order for CR to get the LOV it has to read the DB and pull all the records to build the full LOV set. however the beauty of using SP params is that the data selection happens at teh server side before the full LOV is created and moved to CR.
IF you get rid of the SP param and replace it with a crystal param that is a dynamic param it will use the LOV then in your select statment you just limit the data there:
{sp.cube} = {?CUBE ID}
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Sep 2010 at 11:04am

basically your SP has to create the full LOV (no pram usage) pass it to CR to build the LOV at which point crystals select statmetn would limit the data that appears in the report. It dilutes the power of a SP param but should do what you need....

If anyone else sees an issue with what I am posting please jump in.


Edited by DBlank - 27 Sep 2010 at 11:07am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.012 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.