Multi-value Parameter Calculation

OK Guys....

If you answer this one, you will save my life...and you WILL be the MAN or WOMAN!!!!

Problem: I have a set of 24 matrix's that need to calculate the difference between the last two years and display in a field to the right of the last rendered column. Since I have been struggling with this, let's just assume there is no better way than how I currently have it set up. (one table that does the calculations for me and I set one field on the report to display the most recent two columns difference in my report)

what I can not figure out is: when I choose one of my parameters the report displays the information I want...but when I choose more than one...well there is the problem....

In order to obtain the most help for myself I will ask this in the most general way possible so as not to get bogged down into my specific solution...

Desired Result: How to pass all my parameter values from my multi-value parameter during runtime to a SQL Stored proc from my dataset within Reporting Services at runtime, Match the parameter to the field, get the result and store it in a variable, then do it again and add the second to the first within the variable, and so on and so on , until all of the parameters are used. Then sum the values and display in a field.

HELP, HELP, HELP Please....

c-mon anyone know how to do this?|||

Well, I think you might have gone a bit too general for me to really follow what you're asking.

Have you narrowed down at all where the problem lies?

As far as Multi-valued parameters and Stored Procs,

You may have noticed that if you use a text query instead of a stored procedure, you can use the syntax:

Code Snippet

select * from foo where bar in (@.MultiValuedParameter)

And you are looking for a way to do the same, but use a stored procedure. Is that about right, or are you wanting me to comment on your 24 matrices?

If you are using sql2005, you can get very close if you use a table-valued function instead of a stored procedure, as long as your stored procedure does not modify the database in any way.

The way you would do that by creating a text dataset that looked something like this:

Code Snippet

SELECT *

FROM foo CROSS APPLY fnNewTVF(foo.bar, ...)

WHERE foo.bar IN (@.multiValuedParameter)

Cross Apply passes each value for foo.bar to your function in turn, as you described.

If you must use a stored proc, you can either use JOIN to make a comma-delimeted string value to your stored procedure, or you can use some custom code that will generate the query you need on the fly.

Does that answer your question?

|||Thank You....

multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?