June 20, 2018 at 10:43 am
I am trying to write an array expression to be one that returns all values if "ANY" is input, or a specific value if that is typed into the parameter box. My goal is for the user to either type "ANY" into a parameter field, or type in a specific value, such as "253". the result set would either be for the specific supplied value (in this example, 253), or all values for "ANY".
IIf(Parameters!ReasonCode.Value = "ANY", "", " AND h.reasoncode = " + Parameters!ReasonCode.Value + " ")
This is what I thought would handle it.
However the report returns this:
An error has occurred during report processing. (rsProcessingAborted)Cannot read the next data row for the dataset DS. (rsErrorReadingNextDataRow)
If I remove the above expression, and corresponding parameter, the whole thing works just fine. So something is off in my logic here, but I can't spot it.
If this was purely within SSMS I would do something like this:(h.reasoncode = @ReasonCode OR COALESCE(@ReasonCode,'') = '')
June 20, 2018 at 11:50 am
This kind of thing should be handled in your dataset, as opposed to SSRS. You can also make a parameter multi-valued, and use the JOIN function within SSRS to construct a comma-delimited string that becomes a parameter value, and then you can use Jeff Moden's string splitter function DelimitedSplit8K to turn those values into a table within your stored procedure. The code for that splitter function (which operates at lightning speed, btw), can be found here:
http://www.sqlservercentral.com/articles/72993/
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 12:14 pm
So I am self taught here, I would love to take a class on SSRS, but right now its not in the budget. I am doing the best I can, and if this was just pure SQL I would have had this whole thing done in about 10 minutes (Again self taught there).
regardless of all that :
It is all in the Dataset
EX here is the rest of the where clause
+ " WHERE 1=1 "
+ IIf(IsNothing(Parameters!StartDate.Value),"", " AND (hr.r_date > DATEADD(DAY,-1,''"
+ Parameters!StartDate.Value
+ "'')) ")
+ IIf(IsNothing(Parameters!EndDate.Value),"", " AND (hr.r_date < DATEADD(DAY,1,''"
+ Parameters!EndDate.Value
+ "'')) ")
+ " AND hr.PAY IN (''" + Join(Parameters!P.Value,"'',''") + "'') "
+ IIf(Array.IndexOf(Parameters!Claim.Value, "ANY") > -1, "", " AND hr.claim IN (''" + Join(Parameters!Claim.Value,"'',''") + "'') ")
+ IIf(Array.IndexOf(Parameters!Group.Value, "ANY") > -1, "", " AND h.group IN (''" + Join(Parameters!Group.Value,"'',''") + "'') ")
I have a dataset for all of the other parameters, except the dates, I even had to create a dataset to tell the From section what server/Database to pull the data from bases on a parameter.
I am pretty sure i just messed up an end quote here, I appreciate your reply.
June 20, 2018 at 12:27 pm
If you are going to do dynamic SQL, please do that in your stored procedure. Don't try to do this in SSRS. It's extremely difficult to troubleshoot, and a genuine pain. Pass all your parameters to the stored procedure and let it construct the proper query.
EDIT: That's what I meant in my prior post, and I'll apologize for not making that clear. I did say do it all in the dataset, but I assumed (dumb-a$$ me !!) you would realize I meant the stored procedure. You can still use JOIN function for multi-valued parameters and pass those along as comma-delimited strings, and you can use Jeff Moden's DelimitedSplit8K string splitting inline-table-valued function (search this site's Articles for "Tally Oh". The code is linked to as a zip file at the bottom of that article). It performs like greased-lightning.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 20, 2018 at 12:32 pm
I would if I had a choice, I do not. This is an expression in SSRS, and due to how my employer wants things, I do it this way, or i don't do it at all. Not really an option here considering our economy.
Thank you for your time.
June 20, 2018 at 3:25 pm
cirathorn - Wednesday, June 20, 2018 12:32 PMI would if I had a choice, I do not. This is an expression in SSRS, and due to how my employer wants things, I do it this way, or i don't do it at all. Not really an option here considering our economy.Thank you for your time.
That's beyond absurd. Tell your employer they can be responsible when it breaks, and that it will take you at least twice as long or more to do it. It might not win you any points, but doing your query their way is dangerous and fraught with the potential for problems. It makes no sense to construct the query within SSRS. What happens when you're gone and someone else has to change this report and the change causes the query to bring the server to it's knees because the query is no longer being constructed correctly. Really really bad idea...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 21, 2018 at 6:20 am
So thats not really helpful, or realistic, but I appreciate you taking the time to type it.
Regardless of that, I ended up creating another parameter, to feed the parameter in my first post, and then set a case statement to pass them back and forth through another dataset, which then feeds the main dataset.
July 11, 2018 at 12:05 pm
cirathorn - Thursday, June 21, 2018 6:20 AMSo thats not really helpful, or realistic, but I appreciate you taking the time to type it.Regardless of that, I ended up creating another parameter, to feed the parameter in my first post, and then set a case statement to pass them back and forth through another dataset, which then feeds the main dataset.
All I can tell you is god help the poor soul who has to try and fix that mess when it breaks....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply