Skip to main content

Passing multivalue parameters in an external url ssrs

In most of the reports, we use multivalue parameters and for that we can directly write sql query in a separate data sets and modify the main dataset. It works fine for BIDS and Report Server for multi value parameters. But when we need to pass parameter values from the external portal to the reports then follow below steps:
1) Create a split function in your database (Many examples are available online)
2) Create a separate dataset for multivalue parameters
3) Change main dataset to populate multivalue parameter values from split function i.e: and (param1 in        
    (select column_value as param1 from table(HR.SPLIT(:Param1))) OR :param1 = ‘ALL’)
4) Go to the main dataset properties –> parameter –> select multivalue parameter expression –> add
    this: =Join(Parameters!Param1.Value,”,”)
5) It should work now
For the URL populate multivalue paramter value list externally using sql query. I have also included 'ALL' as a value in a list
Ex: select distinct param1 from table1
       where param1 is not null
       union
      select ‘ALL’ as param1 from dual


Thank you

"Every piece of design can be a work of art"
Labels:

Comments

Post a Comment

Popular posts from this blog

ALL as a parameter value in ssrs oracle sql

In some cases as per the requirements you will need to add 'ALL' as an independent parameter value (not a multivalue parameter) 1) Create a new dataset to populate parameter values Ex: select distinct city from table1 where city is not null        union       select 'ALL' as city from dual       order by 1 2) Modify your main dataset query Ex: where (city = :city or 'ALL' = :city) Thank you
Post a Comment
Read more

_x003C_none_x003E_ FlatFile Error in SSIS

If you are receiving this error in your output file, It is because of the value in the text qualifier property (Text Qualifier = <none>).  When you export the data in a file, you might see the _x003C_none_x003E_ (x003C = "<" and none and x003E = ">") with your original field value. • Do not open the connection manager editor once you finalize everything and just clear out the <none> value  from the property window on the right side (Do not open show editor) and save it. You’re done. • Other way around, You can also define the Expression value for the text qualifier and provide (“”) as a value in the expression. Go to the property --> Expression --> Select the Text qualifier from the drop down list --> provide the expression value (“”)
1 comment
Read more

Default values for a date parameter in SSRS

We can use sql queries or some of the cool expressions available in SSRS to set default date parameters: Set First Day of previous week (Monday) =DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)) Set Last Day of previous week (Sunday) =DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today)) Set First Day of Current Week (Monday) =DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today()) Set Last Day of Current Week (Sunday) =DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today()) Set First Date of last month =DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)) Set Last date of last month =DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1)) Set First date of current month =DateSerial(Year(Now()), Month(Now()), 1) Set Last date of current month =DateAdd("d",-1,(DateAdd("m", 1,...
3 comments
Read more