none
How affect query with all selected parameter values?

    Question

  • Hello,

    let's say I have table with peoples names and their visit cities.

    Name City

    John London

    John Moscow

    John Tokyo

    Peter London

    Peter Moscow

    Josh London

    Parameter has multi value property and has such values:

    None, London, Moscow, Tokyo.

    Now I need to select names according to selected parameter values.

    If user selects 'None', then ALL names should be returned.

    If selected 1 or more city then should be check if ALL cities belongs to Name.

    For example, if I select London, Moscow, query should return John and Peter because they visited ALL selected cities. Josh has visited only London thus his value is not returned.

    If I select London, Tokyo, query should return only John's name because he is the only one who has visited both these cities.

    My idea was to create new parameter which counts selected values and compares this result.

    But I don't know if it is possible:

    Dataset1: set @cities_count = Parameters!CitiesCount

    Main_Dataset:

    select * from names

    where name in (

    select name from visited_cities

    where city in (@cities)

    group by name having count(*) = @cities_count)

    Maybe anyone could offer another method or help to complete this one?


    • Edited by -VB- Thursday, August 22, 2013 10:34 AM
    Thursday, August 22, 2013 10:31 AM

Answers

  • This is called a relational division (one of the relational operator in RDBMS).. this can be done usingg many methods...one method is given below:

    DECLARE @Visit TABLE
    (
    Name VARCHAR(20),
    City VARCHAR(20)
    )
    INSERT INTO @Visit VALUES
    	('John', 'London'),
    	('John', 'Moscow'),
    	('John', 'Tokyo'),
    	('Peter', 'London'),
    	('Peter', 'Moscow'),
    	('Josh', 'London')
    
    SELECT Name
    FROM @Visit
    WHERE City IN ('London', 'Tokyo')
    GROUP BY Name
    HAVING COUNT(DISTINCT City) = 2
    
    /* Result
    Name
    John
    */
    
    SELECT Name
    FROM @Visit
    WHERE City IN ('London', 'Moscow')
    GROUP BY Name
    HAVING COUNT(DISTINCT City) = 2
    
    /* Result
    Name
    John
    Peter
    */

    You can read more about the relational division and various methods here: https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/


    Krishnakumar S

    Thursday, August 22, 2013 11:13 AM
  • Hi VB,

    In Reporting Services, we can use the expression below to get the number of selected parameter values.
    =Parameters!City.Count

    And your report should display all the Name when the City parameter contain 'None', right? In this case, you can use IF ELSE on the query, I have tested it on my local environment, The steps below are for your reference.

    1. Create two more parameters CityCount and CityName, and set their visibility to Hidden.
    2. Use the expression below to set the default values for parameters.
      CityCount:=Parameters!City.Count
      CityName:=Join(Parameters!City.Value,"")
    3. Use the query below to create the dataset.
      IF (PATINDEX('%None%',@CityName)=0)
      SELECT        Name
      FROM          TableName
      WHERE        City IN(@City)
      GROUP BY Name
      HAVING        COUNT(DISTINCT City) =@CityCount
      ELSE
      SELECT DISTINCT(Name) FROM TableName

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support


    Friday, August 23, 2013 2:22 AM

All replies

  • This is called a relational division (one of the relational operator in RDBMS).. this can be done usingg many methods...one method is given below:

    DECLARE @Visit TABLE
    (
    Name VARCHAR(20),
    City VARCHAR(20)
    )
    INSERT INTO @Visit VALUES
    	('John', 'London'),
    	('John', 'Moscow'),
    	('John', 'Tokyo'),
    	('Peter', 'London'),
    	('Peter', 'Moscow'),
    	('Josh', 'London')
    
    SELECT Name
    FROM @Visit
    WHERE City IN ('London', 'Tokyo')
    GROUP BY Name
    HAVING COUNT(DISTINCT City) = 2
    
    /* Result
    Name
    John
    */
    
    SELECT Name
    FROM @Visit
    WHERE City IN ('London', 'Moscow')
    GROUP BY Name
    HAVING COUNT(DISTINCT City) = 2
    
    /* Result
    Name
    John
    Peter
    */

    You can read more about the relational division and various methods here: https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/


    Krishnakumar S

    Thursday, August 22, 2013 11:13 AM
  • Thank for replay.

    2 more questions. How correctly in RS get number of selected parameter values?

    How query should be like when selected value "None". In that way parameter shouldn't be evaluated and all names should be returned.

    Thursday, August 22, 2013 11:23 AM
  • Hi VB,

    In Reporting Services, we can use the expression below to get the number of selected parameter values.
    =Parameters!City.Count

    And your report should display all the Name when the City parameter contain 'None', right? In this case, you can use IF ELSE on the query, I have tested it on my local environment, The steps below are for your reference.

    1. Create two more parameters CityCount and CityName, and set their visibility to Hidden.
    2. Use the expression below to set the default values for parameters.
      CityCount:=Parameters!City.Count
      CityName:=Join(Parameters!City.Value,"")
    3. Use the query below to create the dataset.
      IF (PATINDEX('%None%',@CityName)=0)
      SELECT        Name
      FROM          TableName
      WHERE        City IN(@City)
      GROUP BY Name
      HAVING        COUNT(DISTINCT City) =@CityCount
      ELSE
      SELECT DISTINCT(Name) FROM TableName

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support


    Friday, August 23, 2013 2:22 AM