Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Setting Constraints for a Report Parameter

Answered Setting Constraints for a Report Parameter

  • Monday, January 21, 2013 11:14 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, January 21, 2013 12:03 PM
     
     
    Sign In to Vote
    0
    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


     
  • Monday, January 21, 2013 12:12 PM
     
     
    Sign In to Vote
    0

    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

     
  • Monday, January 21, 2013 12:32 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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

     
  • Tuesday, January 22, 2013 8:53 AM
     
     
    Sign In to Vote
    0
    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

     
  • Tuesday, January 22, 2013 9:39 AM
     
     
    Sign In to Vote
    0
    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