Passing in variables

Question

Post reply

Passing in variables

ste.cox

Mr or Mrs. 500

Points: 584
More actions
May 6, 2008 at 7:20 am

#186107

I have the following code to get the info of students from one report if they meet certain criteria from the mysql datadase.
DECLARE @sql varchar(1000) DECLARE @stat varchar(10) DECLARE @date varchar(20) CREATE TABLE ##v(ref varchar(12) collate Latin1_General_CI_AS)
SET @stat = '"Poor"'
SET @date = '"March2008"'
SET @sql = '
INSERT into ##v (ref) SELECT studentID FROM OPENQUERY(MSQLREVIEW,''SELECT studentID,student FROM Reports
WHERE ((`classwork` = '
+ @stat + ' or `homework` = ' + @stat + ' or `midterm` = ' + @stat + ' ) AND `session` = ' + @date + ')
GROUP BY studentID
HAVING count(*)>=3
ORDER BY TG ASC'')'
EXEC (@sql)
SELECT t2.s_studentreference [Student reference], t1.p_forenames [Forenames], t1.p_surname [Surname], t3.e_reference [TG]
FROM ##v A1, capd_person t1 INNER JOIN
capd_student t2 ON t2.s_id = t1.p_id INNER JOIN
capd_moduleenrolment t3 ON t2.s_studenttutorgroup = t3.e_id
WHERE A1.ref = t2.s_studentreference AND t3.e_reference = @tg DROP TABLE ##v
I want to be able to set the Tutor Group (@tg) this works fine in the generic query design part of the program but once i try and get this working in a report it doesnt work, i get the following error
---------------------------
Processing Errors
---------------------------
An error has occurred during report processing.
Query execution failed for data set 'October'.
Must declare the scalar variable "@tg".
---------------------------
OK
---------------------------
Any help will be very much appreciated

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

as the error msg pointed out, you have not declared @tg.

[font="Courier New"]WHERE A1.ref = t2.s_studentreference AND t3.e_reference = @tg[/font]

you'll need to either make it a report parameter or declare and populate it.

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 2

thanks,

I did make it a parameter but it didn't seem to get passed to the report. it asks for it before i click 'View Report' but then comes up with the error.

do i need to pass it in manually somehow as i did have to add the fields myself (they were not picked up automatically)

also when i try and run the code in the standard coding bit (i don't know its name, the opposite one to the 'Generic Query Designer' (GQD)) i get the following error

---------------------------

Microsoft Visual Database Tools

---------------------------

Syntax error or access violation

---------------------------

OK Help

---------------------------

which i am confused by as like i said, it works in the GQD but hopefully it will help one of you a lot more experienced than me figure out what i am doing wrong.

thanks again for any help.

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

if you're using Biz Intel Design Studio, it should detect the @tg parameter and create a corresponding tg report parameter. but, i've noticed that sometimes it can get confused.

edit the dataset, select the Parameters tab, and ensure that @tg is =Parameters!tg.Value.

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 4

Hi,

I am not using the Intel software, just the standard MS Visual Studio .net 2003 (Reporting Services)

i had a look for something similar in that with no luck. sorry about this, I am not that experienced with this program (had to teach myself everything i know about it because there isnt a training course any time soon. lol)

thanks again for any / all help

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 5

BIDS aka Biz Intel Design Studio (when I'm too lazy to type) aka SQL Server Business Intelligence Design Studio is included with SQL Server and built on Visual Studio 2005. (Why they didn't call it Visual Studio Reporting Edition is beyond me.)

Did you edit the dataset? Click the [Data] tab (to the left of [Layout]), choose your Dataset in the drop down and click the [...] button. Click the [Parameters] tab and ensure that @tg is =Parameters!tg.Value.

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 6

:D:D:D:D:D

Thank you. as u might be able to guess that worked. thanks for your time over the past few days.

now i just hope my training course comes around soon. lol

ste.cox Mr or Mrs. 500 Points: 584 More actions · Answer 7

Hello again, i thought i would re-use this thread as it is dealing with the same report.

I have come round to running this report again for the previous year and everything worked fine last year but now when i try and run it i get the following error:

---------------------------

Microsoft Development Environment

---------------------------

An error occurred while executing the query.

Could not find server 'MSQLREVIEW' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

---------------------------

OK

---------------------------

the only thing that has been changed from the code shown in the first post is the year in the date variable.

i guess i am going to have to run the procedure it suggests but i am wondering why i would need to do this as i did not have to do this last year.

any ideas?

thanks

Ste

p.s. any help on how i would run this procedure would be very much appreciated as i am not 100% sure on how to do this.

thanks