Who is this article for?

Users who need access to custom SSRS reports and charts

Data restrictions are governed by modules, reporting authorities, and roles.

The Reports and Charts shortcut allows you to import and export custom Microsoft SQL Server Reporting Services (SSRS) reports. 

Data & Analysis Administrators can provide access to these custom reports by using a role or by setting up a schedule for report jobs on a regular basis. Reports that are shared by role will appear in the user’s Reports shortcut under the category Custom Reports and Charts.

Custom report role requirements
Some SSRS reports are used by modules directly such as when performing a print or displaying information in the module. Not all SSRS reports will be shared with a role because access is managed by the module.

Customers can create their own SSRS reports and charts but must take care to honor restrictions such as reporting authority and record access control when defining the report.

Run reports on a schedule

Users can use the Reports & Charts to set up a schedule to distribute SSRS reports like Notifications.

Once the schedule has been established, the report can be distributed to the intended recipients ensuring that it reaches one or multiple individuals as required. Furthermore, for those who prefer a more formal presentation of the data, the report can also be attached in various formats, such as a PDF or an Excel file. 

Define parameters

When building SSRS reports to be used directly by a module, any number of parameters can be defined, with them mapping to a field(s) in the module. Examples of parameters are Department, Organization, Date Created, and Severity. Parameters are entered in a string, for example, &Department=Operations&Severity=A.

A special variable, _Today, can also be used with any date field parameter. This variable will set a variable to today's date (in YYYY-MM-DD format) and can be optionally add or subtract days. So, &DateCreated=_Today-7&DateClosed=_Today+7 would cover the two weeks around today's date.

SSRS report configuration

To function properly, all custom reports should be set up to point to a data source called DWAYDataSource. The SQL statement that returns data for the report can involve any of the views available from the Query Builder window. Since users cannot access the database directly, it's often useful to construct a SQL statement using the Query Builder window, export some sample rows to an Excel spreadsheet, and then load that data into a local SQL Server instance, to create the report itself.

By default, reporting authorities do not restrict custom reports. Two special variables, authRA and personID are used.

The authRA is a comma delimited list of the reporting authorities of the user executing the report. The parameter is used like this:

WHERE ReportingAuthorityCode IN 
  (SELECT Item from uf_splitString(@authRA))

The authRA parameter is agnostic regarding modules, to account for users whose RAs vary by module, use the personID parameter combined with the PersonRole and PersonRoleReportingAuthority views:

DECLARE @list varchar(MAX)
SELECT @list=coalesce(@list + ',', '') + T1.Child_Authorities 
FROM (
    SELECT RA.Child_Authorities 
    FROM dbo.v_BEC_PersonRole PR 
      INNER JOIN dbo.v_BEC_PersonRoleReportingAuthority PRRA 
        ON PRRA.PersonRoleID = PR.PersonRoleID 
      INNER JOIN dbo.WWAY_REPORTING_AUTHORITY RA 
        ON RA.Reporting_Authority_Code = PRRA.ReportingAuthorityCode 
    WHERE PersonID = @personID 
      AND PR.Active = 'Y' 
      AND PR.RoleCode IN ('BEC-CR-2AA','BEC-CR-AP') -- Just Participant 
    ) T1 
SELECT DISTINCT Item as RA from uf_splitString(@list)

To insert a link into a custom report that, when clicked, opens the underlying object in its own tab create a link that calls a JavaScript function with the module code and objectID of the object that needs to be opened:

  
<Hyperlink> = iif(
    Globals ! RenderFormat.IsInteractive,
    "javascript:void(
        window.parent.postMessage('{\\'Module\\':\\'DL-SUP\\',\\'Object\_id\\':\\'" 
        & Fields ! ObjectID.Value 
        & "\\'}','\*'))",
    ""
) </Hyperlink>

To add this expression in Visual Studio:

1. Opening the .RDL file in Microsoft Visual Studio and going to Open Design view
2. Right clicking on the interactive link text box in the Design view -> Text Box properties -> Action tab
3. Selecting "Go to URL" option and copying the above expression into the "Select URL" field

Although powerful and highly customizable, custom reports have the following disadvantages:

• Users cannot save criteria and rerun them.
• Users cannot distribute them on a schedule. These must be configured by the admin.
• Only one schedule can be defined, per report.
• Modifications to these reports must be completed by Ideagen.
• A user is unable to add the report to their dashboard, but administrators can create an Admin component and include the report's URL in the component, similar to an external URL.