Cireson Portal

How To: Add Dashboard and View Panel

Introduction

Adding a new dashboard chart or dashboard page is a multi-step process requiring technical knowledge of SQL queries and JSON.  This documentation is intended for a developer.  Over time, Cireson will make the process of creating custom dashboard content easier.

The Cireson Portal user experience architecture is composed of pages and view panels.  Pages are logically the same as any web page you would find on the internet.  Pages have a unique URL that when entered into an address bar will bring up certain content in the browser.  A page is composed of view panels that are laid out in a grid. View panels contain the actual content that is visible to the user.  A view panel can contain content such as a grid of work items, a chart, or even custom HTML content.  Conceptually the architecture is similar to SharePoint pages and web parts.

View panels can be added to existing pages (if the page is not sealed) or new pages can be added with view panels on them.  View panels can be reused on multiple pages.  Updating a view panel definition will update its display in all pages which include the view panel.  View panel and page definitions are made in the JSON format which is a simple attribute-value schema.

Related Articles

How to Add a Dashboard

At a high level, these are the steps required to create a new dashboard and include it on a new view panel:

  1. Add/Insert a new dashboard query to DataSource table
  2. Create the chart definition
  3. Add/Insert a new viewpanel with chart definition
  4. Create a view with the viewpanel definition created in step 3
  5. Add/Insert a new navigation link for the view created in step 4

A more detailed step-by-step guide is below

Important: GUIDs that are written in any JSON strings (e.g. the ViewPanel and Navigation Node definitions are CASE SENSITIVE.

Chart Queries

The SQL queries for chart's data will be saved/taken from the DataSource table in the ServiceManagement database. For now, use a SQL INSERT statement to add in new chart queries. The DataSource table will have the following properties:

  • Id (uniqueidentifier)
  • ​ConnectionString (string) 
    • The database connection string for external database in string format. When left blank, it will connect to ServiceManagement by default. An example connection string would be: “Server=WIN-THLEMGM57VF;Database=ServiceManager;Trusted_Connection=True;”
  • Query (string)​
    • The actual SQL query for the chart's data in string format.

Example:

INSERT INTO DataSource(Id,ConnectionString,Query)
VALUES('0bf5524b-2c55-496a-a5d9-945ab9556628', NULL, 'SELECT AVG(ISNULL(DATEDIFF(minute, CreatedDate, ResolvedDate),0)) AveResolutionTime
FROM [ServiceManagement].[dbo].[WorkItem] wi
WHERE wi.ClassId=''A604B942-4C7B-2FB2-28DC-61DC6F465C68''
AND wi.CreatedDate >= DATEADD(day, -1, GETDATE())
AND wi.ResolvedDate IS NOT NULL')

Note: Single quotes in the query must be escaped by using a double single quote pattern.

Chart Definition

  • General:
    • type (string) 
      • ​The type of chart that will be rendered on the page. Currently it can either be chart or radialGauge.
    • title  (string) 
      • The localization string key that holds the title for the.
    • queryId ​(guid)
      • The Id property of the chart's data source stored in the DataSource table.
  • ​Chart specific:
  • Gauge specific:
    • valueField (string)
      • The column on the SQL result that gives out a value for the gauge's pointer
    • max​ ​(int)
      • The maximum value of the scale. Defaults to 100.
    • startAngle (int)​
      • The start angle of the gauge. Defaults to -50.
    • ​endAngle (int)
      • The end angle of the gauge. ​Defaults to 230.
    • labels​ (object)
      • Configures the scale labels.
    • ranges (array)
      • The ranges of the scale.

Example:

chart:
{
    "type" : "chart",
    "title": "ActiveWorkitemsByAssignedUser",
    "queryId": "1f14573c-d129-4121-a438-4af9aec81541",
    "series": [
       {"field": "ActiveIncident", "name": "Incident", "type": "column", "stack": true},
       {"field": "ActiveChangeRequest", "name": "ChangeRequest", "type": "column", "stack": true},
       {"field": "ActiveServiceRequest", "name": "ServiceRequest", "type": "column", "stack": true},
       {"field": "ActiveProblem", "name": "Problem", "type": "column", "stack": true},
       {"field": "ActiveReleaseRecord", "name": "ReleaseRecord", "type": "column", "stack": true}
    ],
    "categoryAxis":{"field": "AssignedUser"},
    "seriesColors":["orange", "blue", "green", "red", "violet"]
}

radial gauge:
{
    "type" : "radialGauge",
    "title": "AverageResolutionTime",
    "queryId": "0bf5524b-2c55-496a-a5d9-945ab9556628",
    "valueField": "AveResolutionTime",
    "max": 150,
    "startAngle": -50,
    "endAngle": 230,
    "labels": {
        "format": "N0",
        "font": "15px Arial,Helvetica,sans-serif"
    },
    "ranges": [{
        "from": 0,
        "to": 60,
        "color": "green"
    },{
        "from": 61,
        "to": 90,
        "color": "yellow"
    },{
        "from": 90,
        "to": 150,
        "color": "red"
    }]
}​

ViewPanel Definition

  • ​Id
    • Id (string) property in root of definition.
  • Panel Type
    • type (string) what kind of panel contents are in the definition. Possible values are:
      • grid
      • html
      • header
      • chart – use this when creating a new chart view panel
  • Content
    • ​content (string) This is the definition of what the viewPanel has, so it will call existing builder files like grid, or custom build files for specific viewPanels, like home page header.​

Examples:

{
  "Id": "7728C886-CCEE-4523-8800-5842CA0AED25",
  "type": "chart",
  "content": {
    "type": "chart",
    "isLegendVisible": false,
    "queryId": "5de4dfcf-96bf-4b52-b487-496502974cf2",
    "series": [
      {
        "field": "ActiveCount",
        "name": "ActiveIncidents",
        "type": "bar"
      }
    ],
    "categoryAxis": {
      "field": "Tier"
    }
  }
}

Add a View Panel to Database

View panel definitions are stored in the ServiceManagement database on the ViewPanel table.

To add a new view panel definition users may execute spAdd_ViewPanel stored procedure.

The important SP parameters to fill in are:

@Id(uniqueidentifier)
   Refers to the view panel’s unique identification id as described in ViewPanel definition section.

@Definition (varchar(MAX))
   Refers to the view panel JSON definition as described in the ViewPanel definition section.

@TypeId (varchar(50))
   Refers to the view panel type as described in ViewPanel definition section.

Example:

EXEC spAdd_ViewPanel
@ID = '7728C886-CCEE-4523-8800-5842CA0AED25',
       @Definition = '{"Id":"7728C886-CCEE-4523-8800-5842CA0AED25","type":"chart","content":{"type":"chart","isLegendVisible":false,"queryId":"5de4dfcf-96bf-4b52-b487-496502974cf2","series":[{"field":"ActiveCount","name":"ActiveIncidents","type":"bar"}],"categoryAxis":{"field":"Tier"}}}',
       @TypeId = 'chart';

View Definition

A view JSON definition is built using:

  • ​Id
    • A unique identification id  the view in GUID format
  • Layout Type
    • layoutType (string) property in root of definition. Sets up difference rendering depending on type
  • ​View
    • view (object) 
    • ​header (object) sets up page header
    • body (object) page view contents
    • ​Tabs
      • tabs (array of objects) and array of tab object​
      • ​name (string) the key for the localized string, that will be used as a label ​​​for the Tab in the UI
      • content (array) array of rows
  • Rows
    • rows (array) array of objects signifying rows
  • Columns
    • columns (array) an array of column object
    • title: localizable title for the heading text of the column (optional)
    • ColSpan: integer value of the column width (using bootstrap col-md-xx)
    • type: currently the only type is 'viewPanel' which will tell the builder to create a new viewPanel
    • ViewPanelId:  referen​ces the database ID of the view panel.

Example:

{
  "Id": "c40cec6f-36f2-467e-8b22-de8869ed9600",
  "layoutType": "full",
  "view": {
    "header": {
      "title": "IncidentDashboard",
      "subTitle": ""
    },
    "body": {
      "content": {
        "rows": [
          {
            "columns": [
              {
                "ColSpan": 6,
                "title": "ActiveIncidentBySupportGroup",
                "type": "panel",
                "ViewPanelId": "1d769417-b30d-48fa-9cba-ec26501ace4b"
              },
              {
                "ColSpan": 6,
                "title": "NeglectedIncidentBySupportGroup",
                "type": "panel",
                "ViewPanelId": "e2f54cca-e052-4a42-9d06-68ca1681fbb5"
              }
            ]
          },
          {
            "columns": [
              {
                "ColSpan": 12,
                "title": "ActiveWorkitemsByAssignedUser",
                "type": "panel",
                "ViewPanelId": "04a15a24-d60d-4192-a19e-8e32b0357ff3"
              }
            ]
          },
          {
            "columns": [
              {
                "ColSpan": 6,
                "title": "AverageResolutionTime",
                "type": "panel",
                "ViewPanelId": "5b37d774-d1d6-4e27-9c71-d5742170fe29"
              },
              {
                "ColSpan": 6,
                "title": "AverageResponseTime",
                "type": "panel",
                "ViewPanelId": "69a7c802-19e3-47d2-9cf0-142bb7849a5b"
              }
            ]
          }
        ]
      }
    }
  }
}

Add a Navigation Node/Link

View definitions are stored on the ServiceManagement database on the NavigationNodes table.

Use a SQL INSERT statement to add in new navigation node. The NavigationNodes table will have the following properties:

  • LicenseRequired (varchar)
    • Determines the license key type needed for dashboards, set this to “DASHBOARD” for a view that will contain dashboard content
  • ​Location (uniqueidentifier)
    • This is always set to '6cfc4d00-d8eb-4f51-b63d-f776576cf25a'
  • IsPublic (boolean)
    • Sets whether navigation is shown to everyone (public, use value ‘1’) or to specific group only (non-public, use value ‘0’))
  • Id (uniqueidentifier)
    • A unique identification id for navigation node in GUID format
  • ParentId
    • The Id of the parent navigation node
  • Ordinal (int)
    • Sets the display order of the navigation node relative to the other navigation nodes with the same parent
  • Sealed (boolean)​
    • Sets whether the node is sealed or not.  Sealed means that the node is a system-default navigation node.   Any custom nodes should be set to "0".
  • [Definition] (varchar)
    • The view definition JSON

Example:

INSERT INTO NavigationNode(LicenseRequired, Location, IsPublic, Id, ParentId, Ordinal, Sealed, [Definition])
 VALUES ('DASHBOARD', '6cfc4d00-d8eb-4f51-b63d-f776576cf25a', 0, 'c40cec6f-36f2-467e-8b22-de8869ed9600', '61c1c472-56dc-491c-9530-141346323b50', 0, 0, '{"Id":"c40cec6f-36f2-467e-8b22-de8869ed9600","layoutType":"full","view":{"header":{"title":"IncidentDashboard","subTitle":""},"body":{"content":{"rows":[{"columns":[{"ColSpan":6,"title":"ActiveIncidentBySupportGroup","type":"panel","ViewPanelId":"1d769417-b30d-48fa-9cba-ec26501ace4b"},{"ColSpan":6,"title":"NeglectedIncidentBySupportGroup","type":"panel","ViewPanelId":"e2f54cca-e052-4a42-9d06-68ca1681fbb5"}]},{"columns":[{"ColSpan":12,"title":"ActiveWorkitemsByAssignedUser","type":"panel","ViewPanelId":"04a15a24-d60d-4192-a19e-8e32b0357ff3"}]},{"columns":[{"ColSpan":6,"title":"AverageResolutionTime","type":"panel","ViewPanelId":"5b37d774-d1d6-4e27-9c71-d5742170fe29"},{"ColSpan":6,"title":"AverageResponseTime","type":"panel","ViewPanelId":"69a7c802-19e3-47d2-9cf0-142bb7849a5b"}]}]}}}}')

Add a Localization for the Navigation Node

View definitions are stored on the ServiceManagement database on the DisplayString table.

Use a SQL INSERT statement to add in new localization for the navigation node. The DisplayString table will have the following properties:

  • ElementID (uiniqueidentfier)
    • A unique identification id for localization string in GUID format
  • LocaleID (varchar)
    • The three letter localization code (i.e. ENU)
  • DisplayString (varchar)
    • The actual display string for the specified language
  • LocalizationKey (varchar)
    • A unique localization key to be used for future translation
  • CreatedBy (uniqueidentifier)
    • The user's id in GUID format

Example:

INSERT INTO DisplayString (ElementID, LocaleID, DisplayString, LocalizationKey, CreatedBy)
VALUES ('4a40170a-e4b9-40c2-8384-309c57fa5f6a', 'ENU', 'All Changes Calendar', 'AllChangesCalendar', '9B4B0FD1-D62C-3224-B2CB-4EEC08014F60');

ViewPanels in the browser

For performance reasons  all ViewPanel definitions are cached in the browser’s local storage when a new sessions is established.  In order to see changes to a ViewPanel, you will need to clear the cached values.  You can do this by going to the browser JavaScript Console (F12, then select the console tab), and running this command:

app.clearNodeAndViewPanelStorage();

After you run that command refresh your browser to load the new/changed definition.