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....