Stefan on SharePoint | SQL Reporting Services - Multi-value Parameter Default Values Not Working

<< New Home Media Forum/Blog Site | RockBox Rocks my iBox! >>

SQL Reporting Services - Multi-value Parameter Default Values Not Working

19. October 2007 06:22

Today I was having a terrible time with SQL reporting services default values for a multi-value parameter on my report.

I haven't been able to track down the root cause, but it seems a little tweaking to the query that selects the options will usually fix it.

For instance, try adding a rtrim or ltrim around the field when selected in the query.

Before:

select distinct option from options

And After:

select distinct rtrim(option) as option  from options

This seems to help in most cases. If it doesn't, try performing a lower/upper on the string, or a replace on whitespaces.

I'll be interested to hear peoples feedback on this!


Tags:	Comments (7) \| Permalink

SQL Reporting Services Error- Maximum request length exceededWhen trying to deploy a large report to your SSRS server, you may run into an error like this: Error...SQL Reporting Services Switch StatementSSRS has a switch function, which mirrors the functionality of a Case statement, and can really save...ViewState: To be, or not to beIt seems one of the more complicated aspects of ASP.Net for many developers is understanding ViewSta...

Comments

3/11/2009 4:33:42 PM #

Even I am experiencing the same problem, did you find any solution for this? I tried Rtrim, but it doesn't seems to work for me.

Anonymous |

3/11/2009 4:33:42 PM #

I actually had the opposite issue. In my parameter list I had a '' (blank) value as a choice and Default Select All was not working. I removed the blank value from the query in the Where clause, but then unioned it back with Select SPACE(1) and it worked.

burke |

3/11/2009 4:33:42 PM #

This helped me:

select distinct option from options where option is not null

Laci |

3/11/2009 4:33:42 PM #

I'm esperiencing the same problem too. I tried using the tricks suggested but default values for certain datasets (just 2 on 10 or more used) are not populated yet. Any ideas about the reason of this stange behaviour?

Anonymous |

4/28/2009 2:45:51 AM #

Hi all

When you use multi-value parameters in SSRS en run de report, the parameters selected are passed to the database, separated by commas, for example, 'Value1,Value2,Value3,...ValueN,', so you have to do a SP and format de parameter. for example:

Crea pro SPName
@Option varchar(100)
as
set quoted_identifier off
declare @SqlText varchar(3000)
set @SqlText = "select * from options where option in('" + replace(Option, ",","','") +"')"
exec (SqlText)

Excuseme by my English.

D Oleo |

7/8/2009 12:50:59 AM #

I'm having the same problems on a report that uses SSAS Cubes. Bad enough MDX doesnt have an string manipulation functions. Any hints?

andre |

7/8/2009 5:40:24 PM #

If you enter your SQL statement as text instead of ticking Stored Proc, the parameters are interpreted on the report server before sending to the SQL server. You dont need to use the function Join(parameters!parm.value,",") to turn the array of parameters into a commer delimited string.
However the biggest problem is that when "Select All" is selected every item is ticked. This causes a major performance problem. Example - 2000 employees with an eight char ID plus commer = 18K. The network should have no problems, but performance is bad. Even rendering the report is slow.
It is a pity we cant go back to recognising a single select all value and handling it with an OR statement in the SQL. ie When employeeID IN (@parameter) OR @Parameter = ''

David Chapman |

Comments are closed

Stefan Keir Gordon

By Date