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