Workaround for absence of semi-additive function LastNonEmpty in SQL Server 2005 Standard Edition

In my last project I'd met problem when I tried to setup OLAP cube with semi-additive measure on Analysis Service Standard Edition. In my case LastNonEmpty.

As example database I use Adventure Works DW.

Measure [Last NonEmpty Total Product Cost - Workaround] was created under [Sales Summary] measure group with Sum aggregate function. Then in Calculations added next cell calculation member.

 

CREATE CELL CALCULATION [Adventure Works].CalculateLastNonEmpty 
    FOR '({[Measures].[Last NonEmpty Total Product Cost - Workaround]}, DESCENDANTS([Date].[Calendar].[All Periods]))' 
    AS 
' 
    Aggregate( 
        Tail( 
            Filter( 
                Descendants( 
                    [Date].[Calendar].CurrentMember, 
                    [Date].[Calendar].[Date] 
                ) , 
                CalculationPassValue([Measures].[Last NonEmpty Total Product Cost - Workaround], 0) <> null 
            ),
            1
        ), 
        CalculationPassValue([Measures].[Last NonEmpty Total Product Cost - Workaround], 0) 
    ) ' 

After this result of [Measures].[Last NonEmpty Total Product Cost - Workaround] will be similar LastNonEmpty aggregate function.

Customizing EntityEditorWithPicker

Everyone, who use Sharepoint had seen PeopleEditor control. It very useful in many cases - for example in permission forms:

 

But this control can be used not only for looking for users or groups. You can use its base class EntityEditorWithPicker and extend it to search any data. I will show you very simple example how to do this.

We should extend three classes:

using System; 
using System.Collections.Generic; 
using System.Text; 
using Microsoft.SharePoint.WebControls; 

namespace EntityEditorTest 
{ 
  public class CustomEntityEditor : EntityEditorWithPicker 
  { 
    protected override void OnInit(EventArgs e) 
    { 
      base.OnInit(e); 
      PickerDialogType = typeof (CustomPickerDialog); 
    } 

    public override PickerEntity ValidateEntity(PickerEntity needsValidation) 
    { 
      if (needsValidation.Key.Equals("igor", StringComparison.InvariantCultureIgnoreCase)) 
      { 
        needsValidation.DisplayText = "Igor Kozlov"; 
        needsValidation.IsResolved = true; 
      } 
      return needsValidation; 
    } 
  } 
}

 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint.WebControls;

namespace EntityEditorTest
{
  public class CustomPickerDialog : PickerDialog
  {
    public CustomPickerDialog()
      : base(new CustomQueryControl(), new TableResultControl(), new CustomEntityEditor())
    {
      ArrayList columnDisplayNames = ((TableResultControl)base.ResultControl).ColumnDisplayNames;
      columnDisplayNames.Clear();
      columnDisplayNames.Add("Name");
      columnDisplayNames.Add("Description");
      ArrayList columnNames = ((TableResultControl)base.ResultControl).ColumnNames;
      columnNames.Clear();
      columnNames.Add("Name");
      columnNames.Add("Description");
      ArrayList columnWidths = ((TableResultControl)base.ResultControl).ColumnWidths;
      columnWidths.Clear();
      columnWidths.Add("30%");
      columnWidths.Add("70%");
    }

  }
}

 

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Microsoft.SharePoint.WebControls;

namespace EntityEditorTest
{
  public class CustomQueryControl : SimpleQueryControl
  {
    public CustomQueryControl()
    {
      Load += CustomQueryControl_Load;
    }

    void CustomQueryControl_Load(object sender, EventArgs e)
    {
      if (!Page.IsPostBack)
      {
        EnsureChildControls();
        mColumnList.Items.Add("Name");
        mColumnList.Items.Add("Description");
      }
    }

    protected override int IssueQuery(string search, string groupName, int pageIndex, int pageSize)
    {
      DataTable dummyTable = GetDummyTable();

      PickerDialog.Results = dummyTable;
      PickerDialog.ResultControl.PageSize = dummyTable.Rows.Count;

      return dummyTable.Rows.Count;
    }

    private DataTable GetDummyTable()
    {
      DataTable dummyTable = new DataTable();
      dummyTable.Columns.Add("Name");
      dummyTable.Columns.Add("Description");

      DataRow row1 = dummyTable.NewRow();
      row1["Name"] = "Name 1";
      row1["Description"] = "Description 1";
      dummyTable.Rows.Add(row1);

      DataRow row2 = dummyTable.NewRow();
      row2["Name"] = "Name 2";
      row2["Description"] = "Description 2";
      dummyTable.Rows.Add(row2);
      return dummyTable;
    }

    public override PickerEntity GetEntity(DataRow dr)
    {
      PickerEntity entity = new PickerEntity();
      entity.DisplayText = "" +dr["Name"];
      entity.Key = "" + dr["Name"];
      entity.Description = "" + dr["Description"];
      entity.IsResolved = true;
      return entity;
    }
  }
}

And there is result: