# tim laqua dot comThoughts and Code from Tim Laqua

23Feb/090

## Wierd Slice – Revenue for the Most Recent Hour in the Cube Across the Last 14 Days Sliced by Week

Well - there's two parts to this - one is to figure out the most recent hour in the cube that has data. Let's face it, it finishes as soon as it can - but that time may vary now and then. Then, for this particular report, we wanted to see the last seven days on one series and the previous seven days on another series. To do this, we just slice by two calculated measures. But this creates an odd dateset where the This Week column will have no data for the "Date Time" columns that actually belong to the Previous Week.

This isn't really a problem as in SSRS, you can simply define the Category Group for the Series in your chart (ya, I didn't mention that yet - this is all for a chart) as DatePart("w", Fields!DateTime) - which will group everything by the number of the given weekday and then you can just have your "This Week" and "Prevoius Week" series.

```WITH MEMBER [Measures].[This Week] AS Aggregate ([Time].[Calendar Date].LastChild:[Time].[Calendar Date].LastChild.Lag(6) ,[Measures].[Revenue])   MEMBER [Measures].[Previous Week] AS Aggregate([Time].[Calendar Date].LastChild.Lag(7) : [Time].[Calendar Date].LastChild.Lag(13) ,[Measures].[Revenue]) SELECT NON EMPTY {[Measures].[Previous Week], [Measures].[This Week]} ON 0 ,NON EMPTY { [Time].[Date Time].[Date Time].MEMBERS * [Time].[24 Hour].[24 Hour].ALLMEMBERS } ON 1 FROM ( SELECT Tail( Filter( [Time].[24 Hour].[24 Hour].MEMBERS ,Sum([Time].[Calendar Date].LastChild,[Measures].[Revenue]) > 0 ), 1 ) ON 0 FROM (SELECT { [Time].[Calendar Date].LastChild : [Time].[Calendar Date].LastChild.Lag(13) } ON 0 FROM [Cube] ) )```

And to top it all off - after seeing the chart for a while, nobody liked it (after all, it's a relatively abstract view of revenue) and we dumped it. ðŸ˜‰

5Nov/080

## How to add a calculated measure (calculated field) to an Excel 2007 PivotTable with a SSAS data source

As it turns out, you can do it programatically as explained by Allan Folting here:
Microsoft Excel: Common Questions Around Excel 2007 OLAP PivotTables:
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

And the parts that I have to keep looking up (I use my blog as a notebook for things I don't want to forget ;-):

```Sub AddCalculatedMeasure() Dim pvt As PivotTable Dim strName As String Dim strFormula As String   Set pvt = Sheet1.PivotTables("PivotTable1") strName = "[Measures].[Internet Sales Amount 25 %]" strFormula = "[Measures].[Internet Sales Amount]*1.25" pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember End Sub   Sub AddCalculatedMember() Dim pvt As PivotTable Dim strName As String Dim strFormula As String   Set pvt = Sheet1.PivotTables("PivotTable1") strName = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]" strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25" pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember pvt.ViewCalculatedMembers = True End Sub   Sub AddNamedSet() Dim pvt As PivotTable Dim strName As String Dim strFormula As String Dim cbf As CubeField   Set pvt = Sheet1.PivotTables("PivotTable1") strName = "[My Mountain Bikes]" strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children" pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes") End Sub```

He also mentions that you can expose these members to Excel Services 2007 by creating the new objects and then removing the VBA code - very useful article.