Let say that we need the following transformation in SSRS report:
One of the tools to do this is List.
I will explain only the tricky part and will give the .rdl in the end.
Drag-and-drop List in the report body:
Right click the top left corner of the list –> Tablix Properties…:
Attach to the dataset that feeds the report:
Open Group Properties in Row Groups:
Add the group:
Sort the report:
Add two textboxes and the table to the list:
The .rdl (built in Visual Studio 2019):
<?xml version="1.0" encoding="utf-8"?>
<Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
<df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="AdventureWorks2019">
<DataSourceReference>AdventureWorks2019</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>db81d28e-3b60-4489-bba2-e263c21a1c78</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<DataSourceName>AdventureWorks2019</DataSourceName>
<QueryParameters>
<QueryParameter Name="@OrderDate">
<Value>=Parameters!OrderDate.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>SELECT
SOH.SalesOrderNumber
, PP.[Name]
, SOD.UnitPrice
, SOD.OrderQty
, SOD.LineTotal
FROM
Sales.SalesOrderHeader AS SOH
JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN Production.Product AS PP ON SOD.ProductID = PP.ProductID
WHERE SOH.OrderDate = @OrderDate;</CommandText>
</Query>
<Fields>
<Field Name="SalesOrderNumber">
<DataField>SalesOrderNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="OrderQty">
<DataField>OrderQty</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="LineTotal">
<DataField>LineTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportSections>
<ReportSection>
<Body>
<ReportItems>
<Tablix Name="Tablix4">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>5in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.9375in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Rectangle Name="Rectangle2">
<ReportItems>
<Textbox Name="Textbox21">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Sales Order Number:</Value>
<Style>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox21</rd:DefaultName>
<Height>0.25in</Height>
<Width>1.47222in</Width>
<Style>
<Border>
<Style>None</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
<Textbox Name="SalesOrderNumber">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=First(Fields!SalesOrderNumber.Value)</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>SalesOrderNumber</rd:DefaultName>
<Left>1.47222in</Left>
<Height>0.25in</Height>
<Width>1in</Width>
<ZIndex>1</ZIndex>
<Style>
<Border>
<Style>None</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
<Tablix Name="Tablix5">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>2in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.15in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox23">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Name</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox23</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkGreen</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox25">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Unit Price</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox25</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkGreen</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox27">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Order Qty</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox27</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkGreen</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox31">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Line Total</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox31</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>DarkGreen</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.15in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Name">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Name.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Name</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>=iif(rownumber(nothing) mod 2, "White", "WhiteSmoke")</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="UnitPrice">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!UnitPrice.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<Format>C2</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>=iif(rownumber(nothing) mod 2, "White", "WhiteSmoke")</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="OrderQty">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!OrderQty.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<Format>N0</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>OrderQty</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>=iif(rownumber(nothing) mod 2, "White", "WhiteSmoke")</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="LineTotal">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!LineTotal.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<Format>C2</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>LineTotal</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>=iif(rownumber(nothing) mod 2, "White", "WhiteSmoke")</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.15in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox37">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Total</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox37</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>OldLace</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="UnitPrice1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!UnitPrice.Value)</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Format>C2</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>UnitPrice1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>OldLace</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="OrderQty1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!OrderQty.Value)</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Format>N0</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>OrderQty1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>OldLace</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="LineTotal1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!LineTotal.Value)</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontSize>8pt</FontSize>
<FontWeight>Bold</FontWeight>
<Format>C2</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>LineTotal1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>OldLace</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<Group Name="Details1" />
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixMember>
<TablixMember>
<KeepWithGroup>Before</KeepWithGroup>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet1</DataSetName>
<Top>0.31944in</Top>
<Height>0.45in</Height>
<Width>5in</Width>
<ZIndex>2</ZIndex>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<KeepTogether>true</KeepTogether>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Rectangle>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="Details">
<GroupExpressions>
<GroupExpression>=Fields!SalesOrderNumber.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!SalesOrderNumber.Value</Value>
</SortExpression>
<SortExpression>
<Value>=Fields!Name.Value</Value>
</SortExpression>
</SortExpressions>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet1</DataSetName>
<Height>0.9375in</Height>
<Width>5in</Width>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<Height>0.9375in</Height>
<Style />
</Body>
<Width>5in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
</ReportSection>
</ReportSections>
<ReportParameters>
<ReportParameter Name="OrderDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>05/31/2011 00:00:00</Value>
</Values>
</DefaultValue>
<Prompt>Order Date</Prompt>
</ReportParameter>
</ReportParameters>
<ReportParametersLayout>
<GridLayoutDefinition>
<NumberOfColumns>4</NumberOfColumns>
<NumberOfRows>2</NumberOfRows>
<CellDefinitions>
<CellDefinition>
<ColumnIndex>0</ColumnIndex>
<RowIndex>0</RowIndex>
<ParameterName>OrderDate</ParameterName>
</CellDefinition>
</CellDefinitions>
</GridLayoutDefinition>
</ReportParametersLayout>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>394e5ed1-c272-42ae-b5b6-f542a6eae2dc</rd:ReportID>
</Report>
Keep it simple :-)
