# Dashboard widget configuration

# Configuration help

### <span class="mw-headline" id="bkmrk-styling-1">Styling</span>

All widgets have a field called "Custom CSS". This allows addition of styling, when that stylesheet is shown.

As of version 8359 it is possible to use `#THISWIDGET#` to target that specific widget.

`#THISWIDGET#` will automatically be replaced by the unique html-id of that widget, eg. `#Widget69`, thus allowing css that can be copied to another widget without the need to know the ID.

### <span class="mw-headline" id="bkmrk-icons-1">Icons</span>

All the below properties are required for the button

- **ButtonGlyph**: Icon of the button (3 options) 
    - [FontAwesome 4](https://fontawesome.com/v4/icons/): fa-home
    - [Material icons](https://materializecss.com/icons.html): home
    - [Bootstrap Icons](https://icons.getbootstrap.com/): bi-house-door-fill
    - Icon URL: [http://starwars.com/LukeSkyWalker.png](http://starwars.com/LukeSkyWalker.png)
    - Single character: ”P”
- **Title**: Text below the icon
- **Content**: Url of the button

Optimally buttons are places in the **Toolbar** zone on the page, where they are rendered as normal buttons. If placed in other zones they are rendered as large squares.

### <span class="mw-headline" id="bkmrk-sizing-1">Sizing</span>

#### <span class="mw-headline" id="bkmrk-length-1">Length</span>

Setting the length of a widget limits how many rows are shown. This setting only applies to lists.

#### <span class="mw-headline" id="bkmrk-size-1">Size</span>

Setting the height of a widget forces it to be that number, times 100, pixels tall. This setting does not affect lists.

Setting the width of a widget applies a set of [bootstrap column-classes](https://getbootstrap.com/docs/4.1/layout/grid/), setting the width in a responsive manner.

The lookup follows this logic:

- 1 = `col-4 col-md-2 col-xl-1`
- 2 = `col-6 col-md-4 col-xl-2`
- 3 = `col-6 col-lg-4 col-xl-3`
- 4 = `col-12 col-md-6 col-xl-4`
- 5 and 6 = `col-12 col-lg-8 col-xl-6`
- 7, 8, and 9 = `col-12 col-xl-8`
- 10, 11, and 12 = `col-12`
- Above 12 = `col`

# SQL Query

An SQL Query may extract almost anything from the database.

This process ignores the security restrictions in the system, so you will have to implement them yourself (see special keywords below).

This widget supports different rendering modes, for a deep dive on the diagrams, [click here](https://wiki.tsnocode.com/index.php?title=Dashboard_diagram_widget_configuration "Dashboard diagram widget configuration").

#### <span class="mw-headline" id="bkmrk-sample%3A-limited-stat-1">Sample: Limited status distribution</span>

The following example displays quite well in a pie chart.

```mysql
 SELECT s.Status, count(*)
 FROM data_leads1 as d
 JOIN formstatus as s ON  s.StatusID = d.StatusID 
 WHERE d.StatusID IN (11344,11343,11345)
 GROUP BY 1
```

#### <span id="bkmrk-"></span><span class="mw-headline" id="bkmrk-special-keywords-%2F-v-1">Special keywords / variables</span>

- %UserID%
- %ExclusiveGroupID%
- %GroupListSql%
- %ExclusiveGroupListSql%
- %AndRecordFilter%

## SQL query examples

<div class="vector-body" id="bkmrk--1"><div id="bkmrk--2"></div><div id="bkmrk--3"></div><div id="bkmrk--4"></div></div>### <span class="mw-headline" id="bkmrk-grouping-1">Grouping</span>

The following queies are suitable for use with Dashboard SQL tables. With minor modification they can also be used with the Field type "SQL: Table query"

#### <span class="mw-headline" id="bkmrk-status-related-data-1">Status related data</span>

```mysql
SELECT s.Status, COUNT(k.DataID)
FROM data_kuglespil as k
JOIN formstatus as s ON k.StatusID = s.StatusID
GROUP BY 1
```

#### <span class="mw-headline" id="bkmrk-lookup-related-data-1">Lookup related data</span>

```mysql
SELECT l.`Value`, COUNT(k.DataID)
FROM data_kuglespil as k
JOIN formfieldlookup as l ON k.STRRELSE = l.LookupID
GROUP BY 1
ORDER BY l.SortOrder
```

#### <span class="mw-headline" id="bkmrk-grouped-variables-1">Grouped variables</span>

```mysql
SELECT 'Under 5' as Text, COUNT(k.DataID)
FROM data_kuglespil as k
WHERE k.RESULTAT <= 5

UNION

SELECT 'Middel (6-16)' as Text, COUNT(k.DataID)
FROM data_kuglespil as k 
WHERE k.RESULTAT <= 5 AND k.RESULTAT < 17

UNION

SELECT 'Top performer (17)' as Text, COUNT(k.DataID)
FROM data_kuglespil as k
WHERE k.RESULTAT = 17
```

#### <span class="mw-headline" id="bkmrk-population-percentag-1">Population percentages</span>

```mysql
SELECT
  AVG(IF(StatusID=35,0,100)) as `% completed`
FROM
  data_tpdocument
```

### <span class="mw-headline" id="bkmrk-corner-cases-and-wei-1">Corner cases and weird stuff</span>

#### <span class="mw-headline" id="bkmrk-list-of-birthdays-1">List of birthdays</span>

```mysql
SELECT NAVN as Navn, FDSELSDAG as Fødselsdag, ROUND(DATEDIFF(NOW(),FDSELSDAG) / 365 ) as Alder
FROM data_medarbejder
WHERE DATEDIFF( MAKEDATE(YEAR(NOW()),DAYOFYEAR(FDSELSDAG)), NOW() )  BETWEEN 0 AND 7
```

# Lists

To setup a list view, simple copy/paste the URL of a view.

Optimally you should strip anything but the parameters from the URL.

```
?SagID=252&command=list&QUERY_FIELD_1=OPFLGNING& ...
```

#### <span class="mw-headline" id="bkmrk-filtering-the-list">Filtering the list</span>

To filter the list, add three parameters to the url, pr filter. Make sure that every set of parameters have their own unique number.

```
?SagID=252&command=list&QUERY_FIELD_1=OPFLGNING&QUERY_PARAM_1=1&QUERY_VALUE_1=0 ...
```

Operators:

<table class="wikitable" id="bkmrk-id-command-note-1-%3D-"><tbody><tr><th>ID</th><th>Command</th><th>Note</th></tr><tr><td>1</td><td>=</td><td>Only rows with values equal to</td></tr><tr><td>2</td><td>&lt;</td><td>Only rows with value lower than</td></tr><tr><td>3</td><td>&gt;</td><td>Only rows with values higher than</td></tr><tr><td>4</td><td>!=</td><td>Only rows with values not equal to</td></tr><tr><td>5</td><td>&lt;=</td><td>Only rows with value lower than or equal to</td></tr><tr><td>6</td><td>&gt;=</td><td>Only rows with values higher than or equal to</td></tr><tr><td>7</td><td>CONTAINS</td><td> </td></tr><tr><td>8</td><td>Starts with</td><td> </td></tr><tr><td>9</td><td>Ends with</td><td> </td></tr><tr><td>10</td><td>IS null</td><td> </td></tr><tr><td>11</td><td>IS NOT NULL</td><td> </td></tr><tr><td>15</td><td>IN DAY</td><td> </td></tr><tr><td>16</td><td>IN MONTH</td><td> </td></tr><tr><td>17</td><td>IN YEAR</td><td> </td></tr><tr><td>20</td><td>HAS</td><td> </td></tr><tr><td>21</td><td>HAS NOT</td><td> </td></tr><tr><td>22</td><td>HAS NONE</td><td> </td></tr><tr><td>23</td><td>HAS ANY</td><td> </td></tr><tr><td>24</td><td>IN</td><td>Only rows with values equal to one of the given (comma separated list)</td></tr></tbody></table>

If you are signed in as an admin on a server with a version newer than 7956, you will have access to the "url button".

This button redirects you to the full url, that generates the current filtering, that you have on your list.

This might be an easier way to generate the correct url, when creating dashboard-list-widgets.

#### <span class="mw-headline" id="bkmrk-special-case%3A-list-o-1">Special case: List of values in search parameters</span>

Currently lists of search parameters are encoded

```
 ... &QUERY_VALUE_3=11344%2C11343%2C11345& ...
```

To make them work in list views, please decode the kommas

```
 ... &QUERY_VALUE_3=11344,11343,11345& ...
```

(just change "%2C" to ",")

### <span class="mw-headline" id="bkmrk-dynamic-selectors-1">Dynamic selectors</span>

A special case of the SQL query is the selectors. A selector will allow you to select a value or an ID for use as a parameter in another SQL query or a List view.

A selector will have a name defined by setting its column name in the query

```mysql
  SELECT DataID, CUSTOMER AS SelectedCustomer FROM data_customer WHERE StatusID = 123
```

In this case the value of DataID is set in a user specific variable named SelectedCustomer. The proper notation is in brackets \[\].

This can now be used in list and SQL definitions

```mysql
  SELECT BILLINGDATE, AMOUNT FROM data_invoice WHERE CUSTOMER = [SelectedCustomer]
```

# Other widgets

#### <span class="mw-headline" id="bkmrk-codeunit-1">Codeunit</span>

This widget displays raw HTML ouput from any PageContentCodeunit.

#### <span class="mw-headline" id="bkmrk-distribution-1">Distribution</span>

This creates one of many diagrams to show records of an entity. Bar, circle, and table diagrams work, but the others don't.

It should at the very least have the SagID of the entity and a QUERY\_GROUPBY parameter to know what should be shown. If it does not have this, it will always show the status of the records.

Content should look something like this:

```
?SagID=3&QUERY_GROUPBY=TOPIC
```

# Pro tips

### <span class="mw-headline" id="bkmrk-asking-the-right-que-1">Asking the right questions</span>

To add the right content into dashboards widgets you will need to define what should be displayed

First choose either List of records (worklists) or Aggregated values (charts, tables or number)

**List of records**

- Fields displayed in list ?
- Sorting field ?

**Aggregated values**

- Format ? 
    - Number
    - Table
    - Chart (pie,bar)
- Metric ? 
    - Field
    - Method (sum, average, min, max)
- Option: Categorization (group by another fields value)
- Option: Link to detailed data 
    - URL to link to ?
    - Icon on button ? 
        - Browse [| Font awesome version 4](https://fontawesome.com/v4.7.0/icons/) icons

Next consider which **filters** limit the data

- Period ranges ?
- Status limitations ?
- User relation information ?

In case you are generating normal record lists no effort is required for security filters and hiding deleted items.

# Status Overview

Added in version 11977.

Shows a simple overview of amount of records in different status's (and status groups), with an custom line title and color.

### Sample

[![image.png](https://docs.tsnocode.com/uploads/images/gallery/2026-05/scaled-1680-/H7W37Xr5SkOzgCja-image.png)](https://docs.tsnocode.com/uploads/images/gallery/2026-05/H7W37Xr5SkOzgCja-image.png)

### Configuration

The widget expects the "solution" to be the system name of the entity/solution that data is shown from, and the content to be setup in the following manner.  
The css class part is optional.

```
[Line name]#[Line CSS class]:[Comma-list of StatusID or StatusSystemName]
```

Eg.

```
New tasks#orange: 57, NEW
In progress#blue: 58
Done#green: 59, DONE
```

### Translation

Add contant-translation formatted like this:

```
da:Nye opgaver
Igang
Færdig
```

### Styling

The colors green, blue, yellow, orange and red, are defined by default in the platform.  
To use a custom color, use a custom css class in the configuration and add the following code to the widgets style.

```css
#THISWIDGET# .my-color .bar .bar-progress {
  background-color: var(--primary);
}
#THISWIDGET# .my-color .badge-col .badge {
  color: #FFF;
  background-color: var(--primary);
}
```