Reports (Sql Reports)

How to setup a new report or edit an existing

The configuration of SQL reports is done via the Administration Tool under the menu "Edit":

In here you will see a list of existing reports that are either specific for the chosen dataowner or available across all dataowners:

For creating a new report select "New... (create new report)" menu.
For updating an existing report, just choose it in the dropdown.

The Name field is the name of the report which is shown in Link UI.

The Xml field should contain the actual SQL report wrapped in an Xml document (notice that this is new from Link 2.7)

The schema for the Xml is:

Report Query Schema

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="ReportQuery">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Parameters">
          <xs:complexType>
            <xs:sequence>
              <xs:element maxOccurs="unbounded" name="Parameter">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="ParameterName" type="xs:string" />
                    <xs:element name="ParameterType" type="xs:string" />
                    <xs:element name="ParameterValue" type="xs:string" />
                    <xs:element name="ParameterDisplayName" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Query" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

From Link version 2.7, it is possible to use parameters in your Sql Report. That is why it is wrapped in Xml.

You might need to wrap the value of the Query element in CDATA if you use special characters like <, >, or other Xml reserved characters.

An example of how the Xml can look like:

Example of Sql Report

<ReportQuery>
  <Parameters>
    <Parameter>
      <ParameterName>@iPartnerName</ParameterName>
      <ParameterType>NVARCHAR(100)</ParameterType>
      <ParameterValue>Bauhaus</ParameterValue>
      <ParameterDisplayName>Partner Name</ParameterDisplayName>
    </Parameter>
  </Parameters>
  <Query>
    <![CDATA[
                             SELECT p.PartnersId, p.PartnerName
                             FROM [partner].[tPartners] p
                             WHERE p.PartnerName = @iPartnerName;
]]>
  </Query>
</ReportQuery>

All given parameters will be shown in the Link UI like this:

All parameters will always be required. Optional parameters are not support in this version.

Supported data types

Bit
Datetime
Int
(N)Varchar

If other types are provided they will be treaten as Varchars/Strings.

Content on this page: