Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Stored procedures and prompting Post Reply Post New Topic
Author Message
paulsonp
Newbie
Newbie


Joined: 21 Mar 2012
Location: United States
Online Status: Offline
Posts: 3
Quote paulsonp Replybullet Topic: Stored procedures and prompting
     Posted: 22 Mar 2012 at 7:38am
Hi,
 
It's been awhile since I worked with Crystal, and that was with the VS.Net version.  Now I have stand-alone Crystal 2011 and cannot figure out how to get my prompts right.
 
Here's what I have:
3 stored procedures - the first brings up a prompt of the list of facilities for the user to choose from.  The user's choice is then sent as a parameter the second stored procedure, which then shows a list people at that facility (cascade).  THe user chooses a person, and then both choice are sent to the third, and main, stored procedure, which then displays the report on that person at that facility.
 
I've tried all sorts of combinations, and ended up with two prompt groups, one prompt group, have tried commands for calling the second sproc...  It's that the second parameter (to the main sproc) needs a parameter itself that's messing me up.  Sometimes a third parameter shows up, but I can't quite get my head around what the "parameter group" is, and where that parameter for the 2nd sproc should go.
 
Thanks!
Pat
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 22 Mar 2012 at 7:54am
hmmm...
 
what I would have done, as multiple stored procs being used to cascade for parameters scares me....especially as it is to be dynamic...
 
back to what I would have done...
I would use command object to gather the information needed, then I could link the 'tables' together and cascade (filter) the values.  At the end, I would have the parameters to send to the main stored proc.
 
I guess it all depends on how complex the stored procs are...if they are basically simple gets, you use the logic and just remove the part of the where clause that filters...and join the first command object to the second by the filter criteria so that it cascades properly.
 
Hopefully that was too confusing...
IP IP Logged
paulsonp
Newbie
Newbie


Joined: 21 Mar 2012
Location: United States
Online Status: Offline
Posts: 3
Quote paulsonp Replybullet Posted: 23 Mar 2012 at 6:45am
Thanks for the response!  Back in the day when I did some limited VB6 and C# apps that incorporated Crystal, I handled parameters with the app, and pushed all the data from stored procedures to the report.  I was told that was the most efficient method, that letting Crystal handle SQL wasn't always a good idea..... 
 
I think I have a vague idea of what you're saying I should do.  One question, though - by removing the "where" and joining the commands, Crystal is effectively sending the "where" clause (via the filter) to the database so I'm only getting back what I need, right?  I mean, I'm not getting tons of data back (i.e. with no "where") and then filtering, am I?
 
Then, for the main report, should I use another command, or use the stored procedure as a data source.  Oh, my, I'm not even sure what I mean.......I see plenty of experimentation in my near future.  My love-hate relationship with Crystal in the past is coming back to me.....  Dead
 
Oh, and why do the sprocs for cascading parameters scare you?  I'm primarily a database programmer (couldn't you tell?), and as such am a strong advocate of stored procedures.  I surely understand, though, that they sometimes  just don't play nice with other tools!   Knowing your experiences with the interaction of sprocs and Crystal can surely save me some forehead-banging time.
 
Thanks again,
Pat
Pat
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 23 Mar 2012 at 7:25am
1) I agree with pushing the data to the report...it's how all of my reports work.
2) since I always use a stored proc, I don't know if CR is smart enough to use a where first, or gets all the records and filters from there...hence my using the stored proc...I know what sql server is going to do.
 
3) the only reason I am leary of the cascading stored procs, is that when I learned CR, it was in version 7, and I basically taught myself, and one of the 'rules' that I created for myself is that CR only calls out to the database once per report.
 
I could be completely wrong on this, and if it works for you, by all means use it.  I don't have any recent experience with the parameters page as we use an application that we have written a wizard to step the user through the creation of parameters...then we get the dataset (perhaps manipulate it) and then send it on to the report.
 
Pushing the data, as you know, just makes it easier on the programmers as we never have to worry about DSN and other methods to make sure that CR can talk to the database.
 
I do a lot of SQL programming, so between the stored proc and the pushing of the data, CR / any reporting tool isn't so bad.
 
I will always advocate for a stored proc for main report. Both for ease of testing/maintenance and for the shear versatility that a stored proc offers that a command doesn't (temp tables come to mind)
 
This is a belief of mine (since no one has ever agreed or disagreed) as to the 'original' design idea behind command objects, in that they are to gather data for parameters lists, not to gather the data for the main report....
 
Many people though use Commands to get the data for the report as they aren't allowed to write procs against the database. With that said, using parameters in commands can lead to CR double prompting the user for the parameter, once for the command and once for the report...or so seems to be what has been reported on this forum.
 
Hopefully that gives an idea of how I think CR works under the hood / the experiences that I have had.
IP IP Logged
paulsonp
Newbie
Newbie


Joined: 21 Mar 2012
Location: United States
Online Status: Offline
Posts: 3
Quote paulsonp Replybullet Posted: 23 Mar 2012 at 10:58am
Well, I don't seem to have any type of luck using any stored procedures.  I set up commands for the two that retreive data for the prompts, but can't seem to get them to tie together to each other OR pass to the main sproc.  Plus, even after I removed all links between the commands, I ran SQL Profiler and caught one of the prompt sprocs running no less than 9 times, and the other two at least 3-4 times each!  With various parameters (usually none, though).  In Crystal, under the <Database> menu, you can see the SQL it supposedly runs, and it only listed the three EXECS.  And, after all that there were no records returned anyway.
 
Now I'm really scared to just stick the SQL statements in Crystal like the tutorials all say to do.   I might get it to work that way, but at what cost?   I've been scouring the Web for days trying to find a solution, with no luck.  If this wasn't for a 3rd party app that allows you to fire up your own .rpt files to show in the app's cr viewer, I'd write the parameter handling in C#. 
 
I must be missing something....maybe I'll let me head clear over the weekend and start over!
 
Thanks again,
Pat
Pat
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 23 Mar 2012 at 12:18pm
ok.
 
I typically write the command in sql, test in analyzer.  Each command returns a table, link the tables.
 
then for parameters I think that you can display the command fields, which will get to the parameters...
 
the question that I am not sure of, since it has been a really long time since I had to do anything like this, is how to link the cascading parameters to the stored proc, since CR 'assigns' the procs parameters.
 
hope the weekend works to clear everything up!
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.