Hi all.
I've created a horizontal report using a matrix according to the quite useful article Horizontal Tables, but there's one thing I'm trying to do that I'm not having any luck figuring out how to do, and that is creating a footer column for a column group that contains a sum of the values in some of its columns.
In other words, here's how I would like to have it look...
| | 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 |
The fields in the dataset are Month, Tier1, Tier2, Year and Quarter. I've created a group on Year & Quarter, tried right clicking and selecting Subtotal, but that seems to add a subtotal column after every month, not at the end of the quarter group as I want.
Any ideas on what I need to do? Is this even possible?
TIA
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>
