Ian Roof - MSFT
If you create a matrix with a row grouping of the Tier field and a two column groupings you can get pretty close to what you are looking for.
For the column groupings, create a grouping for the Year and Quarter (2 expressions for the this group) and create another, inner grouping for the Month. Then, place the subtotal month grouping.
You should get something like:
| Quarter 1 | Quarter 2 |
Jan |
Feb |
Mar |
Quarter 1 Total |
Apr |
May |
Jun |
Quarter 2 Total |
Tier 1 |
$100 |
$100 |
$100 |
$300 |
$0 |
$0 |
$100 |
$100 |
Tier 2 |
$50 |
$50 |
$0 |
$100 |
$100 |
$100 |
$100 |
$300 |
Here is example RDL using the Northwind database.
< xml version="1.0" encoding="utf-8" >
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Initial Catalog=Northwind; data source=localhost</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>1in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ShipCountry">
<rd:DefaultName>ShipCountry</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ShipCountry.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ShipCountry">
<GroupExpressions>
<GroupExpression>=Fields!ShipCountry.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="OrderDate">
<rd:DefaultName>OrderDate</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderDate.Value.Year & " - " & Fields!OrderDate.Value.Month</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_OrderDate">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Year</GroupExpression>
<GroupExpression>=Cint((Fields!OrderDate.Value.Month+1)/3)</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="OrderDate_1">
<rd:DefaultName>OrderDate_1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderDate.Value.Month</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<Color>Red</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
<Style>
<Color>Red</Color>
</Style>
</Subtotal>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Month</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Top>1.125in</Top>
<Width>3in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.75in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Quantity">
<rd:DefaultName>Quantity</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2.25in</Height>
</Body>
<rd:ReportID>69d1d3a8-e0a0-4c9b-b63e-d7204e0f8f21</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select ShipCountry, OrderDate, Quantity from orders inner join [order details] on orders.orderid = [order details].orderid</CommandText>
<DataSourceName>SharedNorthwind</DataSourceName>
</Query>
<Fields>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="Quantity">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>Quantity</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>