2

I am working on a report in SSRS, I can input the parameter value and obtain results. What I want though, for example is to list all servers if I enter 'all' or to list the specific server that I enter.

Is this a TSQL change? Or a change in SSRS parameter settings? Anyone have suggestions on how to accomplish this?

Here is what I tried:

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

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

           [CLE_ENV_SHORT], [CLE_ENV_CAT_SHORT]



FROM       CCS_Win32_Volume, [dbo].[CCS_V_SERVER_INSTANCE_DETAILS]


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

with the paramter hardcoded with all, i should be seeing all results right? Yet I get no results returned.

choloboy
  • 795
  • 4
  • 16
  • 38
  • 1
    Have you looked at http://stackoverflow.com/questions/14893789/ssrs-how-to-add-all-option-to-ssrs-dropdown-filter ? – Ian Preston May 20 '14 at 13:39
  • 1
    Your parentheses are placed incorrectly. `(a) or (b) and c and d` is equal to `a or (b and c and d)` as `and` comes before `or`. You need (a or b) and c and d. – Thorsten Kettner May 20 '14 at 15:10
  • 1
    BTW: You are cross joining the two tables. Is that on purpose? You should use up-to-date syntax, i.e. explicit joins such as INNER JOIN, CROSS JOIN or whatever. – Thorsten Kettner May 20 '14 at 15:14
  • 1
    The other two parameters in your example are empty, so they won't match any column, right? This explains why with a valid server name you get a record and with 'all' you dont, because 'all' plus the other two criteria will never be met. – Thorsten Kettner May 20 '14 at 15:15

1 Answers1

2

You can write the WHERE clause accordingly:

select ...
from mytable
where @param = col or @param = 'all';
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • for the @param = col part though wouldn't I have to make a set the where for each possible value? I tried what you mentioned but the all returns a blank report. – choloboy May 20 '14 at 13:51
  • Your parameter query should be like: SELECT Name FROM List UNION ALL SELECT 'All' – Matt May 20 '14 at 13:57
  • 1
    @choloboy: Well, maybe I misunderstood. I though when param = 'xxx' you get the record with col = 'xxx' and when param = 'all' then you get all records instead. Is that not what you want? – Thorsten Kettner May 20 '14 at 14:17
  • 1
    @Matt: Ah, you think the question is about how to get all server names plus 'all' to get a list of valid inputs. I thought that 'all' or a server name gets entered and the task is to write a query to use that parameter to find either all or one matching record in a table. – Thorsten Kettner May 20 '14 at 14:20
  • 1
    Thanks for your comments, I am trying to either get all names (x,y,z) or just x or just y or just z depending on what I enter. z,y,z arent actual names, there are over 100 possible server names in my case @Matt – choloboy May 20 '14 at 14:38
  • 1
    @choloboy: So my query should work for you. When param is 'all' then the criteria is met for every record, no matter what col contains, so you get back all records. Maybe you have some more criteria in your query and forgot to use parantheses? – Thorsten Kettner May 20 '14 at 14:47
  • 1
    @ThorstenKettner I added my query in an edit, I don't understand why I end up with no results when parameter is hardcoded to all. But when i hardcode it to a ServerName then it works. – choloboy May 20 '14 at 14:58
  • 1
    @choloboy: Hardcode the parameter? I don't understand. I thought you were just typing in the parameter. And above quere is written such that it gives you one record if you enter a valid server name and all records if you enter 'all' (without the quotes of course). Why would you have to hardcode the Parameter? EDIT: Ah, I see you've updated your request. I will answer there. – Thorsten Kettner May 20 '14 at 15:07
  • 1
    Sorry I wasn't specific. I hardcode the parameter input in management studio while in ssrs it prompts for input @ThorstenKettner – choloboy May 20 '14 at 15:31