calculated fields in ssrs

Hi friends,

hope everyone had a good weekend.

I have one requirement to call the value from different datasets within the same report.But the value should be returned based on multiple column join values.

Ex: Main dataset query

  SELECT *  FROM   table a 
 WHERE  a.ChargeDate>=@TransactionDateFrom AND a.ChargeDate<=@TransactionDateTo AND a.DebtorID= @DebtorID

dataset 2:

SELECT * FROM  table b 
 WHERE  b.ChargeDate>=@GLStartYearDate AND b.ChargeDate<=@TransactionDateTo AND b.DebtorID= @DebtorID

here i have tried to create a calculated field by joining multiple columns to use the lookup to display the value from dataset 2

My approach: 

calculated field in main dataset as Join1

=Parameters!TransactionDateFrom.Value + "||" + Parameters!TransactionDateTo.Value + "||" + Parameters!DebtorID.Value

calculated field in main dataset 2 as JoinArrears

=Parameters!GLStartYearDate.Value + "||" + Parameters!TransactionDateTo.Value + "||" + Parameters!DebtorID.Value

to display:

=LookupSet(Fields!Join1.Value, Fields!JoinArrears.Value, AllocatedAmount.Value, "dataset2")

but it is giving error..Please suggest me any solution for this.

Thanks,

sam.

February 22nd, 2015 7:30pm

Hi Sam,

I have tried to implement as you have mentioned. I got the error while using LookupSet function.

please try to use LOOKUP function instead of Lookupset.

Hope it will resolve.

Thanks

Prasad

My Website : http://msbitips.com
My Blog :http://msbitips.wordpress.com
Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2015 10:55pm

Hi Prasad,

Thanks for the response.Initially i have tried with LOOKUP only since i have combined all the matched columns into one but got the same error later i have tried with LOOKUPSET..

Thanks,

Sam.

February 22nd, 2015 11:09pm

Hi Sam,

I have copied the RDL code , please copy and save as .RDL file. Using Lookup I did this.

In my implementaion if is not matching with your requirement, kindly let me know.

<?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="table1">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.22in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="textbox2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>ID</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <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>fruit</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox5">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Fruit Name</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox5</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.21in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="id">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!id.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>id</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="fruit11">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!fruit1.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>fruit11</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="FruitName11">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!FruitName1.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>FruitName11</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 />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
              <RepeatOnNewPage>true</RepeatOnNewPage>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
            <TablixMember>
              <Group Name="table1_Details_Group">
                <DataElementName>Detail</DataElementName>
              </Group>
              <TablixMembers>
                <TablixMember />
              </TablixMembers>
              <DataElementName>Detail_Collection</DataElementName>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet1</DataSetName>
        <Top>0.36in</Top>
        <Height>0.43in</Height>
        <Width>3in</Width>
        <Style />
      </Tablix>
      <Textbox Name="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>LookupSet</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>20pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.36in</Height>
        <Width>5in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Tablix Name="table2">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.22in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="textbox3">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>ID</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>textbox2</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox4">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>fruit</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox6">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Fruit Name</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                                <FontSize>11pt</FontSize>
                                <FontWeight>Bold</FontWeight>
                                <Color>White</Color>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox5</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <BackgroundColor>SteelBlue</BackgroundColor>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.21in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="id2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!id.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>id</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="fruit2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!fruit.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>fruit</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="FruitName2">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!Join.Value</Value>
                              <Style>
                                <FontFamily>Tahoma</FontFamily>
                              </Style>
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>FruitName</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 />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
              <RepeatOnNewPage>true</RepeatOnNewPage>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
            <TablixMember>
              <Group Name="table1_Details_Group2">
                <DataElementName>Detail</DataElementName>
              </Group>
              <TablixMembers>
                <TablixMember />
              </TablixMembers>
              <DataElementName>Detail_Collection</DataElementName>
              <DataElementOutput>Output</DataElementOutput>
              <KeepTogether>true</KeepTogether>
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet2</DataSetName>
        <Top>1.11229in</Top>
        <Height>0.43in</Height>
        <Width>3in</Width>
        <ZIndex>2</ZIndex>
        <Style />
      </Tablix>
      <Tablix Name="Tablix2">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox19">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>Amount</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox19</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="Textbox20">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Lookup(Fields!FruitName1.Value,Fields!Join.Value,Fields!amount.Value, "DataSet2")</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox20</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 />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <Group Name="Details" />
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet1</DataSetName>
        <Top>1.93625in</Top>
        <Left>0.16542in</Left>
        <Height>0.5in</Height>
        <Width>1in</Width>
        <ZIndex>3</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
        </Style>
      </Tablix>
    </ReportItems>
    <Height>3.05042in</Height>
    <Style />
  </Body>
  <Width>5.16542in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="AdventureWorks2008R2">
      <DataSourceReference>AdventureWorks2008R2</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>830bec66-3eef-4c84-b26b-69b2843906ca</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>AdventureWorks2008R2</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@Fruit">
            <Value>=Parameters!Fruit.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>select * from 
(
Select 1 id , 'Apple' fruit 
union
Select 2 id , 'Mango' fruit
union
Select 3 id , 'Grapes' fruit
) t
where id =@Fruit</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="id">
          <DataField>id</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="fruit1">
          <DataField>fruit</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="FruitName1">
          <Value>=Parameters!Fruit.Value +"||"+Parameters!FruitName.Value +"||" +Parameters!abc.Value</Value>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet2">
      <Query>
        <DataSourceName>AdventureWorks2008R2</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@Fruit">
            <Value>=Parameters!Fruit.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>select * from 
(
Select 1 id , 'Apple' fruit ,1000 amount
) t
where id =@Fruit</CommandText>
      </Query>
      <Fields>
        <Field Name="id">
          <DataField>id</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="fruit">
          <DataField>fruit</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="amount">
          <DataField>amount</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Join">
          <Value>=Parameters!Fruit.Value +"||"+Parameters!FruitName.Value+"||" +Parameters!abc.Value</Value>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportParameters>
    <ReportParameter Name="Fruit">
      <DataType>String</DataType>
      <DefaultValue>
        <Values>
          <Value>1</Value>
        </Values>
      </DefaultValue>
      <Prompt>Fruit</Prompt>
    </ReportParameter>
    <ReportParameter Name="FruitName">
      <DataType>DateTime</DataType>
      <DefaultValue>
        <Values>
          <Value>=now()</Value>
        </Values>
      </DefaultValue>
      <Prompt>FruitName</Prompt>
    </ReportParameter>
    <ReportParameter Name="abc">
      <DataType>DateTime</DataType>
      <DefaultValue>
        <Values>
          <Value>=now()</Value>
        </Values>
      </DefaultValue>
      <Prompt>abc</Prompt>
    </ReportParameter>
  </ReportParameters>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>c87d19dd-1be9-4dc4-9237-63d06aa7a91a</rd:ReportID>
</Report>

Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2015 1:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics