Reporting by Department/Company using Assets (DC)

Fred Selchow

Product/Version:

• Jira Software 13.3 (DC)

• Jira Service Management 11.3 (DC)

• Assets 

• No additional plugins (no ScriptRunner)

---

Background

I am trying to build a reliable reporting and dashboard solution to show:

• Number of tickets per department (current)

• Number of tickets per department over a defined period (e.g. last 12 months)

User data is imported from LDAP/Active Directory and stored as objects in Assets.

---

Assets Data Model

Object Type: User

Key                  Text
Name                 Text
Created              DateTime
Updated              DateTime
Username             Text        // sAMAccountName
Display Name         Text
EMail                Email
Jira User            User
Department           Object → Department
Company              Object → Company
Unique_ID_Attribute  Text

Object Type: Department

Key            Text
Name           Text
Created        DateTime
Updated        DateTime
Company        Object → Company
DepartmentKey  Text

Object Type: Company

(referenced by User and Department)

Relations:

User.Department     → Department
User.Company        → Company
Department.Company  → Company

---

Problem

For reporting and standard Jira dashboards (e.g. Two-Dimensional Filter Statistics), only JQL and issue fields are available.

Limitations:

• No dot notation in JQL (e.g. User.Department.Name)

• No traversal of Assets references in standard gadgets

• Assets AQL cannot be used directly in Jira gadgets

• Data Center is significantly more limited compared to Cloud in this area

---

Current Approach

The idea was to introduce additional fields, e.g.:

Department (Asset Object Reference)
Company (Asset Object Reference)

Goal:
→ populate these fields directly in the issue so they can be used in JQL and gadgets

Problem:

• These fields are not automatically populated when creating an issue

• They would need to be selected manually

• This contradicts the goal of having automated and consistent reporting

---

Goal

I am looking for a clean and maintainable approach to:

1. Automatically bring Department/Company from the Asset User into the issue

2. Make these values usable in JQL and standard gadgets

3. Avoid any manual data entry in issues

4. Avoid using additional plugins

---

Questions

1. Is there a way in JSM Data Center to automatically populate Assets reference fields during issue creation (without ScriptRunner/Groovy)?

2. Is there a recommended best practice for reporting based on attributes from referenced Assets objects?

3. Is the approach of using “flattened” additional fields in the issue the only viable option in Data Center?

4. If yes, what is the typical way to automatically populate these fields (workflow, automation, import logic)?

---

Summary

The main issue is the inability to directly use referenced Assets data (Department/Company) in standard Jira reporting.

I am looking for a way to resolve or “flatten” these references automatically so they become usable in JQL and dashboards — without manual maintenance and without additional plugins.

Jeroen Poismans

Hi and welcome to the community!

That is possible! You were on the right track with the 2 fields, it is just the configuration. We will taken Department as example, Company  will then be similar.

The Department field will have to be on the View screen, not the Edit (otherwise it can be changed).

Let's assume the id of the User field is customfield_xxx

Department field config

• objecttype IN ("Department")
• Filter Assign Scope (this is where the magic happens)
  

  object having inR(Key = ${customfield_xxx.key})

  
  OR

  object having outR(Key = ${customfield_xxx.key})

  Depending of the direction of the link (test both)
• Check the "Make Filter Assign Strict" box

Now what this does is whenever the User is changed, the Department field is updated with the linked Department too.

When you get this to work, same for Company => Department link.

Give it a try!