SSRS query designer passes number as string

Today I was working on a report to show total customers that had less than X dollars or greater than X dollars in
Reporting Services. The query was pretty simple
*formatting courtesty of MDX Studio Online

SELECT
  {
    [Measures].[Revenue]
   ,[Measures].[Units]
  } ON COLUMNS
 ,NonEmpty
  (
    Filter
    (
      {
        [Customer].[Customer].[All].Children
      }
     ,
      [Measures].[ Revenue] < 100 OR [Measures].[Revenue] > 1000
    )
  ) ON ROWS
FROM TestCube
WHERE
  [Date].[Fiscal Date].[Fiscal Week].&[3]&[3]&[2010]
 
So I created two parameters Val1 and Val2 and added the parameters t0 the dataset. The query in Query Designer looked like this:
 

SELECT
  {
    [Measures].[Revenue]
   ,[Measures].[Units]
  } ON COLUMNS
 ,NonEmpty
  (
    Filter
    (
      {
        [Customer].[Customer].[All].Children
      }
     ,
      [Measures].[Revenue] < (@Val1) OR [Measures].[Revenue] > (@Val2)
    )
  ) ON ROWS
FROM TestCube
WHERE
  StrToMember(@FiscalWeek)
 
My default values were Val1 = 100 and Val2 = 1000. When I ran the query in the designer, the query ran but the values were being ignored. After wrestling with "why" for about 10 minutes, a collegue suggested I try and convert the values to a double and try it again. Sure enough when I did that, it worked.
 
So after so further investigation, I realized that in the parameter properties, the DataType was defaulted to TEXT.
Changed this to INTEGER, removed the CDbl in the query, saved it and the query works great. However, if I were to run
the query from the designer, it appears to not be working again. This somehow has to do with the fact that in the designer,
SSRS seems to pass everything as a string regardless of the data. Odd behavior and a little fustrating.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s