Thomas Roschinsky

RSSharePointList-CAML-Hacks

Möchte man mit wenig Aufwand auf SharePoint-Listen mit Hilfe der SQL Server Reporting Services zugreifen, so kommt man am Datenquellen-Verbindungstyp für SharePoint-Listen nicht vorbei.
Dieser Verbindungstyp, auch bekannt als RSSharePointList bzw. SharePoint List Connection Type (Data Extension/Data Provider), ermöglicht einen einfachen Zugriff auf einzelne SharePoint-Listen eines SharePoint-Webs.

Konkret wird dabei ein SharePoint-Web bzw. eine SiteCollection mit einer SSRS-DataSource abgebildet, wohingegen ein SSRS-DataSet eine SharePoint-Liste repräsentiert.

Mittel eines Abfrageassistenten, der alle Listen/Bibliotheken eines Webs auflistet und darin enthaltene Spalten für das zu erstellende DataSet zur Auswahl anbietet, ist eine Definition der Reportdatenquelle schnell zusammengeklickt.

Heraus kommt eine XML-Struktur, eingeleitet mit dem Tag <RSSharePointList>, welches dem Entwickler mit SharePoint-Erfahrung bereits vertraut vorkommen könnte. Spätestens dann, wenn man noch eine Abfragebedingung im Assistenten hinzufügt, sind die Parallelen zu CAML, der in der SharePoint-Welt gängigen Collaborative Application Markup Language, nicht zu leugnen.

So weit, so gut. Solange man mit dem auskommt, was der Abfragedesigner zu leisten vermag, ist der BI-Analyst zufrieden. Letztendlich ist das oben Beschriebene ein alter Hut und die Nachfolgetechnologien stehen lange in den Startlöchern. Dennoch – SSRS und SharePoint sind auch 2016 noch ein Thema und es gibt Situationen, in denen man schneller als erwartet an Grenzen stößt. Ganz besonders dann, wenn man an den Komfort von der Flexibilität von SQL-Statements gewohnt war, um die Daten in eine für den Report sinnvolle Form zu bringen.

Hier wird es eng…

• Sortieren von DataSets bereits zur Abfrage (Äquivalent zum SQL ORDER BY)
• Arbeiten mit Referenzen auf andere SharePoint-Listen bzw. Listenelemente
• Arbeiten mit Referenzen aus MultiValue-Lookups

Das grundlegende Problem wird bereits im Abfragedesigner deutlich. Eine Abfrage, ein DataSet. Sobald eine zweite Liste ausgewählt wird, erinnert eine Messagebox daran, dass die bisherige Auswahl verworfen wird. In nativem CAML wären JOINs möglich – nicht so mit RSSharePointList. Benötigt man nun Daten des referenzierten Datensatzes aus einer zweiten Liste, ist man darauf angewiesen, die zweite Liste ebenfalls als DataSet einzubinden und mittels Lookup-Funktion der Reporting Services dort einzelne Spalten abzuholen. Dies wird zudem noch erheblich erschwert, dass man mit dem Anzeigenamen des referenzierten Datensatzes und nicht mit der ID arbeiten muss, dass die ID hinter dem SharePoint Lookup-Feld nicht im DataSet erscheint.

Hier kommt man weiter…

Sortieren

Glücklicherweise ist im Rahmen von Berichtselementen wie Diagrammen oder Tabellen bzw. Tabellengruppen das Sortieren vorgesehen, so dass man selten die Notwendigkeit hat, dies bereits zur Abfragezeit zu berücksichtigen. Bei Parametern allerdings, deren Daten auf einer Abfrage basieren, ist dies der einzige Weg, da das DataSet selbst keine Sortierung kennt.
Da uns die Ähnlichkeit zu CAML aufgefallen ist, ist es naheliegend das Sortier-Konstrukt aus eben dieser Notation im SSRS Data Provider zu testen. Unterhalb des Query-Elements kann man das OrderBy-Element verwenden…

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>SortTest</ListName>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="Category" />
    <FieldRef Name="DueDate" />
    <FieldRef Name="ID" />
  </ViewFields>
  <Query>
    <OrderBy>
      <FieldRef Name="Category" />
      <FieldRef Name="DueDate" Ascending="FALSE" />
    </OrderBy>
  </Query>
</RSSharePointList>

Referenzen

Hat man innerhalb von SharePoint seine Datenstrukturen rein relational und vollständig 1:n aufgebaut, sprich ohne die Verwendung von Multi-Lookup-Spalten, so will man häufig nur eine Liste anhand einer ID filtern, die aus z.B. einem Report-Parameter stammt oder einem Übergabeparameter für einen Sub-Report dient.
Im Standard kommt man mit der allerdings nicht besonders weit, denn das Where-Element enthält nur einen einfachen, String-basierten Vergleich, der lediglich das Filtern auf Anzeigenamen ermöglicht. Sicherlich ist dies eine Alternative, solange der Titel sich nie ändert und über die gesamte Zeit eindeutig ist. Von diesen idealen Bedingungen kann man aber nicht immer ausgehen.
Ein einfacher Trick aus der CAML-Welt hilft hier weiter: Das Setzen des Attributs LookupId auf TRUE am FieldRef-Element im Query ermöglicht das Nachschlagen per ID.

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>Task</ListName>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="ProjectName" />
    <FieldRef Name="ScheduledDate" />
    <FieldRef Name="ActualDate" />
    <FieldRef Name="ID" />
  </ViewFields>
  <Query>
    <Where>
      <Eq>
        <FieldRef Name="ProjectName" LookupId="TRUE" />
        <Value Type="Lookup">
          <Parameter Name="ProjectId" />
        </Value>
      </Eq>
    </Where>
  </Query>
</RSSharePointList>

Innerhalb von Listen mit Referenzen auf andere Listen steht man vor einem ähnlichen Problem. Möchte man hier die ID zum referenzierten Datensatz erhalten, hat man nur den zum Nachschlagefeld definierten DisplayName. Dieser hilft ebenfalls nur unter idealen Bedingungen weiter und dürfte sich nie ändern. Und auch hier: Entgegen der CAML-Dokumentation führt das LookupId-Attribut am FieldRef-Element im ViewFields-Element zum Erfolg.

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>Task</ListName>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="ProjectName" LookupId="TRUE" />
    <FieldRef Name="ScheduledDate" />
    <FieldRef Name="ActualDate" />
    <FieldRef Name="ID" />
  </ViewFields>
</RSSharePointList>

…leider mit der Einschränkung, dass man sich für ID oder DisplayName des Lookups entscheiden muss.

…und hier hört es auf

• CAML-Join
• CAML-Group
• CAML-RowLimit
• Multi-Lookup Columns

Frohes Reporten weiterhin!