Month: September 2009
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.
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.
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
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
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
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
Code.SafeDivide(MyNumerator,MyDenominator)
This sure made things much simpler.
Special thanks to Elia for doing the majority of the work on this code.