Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

EndScope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

End Scope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

End Scope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.