Setting Constraints for a Report Parameter

Question

Hi Friends,

I have report which has a Parameter named Account. This Account is a Text Parameter. We can send multiple accounts seperated by Comma. I have a requirement which says that I cannot process more than 5 accounts. So I am looking if there is a way to check this in the Report Parameter.

As a work around, I am checking the no of account passed within the SP used in the Report and I throw an exception if the no of accounts is more than 5. But, I would like to know if we can check this in the Report Parameter itself.

Any help would be appreciated.

Thanks

---

Murali Krishnan

Answer 1

declare @account varchar(20)

set @account ='1,2,3,45,55,6,77'---'5,3,4'  


select case when len(@account)-len(replace(@account,',',''))<= 5 then 'that is ok'
else 'you have provided more than 5 accounts' end

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

Hi Uri,

Thanks for the response. I was also doing a similar thing. However, these are done from the DB side. I wanted to know if there are any ways to check this from the SSRS itself. I guess that we do not have that option..

---

Murali Krishnan

Answer 3

hi,

no way to validate input against the parameter until the report is ran. so the work around to this is to create a text box on the report with a static text "Only max of 5 accounts can be processed", that you can set visibility to true after validating the parameter input while setting the visibility of your tablix, table to false.

Create a UDF, put in the report code.

Public Function Validate(byval pValue as string) as boolean
arr=Split(pVal,",")
if UBound(arr)>5 then
  return False
else
  return True
end function

The in your textbox and tablix visibility proprety enter the expression =Code.Validate(Join(Paramaters!Account.Value,","))

---

Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

k r o o t z

Answer 4

Hey Krootz..This seems to be a nice option. However, I would like to know if there is a way to stop the report from running if the number of accounts are more than 5. Otherwise, we are just setting the visibility option to false for the tablix and the tablix is getting generated in the background. Is it possible for us to throw or show some error message using custom code.

---

Murali Krishnan

Answer 5

Hey Krootz. Sorry i was setting the hidden property of Rectable box rather than the tablix. Its working fine.. Thanks for the help

---

Murali Krishnan