can expression based function(s) in ssrs be used to edit parameter entries and generate an error message without using text boxes or tablix rows?

Question

hi we run 2014 enterprise ssrs. 2019 some time in the coming months.

one of my peers told me today that expression based functions (code) in ssrs can prevent our users from entering a 0 in 2 of our $ amount parameter boxes (one is a min, the other is a max). Is that true? how?

if yes, then is it possible that such expressions can also edit the range of those 2 numbers to ensure that 0 isn't included in the range? a negative is allowed. so -1 in the min parameter amount and 1 max in the other param amount would be edited as invalid.

Answer 1

Hi @db042190 ,

You can‘t prevent user to type 0 on the parameter，but you can add a textbox to prompt user to type a value gretter than 0，if user type a zero on the parameter，you can control the fact value pass to the dateset，in the parameters tab on the dateset.
I did a local test ，hope it will help you.

1. First, you can create a textbox on the report to prompt min value should greatter than 0, and then you can create an expression to display or not display this textbox
2. You can right-click in the area outside the report, select report properties, then select code, and then add a function Public Function SelectedMore( ParamCnt As Integer ) As Boolean Dim Cnt As Boolean Cnt = IIf(ParamCnt >0, 1, 0) Return Cnt End Function
3. Next, set the properties in your newly created textbox, select visibility, select show or hide base on an expression, and then click the fx button to create an expression: =Code.SelectedMore(Parameters!min.Value) This will show or hide the textbox based on your input value
4. Now go to the the main dataset (the one which fetches the results for you), right click the dataset, choose
   dateset properties, go to the parameters tab, and clicked the fx button right beside the parameter min, then type
   = iif(Parameters!min.Value<=0,1,Parameters!min.Value), this will pass the value 1 to the dataset if your input is <=0 ，only your input is greatter than 0, the prompt text box will be hidden

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

thx isabella. I'm so sorry. i meant to say without using an existing textbox, new textbox or grid row that is normally used for data. i was juggling a few things and not thinking clearly. i'll change the title a little. the method you showed here is very helpful but we already know about that approach. we have some pretty good reasons for not using text boxes etc.

Answer 3

Hi @db042190

In that case how about using a parameter to show the alert message.

Create a parameter with TEXT type，and in the default values tab，choose the specify values，and clicked the fx button，use the function you created already.
I refer to this thread：https://stackoverflow.com/questions/30477840/report-parameter-validation-in-ssrs-report

Hope this helps.

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

thx isabella. i guess i should have asked that it be a solution that doesn't introduce an additional parameter either. is that possible?

some of the links where you sent me are blocked by my company or obsolete. so basically another param shows the error in that approach? and what if i hit view report in spite of what shows in that param?

Answer 5

thx. its interesting but we are hoping for a solution that doesn't involve extra/existing textboxes or params. Short of enabling remote errors i guess we are stuck. And enabling remote errors exposes a bit too much to our external users as i explained on a different post. i guess the answer is no. thx.