locked
Combining Two Different datasource in single matrix RRS feed

  • Question

  • hi to all,

    Happy evening .

    i got struck up in critical situation, can any one help in this.

    i  have to to combine two dataset in single matrix

    my sample scenario in below attached for reference.

    in this first matrix values from one server and second one is another one server.

    how to connect these to for getting desired result of thrid matirx.

    in this 1,2,3,4,5,6 is in column grouping

    how to solve this in using LOOKUP or CUSTOM CODE

    Thanks in advance.


    GVRSPK VENI


    Tuesday, June 16, 2015 1:42 PM

Answers

  • Hey everybody.
    I would suggest to merge the data into one result dataset using technique described below. It seems that it should solve the original task well (except the unexplained restriction "how to solve this in using LOOKUP or CUSTOM CODE)". 
    Technique uses auxiliary parameter to store secondary dataset's data and adds this data to main dataset via dynamic sql:
    scheme
    1. Add SecondaryDataSQL calculated field to secondary dataset to construct secondary dataset's data parts of the dynamic sql in merged dataset. In this parts we set unknown data to null values.
    2. Add multi-value SecondaryDataSQL parameter with default values from 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 i merged data from 2 data sources like in the topic.
    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. Just the result dataset has to have the ability to dynamically add records to the result set via request text.

    screen shots


    Sergey Vdovin


    Sunday, June 21, 2015 12:11 AM
  • Hello.
    Simplest way is to temporary replace the query text to
    plain original sql:
    SELECT *
    FROM (
    	VALUES (1, 'main data 1', 10, 11, 12), (2, 'main data 2', 12, 13, 14)
    	) AS a(mainkey, maindata, secondarydata1, secondarydata2, secondarydata3)


    refresh fields and than replace it back.

    If to consider continues development I ended up with the following variant as for now:
    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,",")

    hereby 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


    P.S. found that the method is not working when secondary dataset is empty. So you have to use 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 secondary dataset has to have the ability to add records via text as well
    please find 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>
    




    Sergey Vdovin

    • Marked as answer by gvrspk veni Tuesday, June 23, 2015 4:03 AM
    Monday, June 22, 2015 8:43 PM

All replies

  • Hi gvrspk,

    You could use the SSIS to migrate the data to the destination table. The SSIS you can unify the data by differents datasources. It is so easier working in reporting services.

    Best Regards


    Ricardo Lacerda

    Tuesday, June 16, 2015 2:04 PM
  • You could also use a linked server to link the two databases, and then write an SQL query to fetch both datasets at once.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Tuesday, June 16, 2015 2:36 PM
  • Hi GVRSPK, 

    According to your description, you want to put two matrixes into one matrix, right?

    In Reporting Services, Lookup() function works like add a column from other dataset into current data region. In your scenario, what you need is to add detail rows into current dataset. So you can’t avoid joining two result set. I suggest you union two result set together in your query. Please refer to steps below: 

    1. Modify your dataset query like below: 
      select * FROM table_14 where name ='AA' OR NAME ='BB' 
      UNION 
      select * FROM table_15 where name ='CC' OR NAME ='DD'
    2. Create a matrix and drag fields into the matrix. 

    Reference: 
    UNION (Transact-SQL)

    If you have any question, please feel free to ask. 

    Regards, 
    Shrek Li

    Wednesday, June 17, 2015 9:34 AM
  • hi shrek,

    thanks for your reply. since it is two different datasource (different server) cant able to union all in single dataset.


    GVRSPK VENI

    Thursday, June 18, 2015 3:32 AM
  • You can union them in a single query (dataset). You just need to created a linked server..

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, June 18, 2015 2:17 PM
  • Hey everybody.
    I would suggest to merge the data into one result dataset using technique described below. It seems that it should solve the original task well (except the unexplained restriction "how to solve this in using LOOKUP or CUSTOM CODE)". 
    Technique uses auxiliary parameter to store secondary dataset's data and adds this data to main dataset via dynamic sql:
    scheme
    1. Add SecondaryDataSQL calculated field to secondary dataset to construct secondary dataset's data parts of the dynamic sql in merged dataset. In this parts we set unknown data to null values.
    2. Add multi-value SecondaryDataSQL parameter with default values from 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 i merged data from 2 data sources like in the topic.
    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. Just the result dataset has to have the ability to dynamically add records to the result set via request text.

    screen shots


    Sergey Vdovin


    Sunday, June 21, 2015 12:11 AM
  • hi Evolex,

    i tried below query in query command text

    but the fields not showing in dataset

    ="select * "+
    "from ("+
    "values(1,'main data 1',10,11,12), (2,'main data 2',12,13,14)"+
    ")as a(mainkey,maindata,secondarydata1,secondarydata2,secondarydata3) "+
    "          "+
    "union all      "+
    "           "+
    "select *  "+
    "from(          "+
    "values "+join(Parameters!ReportParameter1.Value,",")+"                 "+
    ")as a(mainkey,maindata,secondarydata1,secondarydata2,secondarydata3)          "

    can you suggest , how to set fields of dataset.

    i added calculate fields but getting #error in reports field
    Monday, June 22, 2015 4:40 AM
  • Hello.
    Simplest way is to temporary replace the query text to
    plain original sql:
    SELECT *
    FROM (
    	VALUES (1, 'main data 1', 10, 11, 12), (2, 'main data 2', 12, 13, 14)
    	) AS a(mainkey, maindata, secondarydata1, secondarydata2, secondarydata3)


    refresh fields and than replace it back.

    If to consider continues development I ended up with the following variant as for now:
    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,",")

    hereby 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


    P.S. found that the method is not working when secondary dataset is empty. So you have to use 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 secondary dataset has to have the ability to add records via text as well
    please find 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>
    




    Sergey Vdovin

    • Marked as answer by gvrspk veni Tuesday, June 23, 2015 4:03 AM
    Monday, June 22, 2015 8:43 PM
  • Hi Evolex, 

    Your solution works fine, and it's been weeks since I've been using it in a migration from BIRT to SSRS.

    Recently I've come across a big problem. The resulting query, once the parameters are converted into statements, has more than 65535 bits. How would you deal with this? I am worried that I just found the achilles heel of your solution :(  

    Tuesday, April 11, 2017 9:14 AM
  • hey there

    just have checked a dataset with 70 000 rows generated from text - seems to be working

    https://github.com/sergey-vdovin/ssrs-join-datasets/tree/master/70000rowsfromtextotdataset

    looks like you are experiencing some other problem.


    Sergey Vdovin

    Tuesday, April 11, 2017 10:20 AM