While working in the lab late one night I wondered how I could best determine how long individual stored procedures were taking to run when called from an external "wrapper" script.  Once I had this data I could then use it to determine which stored procedures were taking longer than anticipated and might require performance tuning.

To do this I added a set of statements to the beginning that initialize the time keeping variables, a set in the middle that record the intermediate time(s), and a set at the end that record the overall execution time.

Sample timing segment statements are are follows:

Initial statements:

    DECLARE @StartTime datetime = (SELECT GETDATE())
    DECLARE @TempTime datetime = (SELECT @StartTime)
    DECLARE @EndTime datetime = (SELECT @StartTime)
    PRINT '--Procedure started at: ' + CONVERT(varchar(20),@StartTime, 114)

Intermediate statements:

Note: These statements would be repeated at the end/beginning of every new inner procedure call.

    SELECT @StartTime = (@EndTime)
    SELECT @EndTime = (GETDATE())
    PRINT '--Intermediate stored procedure executed in: ' + CONVERT(varchar(20),@EndTime - @StartTime, 114)

Final statements:

   SELECT @StartTime = (@TempTime)
   SELECT @EndTime = (GETDATE())
   PRINT '--Procedure ended at: ' + CONVERT(varchar(20),@EndTime, 114)
   PRINT '--Procedure executed in: ' + CONVERT(varchar(20),@EndTime - @StartTime, 114)

How to emulate standard SQL's multi-column "IN" statement using a Microsoft SQL Server:

The standard SQL "IN" statement is very handy; however, MS SQL does not support the SQL standard that allows for multiple columns as does other database servers such as Oracle.

Therefore, the SQL standard multi-column IN statement, whilst perfectly acceptable in Oracle (Figure 1) fails altogether if used with an MS SQL Server.  However, not to worry, it can be re-written using an anonymous INNER JOIN (Figure 2) to achieve similar results.

Note that since the IN portion of the WHERE clause has been replaced by the anonymous INNER JOIN, the corresponding filter criteria has been removed from the WHERE clause, and only the "<other_filter_criteria>" remains.  This is because it has been accounted for by the "ON" portion of the INNER JOIN to achieve the same results.

Figure 1:

SQL Standard Multi-Column "IN" statement:

SELECT *
FROM <table_name>
WHERE (column1, column2, ..., columnN) IN
(
	SELECT DISTINCT column1, column2, ..., columnN
	FROM <other_table_name>
	WHERE <where_clause>
)
[AND <other_filter_criteria>...]

Figure 2: Multi-Column "IN" statement simulated BY using AN Anonymous INNER JOIN:

SELECT *
FROM  <table_name> T1
INNER JOIN
(
	SELECT DISTINCT column1, column2, ..., columnN
	FROM <other_table_name>
	WHERE <where_clause>
) T2 ON T1.column1 = T2.column1 AND T1.column2 = T2.column2... AND T1.columnN = T2.columnN
WHERE <other_filter_criteria>

Ok, this exception "A collection with cascade="all-delete-orphan" was no longer referenced by the owning entity instance" can happen with NHibernate if you have an entity with a list of other entities with the .Cascade.AllDeleteOrphan() mapping.

If you clear the list by assigning it a new List<entity>(), this exception will appear.

Instead clear the list by using the .Clear() method.

Steps to create scripts to to create your database

1) Right click your database and choose Tasks->Generate Scripts...

2) On the Choose Objects step, don't choose "Script entire database and all database objects" as this contains roles and other things we don't want. Instead choose "Select specific database objects". typically Tables, Views, and stored procedures.

3) Choose where to save the script. Typically the query window is picked so it can be copied and pasted into a script file for source control.

4) Click on the Advanced button.

5) Typically most values are not changed. To prevent the date time stamp, set "Include Descriptive Headers" to False

6) Press OK on the Advanced Scripting Options screen.

7) Press Next >, Next>, Finish

Note, the "Inflector.Titleize(labelText)" is a UCDArch method to convert Camel case to titleized (first letter capitalized of each word) strings.

Usages:

<%= Html.LabelFor(a => a.Ticket.MessageBody, DisplayOptions.HumanizeAndColon) %>
<%= Html.LabelFor(a => a.Ticket.MessageBody, DisplayOptions.HumanizeAndColon extraText: " (Email Body)") %>
<%= Html.LabelFor(a => a.Ticket.MessageBody, DisplayOptions.HumanizeAndColon, extraText: " (Email Body)", generatedId:true) %>
<%= Html.LabelFor(a => a.Ticket.MessageBody, DisplayOptions.HumanizeAndColon, extraText: " (Email Body)", id: "MyLabelId") %>

Code:

using System;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Web.Mvc;
using UCDArch.Core.Utils;
 
namespace HelpRequest.Controllers.Helpers
{
 
    public static class LabelExtensions
    {
        public static MvcHtmlString Label(this HtmlHelper html, string expression, string id = "", bool generatedId = false)
        {
            return LabelHelper(html,
                               ModelMetadata.FromStringExpression(expression, html.ViewData),
                               expression, DisplayOptions.None, "", id, generatedId);
        }
 
        [SuppressMessage("Microsoft.Design", "CA1006:DoNotNestGenericTypesInMemberSignatures", Justification = "This is an appropriate nesting of generic types")]
        public static MvcHtmlString LabelFor<TModel, TValue>(this HtmlHelper<TModel> html, Expression<Func<TModel, TValue>> expression, DisplayOptions displayOptions, string extraText = "", string id = "", bool generatedId = false)
        {
            return LabelHelper(html,
                               ModelMetadata.FromLambdaExpression(expression, html.ViewData),
                               ExpressionHelper.GetExpressionText(expression), displayOptions, extraText, id, generatedId);
        }
 
        //public static MvcHtmlString LabelForModel(this HtmlHelper html)
        //{
        //    return LabelHelper(html, html.ViewData.ModelMetadata, String.Empty);
        //}
 
        internal static MvcHtmlString LabelHelper(HtmlHelper html, ModelMetadata metadata, string htmlFieldName, DisplayOptions displayOptions, string extraText, string id, bool generatedId)
        {
            string labelText = metadata.DisplayName ?? metadata.PropertyName ?? htmlFieldName.Split('.').Last();
            if (String.IsNullOrEmpty(labelText))
            {
                return MvcHtmlString.Empty;
            }
            var sb = new StringBuilder();
            if (displayOptions == DisplayOptions.Humanize || displayOptions == DisplayOptions.HumanizeAndColon)
            {
                sb.Append(Inflector.Titleize(labelText));
            }
            else
            {
                sb.Append(labelText);
            }
            sb.Append(extraText);
            if (displayOptions == DisplayOptions.HumanizeAndColon && labelText.Substring(labelText.Length - 1) != ":")
            {
                sb.Append(":");
            }
 
            var tag = new TagBuilder("label");
            if (!string.IsNullOrWhiteSpace(id))
            {
                tag.Attributes.Add("id", id);
            }
            else if (generatedId)
            {
                tag.Attributes.Add("id", html.ViewContext.ViewData.TemplateInfo.GetFullHtmlFieldId(htmlFieldName) + "_Label");    
            }
            
            tag.Attributes.Add("for", html.ViewContext.ViewData.TemplateInfo.GetFullHtmlFieldId(htmlFieldName));
            tag.SetInnerText(sb.ToString());
 
            return MvcHtmlString.Create(tag.ToString(TagRenderMode.Normal));
        }
 
        
    }
    public enum DisplayOptions
    {
        Humanize,
        HumanizeAndColon,
        None
    }
}

Existing functionality that will be removed or changed:

We will be removing the project.Deploy "HelpRequest.Deploy":

If we examine this project the two important sections for us are the Compilation and the Deployment. Compilation has the directory where the files to be deployed are written and Deployment has Web.config file section replacement:

From the image above we see that the connection strings and appSettings get replaced. In our project we have Prod and Test versions of each of these:

These contain settings that will replace the sections in the Web.config

<connectionStrings>
  <add name="MainDB" connectionString="Data Source=..." providerName="System.Data.SqlClient"/>
  <add name="CATBERT" connectionString="Data Source=..." providerName="System.Data.SqlClient"/>
  <add name="RATBERT" connectionString="Data Source=..." providerName="System.Data.SqlClient"/>
</connectionStrings>

and this:

<appSettings>
  <add key="RecaptchaPrivateKey" value="..."/>
  <add key="RecaptchaPublicKey" value="..."/>
  <add key="HelpDeskEmail" value="test@ucdavis.edu"/>
  <add key="AppHelpDeskEmail" value="test@ucdavis.edu"/>
  <add key="WebHelpDeskEmail" value="test@ucdavis.edu"/>
  <add key="LDAPUser" value="..." />
  <add key="LDAPPassword" value="..." />
</appSettings>

We will not remove these extra config files yet because we will copy the information for the web config transformation files later on.

next steps:

1) Delete the Deploy project.

2) Right click on Web.config and choose  "Add Config Transforms"

This will create transform files for each configuration under the Web.config

3) We don't need a transform for debug, so we will just delete that one.

4) For each deployment you will do, open of the related config file. My examples will all use the Test configurations.

5) For now, keep the comments to use as examples. Open the old ConnectionStringTest.config file and copy and paste them into the Web.Test.config file.

6) Keep the name value because this is what the locator will match against.

7) Keep the attributes you will change.

8) Copy the xtd:Transform and xtd:Locator from the commented example to the end of each connection string:

  <connectionStrings>
    <add name="MainDB" connectionString="Your Test Values"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    <add name="CATBERT" connectionString="Your Test Values"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    <add name="RATBERT" connectionString="Your Test Values"
         xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  </connectionStrings>

9) Delete the old ConnectionStringTest.config file because you don't need it anymore.

In my example below, I've only changed the app settings that will change with the deployment between the development version, Test, and Production. This requires the web.config to have the values you will need for deployment.

  <appSettings>
    <add key="HelpDeskEmail" value="test1@ucdavis.edu" xdt:Transform="Replace" xdt:Locator="Match(key)"/>
    <add key="AppHelpDeskEmail" value="test2@ucdavis.edu" xdt:Transform="Replace" xdt:Locator="Match(key)"/>
    <add key="WebHelpDeskEmail" value="test3@ucdavis.edu" xdt:Transform="Replace" xdt:Locator="Match(key)"/> 
  </appSettings>

11) Delete the AppSettingsTest.config file because it is no longer needed.

12) Do the same for other configurations you may have like Production, but of course using the related transform file under the Web.config

New deployment steps

double click on properties.

 2) For each configuration, make your changes. These will mostly be in the Package/Publish Web tab.

 3) Choose your configuration

4) Build your solution

5) Right click on your main project and choose "Build Deployment Package"

6) Wait until it is done. By showing all files in your project, you can open the transformed Web.config to ensure it was created as you expect

 7) In the same area, you can look in Package to see that the zip files was created.

8) Right click on you main project and choose publish

 9) Enter a profile name

10) choose the Publish Method (File System for me and the target location). If you choose File System, you can specify a local path so validate that everything is created as you expect. You may or may not want to delete all existing files prior to publishing.

11) Click save.

12) Click Publish

13) repeat for other configurations

14)If you want the publish setting to be saved to source safe, "show all files" for the project, find "projectName".Publish.xml and include it in the project.

If there is a static call using SmartServiceLocator to get the repository, the repository can be mocked in the Unit Test by registering an additional service. The UCDArch Controller test base class has an override-able method to do this.

 protected override void RegisterAdditionalServices(IWindsorContainer container)
        {
            TermCodeRepository = MockRepository.GenerateStub<IRepository<TermCode>>();
            container.Kernel.AddComponentInstance<IRepository<TermCode>>(TermCodeRepository);
            base.RegisterAdditionalServices(container);
        }

The TermCodeRepository can then have the expected values assigned to it in the individual unit test like this:

            var termCodes = new List<TermCode>();
            termCodes.Add(CreateValidEntities.TermCode(1));
            termCodes[0].IsActive = true;
            termCodes[0].SetIdTo("1");
            TermCodeRepository.Expect(a => a.Queryable).Return(termCodes.AsQueryable()).Repeat.Any();

The code that calls this looks like this:

        public static TermCode GetCurrent()
        {
            var repository = SmartServiceLocator<IRepository<TermCode>>.GetService();
            var term = repository.Queryable.Where(a => a.IsActive).OrderByDescending(a => a.Id).FirstOrDefault();
            Check.Require(term != null, "Unable to find valid term.");

            return term;
        }

Database items that automatically get populated like lists of other database records linked with a bag in the mapping file may not be populated if the GetById doesn't think that data has been changed.

For example, if a ceremony has related registrations, and the ceremony is added to those registrations, then the IList will probably not be populated in the ceremony.

You may think, I'll just do a get from the database and it will refresh. But no, NHibernate may think that you have an existing copy of the ceremony in the database, so it will avoid the call to the database, and your data will not be refreshed. To get around this, you want to evict the ceremony entity before getting it, this will force NHibernate to make the call to the database and the list will be populated.

NHibernateSessionManager.Instance.GetSession().Evict(entity);

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:

Due to the changes instituted by Kuali Financials, the FISDataMart now fails to load the Accounts tables as of August 14th, 2010.

This is because DANR, AAES, and BIOS were formerly top-level orgs.  Now they are somewhere near the 4th and 5th levels.  This also means that all other orgs and org levels have been pushed down 4 levels, meaning that that the organizations table will need to be modified to have twelve (12) org levels instead of the former six (6), as well as, usp_DownloadOrganizations modified to download the addition fields.

NOTE: Former level 3/"Department" Orgs are now level 6 Orgs!

The following stored procedures appear to be affected and need a "fix" applied:

• usp_DownloadAccounts,
  
• usp_DownloadBillingIDConversions,
  
• usp_DownloadGeneralLedgerPeriodBalances,
  
• usp_DownloadOrganizations,
  
• usp_DownloadPendingTransactions,
  
• usp_DownloadSubAccount, and
  
• usp_DownloadTransactions
  

Changes in organizational layout:

Prior to 08-14-2010:

Notice that there are now three (3) levels of Organizations on top of the original organizations.  Therefore, now we have to check in Org Level 1 through 5 to determine if it is an org within our college.

Instead of the SQL for the Orgs query being something like

SELECT DISTINCT ORG_ID
FROM FINANCE.ORGANIZATION_HIERARCHY O
WHERE FISCAL_YEAR >= 2009
AND
(
    (CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''AAES'')
  OR
    (CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''BIOS'')
  OR
    (CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''AAES'') 
  OR 
    (CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''BIOS'')
)

 It's now something like this:  Note I now have to check levels 1-2 and 4-5, instead of just 1-2.

SELECT DISTINCT ORG_ID
FROM FINANCE.ORGANIZATION_HIERARCHY O
WHERE FISCAL_YEAR >= 2009
AND
(
    (CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''AAES'')
  OR
    (CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''BIOS'')
  OR
    (CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''AAES'') 
  OR 
    (CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''BIOS'')
  OR 
    (CHART_NUM_LEVEL_4=''3'' AND ORG_ID_LEVEL_4 = ''AAES'')
  OR
    (CHART_NUM_LEVEL_4=''3'' AND ORG_ID_LEVEL_4 = ''BIOS'')
  OR
    (CHART_NUM_LEVEL_5=''L'' AND ORG_ID_LEVEL_5 = ''AAES'')
  OR 
    (CHART_NUM_LEVEL_5=''L'' AND ORG_ID_LEVEL_5 = ''BIOS'')
)

This means I will need to re-factor all or the queries and reports that reference the Organizations and Accounts tables.  This is going to be a pain.

select *
FROM
     OPENQUERY (FIS_DS,
	'SELECT count(*) AS ORG_ID_COUNT
	FROM
		FINANCE.ORGANIZATION_ACCOUNT A 
	WHERE (
				
		A.FISCAL_YEAR >= 2011	
		AND (
			ORG_ID IN 
			(
				SELECT DISTINCT ORG_ID 
				FROM FINANCE.ORGANIZATION_HIERARCHY O
				WHERE
				(
					(CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''AAES'')
					OR
					(CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''BIOS'')
					OR
					(CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''AAES'')
					OR 
					(CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''BIOS'')
					OR 
					(CHART_NUM_LEVEL_4=''3'' AND ORG_ID_LEVEL_4 = ''AAES'')
					OR
					(CHART_NUM_LEVEL_4=''3'' AND ORG_ID_LEVEL_4 = ''BIOS'')
					OR
					(CHART_NUM_LEVEL_5=''L'' AND ORG_ID_LEVEL_5 = ''AAES'')
					OR 
					(CHART_NUM_LEVEL_5=''L'' AND ORG_ID_LEVEL_5 = ''BIOS'')
				)
			)
			OR A.ACCT_NUM in (''EVOR094'',''MBOR039'',''MIOR017'',''NPOR035'',''PBOR023'',''BSOR001'',''BSFACOR'',''BSRESCH'',''CNSOR05'',''EVOR093'',''PBHB024'',''PBHBSAL'')
		)
		AND (A.ACCT_LAST_UPDATE_DATE BETWEEN TO_DATE(''2010.08.17'' ,''yyyy.mm.dd'')
			AND TO_DATE(''2010.08.19'' ,''yyyy.mm.dd''))
	)
	'

Changes to TransactionLogV:

The TransactionLogV database view required that the Organization "normalizing" logic be revised to accommodate the new org hierarchy as  follows:

Old Organization SQL:

 CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Org2 ELSE dbo.Organizations.Org1 END AS Level1_OrgCode, 
 CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Name2 ELSE dbo.Organizations.Name1 END AS Level1_OrgName, 
 CASE WHEN dbo.Organizations.Chart2 = 'L' THEN dbo.Organizations.Org3 ELSE dbo.Organizations.Org2 END AS Level2_OrgCode, 
 CASE WHEN dbo.Organizations.Chart2 = 'L' THEN dbo.Organizations.Name3 ELSE dbo.Organizations.Name2 END AS Level2_OrgName, 
 CASE WHEN dbo.Organizations.Chart3 = 'L' THEN dbo.Organizations.Org4 ELSE dbo.Organizations.Org3 END AS Level3_OrgCode, 
 CASE WHEN dbo.Organizations.Chart3 = 'L' THEN dbo.Organizations.Name4 ELSE dbo.Organizations.Name3 END AS Level3_OrgName, 

Revised Organization SQL:

  CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Org2 
           WHEN dbo.Organizations.Chart1 = '3' THEN dbo.Organizations.Org1 
           WHEN dbo.Organizations.Chart4 = 'L' THEN dbo.Organizations.Org5 
           ELSE dbo.Organizations.Org4 END AS Level1_OrgCode, 
  CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Name2 
           WHEN dbo.Organizations.Chart1 = '3' THEN dbo.Organizations.Name1 
           WHEN dbo.Organizations.Chart4 = 'L' THEN dbo.Organizations.Name5 
           ELSE dbo.Organizations.Name4 END AS Level1_OrgName, 

  CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Org3 
           WHEN dbo.Organizations.Chart1 = '3' THEN dbo.Organizations.Org2 
           WHEN dbo.Organizations.Chart5 = 'L' THEN dbo.Organizations.Org6 
           ELSE dbo.Organizations.Org5 END AS Level2_OrgCode, 
  CASE WHEN dbo.Organizations.Chart2 = 'L' THEN dbo.Organizations.Name3 
           WHEN dbo.Organizations.Chart2 = '3' THEN dbo.Organizations.Name2 
           WHEN dbo.Organizations.Chart5 = 'L' THEN dbo.Organizations.Name6 
           ELSE dbo.Organizations.Name5 END AS Level2_OrgName, 

  CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Org4 
           WHEN dbo.Organizations.Chart1 = '3' THEN dbo.Organizations.Org3 
           WHEN dbo.Organizations.Chart6  = 'L' THEN dbo.Organizations.Org7 
           ELSE dbo.Organizations.Org6 END AS Level3_OrgCode, 
  CASE WHEN dbo.Organizations.Chart1 = 'L' THEN dbo.Organizations.Name4 
           WHEN dbo.Organizations.Chart1 = '3' THEN dbo.Organizations.Name3 
           WHEN  dbo.Organizations.Chart6 = 'L' THEN dbo.Organizations.Name7 
           ELSE dbo.Organizations.Name6 END AS Level3_OrgName, 

January 28, 2011 - Further Investigation:

Upon further investigation it was discovered that the College of Biological Sciences (BIOS) chart "L" organization is either at Org Level 1 or Org Level 4, not Org Level 2 or Org Level 5, as is our college and perhaps others, because some (or all) of their chart "L" funds are not related to ANR, and therefore, do not need to be remapped to a different organization level.

Therefore, I have revised the org logic to correctly represent this accordingly, i.e.,

SELECT *  FROM
     OPENQUERY (FIS_DS,
        'SELECT
            COUNT(*) AS ORG_ID_COUNT
        FROM
            FINANCE.ORGANIZATION_ACCOUNT A
        WHERE (
                (
                    A.FISCAL_YEAR >= 2011  
                )      
                AND (
                        (CHART_NUM, ORG_ID) IN
                        (
                            SELECT DISTINCT CHART_NUM, ORG_ID
                            FROM FINANCE.ORGANIZATION_HIERARCHY O
                            WHERE
                            (
                                (CHART_NUM_LEVEL_1=''3'' AND ORG_ID_LEVEL_1 = ''AAES'')
                                OR
                                (CHART_NUM_LEVEL_2=''L'' AND ORG_ID_LEVEL_2 = ''AAES'')
                                OR
                                (ORG_ID_LEVEL_1 = ''BIOS'') -- NOTE: Same org level (1) for both chart 3 and L for BIOS
                                OR
                                (CHART_NUM_LEVEL_4 = ''3'' AND ORG_ID_LEVEL_4 = ''AAES'')
                                OR
                                (CHART_NUM_LEVEL_5 = ''L'' AND ORG_ID_LEVEL_5 = ''AAES'')
                                OR
                                (ORG_ID_LEVEL_4 = ''BIOS'') -- NOTE: Same org level (4) for both chart 3 and L for BIOS
                            )
                            AND
                            (
                                FISCAL_YEAR >= 2011  
                            )
                        )
                        OR A.ACCT_NUM in (''EVOR094'',''MBOR039'',''MIOR017'',''NPOR035'',''PBOR023'',''BSOR001'',''BSFACOR'',''BSRESCH'',''CNSOR05'',''EVOR093'',''PBHB024'',''PBHBSAL'')
                )
            )
        ')

Re-Revised Organizational VIEW SQL:

Note that the AAES and BIOS were removed from the majority of the logic, and the code modified to "look" for "DANR" instead in order if the Organizational level shift was required.  The only exception is in the OrgR mapping logic

SELECT DISTINCT 
                         Year, Period, Org, Chart, [Level], Name, Type, BeginDate, EndDate, HomeDeptNum, HomeDeptName, UpdateDate, 
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN CHART2
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN CHART5
						WHEN CHART1 = '3' THEN Chart1
						ELSE CHART4 END) AS Chart1, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN ORG2
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN ORG5
						WHEN CHART1 = '3' THEN Org1
						ELSE ORG4 END) AS Org1, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name2
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name5
						WHEN CHART1 = '3' THEN Name1
						ELSE Name4 END) AS Name1, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart3
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart6
						WHEN CHART1 = '3' THEN Chart2
						ELSE Chart5 END) AS Chart2, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org3
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org6
						WHEN CHART1 = '3' THEN Org2
						ELSE Org5 END) AS Org2, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name3
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name6
						WHEN CHART1 = '3' THEN Name2
						ELSE Org5 END) AS Name2, 
                         
                         ISNULL((CASE WHEN (Org1 = 'BIOS') THEN Chart2 
                         WHEN (Org4 = 'BIOS') THEN Chart5 
                         WHEN (CHART = '3' AND Org1 = 'AAES') THEN Chart3 
                         WHEN (CHART = 'L' AND Org2 = 'AAES') THEN Chart4 
                         WHEN (CHART = '3' AND Org4 = 'AAES') THEN Chart6 
                         WHEN (CHART = 'L' AND Org5 = 'AAES') THEN Chart7 END), Chart) AS ChartR, 
                         
                         ISNULL((CASE WHEN (Org1 = 'BIOS') THEN Org2 
                         WHEN (Org4 = 'BIOS') THEN Org5 
                         WHEN (CHART = '3' AND Org1 = 'AAES') THEN Org3 
                         WHEN (CHART = 'L' AND Org2 = 'AAES') THEN Org4 
                         WHEN (CHART = '3' AND Org4 = 'AAES') THEN Org6 
                         WHEN (CHART = 'L' AND Org5 = 'AAES') THEN Org7 END), Org) AS OrgR, 
                         
                         ISNULL((CASE WHEN (Org1 = 'BIOS') THEN Name2 
                         WHEN (Org4 = 'BIOS') THEN Name5 
                         WHEN (CHART = '3' AND Org1 = 'AAES') THEN Name3 
                         WHEN (CHART = 'L' AND Org2 = 'AAES') THEN Name4 
                         WHEN (CHART = '3' AND Org4 = 'AAES') THEN Name6 
                         WHEN (CHART = 'L' AND Org5 = 'AAES') THEN Name7 END), Name) AS NameR, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart4
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart7
						WHEN CHART1 = '3' THEN Chart3
						ELSE Chart6 END) AS Chart3, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org4
                         WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org7
                         WHEN CHART1 = '3' THEN Org3
						 ELSE Org6 END) AS Org3, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name4
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name7
						WHEN CHART1 = '3' THEN Name3
						ELSE Name6 END) AS Name3, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart5
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart8
						WHEN CHART1 = '3' THEN Chart4
						ELSE Chart7 END) AS Chart4, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org5
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org8
						WHEN CHART1 = '3' THEN Org4
						ELSE Org7 END) AS Org4, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name5
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name8
						WHEN CHART1 = '3' THEN Name4
						ELSE Name7 END) AS Name4, 
                         
                          (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart6
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart9
						WHEN CHART1 = '3' THEN Chart5
						ELSE Chart8 END) AS Chart5, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org6
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org9
						WHEN CHART1 = '3' THEN Org5
						ELSE Org8 END) AS Org5, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name6
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name9
						WHEN CHART1 = '3' THEN Name5
						ELSE Name8 END) AS Name5, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart7
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart10
						WHEN CHART1 = '3' THEN Chart6
						ELSE Chart9 END) AS Chart6, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org7
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org10
						WHEN CHART1 = '3' THEN Org6
						ELSE Org9 END) AS Org6, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name7
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name10
						WHEN CHART1 = '3' THEN Name6
						ELSE Name9 END) AS Name6, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart8
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart11
						WHEN CHART1 = '3' THEN Chart7
						ELSE Chart10 END) AS Chart7, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org8
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org11
						WHEN CHART1 = '3' THEN Org7
						ELSE Org10 END) AS Org7, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name8
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name11
						WHEN CHART1 = '3' THEN Name7
						ELSE Name10 END) AS Name7, 
                         
                          (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Chart9
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Chart12
						WHEN CHART1 = '3' THEN Chart8
						ELSE Chart11 END) AS Chart8, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Org9
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Org12
						WHEN CHART1 = '3' THEN Org8
						ELSE Org11 END) AS Org8, 
                         
                         (CASE WHEN CHART1 = 'L' AND ORG1 = 'DANR' THEN Name9
						WHEN CHART4 = 'L' AND ORG4 = 'DANR' THEN Name12
						WHEN CHART1 = '3' THEN Name8
						ELSE Name11 END) AS Name8, 
						
                         ActiveIndicator, 
                         
                         OrganizationPK, 
                         
                         (CASE WHEN ((Chart = '3' AND Org5 = 'ACBS') OR
                         (Chart = '3' AND Org2 = 'ACBS')) THEN 2 
                         WHEN ((Chart = '3' AND Org4 = 'AAES') OR
                         (Chart = 'L' AND Org5 = 'AAES') OR
                         (Chart = '3' AND Org1 = 'AAES') OR
                         (Chart = 'L' AND Org2 = 'AAES')) THEN 1 
                         ELSE 0 END) AS IsCAES
FROM            dbo.Organizations

Tuesday, April 13th, 2010:

Yesterday I created a new PPSDataMart on vogel.

Now I need to test the newly modified stored procedures to verify that I can fully populate an empty database using them.

• Populate Appointments:

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdateAppointments]

SELECT    'Return Value' = @return_value
GO


-- Downloading Appointment records...
-- Selecting records from PAYROLL.EDBAPP_V
-- Update any matching records in the Appointments table where none have been updated previously.
-- IsDebug = False (Run script)
-------------------------------------------------------------------------

(8541 row(s) affected)

-------------------------------------------------------------------------

 Executed in 00:00:04.

• Populate Departments:

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdateDepartments]

SELECT    'Return Value' = @return_value
GO


-- Downloading Department records...
-- Selecting all records from PAYROLL.CTVHME
-- IsDebug = False (Run script)
-------------------------------------------------------------------------

(1216 row(s) affected)

-------------------------------------------------------------------------

• Populate Distributions:

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdateDistributions]

SELECT    'Return Value' = @return_value
GO


-------------------------------------------------------------------------

(15932 row(s) affected)

-------------------------------------------------------------------------

 Executed in 00:00:08.

• Populate Schools:
  

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdateSchools]

SELECT    'Return Value' = @return_value
GO


-------------------------------------------------------------------------

(33 row(s) affected)

-------------------------------------------------------------------------

 Executed in 00:00:00.

• Populate Titles:

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdateTitles]

SELECT    'Return Value' = @return_value
GO


-- Downloading Title records...
-- Selecting all records from PAYROLL.CTLTCI
-- Get updates only = False
-- IsDebug = False (Run script)
-------------------------------------------------------------------------

(2277 row(s) affected)

------------------------------------------------------------------------

Executed in 00:00:05.

•  Populate TitleGroups:
  

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdateTitleGroups]

SELECT    'Return Value' = @return_value
GO

-- Downloading TitleGroup records...
-- Selecting all records from PAYROLL.CTLJGD
-- IsDebug = False (Run script)
-------------------------------------------------------------------------

(162 row(s) affected)

-------------------------------------------------------------------------

Executed in 00:00:00.

• Populate Persons:

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_UpdatePersons]

SELECT    'Return Value' = @return_value
GO

-- Downloading Persons records...
-- Selecting all records from EDBPER_V
-- IsDebug = False (Run script)
-------------------------------------------------------------------------

(7862 row(s) affected)

-------------------------------------------------------------------------

Executed in 00:00:03.

• Update Persons.IsInPPS:

UPDATE PPSDataMart.dbo.Persons
set IsInPPS = (
CASE WHEN EmployeeID IN (select Employee_ID FROM OPENQUERY(PAY_PERS_EXTR, 'SELECT EMPLOYEE_ID FROM PAYROLL.EDBPER_V')) THEN 1 ELSE 0 END )

-------------------------------------------------------------------------

(7862 row(s) affected)

------------------------------------------------------------------------

Executed in 00:00:01.

• Rebuild PPSDataMart Indexes:

USE [PPSDataMart]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[usp_RebuildPPSDataMartIndexes]

SELECT    'Return Value' = @return_value
GO

-------------------------------------------------------------------------

 Executed in 00:00:01.

Several of my programs started acting finicky after a Windows 7 automatic update, so I performed a system restore back to the previous day.  Once of the errors I was getting was similar to “C:\Windows\system32\systempropertiesadvanced.exe File system error (-1073741819)* (*Note the error number; not the particular file name.)  These errors seemed more like file permission problems rather than file corruption issues, and might be due to entering the incorrect credentials.

Therefore, if you experience similar error messages, try re-entering your login credentials before trying anything else!

Once the system restore was complete I received a “The primary trust relationship with this computer…” message when attempting to log into the computer using my domain account.  The IT Help Desk said that this sometimes happens after performing a Windows 7 system restore.

To address this issue, I was instructed to log into the computer’s local “caesadmin” admin account.

I did so and went through the procedure of changing the computer from being a member of the domain to a member of a WORKGROUP.  This portion appeared to complete successfully; however, the change domain widget stopped working when we attempted to rejoin the computer back to the domain.  We attempted to add the computer to the domain several times, and each time the widget stopped working.

Shuka and I spent over an hour trying to resolve this issue when finally the decision was made to backup my personal files and reinstall the OS.   The last phase of the backup process involved saving/exporting the Fire Fox bookmarks; however, in order to do this you need to be logged in to the computer’s domain account, and herein lies the problem.  Please understand that at this point we were over an hour into the backup and pending OS reinstall process.

Necessity being the mother of invention, I tried unplugging the network cable and logging into my domain account again.  Success, it worked!  Once I was logged in I plugged the network cable back in and then went through the “add computer to domain” process*.  Since the current domain was “caesdo.caes.ucdavis.edu” or something similar, I did not see the necessity of adding it to a WORKGROUP first, but instead just changed it to “aesdean”.  This time the widget did not stop working and completed successfully. 

*Note: Since I was logged in with my regular domain account, it was necessary that I enter my domain admin credentials when I selected “Advanced System Properties”.

If the Key value is a string instead of an int, and the mapping file has "<generator class="assigned" />" then the key has to be specified before it is added. This means without a force save, nHibernate thinks it should be doing an update instead of an insert. So do an "Repository.EnsurePersistent(Entity, true);"

Similar to this, if the int key has been manually set to be other than zero, nHibernate thinks you should be doing an update instead of an insert. Preferabbly, don't set the key value and everything should be Ok.

When writing the mock to allow an interface method to pass in the method you are testing, you may initially think you could use .repeat.atLeastOnce():

_emailService.Expect(a => a.SendRegistrationConfirmation(Controller.Repository, registration)).Repeat.
                AtLeastOnce();

Unfortunately, if you were to test this and remove the line of code from the method you are testing it would pass. Instead once your method has passed, you can assert that the method was called by using code like this:

_emailService.AssertWasCalled(a => a.SendRegistrationConfirmation(Controller.Repository, registration));

I had a hidden input field:

I was trying to assign a value to it

$("#displayAmount") = total.toFixed(2);

With the assignment like this it failed. To assign it correctly, I had to use this:

$("#displayAmount").val(total.toFixed(2));

Team Foundation Server is a pain to configure, but recently in 2010 it did become a whole lot better.  Most notably there is a new configuration tool, but a lot of the settings are still managed in the same way.  Permissions being a sort of annoying part because you have to manage it in three seperate places (TFS, SharePoint and Reporting Services).  I've blogged about configuring permissions specifically targeted towards small groups that need to have all developers have access to all projects. You can find the post here.

Secondly making it available to the internet over SSL is another issue that I couldn't find a real guide on how to setup in TFS 2010 RTM.  So I've also got another post here.

Especially with some unit tests, the length of a test class can get very large which can slow down ReSharper because it has to scan through the file. If you notice this happening create a partial class and break the files into whatever form seems logical to you.

 You will almost always have an existing class that you want to split into a partial class instead of designing a partial calss from scratch, so the steps below will be with that in mind.

I prefer to have all partial classes in a single folder so that they are logically grouped together.

1. To prevent naming conflicts, rename your existing file temporally.
2. Create a folder with the name of your old class.
3. Move your file into the new folder.
4. Rename it back to what it was. In the future, you may want to rename this to have Init or some other name that makes sense to you.
5. Change the "class" to "partial class"
6. Create a new class/file. I suggest naming them the same with Part01, Part02 at the end of the file name.
7. replace the class name with the name of the partial class and include the partial key word.
8. If the original class inherited anything, the subsequent partial class don't have them.
9. Cut and paste code into the new partial classes.

if you have any private properties, they may need to be changed to protected. You will notice any issues like this when you compile.

 Previously, if a large number of tests were run that used abstract base classes the runner would crash with a stack overflow error. This has been fixed with 5.0 build 1659

Also in this build Ignored and skipped tests now appear:

When creating a unit test, if a value is created inside the method you are testing, it is useful to be able to determine what was used.

The most common time you would use this is when a database record is saved. We can get those values by using the following code:

TransactionRepository.AssertWasCalled(a => a.EnsurePersistent(Arg<Transaction>.Is.Anything));
var args = (Transaction)TransactionRepository.GetArgumentsForCallsMadeOn(a => a.EnsurePersistent(Arg<Transaction>.Is.Anything))[0][0];

Then we can assert that the values in the saved record (Transaction) are what we expect:

Assert.IsNotNull(args);
Assert.AreEqual(60.00m, args.Amount);

As unit tests are added, often we find that many of the tests have a similar structure, just with a few different values. Once we have identified a group of tests like this we can create a ReSharper template to add the tests while prompting us to enter the values that are different, reusing those values throughout the tests so that they only have to be entered once.

If we look at the following set of tests we can see we are testing the FullName of the Unit record for the following things:

1. That it doesn't save when the FullName is
   
1. Null
2. Empty
3. Spaces only
4. Exceeds the maximum length of 50 characters
2. And that it does save when the FullName is
1. One character long
2. 50 characters long (the maximum length)

        #region FullName Tests
        #region Invalid Tests

        /// <summary>
        /// Tests the FullName with null value does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestFullNameWithNullValueDoesNotSave()
        {
            Unit unit = null;
            try
            {
                #region Arrange
                unit = GetValid(9);
                unit.FullName = null;
                #endregion Arrange

                #region Act
                UnitRepository.DbContext.BeginTransaction();
                UnitRepository.EnsurePersistent(unit);
                UnitRepository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull(unit);
                var results = unit.ValidationResults().AsMessageList();
                results.AssertErrorsAre("FullName: may not be null or empty");
                Assert.IsTrue(unit.IsTransient());
                Assert.IsFalse(unit.IsValid());
                throw;
            }
        }

        /// <summary>
        /// Tests the FullName with empty string does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestFullNameWithEmptyStringDoesNotSave()
        {
            Unit unit = null;
            try
            {
                #region Arrange
                unit = GetValid(9);
                unit.FullName = string.Empty;
                #endregion Arrange

                #region Act
                UnitRepository.DbContext.BeginTransaction();
                UnitRepository.EnsurePersistent(unit);
                UnitRepository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull(unit);
                var results = unit.ValidationResults().AsMessageList();
                results.AssertErrorsAre("FullName: may not be null or empty");
                Assert.IsTrue(unit.IsTransient());
                Assert.IsFalse(unit.IsValid());
                throw;
            }
        }

        /// <summary>
        /// Tests the FullName with spaces only does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestFullNameWithSpacesOnlyDoesNotSave()
        {
            Unit unit = null;
            try
            {
                #region Arrange
                unit = GetValid(9);
                unit.FullName = " ";
                #endregion Arrange

                #region Act
                UnitRepository.DbContext.BeginTransaction();
                UnitRepository.EnsurePersistent(unit);
                UnitRepository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull(unit);
                var results = unit.ValidationResults().AsMessageList();
                results.AssertErrorsAre("FullName: may not be null or empty");
                Assert.IsTrue(unit.IsTransient());
                Assert.IsFalse(unit.IsValid());
                throw;
            }
        }

        /// <summary>
        /// Tests the FullName with too long value does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestFullNameWithTooLongValueDoesNotSave()
        {
            Unit unit = null;
            try
            {
                #region Arrange
                unit = GetValid(9);
                unit.FullName = "x".RepeatTimes((50 + 1));
                #endregion Arrange

                #region Act
                UnitRepository.DbContext.BeginTransaction();
                UnitRepository.EnsurePersistent(unit);
                UnitRepository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull(unit);
                Assert.AreEqual(50 + 1, unit.FullName.Length);
                var results = unit.ValidationResults().AsMessageList();
                results.AssertErrorsAre("FullName: length must be between 0 and 50");
                Assert.IsTrue(unit.IsTransient());
                Assert.IsFalse(unit.IsValid());
                throw;
            }
        }
        #endregion Invalid Tests

        #region Valid Tests

        /// <summary>
        /// Tests the FullName with one character saves.
        /// </summary>
        [TestMethod]
        public void TestFullNameWithOneCharacterSaves()
        {
            #region Arrange
            var unit = GetValid(9);
            unit.FullName = "x";
            #endregion Arrange

            #region Act
            UnitRepository.DbContext.BeginTransaction();
            UnitRepository.EnsurePersistent(unit);
            UnitRepository.DbContext.CommitTransaction();
            #endregion Act

            #region Assert
            Assert.IsFalse(unit.IsTransient());
            Assert.IsTrue(unit.IsValid());
            #endregion Assert
        }

        /// <summary>
        /// Tests the FullName with long value saves.
        /// </summary>
        [TestMethod]
        public void TestFullNameWithLongValueSaves()
        {
            #region Arrange
            var unit = GetValid(9);
            unit.FullName = "x".RepeatTimes(50);
            #endregion Arrange

            #region Act
            UnitRepository.DbContext.BeginTransaction();
            UnitRepository.EnsurePersistent(unit);
            UnitRepository.DbContext.CommitTransaction();
            #endregion Act

            #region Assert
            Assert.AreEqual(50, unit.FullName.Length);
            Assert.IsFalse(unit.IsTransient());
            Assert.IsTrue(unit.IsValid());
            #endregion Assert
        }

        #endregion Valid Tests
        #endregion FullName Tests

 We can see "Act" region is always the same, and that the "Arrange" region is very similar except where we set the specific piece of data that we want to test, the name of the test always contains the field "FullName" that we are testing, but the "Assert" region is different because of what we are testing.

Steps to create the template

1. Identify a set of tests with common structure that will probably be used again. As tests are added and you keep adding these similar tests over and over this will become obvious.
2. Refactor the tests as much as possible to make them as generic as possible. This would include creating a generic method like "GetValid" to create a record that is always valid so we know that the test is only testing one specific element. BTW, this is a good practice anyway as it make it easier to fix many tests is something is changed on purpose (maybe a field is added or the validation is changed for an existing field).
3. Now, copy and paste the tests you want to create into a new ReSharper live template.
   -> ->
   
4. Uncheck reformat.
5. Identify values that will change depending on the field name and record that the tests will be run against. For this example, that would be unit and FullName. Replace them with ReSharper values line $FieldToTest$ and $Entity$.
6. Everywhere these names occur, replace them With the ReSharper variable. This can be in comments, method names, or part of variable names.
7. The two other ReSharper variables used in my example are $entityVariable$ and $MaxLength$
8. $Entity$ should be set to not to use a macro and the first time this appears is in the code. You want to set the Editable Occurrence to be a place in the code where intellisense will let the Entity be populated without typing the full name.
9. The $FieldToTest$ is also set not to use a macro, but the first occurrence in the code is in position #4. Note, for intellisense to work correctly, the order of the macros can be important. In my example, the $Entity$ is first.
10. $entityVariable$ is not editable because this is set with the macro to be the name of the $Entity$ but starting with a lower case letter.
11. $MaxLength$ is used in the tests checking the maximum length of the string. 1 is added to it to make it fail the validation.
12. Once you have created this, test the template with different records and different fields (with the same type of validation) to see if you missed anything.

The ReSharper template

        #region $FieldToTest$ Tests
        #region Invalid Tests

        /// <summary>
        /// Tests the $FieldToTest$ with null value does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void Test$FieldToTest$WithNullValueDoesNotSave()
        {
            $Entity$ $entityVariable$ = null;
            try
            {
                #region Arrange
                $entityVariable$ = GetValid(9);
                $entityVariable$.$FieldToTest$ = null;
                #endregion Arrange

                #region Act
                $Entity$Repository.DbContext.BeginTransaction();
                $Entity$Repository.EnsurePersistent($entityVariable$);
                $Entity$Repository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull($entityVariable$);
                var results = $entityVariable$.ValidationResults().AsMessageList();
                results.AssertErrorsAre("$FieldToTest$: may not be null or empty");
                Assert.IsTrue($entityVariable$.IsTransient());
                Assert.IsFalse($entityVariable$.IsValid());
                throw;
            }
        }

        /// <summary>
        /// Tests the $FieldToTest$ with empty string does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void Test$FieldToTest$WithEmptyStringDoesNotSave()
        {
            $Entity$ $entityVariable$ = null;
            try
            {
                #region Arrange
                $entityVariable$ = GetValid(9);
                $entityVariable$.$FieldToTest$ = string.Empty;
                #endregion Arrange

                #region Act
                $Entity$Repository.DbContext.BeginTransaction();
                $Entity$Repository.EnsurePersistent($entityVariable$);
                $Entity$Repository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull($entityVariable$);
                var results = $entityVariable$.ValidationResults().AsMessageList();
                results.AssertErrorsAre("$FieldToTest$: may not be null or empty");
                Assert.IsTrue($entityVariable$.IsTransient());
                Assert.IsFalse($entityVariable$.IsValid());
                throw;
            }
        }

        /// <summary>
        /// Tests the $FieldToTest$ with spaces only does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void Test$FieldToTest$WithSpacesOnlyDoesNotSave()
        {
            $Entity$ $entityVariable$ = null;
            try
            {
                #region Arrange
                $entityVariable$ = GetValid(9);
                $entityVariable$.$FieldToTest$ = " ";
                #endregion Arrange

                #region Act
                $Entity$Repository.DbContext.BeginTransaction();
                $Entity$Repository.EnsurePersistent($entityVariable$);
                $Entity$Repository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull($entityVariable$);
                var results = $entityVariable$.ValidationResults().AsMessageList();
                results.AssertErrorsAre("$FieldToTest$: may not be null or empty");
                Assert.IsTrue($entityVariable$.IsTransient());
                Assert.IsFalse($entityVariable$.IsValid());
                throw;
            }
        }

        /// <summary>
        /// Tests the $FieldToTest$ with too long value does not save.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void Test$FieldToTest$WithTooLongValueDoesNotSave()
        {
            $Entity$ $entityVariable$ = null;
            try
            {
                #region Arrange
                $entityVariable$ = GetValid(9);
                $entityVariable$.$FieldToTest$ = "x".RepeatTimes(($MaxLength$ + 1));
                #endregion Arrange

                #region Act
                $Entity$Repository.DbContext.BeginTransaction();
                $Entity$Repository.EnsurePersistent($entityVariable$);
                $Entity$Repository.DbContext.CommitTransaction();
                #endregion Act
            }
            catch (Exception)
            {
                Assert.IsNotNull($entityVariable$);
                Assert.AreEqual($MaxLength$ + 1, $entityVariable$.$FieldToTest$.Length);
                var results = $entityVariable$.ValidationResults().AsMessageList();
                results.AssertErrorsAre("$FieldToTest$: length must be between 0 and $MaxLength$");
                Assert.IsTrue($entityVariable$.IsTransient());
                Assert.IsFalse($entityVariable$.IsValid());
                throw;
            }
        }
        #endregion Invalid Tests

        #region Valid Tests

        /// <summary>
        /// Tests the $FieldToTest$ with one character saves.
        /// </summary>
        [TestMethod]
        public void Test$FieldToTest$WithOneCharacterSaves()
        {
            #region Arrange
            var $entityVariable$ = GetValid(9);
            $entityVariable$.$FieldToTest$ = "x";
            #endregion Arrange

            #region Act
            $Entity$Repository.DbContext.BeginTransaction();
            $Entity$Repository.EnsurePersistent($entityVariable$);
            $Entity$Repository.DbContext.CommitTransaction();
            #endregion Act

            #region Assert
            Assert.IsFalse($entityVariable$.IsTransient());
            Assert.IsTrue($entityVariable$.IsValid());
            #endregion Assert
        }

        /// <summary>
        /// Tests the $FieldToTest$ with long value saves.
        /// </summary>
        [TestMethod]
        public void Test$FieldToTest$WithLongValueSaves()
        {
            #region Arrange
            var $entityVariable$ = GetValid(9);
            $entityVariable$.$FieldToTest$ = "x".RepeatTimes($MaxLength$);
            #endregion Arrange

            #region Act
            $Entity$Repository.DbContext.BeginTransaction();
            $Entity$Repository.EnsurePersistent($entityVariable$);
            $Entity$Repository.DbContext.CommitTransaction();
            #endregion Act

            #region Assert
            Assert.AreEqual($MaxLength$, $entityVariable$.$FieldToTest$.Length);
            Assert.IsFalse($entityVariable$.IsTransient());
            Assert.IsTrue($entityVariable$.IsValid());
            #endregion Assert
        }

This ReSharper Template can be downloaded here to be imported into your version. Rename the ShortCut and description to something meaningful to you.

Add common string tests