Elements has the capability to render custom formatted reports using SQL Server Reporting Services (SSRS). This document will outline the requirements for developing these reports as well as how to register custom reports within Elements so that they can be accessed by appropriate users of the system.

The Reporting Database is used as the data source for SSRS reports. It must therefore be installed. All SSRS reports are based on data taken from the reporting database.

SSRS needs to have been installed, configured and the stock Elements report templates are required to have been deployed to it using the Reporting Setup utility. (See Configuring CVs in Elements for details).

Note: The above installation-related tasks are performed by Symplectic for instances of Elements hosted by Symplectic.

The Reporting Synchroniser needs to be running, and at least one full data synchronisation to have been completed by it.

When designing, deploying and registering custom reports for use, it is your responsibility to ensure that use of the report aligns with the privacy policy of your institution - and in particular that you are not exposing data in the report to unauthorised individuals. This includes the need to:

• Review the data that you plan to use in the report for its privacy level (please see the Introduction to Data Privacy and Personal Data in Elements for an overview of data privacy in Elements, and the Reporting Database Guide for how configurable privacy levels are represented in the reporting database);

• Ensure that the individuals to whom you intend to give access to the report are all authorised by your institution to access the data it exposes.

Elements custom reports are generated using Microsoft SSRS Reporting Services and the Elements Reporting Database. When a report is requested, Elements contacts SSRS and to request the rendering of a particular report. The report server then contacts that report's 'data source' (the Elements Reporting Database) to execute SQL data queries ('datasets') to generate the report. It formats this information as defined in the report definition and passes the generated document back to Elements which offers the document for the user to download.

To ensure that the data presented in a report is up to date, ongoing synchronisation is required between the Elements operational database and the Reporting Database.

Note: As the Reporting Database structure can change with different version of Elements, please review your custom reports after every upgrade to ensure they are still functional and presenting the data as expected.

When deploying the Stock SSRS reports for a particular Elements instance (see Configuring CVs in Elements) you have to select a folder on the SSRS report server which is used to hold all the SSRS definitions pertaining to that specific instance of Elements. This base folder contains a "Custom Reports" subfolder.

All custom report definitions you wish to associate with a particular instance of Elements must be deployed to SSRS within that instance's Custom Reports folder.

Note: This means you may need to deploy multiple copies of a particular report to SSRS in multiple locations to make it available for multiple instances of Elements sharing the same report server.

Elements supports custom reports that show information about either an individual user or a specific group of users. This is achieved by passing the appropriate user or group identifier to the SSRS report server as an SSRS report parameter value when a specific report is requested from Elements. Optionally, depending on the report registration, Elements can request a date range from the user at the point a report is requested and pass this range on to SSRS as additional parameters.

Note: It is entirely down to the designer of the SSRS report to decide what to do with the parameter values being passed over by Elements. Care should be taken to ensure that any custom reports represent an accurate description of the requested user or group data before they are released to end users.

SSRS is a third party product developed by Microsoft and made available as an optional part of Microsoft SQL Server. Microsoft provides a range of tools and rich documentation about how to build reports using SSRS. Reports can be written using either of:

• Microsoft Visual Studio with SQL Server Data Tools (SSDT) and the SSDT BI templates installed (see here and here).

• SSRS's Report Builder (see here).

Note: The SSRS Report Builder in particular can be accessed directly from within SSRS's Report Management web interface.

A particularly good resource for SSRS documentation is the Microsoft TechNet blog: (e.g. https://msdn.microsoft.com/en-us/library/bb522859.aspx). This guide will therefore not go into the detail of basic report design, but will instead cover the requirements you will need to adhere to in order to make a report that is suitable for use with Elements.

The tools listed above offer relatively similar toolsets for building reports so it will primarily be a case of what your report designers feel most comfortable using. The image below is a screenshot from SSRS's Report Builder:

The interface allows you to define:

1. Report Parameters.
   These are inputs to the report that can be used to alter what the report displays.

2. Data Sources
   Where data should be retrieved from (this should be the Elements reporting database associated with the Elements instance you are creating the report for).

3. Datasets
   These are effectively SQL queries that retrieve data from the data sources (potentially filtering the data being returned based on the provided report parameters.

Once these basics are defined you place "widgets" onto the page and wire up each widget's properties to aspects of the data from within the various datasets in order to define your report's layout.

Tip : As you must eventually deploy (save) your report to the "Custom Reports" folder associated with a specific instance of Elements you can make use of the pre-existing data source located within the "Data Sources" folder alongside your target instance's "Custom Reports" folder. This data source is the one used by the Elements stock CV templates, and is therefore already set up to retrieve data from the reporting database associated with the appropriate Elements instance.

Tip: When deploying reports to multiple Elements instances sharing the same SSRS report server, be aware that there are tools within the SSRS Report Management web interface to allow you to copy, move and rewire (e.g. remap the data source being used by a report) report definitions. Please take great care when using these tools, however, to ensure that all the report definitions within a particular instance's "Custom Reports" folder are using the correct data source (i.e that they are using a data source connected to that instance's reporting database).

Reports to be registered with Elements must have one non-nullable, single valued, visible integer parameter. If the report is about an individual user this parameter must be named "User_ID". If the report is about a group, it must be named "Group_ID":

This parameter is the key parameter that should be used within the report to retrieve only relevant information from the reporting database. This parameter should have no default or available values configured whatsoever:

Reports to be used with Elements can optionally accept two datetime parameters, which can be used by the report designer to restrict the report's content to items within a certain time frame. If you wish to make use of this feature, the report must contain two nullable, single-valued, visible datetime parameters, named "Date_1" and "Date_2":

These parameters should be configured to have a default value of null but no available values should be configured whatsoever:

Note: It is entirely down to the designer of the SSRS report to decide what to do with the parameter values being passed over by Elements. Care should be taken to ensure that any custom reports represent an accurate description of the requested user or group data before they are released to end users.

The report must not contain any other non-nullable parameters. To verify that the report parameters are configured correctly, in the SSRS web interface click Manage from the context menu of the report, and then navigate to the Parameters tab in the sidebar. The parameter configuration should look similar to the following (the date parameters are optional, and the "User_ID" parameter may be "Group_ID" depending on the type of report):

The completed report must be deployed (saved) onto the SSRS report server in the Custom Reports folder associated with a particular Elements instance (as created by the Reporting Setup utility).

Once the reports are complete, tested and deployed to a specific Elements instance's Custom Reports folder, they can be registered with that Elements instance to provide access to the report from within the Elements user interface.

Reports can be individually switched on or off using the Enable/Disable feature, and the users with whom each report is shared can likewise be configured.

Upon first visiting the Manage Reports page, the Status of all the registered reports will be checked. This will verify that the configuration of Elements and SSRS is correct; if the report status is "Failed", the reason for the failure will be displayed when the cursor is placed over the status icon. Similar functionality exists in the Reporting Hub, though it must currently be manually invoked using the Run Tests button.

Depending on your version of Elements, to register a report you should either visit the Manage Reports page or the Reporting Hub. Click the appropriate 'add new report' button. To edit an existing registration, click the name of the report. This will take you to the appropriate report details page.

Note: The registration of stock Symplectic reports cannot be altered, although you can still enable/disable them and configure who has access to them.

When registering a new report, Elements will contact SSRS and retrieve a list of all the report definitions located in the corresponding Custom Reports folder on SSRS. Select the appropriate report definition from the dropdown and then fill out the details which will be used to configure how the report is displayed and used within Elements.

The "About" dropdown determines whether the report is about a user or a group. The "User supplied date range" should only be checked if the report was written in SSRS to accept the Date_1 and Date_2 parameters. Once the details are completed, click "Register" and you will be taken back to the Manage Reports page or the Reporting Hub as appropriate to your version of Elements and your report type.

You should then locate the report and run it to ensure that it looks as expected. Review and set the new report's viewing permissions as appropriate and then enable it.

Once the report is enabled it will be available to the relevant users through either the user profile (if it is a user report) or the group page (if it is a group report), via the Export dialogue, or in the Reporting Hub (for newer versions of Elements). For reports which have been registered as accepting a user-defined date range, the option to enter a date range will be presented to the end-user in the export dialogue.

No - Custom Reports are available within the Elements Application, but you can always directly query the Reporting Database using Microsoft SQL or other third party reporting solutions such as Oracle, or even Excel.

Choosing to write an SSRS report and register it in Elements allows you to share them with end-users (on the Profile Page) or group managers (on the Group Admin page). These are reports that you want to devolve access to and allow to be run as required. Examples are CV's, Group Activity Reports, and Group Membership.