[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2398
  • Last Modified:

How do you sort parameter drop down lists in SSRS2008 Report Builder 3.0?

we are trying to build a report though Report Builder, which uses a filter parameter on one of the fields. Unfortunately, it is not giving a distinct list, nor is it ordered - is it possible to do either?
0
mr_nadger
Asked:
mr_nadger
  • 5
  • 5
1 Solution
 
Ken FayalCommented:
It is possible, but you have to do it in the underlying query.  What is the query you are using for the report?
0
 
mr_nadgerAuthor Commented:
it's based on a model, which is basically a select * from table.
The parameter is drawing from the existing values in the column we're filtering on, and was set up through report builder rather than BID studio
0
 
mr_nadgerAuthor Commented:
I've tried to  enter a sql query when creating a dataset from which to populate the drop down list, but I'm getting semantic query errors and it won't let me continue.
Is there something you need to set in the original model to allow sql queries to be used in the report builder?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Ken FayalCommented:
Yes, I would do it in the model..

SELECT DISTINCT * FROM .... ORDER BY
0
 
Ken FayalCommented:
Use the text based mode for designing.
0
 
mr_nadgerAuthor Commented:
so I'd have to order it by every column we might want to filter off?

In our more complex models, where we have seven or eight linked tables, that's not really a suitable solution. Is there no simple option within the report builder to sort the drop downs? You'd have thought that a fairly fundamental thing to have.
0
 
Ken FayalCommented:
You are talking about sorting the items in the drop-downs, right?  And the drop-down items are basically the results from a query from other tables?  It's not too unreasonable to think that you'd have to do your own sorts.  Why not base the dropdowns on views?
0
 
mr_nadgerAuthor Commented:
the report builder is for creating ad hoc reports, allowing people to include filters as required - what I'm asking here is would a user be able to add a filter, whose drop down displays the options in a distinct and ordered list.
When I use the Business Intelligence Development Studio, yes, we can easily add the sql queries as described, but we can't give all our users this piece of software, nor can we give repeatedly update the model every time a new filter is required.

At the moment, when we try to design the dataset for a dropdown to display, we cannot enter any kind of SQL query. After we select the data source, the query window does not accept even the simplest SQL and are getting the following exception

The remote server returned an error: (500) Internal Server Error.
----------------------------
The remote server returned an error: (500) Internal Server Error.
<detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsSemanticQueryEngineError</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">Semantic query execution failed. </Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsSemanticQueryEngineError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=10.50.1600.1</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">10.50.1600.1</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>Microsoft.ReportingServices.SemanticQueryEngine</Source><Message msrs:ErrorCode="rsSemanticQueryEngineError" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsSemanticQueryEngineError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=10.50.1600.1" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Semantic query compilation failed: e InvalidSemanticQuery The semantic query is not valid. Details: Data at the root level is invalid. Line 1, position 1. ( '').</Message></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" /></detail>
----------------------------
An error occurred when the query ran. Refer to the inner exception for details.
0
 
Ken FayalCommented:
This is an older problem, but does it look familiar to what you're doing?  

http://connect.microsoft.com/SQLServer/feedback/details/278686/prompt-on-an-entity-filter
0
 
mr_nadgerAuthor Commented:
the answers ignored the fact I was trying to do this from a model which wouldn't allow me to script the look up manually
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now