November 8, 2007 at 3:18 pm
I have a report where I need to pass a parameter to a Scalar variable in the report inside of an EXEC statement. If I statically set the variable before the EXEC statement, the report runs. If I try to pass the parameter in from a drop-down menu on the report, it does not run. The parameter is not passing into the Scalar variable. I'm not sure what I'm missing. Can anyone help?
The variable / parameter in question is @ContactTypeId below. If I set it, as shown, it works. If I erase that SET line and pass it in through the report parameter, it does not work.
******SQL Code************
DECLARE @ContactTypeId varchar(50), @ContactCategory varchar(100);
SET @ContactTypeId = 'new_primaryadministrativecontact';
SET @ContactCategory = 'FilteredAccount.' + @ContactTypeId;
EXEC ('SELECT
Account.name,
Account.address1_state,
Contact.fullname,
Contact.jobtitle,
Contact.emailaddress1,
FROM
Account RIGHT OUTER JOIN Contact ON
Account.accountid = Contact.accountid
WHERE
(FilteredContact.contactid = CAST(' + @ContactCategory + ' AS uniqueidentifier))
ORDER BY Account.name');
November 8, 2007 at 6:53 pm
I must admit, I'm not sure what this query is doing. Why does it attempt to convert a string to a uniqueidentifier? And the dynamic SQL (EXEC) is unnecessary, and may make it more difficult for SSRS to parse parameters.
As far as your immediate issue goes, on the dataset tab, edit the dataset and verify there is a binding between the @ContactTypeID and your external parameter.
November 9, 2007 at 9:42 am
It's converting to a unique identifier because the VARCHAR field needs to match the UNIQUEIDENTIFIER field. I get an error message if it's not converted. (The guys who set up the database used the wrong data type for a GUID field.)
I have to use EXEC because the variable is a database object (field name). That won't work any other way (that I've found, anyway).
The data binding fixed it from the EDIT DATA set dialog box. I didn't even know that existed. I was trying to pass the parameter in like any other ones I've done on previous reports, for use in other queries, not scalar variables.
Thanks for your help.
DG
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply