In this example, we merge data from two data sources into one dataset and then use this dataset to build a summary report. The technique uses an auxiliary parameter to store secondary dataset's data and adds this data to the main dataset via dynamic SQL: 1. Add a SecondaryDataSQL calculated field to a secondary dataset to construct the secondary dataset's data parts of the dynamic SQL in merged dataset. In this part, we set unknown data to null values. 2. Add a multi-value SecondaryDataSQL parameter with default values from the SecondaryDataSQL field of the secondary dataset. 3. Transform original code of the main dataset to dynamic and union it with the data stored in SecondaryDataSQL parameter. In the example, we merged data from two data sources. At this point, it looks like the technique can be used to merge datasets data from any number of sources with any number of fields in each dataset. Both datasets have to have the ability to dynamically add records to the result set via request text (or a secondary dataset which should not be empty).
WITH source AS ( SELECT * FROM ( VALUES (1, 'main data 1', 10, 11, 12), (2, ' main data 2', 12, 13, 33) ) AS A(MainKey, MainData, SecondaryData1, SecondaryData2, SecondaryData3) ), create_source_table_nullable AS ( SELECT TOP 0 B.* FROM source AS A LEFT JOIN source AS B ON 1 = 0 UNION ALL SELECT * FROM source ) SELECT * INTO #results_temporarty_table FROM create_source_table_nullable IF (@SecondaryDataSQL IS NOT NULL) BEGIN DECLARE @sql AS NVARCHAR(max) = ' insert into #results_temporarty_table VALUES ' + @SecondaryDataSQL EXEC (@sql) END SELECT * FROM #results_temporarty_table
=Join(Parameters!SecondaryDataSQL.Value,",")
<?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>1in</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 /> </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 /> </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 /> </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.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 /> </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 /> </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 /> </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> <Group Name="Details" /> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>MainDataSet</DataSetName> <Height>0.71875in</Height> <Width>5in</Width> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> </ReportItems> <Height>0.71875in</Height> <Style /> </Body> <Width>5in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="DataSource1"> <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> </DataSources> <DataSets> <DataSet Name="SecondaryDataSet"> <Query> <DataSourceName>DataSource1</DataSourceName> <CommandText>select * FROM ( VALUES (31, 32, 33), (43,44 , 45), (51, 52,53), (61, 62,63) ) AS A(SecondaryData1, SecondaryData2, SecondaryData3)</CommandText> <rd:UseGenericDesigner>true</rd:UseGenericDesigner> </Query> <Fields> <Field Name="SecondaryDataSQL"> <Value>="(null,null,"+CStr(Fields!SecondaryData1.Value)+","+CStr(Fields!SecondaryData2.Value)+","+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>DataSource1</DataSourceName> <QueryParameters> <QueryParameter Name="@SecondaryDataSQL"> <Value>=Join(Parameters!SecondaryDataSQL.Value,",")</Value> </QueryParameter> </QueryParameters> <CommandText>WITH source AS ( SELECT * FROM ( VALUES (1, 'main data 1', 10, 11, 12), (2, ' main data 2', 12, 13, 33) ) AS A(MainKey, MainData, SecondaryData1, SecondaryData2, SecondaryData3) ), create_source_table_nullable AS ( SELECT TOP 0 B.* FROM source AS A LEFT JOIN source AS B ON 1 = 0 UNION ALL SELECT * FROM source ) SELECT * INTO #results_temporarty_table FROM create_source_table_nullable IF (@SecondaryDataSQL IS NOT NULL) BEGIN DECLARE @sql AS NVARCHAR(max) = ' insert into #results_temporarty_table VALUES ' + @SecondaryDataSQL EXEC (@sql) END SELECT * FROM #results_temporarty_table</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> </Fields> </DataSet> </DataSets> <ReportParameters> <ReportParameter Name="SecondaryDataSQL"> <DataType>String</DataType> <DefaultValue> <DataSetReference> <DataSetName>SecondaryDataSet</DataSetName> <ValueField>SecondaryDataSQL</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>