MS Reporting Services

Location: BlogsComments    
Posted by: Dale Wednesday, July 25, 2007

Query running slow in SSRS but fast in Query Analyser: One reason for this is the fact that SSRS will run as many queries in parallel as it can given the size of the connection pool. On the surface this may seem like a very good thing except if the queries are complex and access the same data in which case they start locking each other out and can take longer to execute than if run sequentially. To change this click the datasource properties beside a dataset, then click "..." next to datasource then tick 'use single transaction'. Slightly odd place for it, especially since it actually applies to all datasets in the report.

Divide by zero: I had an odd experience today when I tried to prevent a divide by zero by using the IIF statement e.g. =IIf(Term2 <> 0, Term1/Term2, 0) however it appears to try and evaluate 'Term1/Term2' even if it 'Term2 = 0' very strange - so I had to use another IIf statement to ensure that a valid computation happens even though it will never be used e.g. =IIf(Term2 <> 0, Term1/(IIf(Term2 <> 0, Term2, 1), 0).

IIf revisited: OK - so now I understand a bit more about VB I've found out that the IIf statement always evaluates both expressions and then returns one of them. The previous *hack* didn't help when it came time to try and display a parameter which could be null (Nothing). The solution it seems was to write a small function which is embedded in the report. This is done on the Report/Parameters/Code tab. And there is no support for debugging your code so good luck!

Public Shared Function HandleNull(ByVal Param As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameter) As String
    If Param.Value Is Nothing Then
      Return "All"
      Return Param.Value.ToString()
    End If
End Function

and then in my text box I call it as:


Update: The null problem can be solved without a function as follows:

=IIf(IsNothing(Parameters!Rejected.Value), "All", CStr(Parameters!Rejected.Value))

Permalink |  Trackback

Your name:
Add Comment   Cancel