Need to group by User name in the multi level pivot table

[SamanthK]

I am trying to create  two different reports on a custom table

Report 1 :  Report Type : Bar graph 

I'm trying to create a bar graph report on a custom table which should be grouped by application field. 

In this report, I want to get only one record for one user, if there are multiple records for one user name

Can this also be applied to multi level pivot table. 

Note : Application and User name is string field. 

[Bhuvan]

@SamanthK 

Use group by 'application field' and select metric aggregation 'Count distinct' on field 'user name'

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[SamanthK]

I am not getting the Custom user name field in count distinct.  The user name is string field. 

[Bhuvan]

@SamanthK 

Can you share your table and fields definition [username and application] along with list view of table records ?

Example I have attached from incident table is a string field 'short description'.  From your initial statement, why user name is a string field and not a reference field ?

If this helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[AbinC]

Hi @SamanthK ,

To create a bar graph report on a custom table, grouped by the application field, and show only one record per user, you can use the following approach:

Report Configuration
1. *Report Type*: Bar Graph
2. *Table*: Custom Table
3. *Group By*: Application (string field)
4. *Aggregate*: Count of Users (or another relevant metric)

Show Only One Record per User
To show only one record per user, you can use a *Distinct* or *Group By* on the User field. However, since you want to group by Application, you can use a *Count Distinct* aggregation on the User field.

Multi-Level Pivot Table
For a multi-level pivot table, you can add additional groupings or aggregations. For example:

1. *Row Grouping*: Application
2. *Column Grouping*: Another field (e.g., Department)
3. *Aggregation*: Count Distinct of Users

Configuration Steps
1. Go to Reports > Create New Report
2. Select the custom table and report type (Bar Graph)
3. Configure the Group By and Aggregate settings as desired
4. Use the *Count Distinct* aggregation on the User field to show only one record per user
5. For a multi-level pivot table, add additional groupings or aggregations as needed

Example
Suppose your custom table has the following fields:

- Application (string)
- User (string)
- Department (string)

You can create a report with:

- Group By: Application
- Aggregate: Count Distinct of Users

This will show the number of unique users for each application.

If you want to add another level of grouping, you can add:

- Column Grouping: Department

This will show the number of unique users for each application and department combination.

If you find this answer helpful please mark it as helpful and accept as solution 

Thanks,

Abin