Duplicate measure’s in Reporting Services

Today a colleague were modifying a Reporting Services 2008 report to add some new measures to a tablix. He opened the query designer up, added his new measures, tested the query which ran fine, saved it and then proceeded to modify the cells in the tablix.

 

After modifying the tablix he ran the report in BIDS and we noticed that he was getting NULL values for one of the measures. We opened the dataset, confirmed the date (which was our only parameter) and tested the query. We tested the query again in the designer and saw that our measure was returning results. We closed the dataset down and tested the report. Again we saw that our report was returning NULL values for the measure. So I copied the query into management studio and ran it. It returned the correct results as expected.

 

At this point we were really getting fustrated. We recreated the dataset, created a brand new tablix and nothing was working. I just happen to look at the query again in Management Studio when I realized that the measure he was having issues with was in the query twice. Once I removed one of the instances of the measure, the report worked as expected.

 

So the lesson I learned today was to always double check my query to make sure there aren’t any duplicate measures. It appears that the conflict with each other in Reporting Services will end up showing a NULL value.

Advertisements

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.

MDX parameter datasets changing

Awhile back I noticed in SSRS 2008 that if your report had a custom dataset for a parameter and you changed your main report dataset,
the custom parameter dataset would revert back to the a vanilla dataset that SSRS creates for you when you create a parameter.
This turned out to be extremely puzzling at first and then down right irritating. So after some searching I found one blog/article
about it at Teo Lachev’s site. Thank goodnesss for him, because I don’t have much hair left and I was ready to pull the rest of it out.
 
Here is the link to the article. 
 

Supressing Auto-Generated datasets

Getting rid of NaN, Infinity and #error in your Reporting Services report

Because of Reporting Services inability to handle some totals when pulling data from Analysis Services,
I ended up writing a lot of expressions. Instead of having to put a bunch of IIF statements in the expression,
we came up with a code block that handles the following conditions…
 
Division:
If the numerator or the denominator is equal to 0 or is null
 
Subtraction
if the numerator or the denominator is a non numeric character. (we did this because the client wanted " – " if the value was 0
 
Addition
same conditions as Subtraction
 
multiply by 100 (also called Index)
We had some calculations that the client wanted to see as an index. So this code was added to do a "safe" division and then multiply by 100
 
 
Public Function SafeDivide(Numerator as String, Denominator as String) as String
Try         
If Numerator = “” or Denominator = “” then
Return “-“
End if
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
If CDbl(Numerator) =0 or CDbl(Denominator) = 0 then
Return “-“
End if
Return String.format((Numerator / Denominator ).ToString("0.0"))
Catch
Return "-"
End Try
End Function

Public Function SafeDivideIndex(Numerator as String, Denominator as String) as String
Try         
If Numerator = “” or Denominator = “” then
Return “-“
End if
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
If CDbl(Numerator) =0 or CDbl(Denominator) = 0 then
Return “-“
End if
Return String.format((Numerator * 100/ Denominator ).ToString("0.0"))
Catch
Return "-"
End Try
End Function

Public Function SafeSubtract(Numerator as String, Denominator as String) as String
If Numerator = "" or Denominator = "" then
Return "-"
End If
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
Try
Return String.format((Numerator – Denominator ).ToString("0.000"))
Catch
Return "-"
End Try
End Function

Public Function SafeAdd(Numerator as String, Denominator as String) as String
If Numerator = "" or Denominator = "" then
Return "-"
End If
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
Try
Return String.format((Numerator + Denominator ).ToString("0.000"))
Catch
Return "-"
End Try
End Function

This code can be added to your SSRS reports by right clicking outside of the matrix/data regions and selecting Report Properties–>Code and paste the code in there. Within an expression, you can call the code like this
 
Code.SafeDivide(MyNumerator,MyDenominator)
 
This sure made things much simpler.
 
Special thanks to Elia for doing the majority of the work on this code.