CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-services
choloboy
choloboy

Reputation: 815

Show all or one for parameter in SSRS

I have been stuck on a problem for over two days now and though it seemed super simple (im sure it is) I cant seem to get it.

I have a query I am using for a report and I have 3 parameters. 2 of them I input a string and thats fine. Though the last parameter which is for server names, I want to either list all available or just the one I enter.

I have tried using a CASE in my select, in a WHERE clause and many other things with no luck at achieving what I want.

The following is the last attempt I tried just to show what I have. 

SELECT DISTINCT    
       c1.BlockSize, c1.BootVolume, c1.Compressed, c1.SystemName, c1.Label,   c1.Caption, c1.PageFilePresent,
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity) AS Capacity,
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.FreeSpace) AS [Free Space], 
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity - c1.FreeSpace) AS [Used Space],
       100 * c1.FreeSpace / c1.Capacity AS [Free Space %],
       [CLE_ENV_SHORT], [CLE_ENV_CAT_SHORT]

FROM       CCS_Win32_Volume c1

JOIN       [dbo].[CCS_V_SERVER_INSTANCE_DETAILS] c2 on c1.SystemName =  c2.CSL_SERVER_NAME

WHERE      (@p_ServerName = c1.SystemName OR c1.SystemName = c1.SystemName) 
 AND   (c2.[CLE_ENV_SHORT] = @p_Env OR @p_Env = 'all')
 AND   (c2.[CLE_ENV_CAT_SHORT] = @p_EnvCat OR @p_EnvCat = 'all')

ORDER BY   c2.[CLE_ENV_CAT_SHORT], c2.[CLE_ENV_SHORT]

So basically I want to be able to see all SystemName or a specific one using the @p_ServerName parameter... Anyone know how this can be done? Or what went wrong with my attempt? The CASE I tried was basically the same as the condition in my WHERE clause, just used the CASE syntax.

All help is greatly appreciated as I have been stuck on this for days now... Cheers!

EDIT: This is something else I tried, and in Management Studio it works just fine, but it SSRS it doesn't... Ignore the DECLARE's, its for testing in management studio...

DECLARE    @p_ServerName nvarchar(10) = 'all'
DECLARE    @p_Env        nvarchar(10) = 'all'
DECLARE    @p_EnvCat     nvarchar(10) = 'all'

SELECT DISTINCT    
           c1.SystemName, c1.BlockSize, c1.BootVolume, c1.Compressed, c1.Label, c1.Caption, c1.PageFilePresent,
           [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity) AS Capacity,
           [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.FreeSpace) AS [Free Space], 
           [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity - c1.FreeSpace) AS [Used Space],
           100 * c1.FreeSpace / c1.Capacity AS [Free Space %],
           [CLE_ENV_SHORT], [CLE_ENV_CAT_SHORT]

FROM       CCS_Win32_Volume c1

JOIN       [dbo].[CCS_V_SERVER_INSTANCE_DETAILS] c2 on c1.SystemName = c2.CSL_SERVER_NAME

WHERE      --(@p_ServerName = c1.SystemName OR c1.SystemName = c1.SystemName) 
     c1.SystemName = 
     CASE
     WHEN @p_ServerName = 'all' THEN c1.SystemName
     WHEN @p_ServerName != 'all' THEN @p_ServerName
     END

     AND   (c2.[CLE_ENV_SHORT] = @p_Env OR @p_Env = 'all')
     AND   (c2.[CLE_ENV_CAT_SHORT] = @p_EnvCat OR @p_EnvCat = 'all')

ORDER BY   c2.[CLE_ENV_CAT_SHORT], c2.[CLE_ENV_SHORT]

I would like to emphasize again that in management studio it works fine, only in ssrs is where i run into problems.

Upvotes: 0

Views: 103

Answers (1)

Turophile
Turophile

Reputation: 3405

Well, let's try to brute force it:

SELECT DISTINCT    
       c1.SystemName, c1.BlockSize, c1.BootVolume, c1.Compressed, c1.Label, c1.Caption, c1.PageFilePresent,
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity) AS Capacity,
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.FreeSpace) AS [Free Space], 
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity - c1.FreeSpace) AS [Used Space],
       100 * c1.FreeSpace / c1.Capacity AS [Free Space %],
       [CLE_ENV_SHORT], [CLE_ENV_CAT_SHORT]
FROM       CCS_Win32_Volume c1
JOIN       [dbo].[CCS_V_SERVER_INSTANCE_DETAILS] c2 on c1.SystemName = c2.CSL_SERVER_NAME
WHERE      c1.SystemName = @p_ServerName
     AND   (c2.[CLE_ENV_SHORT] = @p_Env OR @p_Env = 'all')
     AND   (c2.[CLE_ENV_CAT_SHORT] = @p_EnvCat OR @p_EnvCat = 'all')

UNION

SELECT DISTINCT    
       c1.SystemName, c1.BlockSize, c1.BootVolume, c1.Compressed, c1.Label, c1.Caption, c1.PageFilePresent,
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity) AS Capacity,
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.FreeSpace) AS [Free Space], 
       [dbo].[CCS_DIGITAL_STORAGE_CONVERTER]('B', 'GB', c1.Capacity - c1.FreeSpace) AS [Used Space],
       100 * c1.FreeSpace / c1.Capacity AS [Free Space %],
       [CLE_ENV_SHORT], [CLE_ENV_CAT_SHORT]
FROM       CCS_Win32_Volume c1
JOIN       [dbo].[CCS_V_SERVER_INSTANCE_DETAILS] c2 on c1.SystemName = c2.CSL_SERVER_NAME
WHERE      @p_ServerName = 'all'
     AND   (c2.[CLE_ENV_SHORT] = @p_Env OR @p_Env = 'all')
     AND   (c2.[CLE_ENV_CAT_SHORT] = @p_EnvCat OR @p_EnvCat = 'all')

ORDER BY   c2.[CLE_ENV_CAT_SHORT], c2.[CLE_ENV_SHORT]

Upvotes: 1

Related Questions