1

I've got a report that has several parameters, among them CompanyID. The available values come from a dataset with the Value field set to "Value" and the Label field set to "Label". Not exactly Rocket Science. The query looks like this: 

select null as Value, ' Any' as Label
union all
select CompanyID as Value, CompanyName as Label from core.Companies
order by Label

Ok, no surprises. When I run that query I get things like 

1506 Amalgamated Steel 
2341 Beson Industries
245  Carver Holdings 
etc....

Looks fine. However, when I view the HTML generated in report viewer, the select element that is generated has ordered integers for values: 1,2,3,4 etc. so filtering the results by company ID isn't working.

What could cause this? The query is returning the correct values, the values are being populated in the dropdown, the Value field for the param is set to the Value field from the query. They just don't have the correct value in the select/option field.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
jcollum
  • 43,623
  • 55
  • 191
  • 321

1 Answers1

0

I cannot reproduce this in reporting services, it is working exactly fine for me. My query is similiar although I always want the value "All" or "Any" as you mentioned to be at the top of my list so users dont have to look for it. I also do not include null as a value, I translate null to the value 0:

SELECT
       1 As SortOrder,
       CompanyID as Value,
       Company as Label
FROM
       Core.Companies
UNION ALL
SELECT
       0 as SortOrder,
       0 as Value,
       "All" as Label
ORDER BY 
        'bring all / any to the top
        'then order the rest by company name
        SortOrder, 
        Label

This ensures the null is not included in the result list...then in my main query the query that pulls the data, when I want to filter for the company id or all I do this:

SELECT
      ...
FROM
      Mytable
WHERE
      CompanyID = @CompanyID OR @CompanyID = 0    'handle one customer or all customers

The report works fine and my select has the correct values...

JonH
  • 32,732
  • 12
  • 87
  • 145
  • Mine has a space in front of "Any" so it orders correctly. Your way may be faster though. ' – jcollum Aug 09 '11 at 18:39
  • @jcollum - yes but why would you want an extra space in front of any? It means nothing and it only serves to make your code and sql harder. I've seen people do some crazy stuff like put a "." in front of a word to move it up in a list. If I'm an end user and I have a drop down and I begin typing the letter 'a' so that any comes up, it wont as you've got a space before it. Thats a bad UI in my experience, if you can avoid it, which I've shown here, then avoid it. – JonH Aug 09 '11 at 18:41
  • 'Null' seems like a better choice for a 'Not Selected' value than 0. 0 could be a valid value in most tables with integers for PKs. Imagine someone inserts data and uses 0 for an ID, now your report is broken. – jcollum Aug 09 '11 at 18:43
  • @jcollum - it depends on your key I guess. If you aren't using auto increments that start with 0 sure why not. – JonH Aug 09 '11 at 18:48
  • No, what I'm saying is that someone can come along and insert a record with a perfectly valid PK that would break your report. It's not about seed values. – jcollum Aug 09 '11 at 19:07
  • jcollum - that's not possible because you've defined the range to be > 0. If you try to enter it as 0 sql throws up an error message. I am speaking about my database right now, yours might be more "open" to non domain values. – JonH Aug 09 '11 at 19:08