Difference between revisions of "QPR Reporting Add-on"

From Mea Wiki
Jump to navigation Jump to search
Line 1,870: Line 1,870:
 
||Calculates a sum of array items. Array must contains numerical data.
 
||Calculates a sum of array items. Array must contains numerical data.
 
|- style="vertical-align:top;"
 
|- style="vertical-align:top;"
||Transform (array)||array expression(string)||Transforms every item of an array to another array using the provided expression. The expression has following arguments:
+
||Transform (array)||
 +
* array
 +
* expression(string)
 +
||Transforms every item of an array to another array using the provided expression. The expression has following arguments:
 
:- '''value''' which gets the value of the item.
 
:- '''value''' which gets the value of the item.
 
:- '''index''': iteration order number starting from 0
 
:- '''index''': iteration order number starting from 0

Revision as of 10:30, 9 August 2017

Introduction

QPR Reporting Add-on is a combined installation package containing the following parts:

• QPR Web Views
• QPR Word Reports
• Expression Engine for QPR Suite
• QPR Reports Menu

All parts except QPR Reports Menu are .Net 4.6.1 web services hosted in IIS, and they interact with QPR Suite using QPR Web Services interface. All parts also contain the common expression language.

In addition to the general installation instruction described by this document, the individual parts contain additional settings which are described in their own documentation.

Installation

Follow these steps to install QPR Reporting Add-on. There are different paths for each QPR Suite version. These instructions use X in the folder names, that is replaced by the number of the corresponding QPR Suite version.

1. Check whether the QPR environment uses Windows authentication (IWA) and/or HTTPS connection. Windows authentication is used when QPR system is connected to LDAP/AD and Windows user accounts are used to login to QPR. HTTPS connection is in use when QPR Portal url starts with “https”.

2. Open Programs and Features (in Windows Control Panel) and click Turn Windows features on or off. Check that components listed in the following table are installed. The installation procedure depends on Windows version. QPR Reporting Add-on needs .Net Framework 4.6.1 or later version.


Windows Version Required Components
Windows 10 (already includes .NET Framework 4.6) • All components in Internet Information
Services (except FTP Server)
• Following Windows features: (see image below)
.Net Framework 4.6 Advanced Services > ASP.NET 4.6
.Net Framework 4.6 Advanced Services > WCF Services > HTTP Activation

Windows features 1.jpg
Windows 8 (already includes .NET Framework4.5.1) • All components in Internet Information Services (except FTP Server)
• Following Windows features: (see image below)
.Net Framework 4.5 Advanced Services > ASP.NET 4.5
.Net Framework 4.5 Advanced Services > WCF Services > HTTP Activation

Windows 8 features.jpg
Windows Server 2012 R2
(already includes .NET Framework 4.5.1)
• All components in Internet Information Services (except FTP Server)
• Following Windows features: (see image below)
.Net Framework 4.5 Features > .Net Framework 4.5
.Net Framework 4.5 Features >

ASP.NET 4.5 .Net Framework 4.5 Features > WCF Services > HTTP Activation

600px

Windows Server 2012 (needed .Net Framework is not installed by default) • All components in Internet Information Services (except FTP Server)
• Following Windows features:
.Net Framework 4.5 Advanced Services > .Net Framework 4.5
.Net Framework 4.5 Advanced Services
> ASP.NET 4.5
.Net Framework 4.5 Advanced Services> WCF Services > HTTP Activation
• Install .Net Framework 4.5.2:
Online installer: http://www.microsoft.com/en-us/download/details.aspx?id=42643
Offline installer: http://www.microsoft.com/en-us/download/details.aspx?id=42642
Windows Server 2008 R2
(needed .Net Framework is not installed by default)
• All components in Internet Information Services (except FTP Server)
• All components in .Net Framework 3.5.1 (see image below)
• Install .Net Framework 4.5.2:
Online installer: http://www.microsoft.com/en-us/download/details.aspx?id=42643
Offline installer: http://www.microsoft.com/en-us/download/details.aspx?id=42642

600px

3. Check that QPR Web Services web.config file is a proper one (located in C:\Program Files\QPR Software Plc\QPR 201X.1 Servers\WebServices). In the default QPR installation there are files web.config and web.config.IWA. If QPR environment uses Windows authentication (refer to step 1), the latter file must be taken into use by renaming

a. web.config to web.config.noIWA, and

b. web.config.IWA to web.config.

4. If using Windows authentication (refer to step 1), make sure C:\ProgramData\QPR Software\QPR 201X\201X.1\Servers\Settings\QPR_Servers.ini has settings IWACGIBinaryHost=127.0.0.1 and CGIBinaryHost=127.0.0.1. under WAS Settings section.

5. Copy QPRWebServicesExtensions folder from installation package to IIS published files in C:\inetpub\wwwroot\.

6. Installation package contains the following preconfigured files to be used as QPR Reporting Add-on web.config file:

a) web.config: for HTTP connection and Anonymous authentication
b) IWA.web.config: for HTTP connection and Windows authentication
c) HTTPS.web.config: for HTTPS connection and Anonymous authentication
d) HTTPS+IWA.web.config: for HTTPS connection and Windows authentication

Copy a suitable configuration file to C:\inetpub\wwwroot\QPRWebServicesExtensions\ folder and rename it as web.config.

Do not mix up QPR Reporting Add-on's web.config file (in C:\inetpub\wwwroot\QPRWebServicesExtensions\) with QPR Web Services' web.config file (in C:\Program Files\QPR Software Plc\QPR 201X.1 Servers\WebServices\web.config).

7. Set the parameters (listed in Settings) in the QPR Reporting Add-on web.config's appSettings section (in C:\inetpub\wwwroot\QPRWebServicesExtensions\web.config).

8. Make a backup copy of the C:\Program Files\QPR Software Plc\QPR 201X.1 Servers\WebServices\servicetester.aspx file. Replace the file with the servicetester.aspx. file from the installation package. (Alternatively, use the servicetester.patch file that described the changes to the servicetester.aspx file.)

9. Make sure QPR Web Services Server is running. The installation cannot be continued until QPR Web Services Server is running properly.

10. In IIS Management Console, go to Application Pools (in left side hierarchy). Create a new application pool by clicking Add Application Pool…. Use the settings in the image below (settings v.4.0.x and Integrated). Please do not change settings for existing application pools, if they are used by other web applications because then the other applications may stop working. Especially QPR Suite has an application pool that is v.2.0.x and Integrated.

Add application pool.jpg

11. Click the previously created application pool, click Advanced settings… and select Identity setting. In the opening Application Pool Identity window, click Built-in account and select LocalSystem (see the image below). Click OK for the both open windows.

Application pool.jpg

12. Find the QPRWebServicesExtensions folder in IIS Management Console and click Convert to Application (secondary mouse button). Select the previously created application pool QPR Web Services Extensions.

13. Check the IIS authentication settings by clicking QPRWebServicesExtensions web application is IIS Management Console (on the left side hierarchy). Check that Features View is opened (in bottom), and doubleclick Authentication. The authentication settings must match with the web.config file, which was set in step 3:

a. For Windows authentication: Anonymous Authentication must be Disabled and Windows Authentication must be Enabled. (see the image below)

b. For Anonymous authentication: Anonymous Authentication must be Enabled and Windows Authentication must be Disabled.

ASP.NET Impersonation must be Enabled in both cases.

Authentication.jpg

14. Configure QPR Reporting Add-on settings listed in Settings. Especially check the qprwebserviceaddress carefully. Quick guide for usual configurations (file C:\inetpub\wwwroot\QPRWebServicesExtensions\web.config):

a. When Windows authentication is not in use: wcfsecuritymode=none and qprauthenticationmode=passedsession
b. When Windows authentication is in use: wcfsecuritymode=message and qprauthenticationmode=windows

15. Create folder C:\temp. It is possible to use other folder but its location must be changed to web.config temppath setting.

16. Check that QPR Reporting Add-on is working by making the tests listed in chapter Installation tests. If you encounter any issues, check if any of the error situations described in Troubleshooting were encountered.

17. Copy DWV templates folder from installation package as a DWV templates root folder (setting dwvtemplatesphysicalpath in QPR Reporting Add-on’ web.config file). Also copy DWR templates folder as a DWR templates root folder (setting dwrtemplatesphysicalpath in QPR Reporting Add-on’ web.config file) and trend_down.png and trend_up.png files from DWV templates\Dashboard folder to C:\inetpub\wwwroot\qpr201X-1\qprsoftware\portal\images.

18. Deploy Reports Menu UI element to the Portal by replacing mainview.tpl and headerview.tpl files from the installation package Reports Menu folder to C:\ProgramData\QPR Software\QPR 201X\201X.1\Servers\Templates\WAS\Portal. (Alternatively, use the externalreportsmenu.patch file.)

19. Copy icon_reports.png from Reports Menu folder to C:\inetpub\wwwroot\qpr201X-1\qprsoftware\portal\images.

20. Copy jquery.filedownload.js from Reports Menu folder to C:\inetpub\wwwroot\qpr201X-1\qprsoftware\Common\scripts.

21. Add the following CSS to C:\inetpub\wwwroot\qpr201X-1\qprsoftware\stylesheets\custom.css:

#ReportsToolbarMenu {   cursor: pointer; }
#ReportsToolbarMenu .activetarget {   background-color: inherit; }
.visiblereportmenulink, .reportmenumain > a {   background: url(../portal/images/icon_reports.png) no-repeat left -1px; }
.disabledreportmenulink {   background: url(../portal/images/icon_reports.png) no-repeat left -1px;   color: #BBBBBB !important;   cursor: default; }
#InformationViewFrame {   background-color: white;
}

22. Restart Windows service for QPR Suite, or clear QPR Portal templates cache (http://SERVERNAME/QPR201X-1/Portal/QPR.Isapi.dll?QPRWAS&*cleartemplatecache). In addition, clear web browser’s cache.


Settings

QPR Reporting Add-on is configured using the file C:\inetpub\wwwroot\QPRWebServicesExtensions\web.config. The file has following settings in the configuration > appSettings section. The greyed settings are unusual, so for most installations they can be ignored.

Attribute Description
qprwebserviceaddress QPR Web Services’ url address. This should point directly to QPR Web Services server’s port. The default port is 9002, but the actual port in use can be seen in QPR Configuration Manager (Common > Server locations > Web services server).
Example: http://localhost:9002/QPR201X-1/Portal/QPR.Isapi.dll/wsforward/mainservice.svc/wshttp
Notes:
• It’s recommended to use localhost as a hostname if the QPR Web Services Server is in the same computer.
• The address starts with http even if QPR environment uses https.
• Check the proper url path from QPR Portal address (QPR201X-1 in the example). The path is QPR version specific by default.
• Validity of the address can be checked by opening the address in the server computer using browser without the ending /wshttp, e.g. http://localhost:9002/QPR201X-1/Portal/QPR.Isapi.dll/wsforward/mainservice.svc. A page displaying You have created a service should open. Note that this address doesn’t work in client computers (this is because “localhost” always references to that computer where the browser is running).
wcfsecuritymode QPR Web Services security settings. Must correspond to QPR Web Services settings (in C:\Program Files\QPR Software Plc\QPR 201X.1 Servers\WebServices\web.config). (Refer to step 3 in the installation instructions.) Options:
none: Use this when Windows authentication is not in use.
message: Use this when Windows authentication is in use
transport: Usually not used.
transportwithmessagecredential: Usually not used.
qprauthenticationmode Determines how the add-on parts authenticate to QPR Web Services.
Options:
passedsession: QPR Web Services’ session id must be passed as a parameter. The parameter is passed differently in different accelerators. This options is usually used when wcfsecuritymode is none, but also message is possible.
windows: Windows user making the request is used to authenticate to QPR Web Services. When this option is used, wcfsecuritymode must be message (none is not possible).
commonqprauthentication: Common QPR authentication is a mechanism where QPR Suite products have been configured to trust each other in a way that when a user is authenticated to one of the products, the user is also authenticated to other QPR suite products as well. More information:
https://devnet.onqpr.com/pawiki/index.php/Common_QPR_Authentication#Linking_between_QPR_Products.

When common QPR authentication is in use, session id of the source QPR Suite system is passed as a parameter to QPR Web Services Extensions.
fixedcredentials: A single user account is used for all requests. The account’s username and password are defined in the configuration (see settings username and password). This options is not usually used.
For more information, see Authentication and Data Security

dwrtemplatesphysicalpath Folder in the file system where QPR Word Reports template files are located.
dwvtemplatesphysicalpath Folder in the file system where QPR Web Views template files are located.
installpath Path for QPR Web Services Extensions binaries installation folder, which is by default C:\inetpub\wwwroot\QPRWebServicesExtensions. This information is needed by Expression language WebpageAsImage function.
temppath Path to a file where Reporting Add-on may write temporary files.
templatecaching Determines whether template files caching is enabled (true) or disabled (false). Template caching means that templates are read from file system to server memory when the IIS web application starts. When template caching is enabled, pages are processed faster and disk load as reduces. Template caching should be enabled for production environments.

Template caching is usually be disabled for development work, so that changes in templates can be seen immediately in result pages. When template caching is disabled, all templates are read into memory every time, when a page is requested.

disallowedfunctions Comma separated list of functions that are not allowed to be executed by the Reporting Add-On. There are some functions which are able to manipulate files in the server computer, and enabling those files may cause security issues. Function names must be written in lowercase.
loglevel Possible values: None, Error, Information, Verbose.
qprwebapplicationname Setting for Dynamic Web Views: Name of the QPR web application in IIS. This parameter is not mandatory, but it should be defined, as it can be used by html content to reference QPR resources published in IIS (such as images and css files).
reporttemplateparameter Setting for Word reports: Defines the name of the parameter which passes the report template path. See chapter Working with Report Templates
qprtemplateidparameter Setting for Word reports: Parameter name which passes QPR’s Word report template object id. This can be used when the report templates are stored in QPR Portal (i.e. QPR system objects) (see chapter Working with Report Templates)
defaultimageformat Setting for Word reports: Default image format for QPR Web Service’s GetGraph operation. This setting is used if image format is not explicitly defined.
username Password for QPR Suite when authenticationmode is fixedcredentials.
password Password for QPR Suite when authenticationmode is fixedcredentials.
executionTimeout (in the httpRuntime tag) Timeout for request processing in seconds. Usually there is no need to change this setting.

The value should be increased if there are heavy requests which take time to run. Note that the timeout limit works as a protection when the processing never ends as a results of an error (this may be possible in e.g. recursive reports).

installation test

Do the following tests to confirm that QPR Reporting Add-on is working:

1. Open http://SERVERNAME/QPRWebServicesExtensions/ExpressionEngine.svc. The address starts with https instead of http, when also QPR Portal address starts with https. There should open a page stating You have created a service. If an internal server error (error code 500) with no error details is returned, test the url in the server, because in the server the error message has more details.
a. If this works, the .Net application is running properly in IIS.
b. If this doesn’t work, there is a problem with IIS settings or .Net installation.
2. Open QPR Web Services Tester, which is usually in http://SERVERNAME/QPR201X-1/Portal/QPR.Isapi.dll/wsforward/servicetester.aspx. The SERVERNAME can be found in QPR Portal url address. The address starts with https instead of http, when also QPR Portal address starts with https.
3. Set valid credentials in the Authentication tab. Confirm that QPR Web Services is working by making a query using QPR Web Services Tester in the QueryObjects tab.
4. Check that the page contains RunExpression tab.
5. Query for example [UM].users like in the image below. When clicking QueryObjectsAsXml, the test is successful, if a text starting with <ResultsetHierarchy … appears. Note that your query results may be different than the results in the image. If this test doesn’t work, there is a problem with QPR Web Services.


Installation test.jpg

6. Go to RunExpression tab, click Run using the expression it contains by default. It should return Ok (below the Run button). This confirms that the .Net application is running in IIS and the QPR Web Services connection works.

Authentication and Data Security

This chapter contains information about authentication and data security of the add-on and QPR Suite to make the installation successful also from security point of view. The add-on needs to authenticate to QPR Web Services to get needed data. There are three methods to authenticate:

Use Windows authentication. Windows user authenticates to the add-on (provided by IIS), and same user is used to authenticate to QPR Web Service (this is called impersonation). It’s advisable to use this authentication method if available.
Pass QPR Web Service’s session id as a url parameter to the template. The accelerator uses the session id directly when accessing to QPR Web Services. This approach requires a functionality in QPR Portal to first authenticate to QPR Web Service and then pass the session id to the template. Use this authentication method, if Windows authentication is not possible.
Preset username and password (either QPR or Windows user) in QPR Reporting Add-on configuration. These credentials are used by the add-on when logging in to QPR Web Services. This authentication method is only used for special purposes.

The authentication method is determined by QPR Reporting Add-on setting "qprauthenticationmode".

Running Multiple QPR Reporting Add-on Instances Simultaneously

It may be required to run multiple QPR Reporting Add-on instances simultaneously, e.g. when different versions or different QPR Reporting Add-on settings are needed. Running multiple QPR Reporting Add-on instances simultaneously is possible: Copy the QPRWebServicesExtensions folder with a different name to IIS root folder, and make all the settings made to the default folder to that folder. The other instance is referenced using the other folder name in URLs.

Uninstallation

Follow these steps to uninstall QPR Reporting Add-on:

1. In IIS Management Console click Remove for the QPRWebServicesExtensions web application (mouse secondary button).
2. Delete the web application’s folder C:\inetpub\wwwroot\QPRWebServicesExtensions in the disk.
3. Revert the original C:\Program Files\QPR Software Plc\QPR 201X.1 Servers\WebServices\servicetester.aspx from release package.

Troubleshooting

Issue Resolution
Web browser returns:
Could not load type 'System.ServiceModel.Activation.HttpModule' from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
See the resolution here: https://support.microsoft.com/en-us/kb/2015129
Web browser returns:
Memory gates checking failed because the free memory (nnn bytes) is less than x% of total memory. As a result, the service will not be available for incoming requests. To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.
The reason for this error is that there is little free memory in the system, and thus the primary solution is to get more free memory in the system. It’s still possible to set the free memory limit to a lower level, but this may cause instability. To do that, find the setting minFreeMemoryPercentageToActivateService from the QPR Web Services Extensions’ web.config and set the limit lower (e.g. to 2). More information:

https://msdn.microsoft.com/en-us/library/dn458357(v=vs.110).aspx

Following errors are returned by Expression Engine tester: The message with Action 'http:// schemas.xmlsoap.org/ws/2005/02/trust/RST/ Issue' cannot be processed at the receiver, due to a ContractFilter mismatch at the EndpointDispatcher. This may be because of either a contract mismatch (mismatched Actions between sender and receiver) or a binding\security mismatch between the sender and the receiver. Check that sender and receiver have the same contract and the same binding (including security requirements, e.g. Message, Transport, None). Secure channel cannot be opened because security negotiation with the remote endpoint has failed. This may be due to absent or incorrectly specified EndpointIdentity in the EndpointAddress used to create the channel. Please verify the EndpointIdentity specified or implied by the EndpointAddress correctly identifies the remote endpoint. The reason is the QPR Web Services and QPR Web Services Extensions WCF settings don’t match. Please check installation steps 3, 4 and 6.
When opening QPR Web Services Tester, the following message appears … Redirecting to mainservice page …. There may be a problem with IIS handler mappings. Tests with following settings:
• Remove C:\inetpub\wwwroot\web.config if it exists. Make a backup before deleting.
• Contents of C:\inetpub\wwwroot\qpr201X-1\web.config should be
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <directoryBrowse enabled="false" />
        <httpErrors>
            <clear />
        </httpErrors>
        <handlers accessPolicy="Read, Execute, Script">
            <remove name="AssemblyResourceLoader-Integrated-4.0" />
            <remove name="AssemblyResourceLoader-Integrated" />
            <remove name="AXD-ISAPI-4.0_64bit" />
            <remove name="AXD-ISAPI-4.0_32bit" />
            <remove name="AXD-ISAPI-2.0" />
            <remove name="AXD-ISAPI-2.0-64" />
            <remove name="PageHandlerFactory-ISAPI-4.0_64bit" />             
       <remove name="PageHandlerFactory-Integrated-4.0" />
            <remove name="PageHandlerFactory-ISAPI-4.0_32bit" />
            <remove name="PageHandlerFactory-Integrated" />
            <remove name="PageHandlerFactory-ISAPI-2.0" />
            <remove name="PageHandlerFactory-ISAPI-2.0-64" />
            <remove name="svc-ISAPI-4.0_64bit" />
            <remove name="svc-ISAPI-4.0_32bit" />
            <remove name="svc-Integrated-4.0" />
            <remove name="svc-ISAPI-2.0-64" />
            <remove name="svc-ISAPI-2.0" />
            <remove name="svc-Integrated" />
        </handlers>
    </system.webServer>
</configuration>

 • Contents of C:\inetpub\wwwroot\qpr201X-1\Portal\web.config should be

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <handlers accessPolicy="Read, Execute, Script" />
        <directoryBrowse enabled="false" />
        <defaultDocument>
            <files>
                <clear />
                <add value="Default.htm" />
                <add value="Default.asp" />
                <add value="index.htm" />
                <add value="index.html" />
                <add value="iisstart.htm" />
                <add value="default.aspx" />
                <add value="QPR.Isapi.Dll" />
            </files>
        </defaultDocument>
    </system.webServer>
</configuration>

QPR Expression Engine

Expression Engine for QPR Suite contains the following components:

- Expression Engine Web Service
- Expression Engine Service Tester

Expression Engine Web Service is a web service used to run expressions. This expression engine is embedded into many other accelerators, such as DWR and DWV.

Expression Engine Service Tester is an extension to QPR Web Service Tester, and it can be used to test expressions when e.g. developing report templates or configuration files for other accelerators. It’s possible to define multiple expressions which are calculated consecutively in a single web service operation. In that case expression results are stored in variables, and variables can be used in subsequent expression. Example:


var1=3 + 9 / 3

var2=2*[var1]

var3='Value is ' + [var2]


Expression Engine Web Services is a IIS hosted web application, and Expression Engine Service Tester is implemented with changes to QPR Web Service Tester web page html template.

Expression Engine Input Parameters

Type: rootobject
Attribute Type Description
webServiceSessionId string QPR Web Service’s session id. Used only if authenticationmode=passedsession.
expressionSet string[] List of expressions.
variableNameSet string[] List of variable names. The variables get the calculated expression values, and the variables are available as arguments for the next expressions. The value of the last expression is returned by the operation.

Following output data format is used by both services.

Output Data Format

type: ResultDataTable
Attribute Type Description
headers Header[] Array of header rows. There can be one or two headers.
rows ResultDataTableRow[] Array of data rows.


type: ResultDataTableRow
Attribute Type Description
cells ResultDataTableCell[] Array of data cells.
leftIdentifier string
rightIdentifier string


type: ResultDataTablecell
Attribute Type Description
rawValue string Unformatted raw value.
formattedValue string Value that is displayed to users.
sortValue string Value that can be used to sort the values.
datatype string Datatype of the value: string, numeric, int.


type: ResultDataTableHeader
Attribute Type Description
cells ResultDataTableHeaderCell[]


type: ResultDataTableHeaderCell
Attribute Type Description
label string
columnSpan int Number of columns the header extends.

Example

Following image shows QPR Web Service tester where Expression Engine Service Tester is installed.

Example.jpg

QPR Word Reports

QPR Word Reports is a technology for QPR Suite to generate customized Word reports displaying content from the QPR system. QPR Word Reports works in IIS as a web application, and reports are accesses by a url address (using an http GET request). Data for reports is fetched from QPR system using QPR Web Service interface.

Microsoft Word is not needed on the server side, as Word file generation is implemented using Open XML SDK. Actually Word is not required in the client side either, because Word documents can be opened using OpenOffice (file format is Office Open XML).

Main features

QPR Word Reports contains the following main features:

• The reports are run as they are requested, so the reports always contain the latest data.
• Reports can have parameters affecting report contents.
• QPR Word Reports contains an embedded formula calculation engine. All the tags may be defined using expressions which values are calculated as the report is run.
• The report templates are normal Word files so they can contain any Word file features (such as headings, tables, cross references, headers, footers and images).
• Reports may get theirs content from other reports, called subreports. Also recursive reports are supported (reports calling themselves). Subreports makes it possible to assemble the report from smaller report “parts”.
• Adding Word hyperlinks is possible


Concepts

QPR Word Reports is based on the following concepts:

•Report templates are normal Word files which may have any contents. Reports are based on these template files. When a report is run, a template is taken as a basis, and during processing of the report, content from QPR system is added to the template, forming the final report.
• Templates contain tags, which instruct how the content is added to the report and how dynamic parts of the report are built.
• Tags contain attributes, which offer additional information for the tag.
• QPR Web Service is used through its operations. Operations used by QPR Word Reports are GetAttributeAsString, QueryObjects, GetGraph, GetBinaryData and GetPortalUrl. All QPR Web Service operations are documented under http://kb.qpr.com/qpr2017-1/index.html?functions.htm.
• Subreport is a report that is called from another report (main report). The subreport is processed like any report and its contents are embedded in the main report. Main report may pass parameters to the subreport.
• Loop is a list of QPR system objects (elements) returned by QPR Web Service’s QueryObjects operation. In a loop, a subreport is called for every looped object.

To open QPR Word Reports reports in QPR Portal, QPR Reports Menu is needed. It’s able to automatically pass parameters, such as model or selected object to the report.

QPR Word Reports cannot itself store previously run reports, or run reports based on a schedule. When a report is viewed, it’s always generated at that point and data for the report is fetched from QPR system. Because of this, there may be a delay when getting a report, especially if the report contains dozens of pages.

Reports are accessed using a url address, which determines the report to be run and the url also passes all needed parameters to the report. The url depends on the server computer name and the path where QPR Word Reports is installed in IIS. Note that parameter names are case sensitive.

An example url: http(s)://SERVERNAME/QPRWebServicesExtensions/DynamicWordReports.ashx?report=reportname&parameter1=param1value&parameter2=param2value

Configuration

Following table contains QPR Word Reports’s parameters, which are configured in the web.config file of QPR Web Services Extensions.

Parameter name Description
reporttemplateparameter (optional) Defines the name of the parameter which passes the report template path. See chapter Working with Report Templates for configuring the report template path.
qprtemplateidparameter (optional) Parameter name which passes QPR’s Word report template object id.

This can be used when the report templates are stored in QPR Portal (i.e. QPR system objects) (see chapter Working with Report Templates). Instead of report template path, the Word report template object id can be passed as a parameter. The Word report template object id gets a priority over report template path.

defaultimageformat (optional) Default image format for QPR Web Service’s GetGraph operation. This setting is used if image format is not explicitly defined.

Working With Report Templates

QPR Word Reports is based on Word report templates, which are normal Word files. When a report is run, the template is taken as a basis, and the tags in the template file are processed. Content is added to the report defined by the tags.

There are two options to reference to the used templates:

Reports tab in QPR Portal: This option is suitable for running reports in production environments, because the report templates can be updated by QPR users (Portal administrator rights are required), and no access to QPR server is needed. Also, the report templates are stored in QPR system (i.e. in QPR database).
A disk drive to where there is an access from the server running QPR Word Reports. This option is suitable for developing reports. It is faster for development because the developed template may be open while running the report. Also, option 1 requires the template to be saved manually to QPR Portal so that it can be run.


Both options can be used at the same time. If there are two templates with the same name in both places, the QPR Portal stored templates have priority.

Note that QPR Word Reports templates are entirely incompatible with QPR - Add-in for Microsoft Office report templates, so running them as QPR - Add-in for Microsoft Office reports is likely to cause an error.

Both Word file extensions (docx and docm) are supported. The output extension is the same as the extension of the main report template. File extension is not specified when referencing to template names in disk. If there are two files with the same name but different extension in the same folder, docx is selected as a template. Subreport extensions don’t affect the result report.

When stored in a disk, reports may be organized in folders. In that case report templates are referenced using syntax folderName/reportName. Folders inside folders are also supported.

Report template root location is determined by configuration setting reporttemplatephysicalpath.

When a report processing fails, an html page showing an error message is displayed. The error message is likely to reveal in which report template and tag the error occurred. Also stack trace is shown, but that is helpful information only for developers.

Template Tags

Tags are used to add content to the report. Syntax for defining tags is following:

<#tagname attribute1="attribute1 value" attribute2="attribute2 value" attribute3="attribute3 value">

If an attribute value is calculated using an expression (formula), two equals signs are used instead of one, e.g.

<#tagname attribute1=="1+1" attribute2="1+1"> (attribute1 value is integer 2, attribute2 value is string "1+1").

In the following sub chapters optional tag attributes are marked with "(optional)". Other tags are mandatory – if any of them is missing, an error is caused. In addition, for some of the mandatory attributes the value of the attribute must not be empty (i.e. attributeName="" is not allowed). If the attribute value comes from an expression, note that the expression may evaluate as empty (thus causing an error in non-empty mandatory attribute).

When processing a tag, attribute expressions are calculated from left to right. All report parameters, report variables and already calculated attribute values are available as expression arguments when later attribute expressions are calculated. An argument is kind of an input variable that can be used in an expression with syntax [argumentName]. E.g.:

<#expression attribute1="3" value=="[attribute1] + 2"> (result is 5)

Tag specific arguments have a priority over report parameters and report variables, when there are same names used

Attribute names should only contain characters a-z, 0-9 and _.

Values for all boolean valued tags (such as visible) may be defined as "true" or "false" or as a boolean valued expression, e.g. visible=="[variable1]=1" (the result of the equality comparison is of type boolean). Tag visibility is processed in the normal left to right order. When visibility tag is processed and its value is false, the tag processing stops at that point. This has an effect on report run performance. In addition, possible later expression errors don’t emerge in that case.

Following abbreviated tag names can be used: attribute=att, expression=exp, parameter=par and variable=var. Tags must be written in lower case.

Attribute Tag (att)

This tag is used to get properties of QPR objects by using QPR Web Service’s GetAttributeAsString operation (more information: http://kb.qpr.com/qpr2017-1/index.html?getattributeasstring.htm).

This tag uses QueryObjects instead of GetAttributeAsString, if there are multiple id’s in the object attribute or if attribute followrelations used (more information below). If QueryObjects is used, separator and sortby attributes are applied

Attribute Description
object (string) Object id or list of object id’s separated by comma (,). If this contains only one id, it is passed as a parameter objectId of GetAttributeAsString operation. If this attribute is not provided or is empty, the tag will be evaluated to empty. (optional)
attribute (string) This is passed as parameter attribute of GetAttributeAsString or QueryObjects operation.
options (string) This is passed as parameter options of GetAttributeAsString or QueryObjects operation. (optional)
expression (exp. string) An expression where the result is put as an argument "value" (see the examples). (optional)
followrelations (string) Name of the relation attributes to follow to get another object or a set of other objects. If there are multiple subsequent relations to follow, the relations are separated by comma (,) (see the examples). (optional)
separator (string) Character(s) separating list of attributes of different objects. Empty values are not shown (i.e. no empties between separators). Default is a new line characters. (optional)
sortby (string) Sorting if there are multiple objects returned. Default sorting is by attribute “name”. (optional)
visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
style (string) Determines text style. See Defining Styles. (optional)

Examples

<#attribute object=="[objectId]" attribute="name">
<#attribute object="[measureId]" attribute="measure.value(series=\"ACT\",period=\"1 / 2017\")" expression="[value] * 2.54">
<#attribute object=="[measureId]" followrelations="childobjects,childobjects"attribute="measure.value(series=\"ACT\",period=\"1 / 2017\")" separator=", " sortby="name">

Dataset Tag

This tag is for showing information from a dataset in the report. Dataset consists of multiple columns and rows. Dataset tags shows a header subreport (once) and row subreports for each row in the dataset. Dataset tag has following attributes

Attribute Description
data (dataset) Dataset shown by the tag.
headertemplate (string) Subreport template for dataset header. The header subreport is shown once in the report above the row subreports. Header subreport can be used e.g. for showing a column names. Header subreport is optional, so if the parameter is left out, no header subreport is shown.

Following type of parameters are passed to the header subreport: columnheader_1, columnheader_2, … columheader_n containing names of the dataset columns. If the column names are known, the header can be set fixed in the mainreport and header subreport is not needed. (optional)

headertemplatedata (byte array) Alternative to headertemplate. Contains the used header template as a byte array. (optional)
rowtemplate (string) Subreport template for dataset rows. The subreport is looped for each row in the dataset in the order rows appear in the dataset. Following parameters are passed to the rows subreport:

- column_1, column_2, … column_n containing dataset cell data
- parameters which names are same as column names, containing dataset cell data
- same parameters that are passed to the header subreport, i.e. columnheader_1, columnheader_2, … columheader_n
(optional)

rowtemplatedata (byte array) Alternative to row template. Contains row template as a byte array. (optional)
visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
(all other attributes) All other attributes are passed to the looped subreports as parameters. Note that all the above mentioned attributes are not passed as parameters, because they have a special meaning in the Dataset tag. (optional)

Embeddedfile Tag

This tags inserts an embedded file (using OLE technology) into the report. The file can be of any type. In addition to the file, an icon image needs to be defined. The size of the embedded object icon is set based on the size of the icon image.

Attribute Description
filedata (byte array) Embedded file data as byte array. See available functions in expression language documentation in Binary Data Functions.
icondata (byte array) Icon image as byte array. See available functions in expression language documentation in Binary Data Functions. Icondata is not needed when using replace mode and replaceable embedded file is showing its content in the Word instead of icon (DrawAspect="Content"). (optional)
contenttype (string) Embedded file media type (also called MIME type). This information can be defined as fixed (when the content type is known in report design), or fetched using function available in expression language documentation in Binary Data Functions. List of media types: http://www.iana.org/assignments/media-types/media-types.xhtml
replace (boolean) Enables the replace mode. This makes it possible to use an existing embedded file (a.k.a. placeholder content) in the template which is replaced by the actual embedded file. The replace mode enables to use Word styles and formatting, which will end up in the actual content of the report. The placeholder embedded file must be in the same or next paragraph of the tag. It’s advisable to use as small files as possible as a placeholder embedded objects so that the size of the template remains small.

Replace mode is activated with "true" or deactivated with "false". Default is false. (optional)

visible (boolean) Determines content visibility shown by the tag. See chapter Setting Content Visibility. (optional)

Example:

<#embeddeddata filedata=="qprEmbeddedFileData([objectid], ‘embeddedata’, ‘’)" icondata=="httpFileData(‘http://url…/’ + GetAttribute([objectid], ‘embeddedfilemimetype’, ‘’) + ‘.png’)" contenttype=="GetAttribute([objectid], ‘embeddedfilemimetype’, ‘’)">

Expression Tag (exp)

This tag is for adding a result of an expression (formula) in the report. The defined expression is evaluated and the result is set in place of this tag.

Note that the tag may contain any attributes which are serving as arguments to the value expression (see examples).

Attribute Description
value (object) Shown value itself. Note that the values shown in the report are always technically strings (sequence of characters), so the value must be of a type that can be converted into a string, such as string, integer, double, datetime, boolean, or an array of any of the previous types. If the value is not a string, an implicit conversion to string is made.

In case of datetime type, dateformat defined in the Reportsettings tag is used.

visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
style (string) Determines text style. See Defining Styles. (optional)

Examples:

<#expression value=="[variable1]">
<#expression value1=="5" formula=="2 * [value1] + 3"> (result is 13).

Image Tag

This tag is used to show an image in the report. Images can be got using QPR Web Service’s GetGraph operation, which can be used to get graphical content from QPR system, such as EA/PD diagrams, Metrics dashboards or measure graphs. For more information about GetGraph, see http://kb.qpr.com/qpr2017-1/index.html?ws_getgraph.htm.

When getting images using http, many kind of errors may be encountered, such as the image is not found, it’s not available, or user rights restrict. In case an image cannot be got, the report run still succeeds. If the reason for the error is not known, it’s possible to see the http error message by using the loggingmode. Note also the noimagemessage attribute which is used in case of these errors.

Image size in Word depends on its resolution and the properties listed below. Image size is calculated as follows:

dpi defines image base size (dpi can be defined explicitly or used image’s dpi value)
scaling is applied after that (expanding or shrinking)
maxwidth and maxheight restrictions are applied last (always shrinking)

In attributes dpi, scaling, maxwidth and maxheight expression it’s possible to use following arguments representing information from the image (see an example below):

width (int)
height (int)
horizontaldpi (decimal)
verticaldpi (decimal)
format (string) (e.g. png, jpeg, gif)

Note that image resolution (width and height in pixels) is determined by QPR Web Service, so that can be affected only using web service’s parameters (parameter options).

If you need an image to appear always as a certain size regardless of its resolution, the scaling can be defined as a large enough number, and set maxwidth (or maxheight) to be the desired image size. This way the maximum width restriction is always applied and the size of the image is the desired.

Attribute Description
imagedata (byte array) Image as a byte array. See available functions in expression language documentation in Binary Data Functions. Note that the type of the file needs to be a bitmap image e.g. png, jpg or gif. (optional)
object (string) This is passed as a parameter objectid of GetGraph operation. (optional)
options (string) This is passed as a parameter options of GetGraph operation. (optional)
dpi (decimal) Determines the image size as dots per inches. If not defined, dpi information of the image itself is used. (optional)
scaling (decimal) Image scaling as percentage value ("100" means no change). The scaling is used to change the size of the image in the report.

E.g. value "200" doubles the size of the image. Scaling is applied before maximum width and height restrictions. (optional)

maxwidth (decimal) Maximum image width in centimeters. If the image is wider than this maximum width, the image is scaled smaller to so that its width is this maximum width. (optional)
maxheight (decimal) Maximum image height in centimeters. If the image is higher than this maximum height, the image is scaled smaller to so that its height is this maximum height. (optional)
crop (boolean) Determines whether unnecessary borders are removed from the image. Needed if the image contains too much white (or other background color) space. If the whole image is white color it is removed. Either "true" or "false". Default is false. (optional)
backgroundcolor (string) Defines the background color of the image for cropping unnecessary borders. The background color is defined as an RGB hexadecimals, e.g. "00FFb7". Default is white ("FFFFFF"). (optional)
replace (boolean) Enables the replace mode. There is a possibility to use an existing picture (a.k.a. placeholder picture) in the template which is replaced by the actual picture (this is the replace mode). The replace mode makes it possible to use Word styles and formatting, which will end up in the actual picture in the report. The placeholder picture must be in the next paragraph of the Graph tag. The placeholder picture may be any picture, and its size don’t matter.

Replace mode is activated with “true” or deactivated with “false”. Default is false. (optional)

noimagemessage (string) A text appearing instead of the image if no image could be added to the report for some reason. The reason may be that QPR Web Service didn’t return any image or there was nothing left of the image after the cropping. This message is the only case when style attribute is needed for this tag.

For instance the message could be "No image is available". (optional)

visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
style (string) Determines text style. See Defining Styles. (optional)

Examples

<#image object=="[diagramid]" options="graphtype=processlevel,witdh=1500" dpi="100" maxwidth="17.5">
<#image source="embedded" object=="[attachmentId]" options="embeddeddata" maxwidth="17.5" dpi="1.5*[horizontaldpi]">
<#image imagedata=="httpFileData('http://someurl…')">

Link Tag

This tag is used to add a hyperlink in the report (both QPR Portal and external links). If the link address is empty or not valid for any reason, no link is displayed by the tag.

Attribute Description
address (string) Hyperlink’s url address or location. Note that the address must not be added as a Word’s hyperlink format but as a text. QPR Portal links can be got using an expression and GetPortalUrl function.
text (string) Link’s displayed text.
screentip (string) Link’s screen tip (see Insert hyperlink window). (optional)
target (string) Link’s target (see Insert hyperlink window). (optional)
visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
style (string) Determines text style. See Defining Styles. (optional)

Example

<#link address=="getportalurl([diagramid], '', '')" text="Link to QPR Portal" target="_BLANK" screentip="Click here is go to QPR Portal">

Loop Tag

This tag is for looping through content in following alternative ways:

• Set of QPR elements defined by a QPR Web Service query, or For more information of QueryObjects, see http://kb.qpr.com/qpr2017-1/index.html?queryobjects.htm.
• List of strings (using attribute list). The list itself is a string where items are separated using defined character.

The defined subreport is called for every looped object.

Word sections are not transferred from subreports to a mainreport.

Attribute Description
template (string) Template name for the looped content for file system templates (without the file type extension). For more information see Working With Report Templates. (optional)
templatedata (byte array) Looped template file as a byte array. See available function from Binary Data Functions. (optional)
portaltemplate (string) Template name for the looped content for templates stored in QPR Portal in Reports tab. Templates are referenced using syntax "category/header", where category is the name of the category and header the name of the header in the report template.
query (string) This is passed as a parameter query of QueryObjects operation. "List" attribute is alternative. (optional)
criteria (string) This is passed as a parameter criteria of QueryObjects operation. This filters the output set of objects. (optional)
sortby (string) This is passed as a parameter sortby of QueryObjects operation. This determines the order of the looped objects. Note that without sorting the report may look different between runs. (optional)
attributes (string) This is passed as a parameter attributes of QueryObjects operation. All the queried attribute values are added as parameters to the subreport. (optional)
options (string) This is passed as a parameter options of QueryObjects operation. (optional)
loopparameter (string) Defines the name of the parameter that is passed to the subreport containing the looped object id. Like other parameters, also this parameter needs to be defined in the subreport.
filter (exp.string) Additional filtering for returned objects. This filtering is applied after QPR Web Service query results have been returned to QPR Word Reports. The filter is defined as an expression (it’s not a QPR Web Service criteria), which must evaluate to boolean. The filter expression is evaluated for each returned object separately. If it is evaluated to false, the object is filtered out.

All looped subreport parameters, and report parameters and expressions are available as arguments for the expression. This additional filtering may be used if the desired condition cannot be written using QPR Web Service’s criteria. Using QPR Web Service’s criteria is recommended because it offers better performance. (optional)

sortvalue (exp.string) An expression which results are used to sort the returned objects. This sorting is applied after the QPR Web Service’s sorting (which is determined by attribute sortby). All looped subreport parameters, (main)report parameters, (main)report variables are available as expression arguments (subreport parameters have a priority in name conflicts).

Note that because sortvalue is an expression itself, only a single equation sign is usually used.

list (array) List of items as an array that is looped through (instead of a QPR Web Service query). When this attribute is used, no QPR Web Service query is made, and thus the following attributes are ignored: query, criteria, sortby, attributes and options. (optional)
visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
style (string) Determines text style. See Defining Styles. (optional)
(all other attributes) All other attributes are passed to the looped subreports as parameters. Note that all the above mentioned attributes are not passed as parameters, because they have a special meaning in the Loop tag. (optional)

Example

<#loop template="Process report" loopparameter="diagramid" query=="'[' + [diagramid] + '].ChildObjects(hierarchy=\"processlevels\")'" sortby="name" attributes="description,owner.name(as=\"processowner\")" recursionlevel=="[recursionlevel] + 1">

In the example, "recursionlevel" is an additional parameter that is passed to the subreport. Also parameters "description" and "processowner" are passed to the subreport.

Parameter Tag (par)

This tag defines parameters for the report. Only parameters that are defined using these tags are used by the report. The report request url may contain other parameters which are omitted.

Attribute Description
inputname (string) Name of the parameter (url parameter name or a parameter name passed to subreport). Any tag attribute names mentioned in this documentation cannot be used as parameter or variable names (reserved names).
outputname (string) Parameter name used in the report. If output value is not defined, output value is same as input value. Any tag attribute names mentioned in this documentation cannot be used as parameter or variable names (reserved names). (optional)
defaultvalue (object) The default value is used when a parameter value is not passed to the report. If no default value is defined and no parameter value is passed, an error occurs. (optional)
validation (exp. string) Validation expression for the parameter. There is an argument “value” available for the expression containing the parameter value. If the expression returns false, a validation error is thrown by the report.
validationmessage (string) Error message to show, if the validation fails (see parameter “validation”).

Example

<parameter inputname="nameOfUrlParameter" outputvalue="nameInTheReport" defaultvalue="value1">

Reportsettings Tag

This tag is for defining report level settings. In subreports this tag is not used (it’s ignored). The tag is optional. Only one tag of this type can exist in a template.

Attributes validate and contentdispositiontype are for advanced purposes, so usually they can be ignored.

Attribute Description
name (string) Name of the downloaded file. If this is not defined, file name is same as name of the template. (optional)
updatefields (boolean) Defines that Word updates all the fields in the document (table of contents, references, etc.) when the document is opened. Alternatives are “true” or “false”. Default is false. Note that Word shows an message when a document is opened that fields are to be updated, and gives also an alternative of not to do it. (optional)
dateformat (string) Format for showed dates. More information about formatting http://msdn.microsoft.com/enus/library/8kb3ddd4(v=vs.100).aspx. Default value is dd-MM-yyyy. (optional)
validate (boolean) Defines whether a Word document validation is performed. Validation reveals if a Word document contains structural errors (the xml document is inconsistent with the schema).

It seems that Word sometimes produces files that are not valid (!). If that kind of file is used as a template, it is probable that the output file produced by QPR Word Reports is not valid either and the validation fails. If the validation fails, an error message is shown and the report cannot be downloaded. By default validation is not performed.

Validation is useful, if QPR Word Reports produces a file that Word is not able to open. The validation error message may provide further information of the problem. (optional)

contentdispositiontype (string) Alternative values are "attachment" and "inline". This is the http response’s content disposition’s disposition type. Default value is attachment. For more information http://www.ietf.org/rfc/rfc2183.txt. (optional)

Example

<#reportsettings name="Process report" updatefields="true" contentdispositiontype="inline" validate="true">

Subreport Tag

This tag is for adding content of another report (subreport) to a report (main report). This tag is useful e.g. when there are parts in a report that are structurally similar (making it possible to reuse a subreport where a part is defined once).

Word sections are not transferred from subreports to a mainreport.

Attribute Description
template (string) Report template name (without file type extension in file system templates). For more information see Working With Report Templates. (optional)
templatedata (byte array) Looped template file as a byte array. See available function to get data in Binary Data Functions. (optional)
passallparameters (boolean) Passes all report parameters and variables to the sub report.
visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
style (string) Determines text style. See Defining Styles. (optional)
(all other attributes) All other attributes are passed to the subreport as parameters. Note that all the above mentioned attributes are not passed as parameters, because they have a special meaning in the Subreport tag. (optional)

Example

<#subreport template="Report 2" param1="value1" param2="value2" param3="value3">

Variable Tag (var)

This tag is used to add variables to the report. One tag defines one variable. Variables can be referenced in other tags using expression arguments. Variables are processed in the order they appear in the report. It’s a good practice to place the variables in a same place in the beginning of a report. Variable values cannot be changed during the report run – their value is calculated when the report run begins.

Variables are useful, when there is an expression which value is used several places in the report. Variables may also be used to simplify formulas, when part of a formula is defined as a variable.

There must not be a variable having a same name as a parameter in the same report. The scope of variables is the report template. If the variable must available in a subreport, the variable must be passed as a parameter to the subreport.

Attribute Description
name (boolean) Variable name. Any tag attribute names mentioned in this documentation cannot be used as parameter or variable names (reserved names).
value (object) Variable value.

Example

Report has tag <#variable name="variable1" value="2">. This variable can be used in other expressions, e.g. <#expression value="3 + [variable1]"> (result is 5).

Direct Tags

It’s possible to reference to report parameters and variables using tags which name equal parameter or variable name. These are called direct tags. For example if a report has a variable var1, a tag <#var1> can be used. Note that, the same result can be achieved using variable tag: <#variable value=="[var1]">. Purpose of direct tags is to shorten tag syntax.


Note the following when using direct tags:

• Direct tags cannot contain any attributes, unlike all other tags always contain at least one attribute.
• In the direct tags, variable value is implicitly converted into string. E.g. date variables are converted using date format defined in the Reportsettings tag.
• In direct tags it’s not possible to define styles using styles attribute.
• It’s not possible to use "visible" attribute.
• When writing direct tags, note the letter case in parameter and variable names.

Expression Language

QPR Word Reports uses an expression language enabling all tag parameters can be written using expressions (formulas). Expressions makes it possible to define values dynamically (values that are calculated when the report is run). Expression parameters are defined using two equal signs instead of one. As an example, a parameter value may be static, such as visible="false", or the parameter value may come from an expression, such as visible=="[typename]='Activity'".

When writing tags, characters escaping is needed. There are three levels where escaping need to be done, explained in the table below.

Level Purpose Needed escapings Example
1 QPR Web Services Characters \ and " are written \\ and \" QPR Web Service query:

[PG].models(criteria="name=\"model'1\"")

2 Expressions Characters \ and ' are written \\ and \' Previous query escaped for an expression:

[PG].models(criteria="name=\\"model\'1\\"")

3 Template tags Character " is written \" Previous expression escaped for template tag:

[PG].models(criteria=\"name=\\\"model\\'1\\\"\")

Note that the escaping for expressions (level 2) is only needed, when the query is an expression.

Data from main report to subreport may be passed in any datatype, so it doesn’t have to be converted to string. Still, parameters passed to a main report from a url are always strings.

If an expression returns a datetime value, it is implicitly converted to string if it’s shown in Word report (reportsettings tag’s dateformat parameter is used here).

Setting Content Visibility

All applicable tags support the parameter visible, which is used to remove the whole content that the tag is about to render. Available values are true (shown) or false (hidden). The value may be a static or a result of an expression.

When a tag is set to hidden, its expressions are not evaluated nor is related QPR data fetched. Thus the tag may contain errors, which are not emerged when the tag is hidden.

The tag is optional, and by default its value is true (the tag is shown).

Defining Styles

There are two ways to defined styles in the reports:

Apply styles in the template (static way): All the styles applied in the template are in the final reports. Styles can also be defined for tags, meaning that the style is applied for the content produced by the tag. If a style of a text block is always same, it is easiest to use this way.
Use the "styles" parameter (dynamic way): All applicable tags support the parameter style, which is used to dynamically determine the style of the paragraph or characters that is rendered by the tag. Value of the style tag is simply the name of the style. Both paragraph and character styles may be used.


Note that the style must exist in the Word file, so that it can be used dynamically. All the available styles in Word are not necessary stored in the Word file, although they are visible in the ribbon in the Word application. To make sure the style definition is in the Word file, the style needs to be used somewhere in the template. If you need to add text to the report that should not to be in the final report, it is possible to use Variable tags for this purpose because they disappear in the final report.

As an example, in recursive reports the heading level is determined by how deep the processing is in the recursive tree. For that you can add style==‘Heading ’ + [recursionlevel]", where recursionlevel is a parameter or a variable increased by one on every subreport call when going deeper in the hierarchy.

Authentication and Data Security

When running reports there are three levels of security to consider:

QPR Web Service: Data to the report is fetched using QPR Web Service, and thus available data is restricted by the rights of the user that is used to authenticate to the QPR Web Service. Authentication options to the QPR Web Service is explained above.
Report template files: If the report templates are in the file system, there are no security restrictions to the report template files itself (the files cannot be accessed, but the result report may reveal the content of the template). If the report template files are in QPR Portal, normal QPR Portal access control is applied (Publish to section in the Modify Word report template window, in the tab Reports > Manage Reports), and this way access to report templates can be restricted. This is useful when report templates itself contain classified information.
Links to reports: Reports are run by referencing them using a url, and these links may be in the portal (e.g. in the reports dropdown list). It’s advisable that users which don’t have rights to a report, don’t see the report link at all (otherwise they get a “not found” error message). This is actually a usability issue, not security issue.

Best Practices for Developing Reports

Use the QPR Web Services tester

Most of the errors are related to QPR Web Services query or the expression language syntax. That’s why, before placing queries to QPR Word Reports templates, use the QPR Web Services tester to check that the query syntax is right.

Use descriptive names for templates and variables

Prefer names that describe content of report or variables.

Use subreports to reuse report parts

If there are parts in a report that are structurally same, they can be put in a subreport and reused by calling the subreport with different parameters.

Use variables to split complex tags

If there are complex formulas, parts of the formulas can be put in variables. This makes the report templates easier to read. Also, if there is same static formula in multiple tags, the formula may be put in a variable, so that it is calculated only once for the report offering performance advantages.

Prefer to run the logic in QPR Web Service

When possible, write logic to run in QPR Web Service instead of formulas, because it offers performance advantages when round trips to Web Service are reduced. 

QPR Web Views

QPR Web Views is a technology for QPR Suite to generate web pages (html pages) displaying content from a QPR system. QPR Web Views works as an IIS web application, and data is fetched from QPR using QPR Web Service interface. The web views are based on file templates containing html, css and javascript. QPR Web Views works like QPR Web Application Server (WAS), but QPR Web Views tags are QPR Web Service based and more generic purpose.

QPR Web Views contains following main features:

• Web views are generated as they are requested, so the web views always contain the latest data.
• Web views can have parameters affecting web view’s contents.
• QPR Web Views contains an embedded formula calculation engine. All tags may be defined using expressions which values are calculated as the views are generated.
• Web views may get their content from other templates, and templates can also be called recursively (templates calling themselves). Templates makes it possible to assemble views from smaller parts.

QPR Web Views is based on the following concepts:

• Web views are based on templates, which are normal text files which contain html, css and javascript. When a view is generated, a template is taken as a basis, and in processing, content from QPR system is added to the template, forming the final web page.
• Templates contain tags, which instruct how the content is added to the page and how dynamic parts of the view are built.
• Tags contain attributes, which offer additional information of the tag.
• QPR Web Service is used through its operations. Operations called by QPR Web Views directly are GetAttributeAsString and QueryObjects. Also GetPortalUrl, GetGraphAsStream and GetBinaryDataAsStream can be utilized but they are called by the result web page from user browser. All QPR Web Service operations are documented under http://kb.qpr.com/qpr2017-1/index.html?functions.htm.
Loop is a list of QPR system objects (elements) returned by QPR Web Service’s QueryObjects operation. In a loop a template is called for every looped object.

QPR Web Views views can be embedded as part of QPR Portal or called from External Reports Menu (accelerator) dropdown list. It’s able to automatically pass parameters, such as model or selected object to the report.

Web views are accessed using a url, which determines the template to be run (parameter tpl), and the url also passes all needed parameters to the template. The url depends on the server computer name and the path where QPR Web Views is installed in IIS. Note that parameter names are case sensitive.

An example url: http://SERVERNAME/QPRWebServicesExtensions/DynamicWebViews.ashx?tpl=viewname&parameter1=param1value&parameter2=param2value

Parameters

Following table contains QPR Web Views’s parameters, which are configured in the web.config file of QPR Web Services Extensions

Parameter Name Description
dwvtemplatesphysicalpath Folder in the file system where template files are located. Also this folder needs to be created.
templatecaching Determines whether template files caching is enabled (true) or disabled (false). Template caching means that templates are read from file system to server memory when the IIS web application starts. When template caching is enabled, pages are processed faster and disk load as reduces. Template caching should be enabled for production environments.

Template caching is usually be disabled for development work, so that changes in templates can be seen immediately in result pages. When template caching is disabled, all templates are read into memory every time, when a page is requested.

qprwebapplicationname Name of the QPR web application in IIS. In the default installation for QPR 2017, it is QPR2017-1. This parameter is not mandatory, but it should be defined, as it can be used by html content to reference QPR resources published in IIS (such as images and css files).

Working with Templates

QPR Web Views is based on text templates files. When a web view is called, the template is taken as a basis, and all tags in the template are processed.

Templates are stored in disk drive where there the IIS is running. Template files have extension tpl. The result content fetched using http have in http response header Content-Type: text/html; charset=utf-8.

Templates may be organized in folders. Folders inside folders are also supported. Template root location is determined by configuration setting templatephysicalpath.

There are two ways to reference templates:

absolute: Absolute paths start with / and they contain the full path to the template starting from the templates root. Example: /folderName1/folderName2/templateName.

relative: Relative paths don’t start with /, and they reference to a template in relation to the template where the reference is made. Example: to reference a template in the same folder use syntaxt templateName.

When a web view processing fails, an html page showing an error message is displayed. The error message a likely to reveal, in which template and tag the error occurred. Also stack trace is shown, but that is helpful information only for developers.

Template Tags

Tags are used to add content to the web view. Syntax for defining tags is following:

<#tagname attribute1="attribute1 value" attribute2="attribute2 value" attribute3="attribute3 value">

If an attribute value is calculated using an expression (formula), two equals signs are used instead of one, e.g.

<#tagname attribute1=="1+1" attribute2="1+1"> (attribute1 value is integer 2, attribute2 value is string "1+1").

In the following sub chapters optional tag attributes are marked with "(optional)". Other tags are mandatory – if any of them is missing, an error is caused. In addition, for some of the mandatory attributes the value of the attribute must not be empty (i.e. attributeName="" is not allowed). If the attribute value comes from an expression, note that the expression may evaluate as empty (thus causing an error in non-empty mandatory attribute).

When processing a tag, attribute expressions are calculated from left to right. All template parameters, template variables and already calculated attribute values are available as expression arguments when later attribute expressions are calculated. An argument is kind of an input variable that can be used in an expression with syntax [argumentName]. E.g.

<#expression attribute1="3" value=="[attribute1] + 2"> (result is 5)

Tag specific arguments have a priority over template parameters and template variables, when there are same names used.

Attribute names should only contain characters a-z, 0-9 and _.

Values for all boolean valued tags (such as visible) may be defined as "true" or "false" or as a boolean valued expression, e.g. visible=="[variable1]=1" (the result of the equality comparison is of type boolean). Tag visibility is processed in the normal left to right order. When visibility tag is processed and its value is false, the tag processing stops at that point. This has an effect on processing performance. In addition, possible later expression errors don’t emerge in that case.

Following abbreviated tag names can be used: attribute=att, expression=exp, parameter=par and variable=var. Tags must be written in lower case.

Attribute Tag (att)

This tag is used to get properties of QPR objects by using QPR Web Service’s GetAttributeAsString operation (more information: http://kb.qpr.com/qpr2017-1/index.html?getattributeasstring.htm).

This tag uses QueryObjects instead of GetAttributeAsString, when there are multiple id’s in the object attribute or attribute followrelations used (more information below). If QueryObjects is used, separator and sortby attributes are applied.

Attribute Description
object Object id or list of object id’s separated by comma (,). If this contains only one id, it is passed as a parameter objectId of GetAttributeAsString operation. If this attribute is not provided or is empty, the tag will be evaluated to empty. (optional)
attribute This is passed as parameter attribute of GetAttributeAsString or QueryObjects operation.
options This is passed as parameter options of GetAttributeAsString or QueryObjects operation. (optional)
expression An expression where the result is placed as an argument "value" (see the examples). (optional)
followrelations Name of the relation attributes to follow to get another object or a set of other objects. If there are multiple subsequent relations to follow, the relations are separated by comma (,) (see the examples). (optional)
separator Character(s) separating list of attributes of different objects. Empty values are not shown (i.e. no empties between separators). Default is a new line (<br />). (optional)
sortby Sorting if there are multiple objects returned. Default sorting is by name. (optional)
visible Determines content visibility shown by the tag. See Setting Content Visibility. (optional)

Examples:

<#attribute object=="[objectId]" attribute="name">
<#attribute object="[measureId]" attribute="measure.value(series=\"ACT\",period=\"1 / 2017\")" expression="[value] * 2.54">
<#attribute object=="[measureId]" followrelatiosn="childobjects,childobjects"attribute="measure.value(series=\"ACT\",period=\"1 / 2017\")" separator="," sortby="name">

Dataset Tag

This tag is for showing information from a dataset in the web view. Dataset consist of multiple columns and rows. Dataset tags shows a header template (once) and row templates for each row in the dataset. Dataset tag has following attributes

Attribute Description
data (dataset) Dataset shown by the tag.
headertemplate (string) Template for dataset header. The header template is shown once before the row templates. Header template can be used e.g. for showing a column names. Header template is optional, so if the parameter is left out, no header template is shown.

Following type of parameters are passed to the header template:

- columnheader_1, columnheader_2, … columheader_n containing names of the dataset columns

- headerarray containing column names as an object array

Note that if the column names are known, the header can be set fixed in the web view and header template is not needed. (optional)

headertemplatedata (byte array) Alternative to headertemplate. Contains the used header template as a byte array. (optional)
rowtemplate (string) Template for dataset rows. The template is looped for each row in the dataset in the order rows are in the dataset. Following parameters are passed to the rows template:

- column_1, column_2, … column_n containing dataset cell data

- parameters which names are same as column names, containing dataset cell data

- same parameters that are passed to the header template, i.e. columnheader_1, columnheader_2, … columheader_n

- dataarray containing row’s data s an object array

- headerarray containing column names as an object array (optional)

rowtemplatedata (byte array) Alternative to row template or rowtemplateexpression. Contains row template as a byte array. (optional)
rowtemplateexpression (string) Expression defining a row template. Alternative to row template or rowtemplatedata. (optional)
footertemplate (string) Template for dataset footer. The footer template contents is shown once after the row templates. Footer template can be used e.g. for adding table end tags. The footer template is optional, so if the parameter is left out, no footer template is shown. The footer template has the same parameters as the header template.

Following type of parameters are passed to the footer template:

columnheader_1, columnheader_2, … columheader_n containing names of the dataset columns. (optional)

footertemplatedata (byte array) Alternative to footer template. Contains the used footer template as a byte array. (optional)
visible (boolean) Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
(all other attributes) All other attributes are passed to the looped template as parameters. Note that all the above mentioned attributes are not passed as parameters, because they have a special meaning in the Dataset tag. (optional)

Expression Tag (exp)

This tag is for adding a result of an expression (formula) in the web view. The defined expression is evaluated and the result is set in place of the tag. Also static values can be used.

Note that the tag may contain any attributes which are serving as arguments to the value expression.

Attribute Description
value value of the tag
visible Determines content visibility shown by the tag. See Setting Content Visibility. (optional)

Examples:

<#expression value=="[variable1]">
<#expression value1=="5" formula=="2 * [value1] + 3"> (result is 13).

Loop Tag

This tag is for looping through content in following alternative ways:

• Set of QPR elements defined by a QPR Web Service query (more information of QueryObjects, see http://kb.qpr.com/qpr2017-1/index.html?queryobjects.htm
• List of strings (using attribute list). The list itself is a string where items are separated using defined character.

The defined template is called for every looped object.

Following additional parameters are passed to the template:

loopindex is an integer starting from zero
attributesasarray contains all attribute values as an array
Attribute Description
tpl Template name for the looped content (without the file type extension in file system templates). For more information see Working with Templates. (optional)
tplexpression Expression defining the template. Alternative to tpl or templatedata. (optional)
templatedata Looped template file as a byte array. See available function from Binary Data Functions. (optional)
query This is passed as a parameter query of QueryObjects operation.
criteria This is passed as a parameter criteria of QueryObjects operation. This filters the output set of objects (optional)
sortby This is passed as a parameter sortby of QueryObjects operation. This determines the order of the looped objects. Note that without sorting the web views may look different between runs. (optional)
attributes This is passed as a parameter attributes of QueryObjects operation. All the queried attribute values are added as parameters to the template. (optional)
options This is passed as a parameter options of QueryObjects operation. (optional)
loopparameter Defines the name of the parameter that is passed to the template containing the looped object id. Like other parameters, also this parameter needs to be defined in the template.
filter Additional filtering for returned objects. The filtering is applied after QPR Web Service query has been run. The filter is defined as an expression (it’s not a QPR Web Service criteria), and the expression must be evaluated to boolean. All looped template parameters are available as arguments for the expression.

The filter expression is evaluated for each returned object separately. If it is evaluated to false, the object is filtered out.

This additional filtering may be used if the filtering condition cannot be written using QPR Web Service’s criteria. Using QPR Web Service’s criteria is recommended because it offers better performance. (optional)

sortvalue This is an expression which results are used to sort the returned objects. This sorting is applied after the QPR Web Service’s sorting (that is determined by attribute sortby). All looped parameters, template parameters, template variables are available as expression arguments (template parameters have a priority in name conflicts).

Note that because sortvalue is an expression itself, only a single equation sign is usually used.

list List of items as an array that is looped through instead of a QPR Web Service query. When this attribute is used, no QPR Web Service query is made, and thus the following attributes are ignored: query, criteria, sortby attributes, and options. (optional)
visible Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
(all other attributes) All other attributes are passed to the looped template as parameters. Note that all the above mentioned attributes are not passed as parameters, because they have a special meaning in the Loop tag. (optional)

Example:

<#loop template="ProcessReport" loopparameter="diagramid" query=="'[' + [diagramid] + '].ChildObjects(hierarchy=\"processlevels\")'" sortby="name" attributes="description,owner.name(as= \"processowner\")" recursionlevel=="[recursionlevel] + 1">

In the example, "recursionlevel" is an additional parameter that is passed to the template. Also parameters "description" and “processowner” are passed to the template.

Parameter Tag (par)

This tag defines parameters of the template. Only parameters that are defined using these tags are used by the template, i.e. the url may also contain other parameters that are omitted.

Attribute Description
inputname Name of the parameter (url parameter name or a parameter name passed to template)
outputname Parameter name used in the template. Makes it possible to use a parameter with different name than the input name. If output value is not defined, output value is same as input value. (optional)
defaultvalue The default value is used when a parameter value is not passed to the template. If no default value is defined and no parameter value is passed, an error occurs. (optional)

Example

<parameter inputname="nameOfUrlParameter" outputvalue="nameInTheTemplate" defaultvalue="value1">

Templatesettings Tag

This tag defines template level settings. The tag is optional. Only one tag of this type can exist in a template.

Attribute Description
allInputDataAsParameters (boolean) If true, all parameters that are provided to the template, are initialized as parameters. Each parameter contained in the url should be mentioned by name when using the parameter tag, but with allInputDataAsParameters attribute all parameters are initialized as template parameters without explicitly defining each. Default value is false. (optional)
dateformat (string) Format for shown dates. More information about formatting http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.100).aspx. (optional)
contenttype (string) Http response’s Content-Type header when QPR Web Views data is requested. This setting only have an effect in the top level template. Default value is text/html. E.g. application/xml can be used for xml data. Available values: http://www.freeformatter.com/mime-types-list.html. (optional)
contentdisposition Alternative values are "attachment" and "inline". This is the http response’s content disposition’s disposition type. Default value is attachment. For more information: https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition. (optional)
filename (string) Filename is used with the contentdisposition attribute defined above. More information: https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition. (optional)

Template Tag

This tag is for adding content from another template. This tag is useful e.g. when there are parts that are structurally similar (making it possible to reuse a template where a part is defined once).

Attribute Description
tpl Template name (without file type extension in file system templates). For more information see Working with Templates. (optional)
templatedata Looped template file as a byte array. See available functions in expression language documentation in QPR Web Services Extensions Expression Language. (optional)
passallparameters Passes all parameters and variables in this template to the called sub template.
visible Determines content visibility shown by the tag. See Setting Content Visibility. (optional)
(all other attributes) All other attributes are passed to the called template as parameters. Note that all the above mentioned attributes are not passed as parameters, because they have a special meaning in the template tag. (optional)

Example:

<#template template="Template2" param1="value1" param2="value2" param3="value3">

Variable Tag (var)

This tag is used to define variables in the template. Variables can be referenced in other tags using expression arguments. Variable tags are processed in the order they appear in the template. It’s a good practice to place the variables in a same place in the beginning of a template. Variable values cannot be changed during the generation – their value is calculated when the run begins.

Variables are useful, when there is an expression which value is used several places in the template. Variables may also be used to simplify formulas, when part of a formula is defined as a variable.

There must not be a variable having a same name as a parameter in the same template. The scope of variables is the template. If the variable must available in other called templates, the variable must be passed as a parameter to the template.

Attribute Description
name Variable name
value Variable value. May be a static value or calculated from an expression.

Example:

Template has tag <#variable name="variable1" value="2">. This variable can be used in other expressions, e.g. <#expression value="3 + [variable1]"> (result is 5).

Expression Language

QPR Web Views uses an expression language enabling all tag parameters can be written using expressions (formulas). Expressions make it possible to define values that are calculated when the web view is generated. Expression valued parameters are defined using two equal signs instead of one. As an example, a parameter value may be

- static (i.e. not an expression), such as visible="false", or
- calculated using an expression, such as visible=="[typename]='Activity'".

See QPR Web Services Extensions Expression Language for available functions and more information of the expression language.

When writing tags, character escaping is required. There are three levels of escaping, explained by the following table.

Level Purpose Needed escaping Example
1 QPR Web Services input Described in the QPR Web Services documentation QPR Web Services query:

[PG].models(criteria="name=\"model1\"")

2 Expressions Characters \ and ' need to be written as \\ and \' Above query escaped for an expression:

[PG].models(criteria="name=\\"model1\\"")

3 Template tags Character " need to be written as \" Previous expression escaped for template tag:

[PG].models(criteria=\"name=\\\"model1\\\"\")

Note that the escaping for expressions (level 2) is not used, when the query is not in an expression.

Data from templates to other templates may be passed with any datatype, so data don’t have to be converted to string. Parameters passed to a template from a url are always strings.

If an expression tag returns a datetime value, it is implicitly converted to string if it’s shown in the web view. Templatesettings tag’s dateformat parameter is used for this.

Setting Content Visibility

All tags support the parameter visible, which is used to hide the whole content the tag is about to render. Available values are true (shown) or false (hidden). Also visible parameters can be defined using expressions.

When a tag is set to hidden, its expressions are not evaluated nor is related QPR data fetched. The tag may thus contains errors, which don’t emerge if the tag is hidden.

Visible tag is optional, and by default its value is true (the tag is shown).

Calling QPR Web Services from Javascript

Some QPR Web Views based solutions need to call QPR Web Services from javascript. The following wrappers are available for usually needed QPR Web Services operations. It’s recommended to use the wrappers instead of calling QPR Web Services directly.

Web Services operation Description
GetBinaryData.ashx Wrapper for QPR Web Services’ GetBinaryData operation (http://kb.qpr.com/qpr2017-1/index.html?getbinarydata.htm).

Parameters:

• wssession
• objectid
• attribute
• options
GetGraph.ashx Wrapper for QPR Web Services’ GetGraph operation (http://kb.qpr.com/qpr2017-1/index.html?ws_getgraph.htm).

Parameters:

• wssession
• objectid
• attribute
GetPortalUrl.ashx Wrapper for QPR Web Services’ GetPortalUrl operation (http://kb.qpr.com/qpr2017-1/index.html?getportalurl.htm).

Parameters:

• wssession
• objectid
• viewname
•options

Example:

http://localhost/QPRWebServicesExtensions/GetBinaryData.ashx?objectid=PG.12345.6789&attribute=embeddeddata&options=

QPR Reports Menu

Reports Menu is a dropdown menu for QPR Portal to open reports and web pages that can be referenced using urls. The menu is able to add context related information automatically to the url as parameters, such as model, diagram and object id. Report items in the menu are configured in the file C:\DWV templates\ReportsMenu\ReportsMenuConfiguration.xml (the location may vary).

Reports Configuration

Report items in the menu are configured in the file C:\DWV templates\ReportsMenu\ReportsMenuConfiguration.xml (note that the location may be different). The configuration is an xml file having a root tag reportsmenu and subtags menuitem for individual report items. The menuitem tag has menu item settings as subtags which are described in the following table.

Settings can be defined using static string values or expressions. If the value is defined using an expression following attribute is added to the settings tag: expression="true".

Note that the setting tags must be in the order that they are defined in the following table. Settings that are not mandatory, can be left out.

Parameter Description
reportname (string) Visible name of the report in the dropdown menu.
reporturl (string) Url to the report. Characters ? and & are automatically added to the end of the url if needed for appending url parameters. To build SSRS report urls, see http://msdn.microsoft.com/en-us/library/ms153586(v=sql.110).aspx.
availabletabs (string) List of QPR Portal tabs where the report is available. Available tab names can be found in http://kb.qpr.com/qpr2017-1/index.html?getportalurl.htm (see "Also Portal internal view names..."). The tabs need to be defined in lower case. If this parameter is empty, the report is visible in all tabs.

Following default tabs name are available: pgplugin_processmaps, pgplugin_navigator, pgplugin_actions, pgplugin_organizations, scplugin_scorecards, scplugin_strategymaps, scplugin_analysis, scplugin_navigator, scplugin_reports, scplugin_actions, discussion, byuser, bytime, actionanalysis

passparameters (string) List of parameters that are passed to the external report (see available parameters in chapter Portal Context Parameters). Passed parameters may be restricted, because e.g. SSRS gives an error when parameters are passed that are not defined in the report.
order (integer) An integer which determines order of reports in the menu. The reports are shown in the ascending order. If there are multiple reports with a same order number, the alphabetical order of the reportName parameter determines the ordering (secondary sorting).
waitanimation (boolean) Determines whether to use report loading wait animation, which is able to improve user experience. The report source needs to support this feature (DWR and DER does). Alternatives true and false.

The animation may only be used if the report access url has same DNS name and port than QPR Portal. This is because the disappearance of the animation is based on a cookie that is set by report providing server when the report is ready. When the cookie is received from the response of the report providing server, the loading animation is removed.

If the loading animation doesn’t work correctly for any compatibility issue, disable it (set to false).

visibility (string) Determines the visibility status of the report item in the menu. Available statuses are:
• visible: The report is visible and can be opened.
• disabled: The report is visible in the menu in a grey color, and it’s not openable. This status can be used to imply, that there is some condition preventing from running the report, such as the required type of element is not selected.
• hidden: The report item is not visible in the menu at all.
visiblemessage (string) Tooltip text shows for visible report items when cursor is moved over the report item. The text may contain html code.
disabledmessage (string) Tooltip text shown for disabled report items when cursor is moved over the report item. The text may contain html code.
accessrights (boolean) Determines if user has access to see the report item as boolean value (“true” if there are rights to see the report item).
[OpenWindow parameters] QPR Portal’s OpenWindow function parameters. These parameters determine e.g. size and position of the window. Available parameters:
• target (string)
• width (integer)
• height (integer)
• scroll (boolean)
• preventcaching (boolean)
• x (integer)
• y (integer)

Portal Context Parameters

The following table lists information that can be passed to reports as url parameters.

Parameter Description
modelid PD/EA or Metrics model id
diagramid Diagram id (i.e. process level). Note that for the top diagram level, the id is "PG.<modelid>.0" which represents the model object.
objectid Object in details pane.
tab Name of the tab where the report is run.
zoom Zoom percentage. Only available in PD/EA tabs.
viewid View id
xsession QPR Web Service session id. External report can use this to login to QPR Web Service.
portalsessionid QPR Portal session id. Note that this is different than QPR Web Service session id. Portal session id is needed, when the url refers to the QPR Portal itself and no Windows authentication is used. QPR Portal session id is added to the url as a parameter named SES.
currentuserid Current user id.
scorecardid Scorecard id. Only available in Metrics.
seriesid Series id. Only available in Metrics.
periodid Period id. Only available in Metrics.
uilanguage
Modelinglanguage

QPR Web Services Extensions Expression Language

This chapter describes the expression language used by QPR Suite Reporting Add-on and several QPR Suite accelerators. Expressions (formulas) enable to define values dynamically, meaning that values can be calculated based on the information that is available only at runtime. The expression evaluation is case sensitive, which applies e.g. for parameter and function names. Actually all function names are not case sensitive, but it’s easiest to consider they are. Expressions can be combined using operators. Expression's priority order starting from the greatest priority is: primary, unary, power, multiplicative, additive, relational, logical and, logical or. Following operators can be used:

  • Logical or: or, || (these are equivalent)
  • Logical and: and, && (these are equivalent)
  • Relational: =, !=, <>, <, <=, >, >= (!= and <> are equivalent)
  • Basic calculations: +, -, *, /, %
  • Bitwise: & (bitwise and), | (bitwise or), ^ (bitwise xor), << (left shift), >> (right shift)
  • Unary: !, not, -, ~ (bitwise not)
  • functions: Abs(1), doSomething(1, 'dummy')

In expressions, characters \ and ' need to be written as \\ and \' (i.e. escaped).

The expression language supports .Net 4.5 datatypes. Following table contains examples, how to write literals of some basic datatypes.

Data type Example
integer 123456
double 123.456, 0.123
boolean true, false
string 'Hello world!'

When operating with dates, note that all dates returned by QPR Web Services are strings formatted in XML date format (yyyy-MM-ddTHH:mm:ss.fffzzz). They can be converted to datetimes using StringToDate function.

In the expressions unicode characters can be defined using syntax \uxxxx. For example, new line is \u000D\u000A (carriage return and line feed characters used in Windows systems).

Arithmetic Functions

Function Description Example expression Result
Abs Returns the absolute value of a specified number. Abs(-1) 1
Acos Returns the angle whose cosine is the specified number. Acos(1) 0 (double)
Asin Returns the angle whose sine is the specified number. Asin(0) 0 (double)
Atan Returns the angle whose tangent is the specified number. Atan(0) 0 (double)
Ceiling Returns the smallest integer greater than or equal to the specified number. Ceiling(1.5) 2 (double)
Cos Returns the cosine of the specified angle. Cos(0) 1 (double)
Exp Returns e raised to the specified power. Exp(0) 1 (double)
Floor Returns the largest integer less than or equal to the specified number. Floor(1.5) 1 (double)
IEEERemainder Returns the remainder resulting from the division of a specified number by another specified number. IEEERemainder(3, 2) -1 (double)
in Returns whether an element is in a set of values. in(1 + 1, 1, 2, 3) true
Log Returns the logarithm of a specified number. Log(1, 10) 0 (double)
Log10 Returns the base 10 logarithm of a specified number. Log10(1) 0 (double)
Max Returns the larger of two specified numbers. Max(1, 2) 2
Min Returns the smaller of two numbers. Min(1, 2) 1
Pow Returns a specified number raised to the specified power. Pow(3, 2) 9 (double)
Random Returns a random value between 0 and 1 (the value can be 0 but cannot be 1). Random() 0.358024762 (double)
Round Rounds a value to the nearest integer or specified number of decimal places. Round(3.222, 2) 3.22 (double)
Sign Returns a value indicating the sign of a number. Sign(-10) -1
Sin Returns the sine of the specified angle. Sin(0) 0 (double)
Sqrt Returns the square root of a specified number. Sqrt(4) 2 (double)
Tan Returns the tangent of the specified angle. Tan(0) 0 (double)
Truncate Calculates the integral part of a number. Truncate(1.7) 1

String Functions

Function Parameters Description
CharAt (string)
  • input string
  • index number (int)
Return character of the index number position in the input string. The indexing starts from zero.
Contains (boolean)
  • first string
  • second string
Return true if the first string contains the second string; otherwise false.
CropText (string array)
  • input string
  • min characters (int)
  • max characters (int)
  • crop characters (string array)
Crops a text into several parts. Length of the parts is between provided minimum and maximum characters. Text is cropped in positions where the crop characters appear (crop characters are excluded from the result).

If no crop characters are found, text is cropped from the max position.

EndsWith (boolean)
  • first string
  • second string
Return true if the first string ends with the second string; otherwise false.
IndexOf (int)
  • first string
  • second string
  • start index (int)
Return the index number of the first occurrence of the second string in the first string. Indexing starts from zero. If the start index is provided, the search is started from that index.
LastIndexOf (int)
  • first string
  • second string
  • index (int)
Return the index number of the last occurrence of the second string in the first string. Indexing starts from zero. If the start index is provided, the search is started from that index (calculated from the start of the string) towards the beginning of the string.
Length (int)
  • input string
Return the number of characters in the input string.
RemoveIllegalChars (string)
  • input string
  • allowed characters (string)
  • replace string
Removes from the input string all characters that are not part of the listed chars. If the replace string is provided, removed characters are replaced with that one. Examples:

RemoveIllegalChars('ABCDE', 'BD', nullvalue(), ") gives 'BD'

RemoveIllegalChars('ABCDE', 'BD', nullvalue(), ' ') gives ' B D '

Replace (string)
  • first string
  • second string
  • third string
Replaces all occurrencies of the second string with the third string in the first string. Example:

Replace('abcd', 'b', 'e') gives aecd.

StartsWith (boolean)
  • first string
  • second string
Return true if the first string starts with the second string; otherwise false.
Substring (string)
  • input string
  • start index (int)
  • length (int)
Returns a substring of the input string starting from the start index. If the length is provided, the returned string contains maximum of that number of characters.
ToLower (string)
  • input string
Return a string where all the characters of the input string have been converted to lower case characters.
ToUpper (string)
  • input string
Return a string where all the characters in the input string have been converted to upper case characters.
Trim (string)
  • input string
  • string array
Removes spaces from the start and end of the input string (if string array is not provided). If the string array is provided, the string in the array are removed from the input array.

Datetime Functions

Custom function Parameters Description
AddMilliseconds (datetime)
  • datetime
  • milliseconds (double)
Adds the specified number of milliseconds to a datetime.
AddMonths (datetime)
  • datetime
  • number of months (int)
Adds the specified number of months to a datetime. Negative number of months means number of months to subtract. This function takes into account years, so e.g. when adding one month to a date in December, the result is January in the next year.
AddTimespan (datetime)
  • datetime
  • timespan
Adds the specified timespan to a datetime.
CompareDates (int)
  • datetime 1
  • datetime 2
Compares two datetimes and returns zero when the datetimes equals, greated than zero if the former is later, and less than zero if the former is earlier.
CurrentDateTime (datetime) [none] Returns a datetime representing the current date and time as UTC. As there are no parameters, use syntax CurrentDateTime()
FromMetricsDateFormat (datetime)
  • numerical value (string, double, int)
Returns a date that this the provided number of days from 1.1.1900 00:00:00 UTC. QPR Metrics date values uses this date format.
GenerateTimeStampArray (datetime array)
  • start timestamp (datetime)
  • end timestamp (datetime)
  • generating type (string)
  • increment (timespan)
Generates an array of timestamps between the start and end timestamps. The first generated timestamp is earlier or equal than the start timestamp, and the last generated timestamp is later or equal than the end timestamp.

Available generating types:

- year
- halfyear
- third
- quarter
- month
- custom

If type is "custom", the fourth parameter is provided, which is the increment timespan (resulting equally spaced timestamps). Note that e.g. day, week, hour are equally spaced timestamps, so they can be generated using "custom" type.

GetDate (int)
  • datetime
See (1) below (property: Date)
GetDay (int)
  • datetime
See (1) below (property: Day)
GetMonth (int)
  • datetime
See (1) below (property: Month)
GetTicks (int)
  • datetime
See (1) below (property: Ticks)
GetYear (int)
  • datetime
See (1) below (property: Year)
NewDatetime (datetime)
  • year (int)
  • month (int)
  • day (int)
  • hour (int)
  • minute (int)
  • second (int)
  • millisecond (int)
  • local time (boolean)
Returns a new datetime with the following parameter values.
- year: 1 - 9999
- month: 1 - 12
- day: The day 1 through the number of days in month
- hour: 0 - 23 (default: 0)
- minute: 0 - 59 (default: 0)
- second: 0 - 59 (default: 0)
- millisecond: 0 - 999 (default: 0)
- local time (default: true)

First two parameters are mandatory.

SubtractTimespan (datetime)
  • datetime
  • timespan
Substracts the specified timespan from the datetime.
Timespan (datetime)
  • days (int)
  • hours (int)
  • minutes (int)
  • seconds (int)
  • milliseconds (int)
Creates a new timespan. A timespan represent an interval between two datetimes.
ToMetricsDateFormat (double)
  • datetime
Returns number of days between 1.1.1900 00:00 UTC and the provided datetime. QPR Metrics handles date values using this format. Also the function is needed for criteria in Web Service’s QueryObjects, e.g.

'createddate>' + ToMetricsDateFormat(CurrentDateTime())

(1) DateTime properties in .Net Framework 4.5: https://msdn.microsoft.com/en-us/library/system.datetime_properties(v=vs.110).aspx

Array Functions

An array is a list of objects of any type, such as strings or integers. Arrays may contain same valued items multiple times.

Function Parameters Description
ArrayExcept (array)
  • array 1
  • array 2
Returns an array which contains items that are in the array 1 but not in the array 2.
ArrayIntersect (array)
  • array 1
  • array 2
Returns an array which contains items that are in both provided arrays.
ArrayReverse (array)
  • array
Reverses an array, i.e. first element becomes last and so on.
ArraySize (int)
  • array
Calculates number of items in an array.
ArraySort (array)
  • array to sort
  • sort order (array)
Sorts an array provided as a first parameter. The second parameter provides an array that contains an explicit order for the items to be used in sorting. The rest of the items that are not listed, are sorted normally and placed in the end of the outputed array.
ArrayUnion (array)
  • array 1
  • array 2
Joins two arrays. Duplicates are not removed. If only one parameter is provided, that parameter is expected to contain an array, item of which are arrays. In that case all the items of all arrays are joined.
ArrayUnique (array)
  • array
Returns an array where duplicate values are removed. Order of items is preserved.
ArrayWhere (array)
  • array
  • expression (string)
Filters out all items in an array, where the expression is evaluated as false. The expression must return boolean value. Available arguments in the expression:
- item (item in the array)
- index (iteration order number starting from 0)
Average (double)
  • array
Calculates an average of array items. Array must contains numerical data.
Concatenate (string)
  • array
  • separator (string)
Concatenates items of an array into a string separated by a provided separator. Array must contain items that can be converted into string.
IndexOfAnyInArray (int)
  • array 1
  • array 2
Returns an index number of the any of objects of the second array in the first array. The second array objects are searched from left to right. First object array starting from index 0. If the first array doesn’t contain any of objects in the second array, -1 is returned.
IndexOfInArray (int)
  • array
  • item to search (object)
Returns an index number of an object in the array. First object array starting from index 0. If object is not found, -1 is returned.
ItemAt (object)
  • array
  • int
Returns an item from an array in the provided index number. The first item has an index number zero (0).
Median (double)
  • array
Returns the median of the provided array.
Minimum (double)
  • array
Returns the smallest number in the provided array.
Maximum (double)
  • array
Returns the largest number in the provided array.
OnlyValue (object)
  • array
Returns the only value of the array. Throws an exception if the array contains more than one value. Return null if the array contains no values.
Sum (double)
  • array
Calculates a sum of array items. Array must contains numerical data.
Transform (array)
  • array
  • expression(string)
Transforms every item of an array to another array using the provided expression. The expression has following arguments:
- value which gets the value of the item.
- index: iteration order number starting from 0

E.g. transform([inputarray], 'substring([value], 5)') returns first five characters from every string of the array.

Dictionary Functions

Function Parameters Description
Dictionary (dictionary) keys (keys array) values (object array) Creates a new dictionary based on two arrays. The two arrays must not be null, and must be the same length.
DictionaryContainsKey (boolean) dictionary key (string) PReturns true if the dictionary contains the provided key; otherwise the function returns false.
GetDictionaryValue (object) dictionary key (string) default value(object) Returns a value from a dictionary based on key. If the key is not found and a default value is defined, the default value is returned. If the key is not found and a default value is not defined, an exception is thrown.
ContainsValue (boolean) dictionary key (string) Returns true if the provided dictionary contains the provided value; otherwise false.

Conversion Functions

Function Parameters Description
Array (array) object 1 object 2 ... Converts provided objects into an array. This is needed for example in functions which need their parameters as arrays.
ArrayToDataset (dataset) array column name(string) Converts an array to a dataset. The dataset has one column which name is given as a second parameter.
ByteArrayToString (string) input byte array encoding (string) Converts byte array into string using the provided encoding. Supported encodings depend on the system. Possible encodings are unicode, utf-8, utf-7, utf-32, iso8859-1, ascii. If null is provided, null is returned. If zero valued array is provided, an empty string is returned.
ConvertToBoolean (boolean) input value (object) value in error (double) Converts a value to a boolean. The second parameter is optional and it is returned if the conversion fails. If it is not defined and the conversion fails, an error is thrown.
ConvertToDouble input value Converts a value to a double. The second parameter is
(double) (object) value in error (double) optional, and it’s returned if the conversion fails. If it’s not defined and the conversion fails, an error is thrown.
ConvertToInt (int) input value (string) value in error (int) Converts a value to an integer. The second parameter is optional, and it’s returned if the conversion fails. If it’s not defined and the conversion fails, an error is thrown.
ConvertToString (string) input value (object) date format (string) value in error(string)

Converts a variable of many datatypes to string as follows:

- null: empty
- DateTime: formatted using provided or XML date format
- Boolean: "true" or "false"
- Array: items comma separated enclosed with []

The second parameter is optional. The third parameter is optional, and it’s returned if the conversion fails. If it’s not defined and the conversion fails, an error is thrown.

DatasetColumnToArray (array) dataset column name(string) Converts a column of a dataset to an array. The column is referenced using the column name.
DatasetRowToArray (array) dataset row number (int) Converts a row of a dataset to an array. The row is referenced using the row number (the first is 1).
DateToString (string) date to convert (datetime) local time (boolean)dateformat(string) Converts a datetime variable to a string. The local time parameter is optional; if omitted, the string is assumed to represent local time. If the third parameter is omitted, the string is interpreted as XML schema date format. More information of how to construct the dateformat:

https://msdn.microsoft.com/en-us/ library/8kb3ddd4(v=vs.110).aspx

HtmlEncode (string) string Performs an HTML encoding. More information: https:// msdn.microsoft.com/en-us/library/ ee388364(v=vs.110).aspx
InitializeScriptEngine (ScriptEngine) script sources (string or string array) Initializes a script engine and returns a script engine object. The script engine object can be used to run script functions (see RunScript function). A single source can be provided as a string or several sources as a string array.
InstanceIdFromFullId (string) element full id (string) Extracts an instance id from a full id. The function returns an empty string, if the instance id cannot be extracted.

Example: InstanceIdFromFullId('SC.6346904. 5290187.4353690') returns 4353690

ItemsToArray (array) object 1 object 2 … Forms an array based on provided parameters. There must be at least one parameter. Number of parameters is unrestricted.
ModelIdFromFullId (string) element full id (string) Extracts a model id from a full id. The function returns an empty string, if the model id cannot be extracted.

Example: ModelIdFromFullId('SC.6346904. 5290187') returns 6346904

NumberToString(string) value (int, double) format (string) value in error (string) Converts a numerical value to string. This function can be used when there are special requirements related to e.g. thousand separators. Behaviour of this function depends on the server’s locale settings. If the convertion fails and the value in error is defined, that value is returned. Otherwise an error is thrown. More information about formattings:

https://msdn.microsoft.com/en-us/library/ dwhawy9k(v=vs.110).aspx#NFormatString https://msdn.microsoft.com/en-us/ library/0c899ak8(v=vs.110).aspx

ObjectIdFromFullId (string) element full id (string) Extracts an object id from a full id. The function returns an empty string, if the object id cannot be extracted.

Example: ObjectIdFromFullId('SC.6346904. 5290187') returns 5290187

PresentObjectAsString input object date format (string) value in error(object) Presents variables of many datatypes as string for debugging purposes as follows:
- null: "[null]"
- DateTime: formatted using provided or XML date format
- String: enclosed using quotes
- Boolean: "true" or "false"
- DataSet: {{}}
- Array: items comma separated enclosed with [] The second and third parameters are optional.
RunScript (object) script engine function name (string) function parameters (object)... Runs a script engine function. A script engine used to run the script needs to be provided (use function

InitializeScriptEngine). Function name is provided as a second parameter. Rest of the parameters are parameters for the function.

StringToArray (array) string to convert Converts a string to an array using provided separator string. If remove empties is true, there are no empty
separator(string) remove empties(bool) strings in the output array.
StringToDataFile input string encoding (string) Converts a string into a byte array using the provided encoding. Supported encodings depend on the system. Possible encodings are unicode, utf-8, utf-7, utf-32, iso8859-1, ascii.
StringToDate (datetime) string to convert local time (boolean)dateformat(string) alternative dateformat(string), ... Converts a string variable to a datetime. The local time parameter is optional; if omitted, the string is assumed to represent local time. If the third parameter is omitted, the string is interpreted as XML schema date format.

There can also be alternative date formats provided starting from the 4. parameter in case that date conversion fails. All the provided date formats are tested and an exception is thrown only if none of them could be used for the formatting. More information of how to construct the dateformat: https://msdn.microsoft.com/en-us/ library/8kb3ddd4(v=vs.110).aspx

ToBase64 (string) byte array Converts a byte array (data file) into a base64 encoded string. More information: https://en.wikipedia.org/wiki/ Base64
UrlEncode (string) string Performs a URL encoding. Corresponds to JavaScript

EnticeUriComponent() function. More information:https://msdn.microsoft.com/en-us/library/ system.net.webutility.urlencode(v=vs.110).aspx

QPR Web Services Functions

Function Parameters Description
BelongsToGroup (boolean) group name (string) Returns true if the current user belongs to the provided group; otherwise false. QPR Web Services query is made to get the information. The current user is the user account which is logged to the QPR Web Services when the query is made. False is returned if the provided group doesn’t exist. Upper and lower case characters need to be written correctly.

Example: BelongsToGroup("Administrators")

CurrentUser (string) attribute name (string) Returns any attribute of the current user object. If no attribute is provided, user id is returned (attribute "id").
Example: CurrentUser()
GetAttribute (string) objectid (string) attribute (string) options (string) QPR Web Service’s GetAttributeAsString operation, see http://kb.qpr.com/qpr2017-1/ index.html?getattributeasstring.htm.
GetAttributes (string array) objectid (string) attributes (string) options (string) Returns multiple attributes of a single object as an

array. Attributes are defined as comma separated. Based on QPR Web Service’s QueryObjects operation.

GetPortalUrl (string) objectid (string) view (string) options (string) QPR Web Service’s GetPortalUrl operation, see http://kb.qpr.com/qpr2017-1/index.html? getportalurl.htm.
LatestValuePeriod (string) objectid (string) series symbol or series id (string) Returns the id of the period that contains the latest (newest) measure value. Return an empty

string, if no period contains a value. This information can be used e.g. in web service attribute prettyvalue(period=' + [periodid] + ')

QueryObjects (string array) query (string) filter (string) sortby (string) attributes (string) options (string) Executes QPR Web Service’s QueryObjects and returns results as a string array. Only one attribute should be defined for "attributes" parameter.
QueryObjectsAverage (double) query (string) filter (string) attributes (string) options (string) Average of queried objects numerical attributes. Only one attribute is defined in "attributes". Error is thrown if attribute values are not numerical.
QueryObjectsConcatenate (string) query (string) filter (string) sortby (string) attributes (string) options (string) separator (string) Concatenates attribute values of all queried objects using defined separator. Only one attribute is defined in "attributes".
QueryObjectsCount (int) query (string) filter (string) options (string) Returns number of objects returned by

QueryObjects. "sortby" and "attributes" cannot be defined as they don’t affect the result.

QueryObjectsFirstAttribute (string) query (string) Attribute value of the first object of queried objects. Only one attribute is defined in
filter (string) sortby (string) attributes (string) options (string) "attributes".
QueryObjectsSum (double) query (string) filter (string) attributes (string) options (string) Sum of queried objects of numerical attributes. Only one attribute is defined in "attributes". Error is thrown if attribute values are not numerical.
QueryObjectsUnique (string) query (string) filter (string) sortby (string) attributes (string) options (string) separator (string) Concatenates unique attribute values of all queried objects using defined separator. Only one attribute is defined in "attributes".
SubAttributesAsArray (array) objectid (string) attribute name(string)subattribute name(string)filter expression(string)

Returns attribute values (as an array) which are presented in more complex structures. This type of attributes are e.g. graphicalproperties, customattributetypes and properties. The filter expression is for selecting desired rows. In the filter expression there are following arguments:

- all sub tag attributes (with their tag names)
- argument ordernumber having values 0, 1, … for xml tag order number.

Example, following graphicalproperties attribute: <graphicalproperties>

 <symbol id="920256947" x="790" y="190" width="400" height="30" instanceid="2029031131" />
 <symbol id="2009602777" x="230" y="640" width="200" height="40" instanceid="0" />
 <symbol id="368985118" x="730" y="40" width="100" height="60" instanceid="369716419" />

</graphicalproperties> Calling: SubAttributesAsArray([instanceid], 'graphicalproperties', 'width', '[instanceid] =0') returns 200.

SubAttributesAsDataset (dataset) objectid (string) attribute name(string) Returns attributes which are presented in more complex structures as a dataset. This type of attributes are e.g. graphicalproperties, customattributetypes, properties and hotspots.
columns (array) Dataset columns (names of the sub attributes) are listed as an array (in lower case).

Dataset Functions

Dataset is a flat table like object containing multiple named columns and multiple rows. Dataset can contains zero rows, but at least one column must exist. Dataset may contain any type of data. Dataset is like a table in relational database or a worksheet in Excel. Usually dataset functions don’t change the input dataset, but create new dataset (exceptions are mentioned). Thus the input datasets may also be used in other functions. Dataset functions get their idea from SQL language.

Function Parameters Description
AddColumn(dataset) dataset columnName (string) expression(string)compare mode(boolean) Adds a new column to the dataset based on an expression. All other column values are available as arguments in the expression. Also there is an argument rowordernumber, which is the row order number starting from 0.

In the compare mode (true), there are also values of the previous and next rows available as arguments with suffixes _previous, _current and _next. For example if there are columns column1 and column2, and adding a new column column3, there are arguments column1_previous, column2_previous, column3_previous , column1_ current, column2_current, column1_next and column2_next.

AddDatasetRow (dataset) column 1 value (object) column 2 value (object)… Adds a new row to a dataset with the provided values. A row is added to the provided dataset, i.e. no new dataset is created.
BuildHierarchy (dataset) dataset instance id column name(string) object id column name (string) parent object idcolumn name(string)Hierarchy id column name(string) Builds an element hierarchy based on data containing element instances. The data is provided in a dataset, where there are following columns: instance id, object id and parent object id. The object id can be derived from instance id.

The function constructs a new dataset where lines are in the hierarchy order. The function can handle situations where also other than the bottom level objects have been instantiated; it means that the result hierarchy will contain same instances multiple times. Following new columns are added: - hierarchy id (explained below) - hierarchy parent id (referring to the hierarchy id)

Hierarchy parentid column name(string) level column name (string) filtering formula(string)sorting formula(string) - level (top level is 0, the level directly below top is 1, …)

The hierarchy id means an id that is unique for the whole hierarchy. To enable this following technique is used: If there is an instance id PG.x.y.z which appears multiple times, the first hierarchy id is PG.x.y.z, the second is PG.x.y.z.1, the third is PG.x.y.z.2, and so on.

CreateDataset (dataset) column names (string array) Creates a new dataset with provided column names. The created dataset contains no rows.
DatasetCell (object) datasetcolumn name(string) row number (int) Gets an item from a dataset from the named column and index (first row is number 1). Returned type of data is the type of the cell.
DatasetRows (dataset) input dataset start row (int) rows count (int) Returns a new dataset containing the defined rows (based on start row and rows count). Index is zero based.
DatasetSize (int) dataset Number of rows in a dataset. If dataset is null, -1 is returned.
Distinct (dataset) dataset Removes duplicate rows, i.e. rows that contains identical data.
Except (dataset) dataset1 dataset2 Result dataset contains rows that are in the first dataset but not in the second dataset. The datasets must have same columns.
From (dataset) query (string) filter (string) sortby (string) attributes (string) options (string) Returns QPR Web Service’s QueryObjects result in a dataset as string values. Columns get their names from the result data. Column names can be changed with

QueryObjects syntax attribute(as="ColumnName").

matching expression(string)
GroupBy (dataset) dataset list of grouped columns (string array) list of combined column names(string array) list of combine expressions (string array) Groups the dataset. Parameters:
- 1. parameter is the dataset to group
- 2. parameter is an array of columns to group
- 3. parameter is an array of combined column names
- 4. parameter is combine expression (e.g. count, sum, average or number of rows). All expressions have as arguments an array of objects to combine. Parameter names equal to column names.

Example: GroupBy([dataset1], Array('modelname', 'typename'), Array('count', 'objectnames'), Array('ArraySize([name])', 'Concatenate([name])'))

InnerJoin (dataset) left dataset right dataset matching expression (string) Inner join of two datasets. The expression has columns names as available arguments. The datasets cannot have columns with same names. Efficiency is n2.
InnerJoinEquals (dataset) left dataset right dataset left column name(string) right column name (string)

Inner join of two datasets where the matching expression is left column equals right column. Efficiency is n.

Intersect (dataset) dataset1 dataset2 Intersect of two datasets. Result dataset contains only those rows that are in both datasets. The datasets must have same columns.
JsonToDataset (dataset) json object (string) Convert a JSON object into a string.
LeftJoin (dataset) left dataset right dataset matching expression (string) left dataset right dataset matching expression

(string) Left join of two datasets. Expression have columns names as available arguments. The dataset cannot have columns with same names.

Lookup (object) datasetsearch column(string) search value(object) Makes a lookup to a dataset and returns the value of the return column for the first matching row.
return column name (string) error if not found(boolean)
Matrix (dataset) dataset row expression(string) column expression (string)value expression(string) grouping expression(string)row column name (string) Builds a matrix based on the provided dataset. Row and column expressions are executed for each row of the dataset, and the data is positioned to rows and columns in the matrix based on the row and column expression values.

The value for the input dataset row is determined by the value expression. The grouping expression has an input argument "value", containing an array of all calculated value expressions for the matrix cell. Row column name defines the column containing matrix row names. Columns are sorted as an alphabetical order (except the matrix row name is the first).

MetricsMeasureValu e (object) scorecard id(string)measure symbol(string) attribute name(string) series symbol(string)period name(string) throw error if not found (boolean) Returns Metrics measure value based on following information: scorecard id, measure symbol, series symbol and period name. This can be used when scorecards are queried and there is a need to get data from different measures.

If set to throw errors, an error is thrown if scorecard is not found, measure is not found or value is not found.

MetricsMeasureValu es (dataset) element id’s (string array) series symbols (string array) value attribute names (string array) value column names (string array) Returns Metrics measure values as a dataset from multiple periods (dataset rows) and multiple series or measures (dataset columns). Each period id will have a separate row, i.e. data from different measures are combines in a same row if the measures have a same period level.

Element id’s, series symbols and value column names in the result dataset are provided as separate arrays. If element id's, series symbols and value attribute names arrays have less items than in the value column names, the last item is used as a rest of the items. The result dataset will have columns, periodid, periodname, startdate and enddate. Valid value attribute names are value and prettyvalue (these are from QPR Web Service’s values attribute). Data in result dataset are strings, and in case "value" attribute is

used, they can be converted to numeric.

Example: MetricsMeasureValues(Array('SC.1938773693.159'), Array('ACT_AVG'), Array('value', 'prettyvalue'), Array('valueColumn', 'prettyvalueColumn'))

RemoveColumns (dataset) dataset columnNames (string array) Removes the specified columns from a dataset. Example:

RemoveColumns([dataset1], Array('name'))

RightJoin (dataset) left dataset right dataset matching expression (string) Right join of two datasets. Expression have columns names as available arguments. The dataset cannot have columns with same names.
SearchDataset (dataset) input dataset search string date format(string) Searches a string in a dataset and returns only matched rows. Date format is needed to convert dataset to string for the searching.
SortBy (dataset) dataset sorting definition(string) Sorts the dataset. Sorting is defined as comma separating the sorted columns. Optionally asc (default) or desc can be added after the column name. E.g. attribute1 asc, attribute2 desc
Split (dataset) dataset split column name (string) split expression (string) Splits every row of a dataset into multiple rows based on the split expression. The split expression must return an array containing splitted items for a single row. Thus, the number of items in the array determines, to how many rows a single row is splitted. The splitted items are stored in a new column.

The split expression has all dataset column values as arguments. Note that the splitting may also result to a single row or even zero rows, if the split expression returns an array of one or zero items. Example: Split([dataset], 'splitted', 'StringToArray([column_1], \',\')')

Transpose(dataset) dataset column name for headers (string) Transposes a dataset, i.e. changes its rows to columns and columns to rows.

If column name for headers is not provided, in the transposed dataset first column name is headers and it contains the header names of the original dataset. Rest of the header names are column_1, columns_2, … If column name for headers is provided, data in that column is used as headers for the new dataset (instead of

generated headers described above).
Union (dataset) dataset1 dataset2 Union of two datasets. Results dataset contains all rows of the input datasets. The datasets must have same columns.
UnionDatasetArray (dataset) input dataset (dataset array) Unions multiple datasets provided in an array.
Where (dataset) dataset expression (string) Filters out all rows in the dataset where expression is evaluated as false. The expression has all columns as arguments. Also, there is an argument rowordernumber, which is the row order number starting from 0.

XML Functions

Function Parameters Description
SelectXMLAttribute (string) XDocument or XElement XPath expression (string) namespace prefix (string) namespace URI (string) attribute name (string) Gets an XML attribute value from an XML element that is selected from an XML document using an XPath expression. An empty string is returned if no element matches or if the attribute is not found.
SelectXMLAttributes (string array) XDocument or XElement XPath expression (string) namespace prefix (string) namespace URI (string) attribute name (string) Gets a list of XML attribute values from a list of XML elements that are selected from an XML document using an XPath expression. An empty set is returned if no element matches.
SelectXMLElement (XElement) XDocument or XElement XPath expression (string) namespace prefix (string) namespace URI (string) Selects an element from an XML document using XPath expression. The namespace definition is for the XPath expression. Null value is returned if no element matches.
SelectXMLElements (XElement array) XDocument or XElement XPath expression (string) namespace prefix (string) namespace URI (string) Selects an array of elements from an XML document using XPath expression. The namespace definition is for the XPath expression. An empty set is returned if no element matches.
XmlAttribute (string) XElement attribute name (string) default value (string Gets an XML attribute value from an XML element. Empty value (or optional default value) is returned if the attribute is not found.
XmlDocument (XDocument) xML data (string) XML schema (string)custom error message(string) Constructs an XML document from a string and validates it. Type of the returned object is

XDocument. The XML data is provided as the first parameter and an XML schema as a second parameter. If the XML document is not compatible with the schema (i.e. valid), an error is thrown.

XPathEncode (string) string Encodes a string to be suitable for an XPath expression. The function adds quotes ("") if needed.

Binary Data Functions

File data functions are for getting file contents as a byte array from different sources. There are also functions for getting media types of files.

Function Parameters Description
HttpFileData (byte array) url (string) alternateUrl (string) Fetches a file as byte array through an http(s) using the provided url address. The address must point directly to the file resource. If no resource is found from url, an alternateUrl is tried.
HttpFileMediaType (string) url (string) alternateUrl (string) Media type of the file fetched using HttpFileData function with a same parameter. If no resource is found from url, an alternateUrl is tried.
LoadFileFromDisk (byte array) location (string) Loads a file from the local disk drive as byte array. To take into account data security, avoid implementation where users are able to select the file to load. Users need to have read access to the file.
QprEmbeddedFileData (byte array) object (string) attribute (string) options (string) Gets an embedded file from QPR system as byte array using QPR Web Service’s GetBinaryData operation with provided parameters. More information http://kb.qpr.com/ qpr2017-1/index.html?getbinarydata.htm.
QprGraphData (byte array) object (string) options (string) Gets an image file as byte array using QPR Web Service’s GetGraph operation with provided parameters.
QprGraphDimensions object (string) options (string) Returns width and height of the bitmap image that is returned by QPR Web Services GetGraph method. Width and height is returned as comma separated in the following format:

width,height For example: 700,300

QprGraphMediaType (string) object (string) Media type of the file fetched using qprEmbeddedFileData function with same parameters.
options(string)
WebpageAsImage (byte array) pageUrl (string) width (int) height (int) zoom (float) renderingdelay(int) timeout (int) For more information, seeWebpageAsImage Function Configuration

Other Functions

Function Parameters Description
AggregateIntervals (dataset) input dataset numeric behaviour (string) aggregation type (string) calculate empties (boolean) value column (string) period start column (string) period end column (string) target intervals (datetime array) Aggregates a dataset of values in one interval to another interval e.g. month to year. Works generically from any interval to another.

Parameters:

- numeric behaviour:
- sum
- average
- averageskiptempties - aggregation type:
- distribute
- first
- middle
- last
- calculate empties: true or false

Value column, period start column and period end column are the column names in the input dataset. Same column names are also used in the output dataset. The input dataset may also contain other columns and they are not in the output dataset. Target intervals is an array of datetimes representing output dataset intervals. There must be output intervals for all range of input intervals.

ApplicationSetting (string) setting name (string) Returns application setting value by its name. Settings depend on the application where the expression language is used. E.g. application settings for applications, that are part of QPR Web Services Extensions, are defined in web.config ofQPR Web Services Extensions.
ApplicationVersion (string) [none] Returns a version number of the application using the expression language, e.g. DWR or DWV.
Coalesce (object) object_1 object_2, … Returns first of the objects (counting from left to right) which value is

- for strings, first string that is not null or empty string, or - for other than strings, first value that is not null. Minimum of two parameters are needed. If all parameters are nulls or empties, the first parameter is returned.

DataType (string) object Return the type of provided parameter, e.g. int, string, datetime.
DiagramPath (string array) object id (string) attribute name (string) parent relation (string) separator (string) options (string) Returns an array of EA/PD diagram paths for an object or an object instance. An array is returned as there can be several diagram paths if the object has several instances or if diagrams have been instantiated. Value of the provided attribute is used to identify an object in the diagram path (usually object "name" is used).

Parent relation is the relation attribute name for getting parent objects (usually “parentobjects” is used). Also a "separator" character must be provided (usually "/"). "Options" is for QueryObjects operation for getting parent objects. This function can be used more generically to follow any relation path e.g. Metrics scorecards. Note that the object itself (1. parameter) is not part of the path.

DynamicWebViews (string) template name (string) parameter names (string array) parameter values (object array) Runs a QPR Web Views template.
DynamicWordReport (byte array) report name (string) parameter names (string array) parameter values (object array) Runs a QPR Word Reports report.
Escape (string) input string escaping types (string) number of escapings (int) Available escaping types:
- javascript: chars to escape: double-quote, single-quote, backslash ("'\)
- json: chars to escape: double-quote, backslash ("\)
- qprws: chars to escape: double-quote, backslash ("\)

In all escaping the escape char is \. Number of escapings means how many times the escapings are applied to the input string. It's optional (default is 1).

Eval (object) expression (string) Evaluates an expression.
ExecuteRecursion (object) return expression (string) recursion expression (string) recursion initial value (object) exclude traversed (boolean) Executes a recursion based on provided expressions. There are two expressions:
- return expression: Determines the value a recursion step returns. The expression contains an argument recursionresult which is an array containing the return expressions of all the one level below recursion steps.
- recursion expression: Determines the next level recursions item. Must return an array. The value of this array item will be given to the next step of the recursion. The expression contains an argument currentrecursionstep which is the recursion value of the current step.

The recursion initial value is the value where the recursion starts. Exclude traversed means that items that have already been encountered during the recursion are excluded from next level recursion items. Example: ExecuteRecursion('1 + Sum([value])', 'QueryObjects(\'[\' + [value] + \'].childobjects\', \'\', \'\', \'id\', \'\')', 'PG.123.456')

ExecuteSearch (string array) search configuration (XDocument) Executes search for QPR objects and returns matched object id's as a string array. The search is based on a search configuration provided as an xml document. The schema of the XML document is described in Appendix 1.
ExecuteTasks configuration file contents (string) default log file Executes QPR Suite ETL Task Workbench task.
location (string)
ExecuteVBScript scriptParameters (string) workingDirectory (string) timeout (string) Executes Visual Basic script using Windows Script Host.
ExpressionLanguageVersion (string) [none] Returns expression language version number, i.e. version of QPR Suite Accelerator .Net Tools. QPR Suite Accelerator .Net Tools can be upgraded by replacing the QPRSuiteAcceleratorTools.dll in the QPR Web Services Extensions folder under IIS with a new one.
GenerateNumberArray start (int) increment (int) count (int) Generates an integer array starting from start integer, using defined increment till the count is generated.
GetLog (string array) dateformat (string) Get QPR Web Services Extensions log written thus far as a string array.
GetSessionId (string) [none] Returns QPR Web Service session id, if there is a valid session. If not, returns an empty string.
If condition (boolean) true value (object) false value (object) Usual programming conditional statement. The condition is evaluated, and if the condition is true, the true value is returned; otwerwise it returns the false value. If the condition is evaluated to a null value, the false value is returned. The false value is optional, and in that case null is returned if condition is false.

If the condition is true the false value (expression) is not evaluated and vice versa. Example: if([variable1] = 2, 'value is two', 'value is something else')

IsNull (boolean) object Return true if the value is null, otherwise false.
IsNumeric (boolean) string Returns true if the input string can be converted to a numerical value, otherwise false.
ListFiles (dataset) directory (string) recursive (bool) Returns a list of files in the provided directory as a dataset. In the recursive mode, also files in the subdirectories are returned. Files full path is returned. Name of the column is "filepath".
Loop (object) array expression (string) Loops through an array and calculates an expression for every iteration. The function gives a value of the last iteration’s expression as a result. If array length is zero, a null value is returned. Following arguments are available in the
expression:

- value: Item in the array. - previousresult: Result of previous iteration’s calculated expression. For the first iteration, this value is null. - index: Iteration order number starting from 0. Example: Loop(StringToArray('4,2,3', ','), 'coalesce([previousresult], 0) + [value]') (gives 4+2+3=9)

MeaPortalSessionId portal url (string) username (string) password (string) Logins to QPR Portal and returns QPR Portal session id. Portal url is in the form http://HOSTNAME/QPR/ Portal/QPR.Isapi.dll?QPRPORTAL&*pudev
NullValue (null) [none] Return a null value.
OdbcReadData (dataset) connection string query (string) connection timeout (int) query timeout (int) Reads data from an ODBC source and returns the data in a dataset. To use the function, connection string and query need to be defined. Connection strings can be found in connectionstrings.com.

Connection and query timeouts are optional; they can be changed if needed. Appropriate ODBC drivers need to be installed. Example for Excel file (read all data from sheet "Sheet1"): OdbcReadData('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; DBQ=C:\\test.xlsx;', 'Select * from [Sheet1$1]')

RaiseError error message (string) Raises (throws) an error and shows an error message that is passed as a parameter. This can be used if there is a more complex logic for parameter validation.
ReportsMenu (string) menu configuration(s) (string or string array) template name (string) Returns html code for configured report items.

Template must have parameter reportitems, which is a dataset containing report definitions. visibility, reporturl, reportname, visiblemessage, disabledmessage, target, width, height, scroll, preventcaching, x, y

SwitchCase (object) control expression (object) condition1 (object) value1 (object) condition2 Conventional programming "switch" statement. If control expression equals condition1, value1 is returned and so on. Control expression may be string, integer, double or date. If no condition matches, the default value is returned. Note that the number of parameters must be an equal number (4, 6, 8, …).

Example: SwitchCase([variable1], 1, 'value is

(object) value2 (object) default value (object) one', 2, 'value is two', 'value is something else')
ToJavascript (string) input object Convert an object into a notation that can be used in JavaScript code. Dates are converted into following kind of format: new Date(123456789)
ToJson (string) input object Converts an object into JSON string. Dates are

converted into following kind of format / Date(123456789)/

UsageLog (string) Log line (string) Log file (string) Log mode (string) Writes a line of text to a local file in disk. The function can be used e.g. for usage logging. Parameters:
- Log line is a line of text to append to the log file.
- Log file is the full path and name for the log file.
- Log mode is optional can be one of the following: o 1: Errors are skipped.
o 2: Error message is returned by function as a string.
o 3: An exception is thrown by the function. This means that the expression calculation fails.

If no errors occur, the function returns an empty string. When using Windows authentication, all users need to have write access to the log file. The function may be used in an Expression or Variable tag in Word reports. Note the possible security and performance issues when writing files. Example: UsageLog(CurrentDateTime () + ';' + CurrentUser() + ';' + ReportName(), 'C:/ Logs/QPRReportsLog.txt')

QPR Word Reports Functions

These functions are only available when used in QPR Word Reports.

Function Parameters Description
ReportTemplateData (byte array) templatepath Gets a QPR Word Reports template Word file as byte array based on provided template path.
ReportName (string) [note] Returns the name of the current report.

QPR Web Views Functions

These functions are only available when used in QPR Web Views templates.

Function Parameters Description
ExistsTemplate (boolean) template name (string) Returns true if the template exists; otherwise false.
ReadTemplate(string) template name (string) Returns template contents as a string. Template must be referenced using absolute path (see DWV documentation). It’s possible to read any type of files by adding the file suffix to the file name (for tpl files, no suffix is added) Example: to read file schema.xsd in the root folder, use path / schema.xsd.
TemplateName (string) [none] The current template, i.e. the name of the template where the expression is run.
TemplateParameters (dictionary) [none] Returns all template parameters as a dictionary (string, object).
TemplatePath (string) [none] The current template’s path, i.e. the folder path of the template where the expression is run. The folder path starts from the templates root folder. No starting or ending slashes are part of the path.

WebpageAsImage Function Configuration

WebpageAsImage function takes a screenshot of a webpage and returns it as a PNG image (datatype is byte array). The function uses PhantomJS, a web browser that doesn’t contain a graphical user interface (headless browser), in the server to open the page.

  • page url: Url address of the webpage. Mandatory parameter. width (px): Width of the headless browser window in pixels. Mandatory parameter. height (px): Height of the headless browser window in pixels. Mandatory parameter.
  • zoom: Web browser zooming given as a decimal number. E.g. value 0.75 means 75% zooming. Parameter is optional and it’s by default 1.0.
  • rendering delay (ms): After all http resources related to the webpage have been downloaded, the rendering delay time (defined in milliseconds) is waited until the screenshot is taken. Default value is 0 meaning the screenshot is taken immediately. Use the rendering delay, if it takes some time for the web page to render itself, or run animations and the screenshot should be taken after animating.
  • timeout (ms): Timeout in milliseconds after which the function call is aborted and an error is returned. This is optional parameter and the default value is 30 seconds. Increase the timeout if it takes long time to download and render the target webpage.

Following configurations in QPR Reporting Add-on’s web.config are used by the function:

  • temppath: Folder path that is used by the WebpageAsImage function to temporarily store

PhantomJS configuration files during function execution. No files are left after function is finished. This setting is mandatory. Make sure that the user running the IIS application has write permissions to the

  • installpath: Path for the PhantomJS executable file phantomjs.exe. This setting is optional, and by default it is C:\inetpub\wwwroot\QPRWebServicesExtensions\bin\phantomjs.exe. When making the installation as instructed, the default path is correct.

In addition, in IIS Management Console, application pool’s identity must be set to LocalSystem to that running PhantomJS has enough rights.

These settings should have been set properly during the QPR Reporting Add-on’s installation.

Example Following example uses the WebpageAsImage function to get screenshot of QPR Community’s main page as an image to a Word report: <#image imagedata==”WebpageAsImage('https://community.qpr.com', 2000, 1000, 1.0, 1000)” maxwidth=”17” noimagemessage=”No webpage image available.”>

More information about PhantomJS: http://phantomjs.org https://en.wikipedia.org/wiki/PhantomJS

ExecuteSearch Function Configuration

The search is based in a defined scope, which is a set of objects where the search is targeted. A scope can be e.g. all published models, all certain types of elements. The scope also includes element attributes where the search is targeted. A scope consist of multiple scope parts, which are QPR Web Services queries.

The objects defined by the scope are filtered using a criteria(the search results are the matching objects). Criteria can be any expression containing and, or, not and parenthesis.

XML element Description Attributes
executesearch (1) parent: none scopecombining: and, or
scopepart (1…n) Defines a scope part, which contains a web service query, transformation and attributes. Possible parent

element:executesearch

query: QPR Web Services query options: QPR Web Services query options

transformation: transformation relation name

criteria (1…n) Defines criteria for the search. There can be criteria elements inside other criteria elements to form a type:

and (one to many sub criteria)

hierarchical structure which represents expression calculation order. Criteria can be defined under executesearch for a criteria for all criteria parts, or the criteria can be defined under a certain criteria part. Possible parent elements: executesearch, scopepart, criteria •or (one to many sub criteria) •not (one sub criterion) •comparison (no sub criteria)

Rest of the attributes may be used when type is comparison:

•text search: contains, begins, is  •number/date search: =, <, >, <=, >=,

<> text/number/date search: isnull text/number/date: reference value depending on search type.

attribute: compared QPR Web Services attribute.
matchcase: true or false. Used only in text search.
attribute (0..n) Defines searched attributes related to a scope part. Parent element is scopepart name: name of the attribute


Example:

<?xml version=”="1.0”" encoding=”="utf-8”?>"?>
<executesearch xmlns=”="http://www.qpr.com/QPRSuite/ExecuteSearch”" scopecombining=”and”>="or">
   <scopepart query=”[="[PG.1221241820].Ominaisuus”1127739389].activity" transformation=””="" options=”QueryModelingLanguage=EN”>="">
      <criteria type=”="and”>">
         <criteria type=”="comparison" text” searchtext=”haku12”="Review" attribute=”="name”"
comparisontype=”begin”="begins" matchcase=”="true”/>" />          <criteria type=”text” searchtext=”haku3”="comparison"
attribute=”sanastontermi="customattribute1.value”" comparisontype=”is” matchcase=”false”/>="isnull" /
>
         <criteria type="or">
            <criteria type="comparison" attribute="startdate.value" comparisontype="isnull" />             <criteria type="comparison" date="2016-12-18T00:00:00" attribute="startdate.value" comparisontype="<=" />
         </criteria>
         <criteria type="or">
            <criteria type="comparison" attribute="enddate.value" comparisontype="isnull" />             <criteria type="comparison" date="2016-12-18T00:00:00" attribute="enddate.value" comparisontype=">=" />
         </criteria>
      </criteria>
   </scopepart>
   <scopepart query=”[="[PG.1221241820].Käsite”1127739389].activity" transformation=”="käsitteenominaisuus”>">
      <criteria type=”="and”>">
         <criteria type=”="comparison" text” searchtext=”="haku1”" attribute=”="name”" comparisontype=”contain”="contains" matchcase=”="false”/>" />
      </criteria>
   </scopepart>
</executesearch>
Search configuration schema:
<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.qpr.com/ QPRSuite/ExecuteSearch" xmlns:search="http://www.qpr.com/QPRSuite/ExecuteSearch" elementFormDefault="qualified">
  <xs:complexType name="criteria">
    <xs:sequence>
      <xs:element name="criteria" type="search:criteria" minOccurs="0" maxOccurs="unbounded"/>     </xs:sequence>
    <xs:attribute name="type" use="required">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:enumeration value="textcomparison"/>
          <xs:enumeration value="and"/>
          <xs:enumeration value="or"/>
          <xs:enumeration value="not"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="searchtexttext" type="xs:string"/>
    <xs:attribute name="attributenumber" type="xs:stringdouble"/>
    <xs:attribute name="comparisontypedate" type="xs:dateTime"/>
    <xs:attribute name="attribute">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:minLength value="1"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="comparisontype">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:enumeration value="containbegins"/>
          <xs:enumeration value="contains"/>
          <xs:enumeration value="is"/>
          <xs:enumeration value="begin="="/>
          <xs:enumeration value="<"/>
          <xs:enumeration value=">"/>
          <xs:enumeration value="<="/>
          <xs:enumeration value=">="/>
          <xs:enumeration value="<>"/>
          <xs:enumeration value="isnull"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="matchcase" type="xs:boolean"/>   </xs:complexType>
  <xs:element name="executesearch">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="scopepart" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="criteria" type="search:criteria" minOccurs="0" maxOccurs="1"/>
              <xs:element name="attribute" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:attribute name="name" type="xs:string"/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="query" type="xs:string" use="required"/>
            <xs:attribute name="options" type="xs:string"/>
            <xs:attribute name="transformation" type="xs:string"/>
          </xs:complexType>
        </xs:element>
        <xs:element name="criteria" type="search:criteria" minOccurs="0" maxOccurs="1"/>
      </xs:sequence>
      <xs:attribute name="scopecombining">
        <xs:simpleType>
          <xs:restriction base="xs:string">
            <xs:enumeration value="and"/>
            <xs:enumeration value="or"/>
          </xs:restriction>
        </xs:simpleType>
      </xs:attribute>
    </xs:complexType>
  </xs:element>
</xs:schema>

Acknowledgements

Open XML Power Tools. Copyright (c) Microsoft Corporation

This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software. 1. Definitions The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law. A "contribution" is the original software, or any additions or changes to the software. A "contributor" is any person that distributes its contribution under this license. "Licensed patents" are a contributor's patent claims that read directly on its contribution.

2. Grant of Rights (A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create. (B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software.

3. Conditions and Limitations

(A) No Trademark License- This license does not grant you rights to use any contributors'name, logo, or trademarks.

(B) If you bring a patent claim against any contributor over patents that you claim areinfringed by the software, your patent license from such contributor to the software ends automatically.

(C) If you distribute any portion of the software, you must retain all copyright, patent,trademark, and attribution notices that are present in the software.

(D) If you distribute any portion of the software in source code form, you may do so onlyunder this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license.

(E) The software is licensed "as-is." You bear the risk of using it. The contributors giveno express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement.

1. Definitions.

"License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document.

"Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License.

"Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity.

"You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License.

"Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files.

"Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types.

"Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below).

Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof.

"Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution."

"Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work.

2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form.


3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed.

4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions:

(a) You must give any other recipients of the Work or Derivative Works a copy of this License; and

(b) You must cause any modified files to carry prominent notices stating that You changed the files; and

(c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and

(d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License.

5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions.

6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file.

7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License.

8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages.

9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability.


END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "{}" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright 2014 Microsoft Open Technologies, Inc. Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.


NCalc. Copyright (c) 2011 Sebastien Ros

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.