17608

Parameter functioning in SSMS but not SSRS

Question:

Assume I have the following query (simplified version of what im really using)...

DECLARE @p_ServerName nvarchar(10) SELECT SystemName FROM Table_x WHERE SystemName = @p_ServerName OR @p_ServerName = 'all'

When I set the parameter to a specific name ex: DB1 the rows are displayed in SSRS but when I input 'all' as the parameter, nothing is displayed. Yet when I do the same in SSMS it works just fine.

Does anyone know what can be the problem here? Thanks.

EDIT

Here is the first query I tried (ignore the declares, it was just used for ssms testing in which it worked just fine):

DECLARE @p_ServerName nvarchar(10) = 'all' DECLARE @p_Env nvarchar(10) = 'all' DECLARE @p_EnvCat nvarchar(10) = 'all' 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 (c1.SystemName = @p_ServerName 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]

Here is the other query i tried (again ignore the declares):

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 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], c1.SystemName

Since multiple variations of queries work fine in SSMS I am assuming the issue is how SSRS handles 'all'. Because when I enter any server name it works fine. Only 'all' has a problem. So I guess my question is, how do you set up parameters to do this?

Answer1:

It isn't very clear from your question which parameter you're having trouble implementing the "ALL" filtering on. But from looking at your two queries it looks like it is the @p_ServerName parameter. In your first query the other two seem to have had the 'ALL' filtering done properly.

So the problem must be here:

(c1.SystemName = @p_ServerName OR c1.SystemName = c1.SystemName)

First, what are you trying to achieve with the second part? c1.SystemName=c1.SystemName will always be true (unless c1.SystemName is NULL and if that is your goal then use c1.SystemName IS NOT NULL

I think this should look more like:

(c1.SystemName=@p_ServerName OR @p_ServerName='all')

Which is how you've implemented the other two parameters, so perhaps I'm missing something.

Also, I'm not sure what you mean when you say that the UNIONed query solution must be how SSRS is doing things. SSRS doesn't rewrite your queries based on the parameters, it just sticks that values in those variables for you to use in your query. If your parameter @p_ServerName is a string then it is just a VARCHAR variable with the characters "all" in it.

Recommend

  • Grab the frame from gst pipeline to opencv with python
  • Adding javascript function to jMeter using jsr223
  • Rijndael managed: plaintext length detction
  • Check if coordinates is within a specific distance from other coordinates
  • EF ObjectQuery Context, Parameters, Connection properties equivalent on DbSet
  • subclass of thread implementing Runnable interface
  • How to get elements value with Linq To XML
  • How get a focus element in QWebView/QWebPage?
  • Can I manage incomplete class objects using STL containers?
  • setvbuf() - size parameter when buf is NULL
  • no @interface for 'UITableView' declares the selector 'initWithStyle:reuseIdentifiers
  • how do disable disk cache in c# invoke win32 CreateFile api with FILE_FLAG_NO_BUFFERING
  • C++ std::set comparator
  • Prolog Query - Trying to understand how this result happens
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Capturing STDOUT in RSpec
  • JOOQ nested condition
  • Query to get the Top 2 from each group
  • Does the failbit effect the call ignore on cin?
  • What exactly is the height of modalPresentationStyle - FormSheet on iPad?
  • Why does IE8 fail to resolve my JQuery selector for a checked radio option?
  • How to implement limit with Nhibernate and Sybase
  • Mockery and Laravel constructor injection
  • Are there “Dynamic Playlists” of unit tests in Visual Studio?
  • Inversing an interpolation of rotation
  • Scanner nextInt() and hasNextInt() problems
  • jQuery: How to AJAXify WordPress Search?
  • joining two bezier curves
  • Who propagate bugfixes across branches (corporate development)?
  • Why use database factory in asp.net mvc?
  • Implicit joins and Where in Doctrine - how?
  • Implementation of State Monad
  • Button text different than value submitted in query string
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • ImageMagick, replace semi-transparent white with opaque white
  • sending/ receiving email in Java
  • log4net write single file for each call to log.info
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`