Using API Reporting Views

As of the EOS release, reporting views are available to help you efficiently run reports in any application of your choice.

You can extend existing entities or add other data types. In both cases, you will also need to add the view to the API.

Before You Start

You must be a qualified System Administrator.
You must have API Explorer enabled on your General Access Security Role.
You will need to be able to log into the SQL server hosting the ASM application Database.
You should understand the basic capabilities and functions of an API.
You should understand how to write statements/queries in SQL.

Extend existing entities

Define a View

This view will be accessible via the API and all Service permissions will be applied to API queries

create view SpecialServices as
select
--PrimaryRelationId is the primary key and will be modelled as a navigation property
--Navigation properties are used for linked fields and joins via the API
--the Data Type exposed by the API for this property is dictated by the RELATED_ENTITY_ID setting
--the API will filter the results based on the current users permissions for this primary relation
ITEM_REF as PrimaryRelationId,
--Select any data in this view
Title as Title,
--Columns ending with Id will be modelled as navigation properties if the name matches the name of an existing entity type
--in this example the API will expose a property named ServiceLevel and you will be able to select ServiceLevel.Name via the API
SERVICE_LEVEL as ServiceLevelId
from AR_PRIMARY_ASSET
--apply relevant filters
--you can join on any table
where ROOT_ENTITY_TYPE = 2
go

Add the View to the API

Insert a record into SU_API_VIEW so that it will be accessible by the API

VIEW_NAME must be unique

RELATED_ENTITY_ID is the Entity Id of the primary type e.g. Configuration Item (or even Desktop)

Entity Id's are visible in the API Explorer

Add Other Data Types

Define a New Custom View

Define a custom view for aggregation and reporting which does not use the primary relation

Permissions are applied to data from navigation properties but not to the data exposed directly from the view

Analysts must have the Dashboards permission (formerly Reporting) from the Dashboard Security Role to use these aggregate views

Users must have the Dashboards permission in the Dashboard Management section for their portal security role

create view CallsByProblemType As
select
--The view must include a primary key column named Ref
--values returned by this column must be unique
--ROW_NUMBER is usually appropriate

ROW_NUMBER() over (order by a.instances) as Ref,
--select any other columns

b.NAME_COMPOSITE as ProblemTypeName,
a.Instances
--navigation properties are also supported when columns follow the naming convention above (e.g. ServiceLevelId)

from (
select REF_COMPOSITE, count(CALL_NUMBER) as Instances
from cl_call_logging
group by REF_COMPOSITE
) a
join CL_PROBLEM_TYPE b on a.ref_composite = b.REF_COMPOSITE
go

Add the View to the API

Insert a record into SU_API_VIEW so that it will be accessible by the API

VIEW_NAME must be unique

RELATED_ENTITY_ID must be 00000000-0000-0000-0000-000000000000 to disable the (otherwise mandatory) join on a primary relation

insert into SU_API_VIEW (VIEW_NAME, RELATED_ENTITY_ID)
select 'CallsByProblemType', '00000000-0000-0000-0000-000000000000'