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)NOTICE! From Link 2.8 the administration of Reports is moved to the Link Web Site: Reports - Administration
Technical documentation for Reports are here.
The schema for the Xml is:
| Code Block | ||||
|---|---|---|---|---|
| ||||
<?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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
<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.
| Panel | ||
|---|---|---|
| ||
Content on this page:
|