All Multiple Collection to be selected (How to?)

[cfreeman21]

I have my main SQL Query:

SET DATEFORMAT dmy
DECLARE @TODAY smalldatetime = GETDATE()
SELECT DISTINCT
  SYS.Name0 AS [Computer Name],
  CPU.Manufacturer0 AS [Manufacturer],
  CPU.Name0 AS [Name],
  CPU.NumberofLogicalProcessors0 AS [Logical CPU Count],
  CPU.NumberofCores0 AS [Number of Cores per CPU],
  CS.NumberofProcessors0 AS [Number of CPUs],
  CASE
    WHEN SYS.Is_Virtual_Machine0 = 1 THEN 'Virtual'
    ELSE 'Physcial'
  END AS [Server Type],
  RAM.TotalPhysicalMemory0 / 1024 / 1024 AS [Memory (GB)],
  SQL.InstanceName0 AS [Instance Name],
  CASE
    WHEN SQL.Version0 LIKE '8.%' THEN 'SQL 2000'
    WHEN SQL.Version0 LIKE '9.%' THEN 'SQL 2005'
    WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008'
    WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008 R2'
    WHEN SQL.Version0 LIKE '11.%' THEN 'SQL 2012'
    WHEN SQL.Version0 LIKE '12.%' THEN 'SQL 2014'
    WHEN SQL.Version0 LIKE '13.%' THEN 'SQL 2016'
    ELSE 'UnKnown'
  END AS [Version],
  SQL.Edition0 AS [Edition],
  SQL.InstanceType0 AS [Instance Type],
  SQL.TCPPort0 AS [SQL TCPPort]
FROM v_R_System SYS
INNER JOIN v_GS_Processor CPU
  ON SYS.ResourceID = CPU.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM CS
  ON SYS.ResourceID = CS.ResourceID
INNER JOIN v_gs_x86_pc_memory RAM
  ON SYS.ResourceID = RAM.ResourceID
INNER JOIN v_GS_SQLINSTANCE SQL
  ON SYS.ResourceID = SQL.ResourceID
INNER JOIN v_FullCollectionMembership FC
  ON SYS.ResourceID = FC.ResourceID
INNER JOIN v_Collection VC
  ON FC.CollectionID = VC.CollectionID
WHERE FC.CollectionID LIKE @ColID
AND SQL.InstanceType0 LIKE @SQLInstanceTypes
AND CASE
  WHEN SQL.Version0 LIKE '8.%' THEN 'SQL 2000'
  WHEN SQL.Version0 LIKE '9.%' THEN 'SQL 2005'
  WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008'
  WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008 R2'
  WHEN SQL.Version0 LIKE '11.%' THEN 'SQL 2012'
  WHEN SQL.Version0 LIKE '12.%' THEN 'SQL 2014'
  WHEN SQL.Version0 LIKE '13.%' THEN 'SQL 2016'
  ELSE 'UnKnown'
END LIKE @SQLVersions

Then I have a few secondary querries:

All_Collections:

SELECT DISTINCT
  c.CollectionID,
  c.Name
FROM v_collection c
WHERE c.CollectionType = 2
ORDER BY c.Name

SQL_Instance_Type:

SELECT DISTINCT
SQL.InstanceType0 AS [Instance Type]
FROM v_GS_SQLINSTANCE SQL
UNION SELECT ''
ORDER BY [Instance Type]

SQL_Version:

SELECT DISTINCT
  CASE
    WHEN SQL.Version0 LIKE '8.%' THEN 'SQL 2000'
    WHEN SQL.Version0 LIKE '9.%' THEN 'SQL 2005'
    WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008'
    WHEN SQL.Version0 LIKE '10.%' THEN 'SQL 2008 R2'
    WHEN SQL.Version0 LIKE '11.%' THEN 'SQL 2012'
    WHEN SQL.Version0 LIKE '12.%' THEN 'SQL 2014'
    WHEN SQL.Version0 LIKE '13.%' THEN 'SQL 2016'
    ELSE 'UnKnown'
  END AS [Version]
FROM v_GS_SQLINSTANCE SQL
UNION SELECT ''
ORDER BY [Version]

Then of Course I have 3 parameters @ColID, @SQLInstanceTypes, @SQLVersions

 

I do not know how to allow for multiple selections on the parameters for example I may want to choose multiple collections or SQL Version and so... I now within the parameter you can set to allow multiple but then I just get an error when choosing multiple. What is the proper way to allow multiple choices how do I need to update the parameter and the queries?

[Kakemonstre]

Hi

You have to use the "in" keyword when working with multi valued parameters.

 

instead of "WHERE FC.CollectionID LIKE @ColID AND SQL.InstanceType0 LIKE @SQLInstanceTypes"

 

use

 

"WHERE FC.CollectionID in (@ColID) AND SQL.InstanceType0 in (@SQLInstanceTypes)"

 

parameter has to be in parenthesis

[cfreeman21]

I made the change but I still get nothing?

[Kakemonstre]

On the end of the main query, I not sure you can use the CASE statement like that.

I would try to replace it with a "...AND SQL.Version0 in (@SQLVersions)

 

then i would change the 3rd parameter query to return both the text value from the case statement and the version number. then use the Text for Parameter Name and the Versionnumber as value on the parameter itself.

[cfreeman21]

On the end of the main query, I not sure you can use the CASE statement like that.

 

I would try to replace it with a "...AND SQL.Version0 in (@SQLVersions)

I made this change... No Luck

 

 

then i would change the 3rd parameter query to return both the text value from the case statement and the version number. then use the Text for Parameter Name and the Versionnumber as value on the parameter itself.

Sorry I am not following you here on exactly what you are saying to do. Can you show an example?

 

On my main Dataset the 3 parameters I have setup (@ColID, @SQLInstanceTypes, @SQLVersions) each parameter is using an expression.

 

@ColID =join(Parameters!ColID.Value,",")

@SQLVersions =IIF(Parameters!SQLVersions.Value ="","%",Parameters!SQLVersions.Value)

@SQLInstanceTypes =IIF(Parameters!SQLInstanceTypes.Value ="","%",Parameters!SQLInstanceTypes.Value)

 

Are these the settings you want me to change?

[cfreeman21]

I changed the parameter @CollID from

 

@ColID =join(Parameters!ColID.Value,",")

 

to

 

@ColID =Parameters!ColID.Value

 

appears to be working now.