Hi legendsfan,

The only way to filter a rollUpLists (or multiList type="RollUpList") statement before retrieving the data is to specify a different relativeSiteUrl.

If every Milestones lists that you want to retrieve are in the same site, you can use a query which looks like this one:

<multiList title="Milestones" relativeSiteUrl="@paramProjectName!" type="RollUpList" tableName="Milestones">

or with rollUpLists:

<rollUpLists title="Milestones" relativeSiteUrl="@paramProjectName!" tableName="Milestones">

If the lists that you want to retrieve are not in the same site, you won't be able to filter the data.

Regards,
Marien MONNIER
Enesys

Site names do match the project names.  Currently, we retrieve the project names from a Project Server database as the label/name of the parameter.  The value of the parameter is a field named "projectCode".  The "projectCode" field is what we use for the URL when creating a project site.  The sites are in a flat hierarchy so all project sites are 1 level below top level site. 

Summary: "projectCode" = relativeSiteURL and all sites at the same level in the hierarchy.

Thanks,

Colin

Bummer. The query works but the performance didn't seem to improve. There are 342 Milestones from about 30 different sites and the query takes 13 seconds to run when retrieving all milestones. If I filter to just retrieve from one or two sites it take almost the exact same amount of time.  I was assuming the query would speed up.  13 seconds seems like an awful long time considering I can run SQL queries to our project management database and retrieve thousands of rows in a second.

Any ideas why performance is so slow?

Thanks,

Colin

Yes. Whether I filter or not the query takes the same amount of time.  I've included my code in the post and I'll send you the RDL file.  I stripped out a bunch of datasets to keep it simple.  The odd part is one rolluplist dataset takes the same amount of time to run as 4 of them together.  This query is only using a single select parameter but in the future I may want it to be a multi-select parameter dropdown. Will the below query work with multi-select parameters?

There are about 50 project sites to loop and rollup through on the server and I expect that number to be well over 100 in another 6 months.  These rollup queries work fine on another site collection where there are only a couple sites.

<?xml version="1.0" encoding="utf-8"?>
<root xmlns="http://enesyssoftware.com/schemas">
  <queryName>Query1</queryName>
  <description></description>
  <rollUpLists title="Milestones" relativeSiteUrl="/" tableName="Milestones">
    <fields>Title,Date,Owner,Status,Health,Category,Notes,FileDirRef</fields>
    <query>
      <Where>
    <And>
        <Eq>
          <FieldRef Name="Status" />
          <Value Type="Choice">(1) Active</Value>
        </Eq>
    <In>
    <FieldRef Name="FileDirRef" />
    <Value Type="Lookup">@paramProjectName!</Value>
    </In>
    </And>
      </Where>
    </query>
  </rollUpLists>
  <resultSet>Milestones</resultSet>
</root>