You are here: Home / Blog / posts / Programming Magic / Tips and Tricks Using SQL Server 2008 R2 Reporting Services and Report Builder 3.0

Tips and Tricks Using SQL Server 2008 R2 Reporting Services and Report Builder 3.0

by Ken Taylor published Aug 26, 2010 01:45 PM, last modified Jan 28, 2014 12:54 PM
This is a running dialog for documenting tips and tricks found while using SQL Server 2008 R2 Reporting Services and Report Builder 3.0.

Improving report execution time

Below I have listed a query that runs in less than 1 second as a SQL query, but times out and fails to return if run using sp_executesql.  It appears that the issue is due to a bug in SQL server when using executing sp_executesql, which apparently causes it bypass the query optimizer and run nested loops instead of joins.  However, you can add the following option at the end of the select statement in order to suggest how you want to run the query:

option (hash join, hash group)
exec sp_executesql N'SELECT
       [FiscalYear]
      ,[FiscalPeriod]
      ,[Chart]
      ,[OrgCode]
      ,[OrgName]
      ,[OrgLevel]
      ,[OrgType]
      ,[Level1_OrgCode]
      ,[Level1_OrgName]
      ,[Level2_OrgCode]
      ,[Level2_OrgName]
      ,[Level3_OrgCode]
      ,[Level3_OrgName]
      ,[Account]
      ,[AccountNum]
      ,[AccountName]
      ,[AccountManager]
      ,[PrincipalInvestigator]
      ,[AccountType]
      ,[AccountPurpose]
      ,[FederalAgencyCode]
      ,[AccountAwardNumber]
      ,[AccountAwardType]
      ,[AccountAwardAmount]
      ,[AccountAwardEndDate]
      ,[HigherEdFunctionCode]
      ,[AccountFunctionCode]
      ,[OPAccount]
      ,[OPFund]
      ,[OPFundName]
      ,[OPFundGroup]
      ,[OPFundGroupName]
      ,[AccountFundGroup]
      ,[AccountFundGroupName]
      ,[SubFundGroup]
      ,[SubFundGroupName]
      ,[SubFundGroupType]
      ,[SubFundGroupTypeName]
      ,[AnnualReportCode]
      ,[SubAccount]
      ,[SubAccountName]
      ,[ObjectCode]
      ,[ObjectName]
      ,[ObjectShortName]
      ,[BudgetAggregationCode]
      ,[ObjectType]
      ,[ObjectTypeName]
      ,[ObjectLevelName]
      ,[ObjectLevelShortName]
      ,[ObjectLevelCode]
      ,[ObjectSubType]
      ,[ObjectSubTypeName]
      ,[ConsolidationCode]
      ,[ConsolidationName]
      ,[ConsolidationShortName]
      ,[SubObject]
      ,[ProjectCode]
      ,[ProjectName]
      ,[ProjectManager]
      ,[ProjectDescription]
      ,[TransDocType]
      ,[TransDocTypeName]
      ,[TransDocOrigin]
      ,[DocumentNumber]
      ,[TransDocNum]
      ,[TransDocTrackNum]
      ,[TransDocInitiator]
      ,[TransInitDate]
      ,[LineSequenceNum]
      ,[TransDescription]
      ,[TransLineAmount]
      ,[TransBalanceType]
      ,[ExpendAmount]
      ,[AppropAmount]
      ,[EncumbAmount]
      ,[TransLineReference]
      ,[TransPriorDocTypeNum]
      ,[TransPriorDocOrigin]
      ,[TransPriorDocNum]
      ,[TransEncumUpdateCode]
      ,[TransCreationDate]
      ,[TransPostDate]
      ,[TransReversalDate]
      ,[TransChangeDate]
      ,[TransSourceTableCode]
      ,[IsPendingTrans]
      ,[IsCAESTrans]
FROM
  TransactionLogV
WHERE
  TransactionLogV.[FiscalYear] = @Year
  AND TransactionLogV.FiscalPeriod IN (N''01'',N''02'',N''03'',N''04'',N''05'',N''06'',N''07'',N''08'',N''09'',N''10'',N''11'',N''12'',N''13'')
  AND TransactionLogV.Chart IN (N''3'',N''L'')
  AND TransactionLogV.AccountNum LIKE @AccountNum
  AND TransactionLogV.ConsolidationCode IN (N''BDGT'',N''BLSH'',N''INC0'',N''INDR'',N''LIEN'',N''SB00'',N''SB01'',N''SB02'',N''SB03'',N''SB04'',N''SB05'',N''SB06'',N''SB07'',N''SB09'',N''SB28'',N''SB34'',N''SB55'',N''SB67'',N''SB73'',N''SB74'',N''SB75'',N''SBMC'',N''SUB0'',N''SUB3'',N''SUB4'',N''SUB5'',N''SUB6'',N''SUB7'',N''SUB8'',N''SUB9'',N''SUBG'',N''SUBS'',N''SUBX'')
  AND TransactionLogV.[TransSourceTableCode] IN (N''A'',N''P'')
  AND ((TransactionLogV.TransPostDate BETWEEN @StartPostDate and @EndPostDate) OR TransactionLogV.TransPostDate IS NULL) option (hash join, hash group)',N'@Year nvarchar(4),@AccountNum nvarchar(7),@StartPostDate datetime,@EndPostDate datetime',@Year=N'2011',@AccountNum=N'ANS766H',@StartPostDate='2010-07-01 00:00:00',@EndPostDate='2010-10-11 00:00:00'

Rewritten as a standard SQL statement with variables:

DECLARE @Year nvarchar(4),@AccountNum nvarchar(7),@StartPostDate datetime,@EndPostDate datetime
select @Year=N'2011'
select @AccountNum=N'ANS766H'
select @StartPostDate='2010-07-01 00:00:00'
select @EndPostDate='2010-10-11 00:00:00'
SELECT
       [FiscalYear]
      ,[FiscalPeriod]
      ,[Chart]
      ,[OrgCode]
      ,[OrgName]
      ,[OrgLevel]
      ,[OrgType]
      ,[Level1_OrgCode]
      ,[Level1_OrgName]
      ,[Level2_OrgCode]
      ,[Level2_OrgName]
      ,[Level3_OrgCode]
      ,[Level3_OrgName]
      ,[Account]
      ,[AccountNum]
      ,[AccountName]
      ,[AccountManager]
      ,[PrincipalInvestigator]
      ,[AccountType]
      ,[AccountPurpose]
      ,[FederalAgencyCode]
      ,[AccountAwardNumber]
      ,[AccountAwardType]
      ,[AccountAwardAmount]
      ,[AccountAwardEndDate]
      ,[HigherEdFunctionCode]
      ,[AccountFunctionCode]
      ,[OPAccount]
      ,[OPFund]
      ,[OPFundName]
      ,[OPFundGroup]
      ,[OPFundGroupName]
      ,[AccountFundGroup]
      ,[AccountFundGroupName]
      ,[SubFundGroup]
      ,[SubFundGroupName]
      ,[SubFundGroupType]
      ,[SubFundGroupTypeName]
      ,[AnnualReportCode]
      ,[SubAccount]
      ,[SubAccountName]
      ,[ObjectCode]
      ,[ObjectName]
      ,[ObjectShortName]
      ,[BudgetAggregationCode]
      ,[ObjectType]
      ,[ObjectTypeName]
      ,[ObjectLevelName]
      ,[ObjectLevelShortName]
      ,[ObjectLevelCode]
      ,[ObjectSubType]
      ,[ObjectSubTypeName]
      ,[ConsolidationCode]
      ,[ConsolidationName]
      ,[ConsolidationShortName]
      ,[SubObject]
      ,[ProjectCode]
      ,[ProjectName]
      ,[ProjectManager]
      ,[ProjectDescription]
      ,[TransDocType]
      ,[TransDocTypeName]
      ,[TransDocOrigin]
      ,[DocumentNumber]
      ,[TransDocNum]
      ,[TransDocTrackNum]
      ,[TransDocInitiator]
      ,[TransInitDate]
      ,[LineSequenceNum]
      ,[TransDescription]
      ,[TransLineAmount]
      ,[TransBalanceType]
      ,[ExpendAmount]
      ,[AppropAmount]
      ,[EncumbAmount]
      ,[TransLineReference]
      ,[TransPriorDocTypeNum]
      ,[TransPriorDocOrigin]
      ,[TransPriorDocNum]
      ,[TransEncumUpdateCode]
      ,[TransCreationDate]
      ,[TransPostDate]
      ,[TransReversalDate]
      ,[TransChangeDate]
      ,[TransSourceTableCode]
      ,[IsPendingTrans]
      ,[IsCAESTrans]
FROM
  TransactionLogV
WHERE
  TransactionLogV.[FiscalYear] = @Year
  AND TransactionLogV.FiscalPeriod IN (N'01',N'02',N'03',N'04',N'05',N'06',N'07',N'08',N'09',N'10',N'11',N'12',N'13')
  AND TransactionLogV.Chart IN (N'3',N'L')
  AND TransactionLogV.AccountNum LIKE @AccountNum
  AND TransactionLogV.ConsolidationCode IN (N'BDGT',N'BLSH',N'INC0',N'INDR',N'LIEN',N'SB00',N'SB01',N'SB02',N'SB03',N'SB04',N'SB05',N'SB06',N'SB07',N'SB09',N'SB28',N'SB34',N'SB55',N'SB67',N'SB73',N'SB74',N'SB75',N'SBMC',N'SUB0',N'SUB3',N'SUB4',N'SUB5',N'SUB6',N'SUB7',N'SUB8',N'SUB9',N'SUBG',N'SUBS',N'SUBX')
  AND TransactionLogV.[TransSourceTableCode] IN (N'A',N'P')
  AND ((TransactionLogV.TransPostDate BETWEEN @StartPostDate and @EndPostDate) OR TransactionLogV.TransPostDate IS NULL)

to create a quick-and-dirty balance

Nearly all of the FIS DataMart balance summary and transaction reports are based off of the transactions log, which is simply a running account of financial transactions similar to a check book register.  Just as with a check book register, there are deposits, i.e. appropriations and withdrawals, i.e. expenditures, plus something akin to pre-allocated expenses, .i.e. encumbrances.  All of these are simply entries in the transactions log with a differing transactional balance type.   Therefore, in order to calculate a balance, it is simply a matter of adding up all of the individual transaction amounts.

=Sum(Amount)

To eliminate entries that have a zero value across all columns, add a filter on the report grouping that contains a sum of absolute values <> 0.

Expression:

=Abs(Sum(Fields!Approp.Value)) + Abs(Sum(Fields!Expend.Value)) + Abs(Sum(Fields!Encumb.Value))

Operator:

<>

Value*:

 =CDec(0)

*Note that the 0 will have to be converted to a decimal, i.e. CDec(0), because of the data types of the fields it's comparing.

To include numeric strings that have leading zeros in a default values list:

Report builder tends to "guess" wrong when it comes to strings that "look" like numbers and will remove any leading zeros from numeric strings.  In order to keep the leading zeros, you must explicitly convert the quoted numeric string to a string. 

The following example builds a list of numeric strings "01" - "13".

NOTE:  This will only work if you select the "Edit query as text" or other similar feature.  You may still need to remove double sets "=CStr(""01"")" or the like once you've set the values, because it really wants to make the whole thing a string.  Also make sure the "Allow multiple..." box is checked or that will foul it up for sure.  I've also found that you may have to delete the parameter all-together and re-add it to the parameters list.  Be sure to use the "Up" arrow to give it its proper position in the list after re-adding it.  Make sure you re-check all of the appropriate "Allow multiple values" boxes as the editor does not remember these settings, even though multiple parameters may have been specified.

=new Object() {CStr("01"), CStr("02"), CStr("03"), CStr("04"), CStr("05"), CStr("06"), CStr("07"), CStr("08"), CStr("09"), 10, 11, 12, 13}

to include a list of character strings in a default values list:

Since Report Builder 3.0 is a fairly new product, and has to reach maturity, it has a number of undocumented "features".  One of them appears to be recognizing multiple character strings in a parameter's default values list, because just adding a comma separated list of double quoted values does not behave properly.  Therefore, after a fair amount of experimentation it appears that the most successful manner of doing so is by adding code for the expression similar to the following:

=new Object() {"SCPESIS", "LYSS", "MOGLI", "TMKAISER"}

Note that for non-numerical character strings it did not appear necessary to include the CStr(... as in the previous example.  Make sure you re-check all of the appropriate "Allow multiple values" boxes as the editor does not remember these settings, even though multiple parameters may have been specified.

to not include a default parameter value

In order not to have a default value appear for your parameter, you will need to unselect the "Default Value" box in the "Dataset Properties" editor of the corresponding parameter value section.  This is displayed in a pop-up window once you select "Set Options Dataset", and then "Parameters".

To Add a List of Report Parameters to the Report Heading:

In order to create a more user-friendly report, you can add a list of the report's parameters to the report header by including a placeholder in the report header, which is populated from a custom report function.

Add a text box or other place holder, and set the place holder's expression similar to the following:

=Code.StringBuilderTest(Parameters)

Add custom code to the report's properties similar to the following, which builds the parameter header and populates it with the corresponding values:

Public Function StringBuilderTest(ByVal params As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters) As String

    Dim builder As New System.Text.StringBuilder

   
    builder.AppendLine("Fiscal Year: " & params!FiscalYear.Label)
    builder.AppendLine("Period(s): " & Join(params!FiscalPeriod.Label, ", "))

    If params!Chart.Count > 0 Then
    builder.AppendLine("Chart(s): " & Join(params!Chart.Label, ", "))
    End If

    builder.Append("Dept/Level 3 Org: ")
    If NOT params!DepartmentLevelOrg.Value Like "%" Then
    builder.AppendLine(params!DepartmentLevelOrg.Label)
    Else
    builder.AppendLine("Any")
    End If

     If params!SubFundGroupType.Count > 0 Then
    builder.AppendLine("Sub Fund Group Type(s): " & Join(params!SubFundGroupType.Label, ", "))
     End If

     builder.Append("Account: " )
     If NOT params!AccountNum.Value Like "%" Then
    builder.AppendLine(params!AccountNum.Label)
     Else
    builder.AppendLine("Any")
     End If

     builder.Append("Sub Fund Group: " )
     If NOT params!SubFundGroup.Value Like "%" Then
    builder.AppendLine(params!SubFundGroup.Label)
     Else
    builder.AppendLine("Any")
     End If

    Return builder.ToString
End Function

Adding formatting to the report parameters list:

You can display a more formatted list of parameter values by changing to a non-proportionally spaced font, such as Courier, etc., and then by modifying the above function to include builder.AppendFormat(...  Below is such an example:

Public Function StringBuilderTest(ByVal params As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters) As String
      Dim builder As New System.Text.StringBuilder
    
      builder.AppendFormat("{0, -40} {1}","Fiscal Year:", params!FiscalYear.Label)
      builder.AppendLine()
            
      builder.AppendFormat("{0, -40} {1}", "Include Chart 3?", params!IncludeChart3.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Include Chart L?",  params!IncludeChartL.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Include CE?", params!IncludeCE.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Include IR?",  params!IncludeIR.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Include OR?",  params!IncludeOR.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Include Pending Transactions?", params!IncludePending.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Include C&G Carry Forward Balances?", params!IncludeCGCarryForwardBalances.Label)
      builder.AppendLine()

      builder.AppendFormat("{0, -40} {1}", "Is CAES?", params!IsCAES.Label)
      builder.AppendLine()

    Return builder.ToString
End Function

Rewriting the Above function to eliminate the need for a separate builder.AppendLine():

A carriage return/line feed (CrLf) can be added as an additional formatting parameter, thus eliminating the need for the separate builder.AppendLine().  Here is the same function rewritten using the single-line approach (Note the added {2} and the vbCrLf):

Public Function StringBuilderTest(ByVal params As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters) As String
      Dim builder As New System.Text.StringBuilder
    
      builder.AppendFormat("{0, -40} {1} {2}","Fiscal Year:", params!FiscalYear.Label, vbCrLf)
            
      builder.AppendFormat("{0, -40} {1} {2}", "Include Chart 3?", params!IncludeChart3.Label, vbCrLf)
      
      builder.AppendFormat("{0, -40} {1} {2}", "Include Chart L?",  params!IncludeChartL.Label, vbCrLf)

      builder.AppendFormat("{0, -40} {1} {2}", "Include CE?", params!IncludeCE.Label, vbCrLf)
      
      builder.AppendFormat("{0, -40} {1} {2}", "Include IR?",  params!IncludeIR.Label, vbCrLf)

      builder.AppendFormat("{0, -40} {1} {2}", "Include OR?",  params!IncludeOR.Label, vbCrLf)

      builder.AppendFormat("{0, -40} {1} {2}", "Include Pending Transactions?", params!IncludePending.Label, vbCrLf)
      
      builder.AppendFormat("{0, -40} {1} {2}", "Include C&G Carry Forward Balances?", params!IncludeCGCarryForwardBalances.Label, vbCrLf)

      builder.AppendFormat("{0, -40} {1} {2}", "Is CAES?", params!IsCAES.Label, vbCrLf)

    Return builder.ToString
End Function          

Here is an example of the formatted list:

Highlighted Example of formatted parameter listing.

 

When:

Where:

Contact