March 11, 2004 at 4:48 am
Hi
if i am using sproc for retrieve data, can i use the rpt wizard to do it.
in the Design The Query Window, how do i specify the proc name
i tried EXEC rptDailyCumulativeProdSummaryByBussUnit '200403' but it returned err...
and if i use the manual report creation way...do i hv to create the fiels objects manually????
plz help
March 11, 2004 at 2:54 pm
I just tried creating a report by using a stored procedure as the data source and it worked for me.
In the 'Design the Query' dialog box, type EXEC rptDailyCumulativeProdSummaryByBussUnit '200403' and hit next, don't use the Query Builder to type it in. It should let you continue to the next screen with your result set's columns, pick out the columns you want to show and continue on with the wizard.
One thing I noticed was that if you use a stored procedure as the data source, when you go into the data source tab to change your parameter's value, you will have to take off the 'exec ' in order to get it to work. You will get a message about it not being able to parse the query text, but if you hit 'ok' and go to the preview tab, it will still show you the correct result set (assuming your result set schema is the same). Instead, you could type EXEC rptDailyCumulativeProdSummaryByBussUnit @parameter1 as your data source and you can just change the parameter value at the top of your report when you run it.
I think the best way to solve this problem is to copy the code out of the rptDailyCumulativeProdSummaryByBussUnit stored procedure and paste it as the query string. The parameter to the stored procedure's code will now be your parameter to the report.
Yes, if you manually create the report, you will have to set up the textboxes used by the report manually as well.
Hope this helps.
Jarret
March 11, 2004 at 6:34 pm
thats excatly what i did...EXEC rptDailyCumulativeProdSummaryByBussUnit '200403'
but at the below of the 'Design the Query' dialog box an err appears when i click next = There is an error in the query. Divide by zero error encountered.
But when i exec it in tha query analyzer the proc runs fine...no error
ur sproc is just a normal selection or using cursors and so on???
March 12, 2004 at 7:01 am
The SP I first used was just a normal select, I will try it with a cursor.
I created a proc with the nested cursor from BOL under 'DECLARE CURSOR' in the pubs database. I changed all of the print statements to insert the strings into a table variable (temp table wouldn't work with RS), then selected out the result set at the end from the table. It worked fine for me.
What if you just paste the code for your SP into the 'Design the Query' dialog box?
Jarret
March 12, 2004 at 2:14 pm
I have encountered the same issue with sp's in RS. I do not use the wizard. In the generic query designer I type in the sp name, rptDailyCumulativeProdSummaryByBussUnit with no parameters. You can get the field list by clicking refresh fields and entering values for the parameters that pop up in RS. If you are using a temp table in the sp it may say that it does not exist occasionally and using the refresh fields button always finds them, at least in my experience.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2004 at 5:42 pm
jarretg,
my proc contains temp table and cursors...why do u say temp table wont work with RS...do u hv more info on this? any writeups?
Jack
yup....i did the same thing...i used the generic query designer n the refresh retrieves the fields from it. This is the same sp that fails with the parser in the wizard in RS. weird huh!!!!!
March 15, 2004 at 6:43 am
Sorry for the confusion, I didn't mean that temp tables don't work with RS, just that when I tried to create the report with the wizard, I got an error message saying that the table didn't exist. That's why I changed my temp tables to be table variables instead.
Jack - When I try to create a report by typing in the SP name with no parameters, I get an error "Procedure 'rptDailyCumulativeProdSummaryByBussUnit' expects parameter '@param1', which was not supplied." I had to type 'exec rptDailyCumulativeProdSummaryByBussUnit @param1' to get rid of the parameter message, then the syntax error because it needed 'exec'. Is this not the behavior you see?
Jarret
March 15, 2004 at 5:47 pm
jarret - in the Generic Query Designer u can just use the sp name without the param but make sure u change the Command Type to StoredProcedure
now if u click the run button, it would prompt for the parameters.....
let me know if it works....
March 16, 2004 at 7:14 am
I guess I hadn't noticed the Command type drop down box.
Once I changed that, it worked like a charm. Thanks!!!
Jarret
September 15, 2004 at 3:37 pm
I'm having the same problem described above, only I don't see the same options you guys seem to have discussed. Has the report designer software changed? My stored proc uses temp tables, neither the query builder or initial report wizard "query string" box like my use of a temp table (I select from the temp table at the end of the stored proc). I get "Invalid object name '#tmp_output'.". Of course the sp works from from query analyzer for example. Also when I paste the exec call into the query builder in report wizard I can right click-."RUN" and it shows the correct number of rows without errors. I just can't click "next" to continue the report wizard.
I don't see ANY options to refresh fields like was mentioned, or an option to change command type?? What am I doing wrong? I don't see any toolbars that I can click either (menus are not clickable when the wizard is running).
EDIT -- after snooping around a little more, I guess you guys were saying you can't use wizard, but I do see the options you were talking about when I add reoprt without using wizard. I guess that's the way to do it? I'll keep playing around with it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply