Quite often we consider the task to merge data from different data sources inside an SSRS report. Usually a linked server is advised in order to complete the task.

The approach described below allows merging data from different data sources into one dataset without lookup functions, code, SSIS or a linked server.

Please find the original discussion and details here.

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:
scheme
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).

screen shots

To continue development we ended up with the following variant for the main dataset:
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




where 
@SecondaryDataSQL
is composed by the following expression
=Join(Parameters!SecondaryDataSQL.Value,",")

Whereby you receive direct fields refresh.

Links:
SSRS: Dataset Field List Is Empty Even After Refresh Fields

How to list fields from dynamic SQL query in SSRS dataset

Create a nullable column using SQL Server SELECT INTO


We found that the method is not working when the secondary dataset is empty. So you have to use a method like this for it (add a row to be sure the dataset is not empty):
Reports in SSRS with Multi Value Parameters and NULL Values

And a secondary dataset has to have the ability to add records via text as well. Please find a full report definition below (you can add the content to an empty rdl file via notepad to get the report):

<?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>

See Also