Hi,
I'm new to work with reporting services, and I have the following problem:

I made 2 datasets:
1) one for the values calles "JoinTest"
<?xml version="1.0" encoding="utf-8" ?>
<root>
 <list title="Payment Announcement Transaction" relativeSiteUrl="/sites/finadm/" tableName="PAT" useDisplyName="true">
  <fields></fields>
  <query>

  </query>
 </list>
 <list title="Locations" relativeSiteUrl="/sites/finadm/" tableName="Locations">
  <fields>Title, Division</fields>
  <query>
      <Where>
          <Eq>
              <FieldRef Name="Division" />
             <Value Type="Text">Soy Foods</Value>
          </Eq>
      </Where>
  </query>
 </list>

 <sqlOp op="join">
  <dstTableName>PACL</dstTableName>
               <parentTableName>PAT</parentTableName>
  <childTableName>Locations</childTableName>
  <parentFieldName>Requester</parentFieldName>
  <childFieldName>Title</childFieldName>
 </sqlOp>

 

<resultSet>PACL</resultSet>

</root>

2) One for the parameters "paramRequester"
<?xml version="1.0" encoding="utf-8" ?>
<root>
 <list title="Payment Announcement Transaction" relativeSiteUrl="/sites/finadm/" tableName="Payment Announcement Transaction" useDisplyName="true">
  <fields>Requester</fields>
  <query>
  </query>
 </list>
 <list title="Locations" relativeSiteUrl="/sites/finadm/" tableName="Locations">
  <fields>Title, Division</fields>
  <query>
      <Where>
          <Eq>
              <FieldRef Name="Division" />
             <Value Type="Text">Soy Foods</Value>
          </Eq>
      </Where>
  </query>
 </list>

 <sqlOp op="join">
  <dstTableName>PACL</dstTableName>
                 <parentTableName>Payment Announcement Transaction</parentTableName>
  <childTableName>Locations</childTableName>
  <parentFieldName>Requester</parentFieldName>
  <childFieldName>Title</childFieldName>
 </sqlOp>

 <sqlOp op="distinct">
  <dstTableName>results</dstTableName>
  <tableName>PACL</tableName>
  <fieldName>Requester</fieldName>
  <addValue>ALL</addValue>

 </sqlOp>

 

</root>

Now I wanted to filter the first dataset using the parameter, so I looked in the forum at the post "Optional Report Parameters" and filled out the dataset filter as follows:
Expression:   =Fields!Requester.Value
Operator: =
Value:

= IIF( Parameters!paramRequester.Value = 'ALL', Fields!Requester.Value, Parameters!paramRequester.Value)

When I try to go to the Preview pane, I get the following error:
"The filter value expression for the dataset 'JoinTest' contains an error: [BC30201] Expression expected."

Does anybody see what I'm doing wrong ?

 

Frederic,

I ran the paramRequester dataset, and the values are indeed the values from the lists I query. I also tried to run the report with the parameter in the first part of the Join Test dataset, and all looks OK. The only problem is the ALL value, that I need to integrate somehow in the filter.

regards,

Eddy d'Artois
Vandemoortele