SSRS: Merge data from different data sources into one data set inside SSRS report using XML Connection
Introduction and Problem
Quite often we consider the task to merge data from different data sources inside SSRS report.
Usually we are advised to use workarounds like SQL Server linked servers or SSIS which uses non ssrs datasources to extract data to be merged or SSRS lookup functions to achieve desired result outside of the report data retrieval stage.
Recent discussion on technet forum revealed that the technique of merging data from different SSRS data sources into one SSRS data set is still not widespread.
In previous article we considered a technique which uses external connection to merge the data. In this article we use XML Connection with Embedded XML document to merge the data what leaves the merge process on the current server.
Alternative solutions (workarounds)
External Data Sources
- SQL Server Linked Server
- T-SQL OPENQURY statement
- SQL Server Integration Services (SSIS)
After data retrieval stage approaches
- SSRS lookup functions
Solution
In sample report we have 2 external data sources - RemoteDataSource and RemoteDataSource2 with corresponding datasets - MainDataSet and SecondaryDataSet. To merge the data we add XML Connection - ResultDataSource to the report and fill the corresponding data set's (ResultDataSet) Embedded XML document with data obtained from the source data sets. To pass the data between data sets we use auxiliary parameters MainDataXML and SecondaryDataXML.
- Add SecondaryDataXML calculated field to SecondaryDataset to construct secondary dataset's XML data parts in the ResultDataSet.
- Add multi-value SecondaryDataXML parameter with default values from SecondaryDataXML field of the SecondaryDataset.
Repeat steps 1-2 for main data. - Add metadata definition query to the ResultDataset query text:
<Query>
<ElementPath>rows{}/row{@MainKey(Integer),@MainData(string),@SecondaryData1(Decimal),@SecondaryData2(Decimal),@SecondaryData3(Decimal)}</ElementPath>
<XmlData>
<rows>
</rows>
</XmlData>
</Query>
Here we define field names and data types. For more information please see article Element Path Syntax for XML Report Data (SSRS).
Replace static empty Embedded XML document with dynamic expression where we merge the data:
="<Query>"+
"<ElementPath>"+
"rows{}/row{"+
"@MainKey(Integer),@MainData(string),@SecondaryData1(Decimal),"+
"@SecondaryData2(Decimal),@SecondaryData3(Decimal)}"+
"</ElementPath>"+
"<XmlData>"+
"<rows>"+
Join(Parameters!MainDataXML.Value,"")+
Join(Parameters!SecondaryDataXML.Value,"")+
"</rows>"+
"</XmlData>"+
"</Query>"
Sample report execution result is depicted below: as a result we have native ability to make aggregations over merged data:
Usage and Limits
- Tests were made with 2 external data sets (as for now there is no considerations about a restriction for the number of source datasets) with 32000 records in each (some considerations about the restriction for number of rows can be found in the following discussion) - full report execution takes several seconds on first surface pro tablet (SSRS 2014 native mode).
- As with previous approach each of source data sets should not be empty - you can use a technique like this to add a record to dataset.
- If to compare to previous approach there is no automatic metadata construction (fields refresh) for result dataset.
Sample Report Definition XML
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<Body>
<ReportItems>
<Tablix Name="Tablix3">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1.41667in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.46875in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox21">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Main Key</Value>
<Style />
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox21</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox16">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Main Data</Value>
<Style />
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox16</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox18">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Secondary Data1</Value>
<Style>
<Format>### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox18</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox22">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Secondary Data2</Value>
<Style>
<Format>### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox22</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox3">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Secondary Data3</Value>
<Style>
<Format>### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox3</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.61458in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox20">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Rows Count: </Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=CountRows()</Value>
<Style>
<Format>### ### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Right</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox19</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox19">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Main Data Rows:</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Count(Fields!MainKey.Value)</Value>
<Style>
<Format>### ### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Right</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox19</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox14">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!SecondaryData1.Value)</Value>
<Style>
<Format>### ### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox14</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox15">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!SecondaryData2.Value)</Value>
<Style>
<Format>### ### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox15</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox17">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!SecondaryData3.Value)</Value>
<Style>
<Format>### ### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox17</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>LightSteelBlue</BackgroundColor>
<VerticalAlign>Middle</VerticalAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="MainKey">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!MainKey.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>MainKey</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="MainData">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!MainData.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>MainData</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="SecondaryData1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!SecondaryData1.Value</Value>
<Style>
<Format>### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>SecondaryData1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="SecondaryData2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!SecondaryData2.Value</Value>
<Style>
<Format>### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>SecondaryData2</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="SecondaryData3">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!SecondaryData3.Value</Value>
<Style>
<Format>### ### ###</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>SecondaryData3</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
<TablixMember />
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<Group Name="Details" />
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>ResultDataSet</DataSetName>
<Height>1.33333in</Height>
<Width>5.41667in</Width>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<Height>1.46875in</Height>
<Style />
</Body>
<Width>5.41667in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="RemoteDataSource">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=.;Initial Catalog=dbe_loc</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
<rd:SecurityType>Integrated</rd:SecurityType>
<rd:DataSourceID>3bb7e75d-13a7-4c0a-a8b5-c4e08adc2e5e</rd:DataSourceID>
</DataSource>
<DataSource Name="ResultDataSource">
<ConnectionProperties>
<DataProvider>XML</DataProvider>
<ConnectString />
</ConnectionProperties>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>30574aec-4b78-49fa-93e0-6b6ad9858f39</rd:DataSourceID>
</DataSource>
<DataSource Name="RemoteDataSource2">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=.;Initial Catalog=dbe_loc</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
<rd:SecurityType>Integrated</rd:SecurityType>
<rd:DataSourceID>b3fed140-036f-46c3-9675-2d3d1c02b8a4</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="SecondaryDataSet">
<Query>
<DataSourceName>RemoteDataSource2</DataSourceName>
<CommandText>declare @N as int = 32767;
WITH NRecs
AS (
SELECT 1 AS SecondaryData1
,2*@N AS SecondaryData2
,4*@N AS SecondaryData3
UNION ALL
SELECT SecondaryData1 + 1
,SecondaryData2 + 1
,SecondaryData3 + 1
FROM NRecs
WHERE SecondaryData1 < @N
)
SELECT *
FROM NRecs
OPTION (Maxrecursion 32767)</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="SecondaryDataXML">
<Value>="<row MainKey="""" MainData="""" SecondaryData1="""+
CStr(Fields!SecondaryData1.Value)+
""" SecondaryData2="""+
CStr(Fields!SecondaryData2.Value)+
""" SecondaryData3="""+
CStr(Fields!SecondaryData3.Value)+
""" />"</Value>
</Field>
<Field Name="SecondaryData1">
<DataField>SecondaryData1</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="SecondaryData2">
<DataField>SecondaryData2</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="SecondaryData3">
<DataField>SecondaryData3</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="MainDataSet">
<Query>
<DataSourceName>RemoteDataSource</DataSourceName>
<CommandText>DECLARE @N AS INT = 11;
WITH NRecs
AS (
SELECT 1 AS MainKey
,'main data ' + CAST(1 AS VARCHAR(MAX)) AS MainData
,2 * @N AS SecondaryData1
,4 * @N AS SecondaryData2
,5 * @N AS SecondaryData3
UNION ALL
SELECT MainKey + 1
,'main data ' + CAST(MainKey + 1 AS VARCHAR(MAX)) AS MainData
,SecondaryData1 + 1
,SecondaryData2 + 1
,SecondaryData3 + 1
FROM NRecs
WHERE MainKey < @N
)
SELECT *
FROM NRecs
OPTION (MAXRECURSION 32767)</CommandText>
</Query>
<Fields>
<Field Name="MainKey">
<DataField>MainKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MainData">
<DataField>MainData</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SecondaryData1">
<DataField>SecondaryData1</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="SecondaryData2">
<DataField>SecondaryData2</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="SecondaryData3">
<DataField>SecondaryData3</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MainDataXML">
<Value>="<row MainKey="""+
CStr(Fields!MainKey.Value)+
""" MainData="""+
CStr(Fields!MainData.Value)+
""" SecondaryData1="""+
CStr(Fields!SecondaryData1.Value)+
""" SecondaryData2="""+
CStr(Fields!SecondaryData2.Value)+
""" SecondaryData3="""+
CStr(Fields!SecondaryData3.Value)+
""" />"</Value>
</Field>
</Fields>
</DataSet>
<DataSet Name="ResultDataSet">
<Query>
<DataSourceName>ResultDataSource</DataSourceName>
<CommandText>="<Query>"+
"<ElementPath>"+
"rows{}/row{"+
"@MainKey(Integer),@MainData(string),@SecondaryData1(Decimal),"+
"@SecondaryData2(Decimal),@SecondaryData3(Decimal)}"+
"</ElementPath>"+
"<XmlData>"+
"<rows>"+
Join(Parameters!MainDataXML.Value,"")+
Join(Parameters!SecondaryDataXML.Value,"")+
"</rows>"+
"</XmlData>"+
"</Query>"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
<Fields>
<Field Name="MainKey">
<DataField>MainKey</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MainData">
<DataField>MainData</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="SecondaryData1">
<DataField>SecondaryData1</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="SecondaryData2">
<DataField>SecondaryData2</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="SecondaryData3">
<DataField>SecondaryData3</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameters>
<ReportParameter Name="MainDataXML">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>MainDataSet</DataSetName>
<ValueField>MainDataXML</ValueField>
</DataSetReference>
</DefaultValue>
<Hidden>true</Hidden>
<MultiValue>true</MultiValue>
</ReportParameter>
<ReportParameter Name="SecondaryDataXML">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>SecondaryDataSet</DataSetName>
<ValueField>SecondaryDataXML</ValueField>
</DataSetReference>
</DefaultValue>
<Hidden>true</Hidden>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>5b905a47-e0c6-4dc3-993e-655cc3ac68dd</rd:ReportID>
</Report>
Steps to Reproduce
1. Create empty text file via notepad and fill the content with Sample Report Defenition XML, change file extension to RDL
2. Open report obtained in previous step in Report Builder or SSDT-BI and point RemoteDataSource and RemoteDataSource2 to your instance of SQL Server.
Now you can launch the report in development environment or deploy it to your SSRS server.
Resources
Troubleshooting Reports: Report Performance
Managing Report ProcessingReporting Services Concepts (SSRS)
Length and Count Limitations of a Multi-Valued Text Parameters in SSRS 2008
XML Query Syntax for XML Report Data (SSRS)
Element Path Syntax for XML Report Data (SSRS)
Reports in SSRS with Multi Value Parameters and NULL Values
SeeAlso
SSRS: Merge data from different datasources into one dataset inside SSRS report
SSRS: Join data from different SSRS data sources into data set