CODEWITHSUNDEEP
sqlsql-servert-sqlreporting-services
choloboy
choloboy

Reputation: 815

Parameter settings in SSRS for all and filtered results

Ok so I have much much trouble with creating this report in SSRS. I have a previous post which can be found here... So as you can see I really need help, because after days of effort and many minds helping me out, still no luck.

To avoid wasting more time playing in SSRS and SSMS to figure this out I thought i'd make one last post.

Basically, if I have the following query (similar query as in the question I linked but without the WHERE and ORDER BY) shown below

how do I set up SSRS parameters/dataset so that I can either show all records OR filtered by specific parameter input (what i type as parameter in SSRS).

Below is the query mentioned above...

DECLARE    @p_ServerName nvarchar(10)  --Declares are just to show what 
DECLARE    @p_Env        nvarchar(10)  --parameters I have been trying to
DECLARE    @p_EnvCat     nvarchar(10)  --use in SSRS

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

I have spent a ton of time trying to get this going, so all help, input and explanation is greatly aprreciated.

Upvotes: 0

Views: 147

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

I'm guessing @p_ServerName is supposed to relate to c1.SystemName, but it's not clear what your other variables/parameters are supposed to be filtering.

However if we just take the example of "server name", then the simplest option is to create a report parameter, e.g. rpServerName, and create a dataset, e.g. dsServerName, that has a query that returns a list of server names. Back in the report parameter, set the the "available values" to be populated from a dataset and select the dataset you created. Assuming you want to be able to select multiple servers, you should check the box to make the report parameter a "multi select".

Next, in your main dataset (presumably based on the query above) you can add a filter into the WHERE clause of your existing query like so:

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 IN (@rpServerName)

The report user will then be able to select individual or multiple servers in the parameter, or choose the "Select All" option (which literally returns all the possible values for the parameter).

An alternative option, if you only want the user to be able to specify an individual server or "All" servers, is to set up the same parameters and datasets, but this time the parameter is not set as multi-select. The query for the parameter dataset will look something like:

SELECT 'ALL' AS ServerName
UNION ALL
SELECT ServerName FROM SomeTableOfServers

and the WHERE clause for the main query will look similar to:

WHERE       ( c1.SystemName = @rpServerName OR @rpServerName = 'ALL' )

If the report user selects "ALL", then the above logic means that effectively there is no filter and all rows are returned.

Upvotes: 1

Related Questions