# 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"
End Sub

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.ViewCalculatedMembers = True
End Sub

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"