Conditional Drop. Down List with SSRSOne of the powerful features of Reporting Services is that everything is an expression, including the datasets SQL statement. Lets say your first parameter the one that describes what to select is called Location and it selects a list of locations such as country, region, etc. Perhaps you get that from a table which has a Location. Id and a Description like so SELECT Location. Id, Description FROM Locations. You hook up the Location parameter to this query to get your drop down list of location selectors for the Location parameter. Now create a second parameter called Select where we want to select from a list of countries or regions. To keep it simple, Im going to assume there are only two locations Country with a Location. Id of 1 and Region with a Location. Id of 2. Create a new dataset called Selections and manually add fields to it called Id and Description. Hook up your Select parameter to this dataset. You dont need gds32. dll or fbclient. dll anymore. Just install Interbase or Firebird SQL server and connect your software in a few minutes with this driver. Multiselect parameters give your users control over their reports while reducing the number of reports they have to work with. In this example, I will demonstrate. Have just deployed my Project on to my reporting Server. I have multiple datasets which are referencing views which exist on the db on that server. When I try to go. Overview A quick review of how to display SingleValue and MultiValue parameter selections within an SSRS report heading. Level Basic familiarity with parameters. Now for the SQL Statement for the Selections dataset, enter the following expression IIFParameters Location. Value 1. SELECT Country. Id AS Id, Country. Name AS Description FROM Countries. SELECT Region. Id AS Id, Region. Name AS Description FROM Regions. So, where the Location parameter is set to 1 Country you select from the Countries table otherwise you select from the Regions table. You alias the field names so you get consistently named fields for your dataset for use in the Select parameter query. Obviously, you can extend this to more selections as required. You get the idea but this is a little fragile whenever you want to add a new location type you have to go through all your reports and update the SQL statement for the Selections dataset. Thats tedious and no one wants that job. What we want is an automated system where all reports get the new selections whenever they are added. So lets add a column to the Locations table, called SQLStatement. For the Country row, this will have the value SELECT Country. Id AS Id, Country. Name AS Description FROM Countries. For the Region row, the SQLStatement field has the value SELECT Region. Id AS Id, Region. Name AS Description FROM Regions. Now the Locations table has the value of the SQLStatement in it for the Selections dataset. You cant use this directly your dataset would just return the value of the SQL statement field, not execute it but you want to have something that returns this string as the expression to use for the SQL statement for the Selections dataset. Custom code can be used to do this. So the expression to use for the Selections dataset will be something like this Code. Get. SQLStatementParameters Location. Value. And then you have custom code function like this significant parts left out Public Function Get. SQLStatementLocation. Id As Integer As String. Dim SQL As String. SQL SELECT SQLStatement FROM Locations WHERE Location. Id CStrLocation. Id. Connect to database, get SQLStatement field. Get. SQLStatement lt Field Value. When you want to add another location selection, for example continents, all you have to do is add another row to the Locations table, say Location. Id 3, Description Continent and SQLStatement SELECT Continent. Id AS Id, Continent. Name AS Description FROM Continents and now every report you have that selects by location will be able to use Continents.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |