Monday, January 21, 2013 11:14 AM
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.
Monday, January 21, 2013 12:03 PMdeclare @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
- Edited by Uri DimantMVP Monday, January 21, 2013 12:03 PM
Monday, January 21, 2013 12:12 PM
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..
Monday, January 21, 2013 12:32 PM
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 functionThe 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
Tuesday, January 22, 2013 8:53 AMHey 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.
Tuesday, January 22, 2013 9:39 AMHey Krootz. Sorry i was setting the hidden property of Rectable box rather than the tablix. Its working fine.. Thanks for the help