How to wrap script in a script include a call from a report created through the report designer

[AndersBGS]

Hi all, 

I have created below script which reference a database view in a custom scoped app, where dt_dashboard.sysid reference the dashboard sysid. I need to populate this script (or similar) in a script include which I then call from a report created through the report designer. How to do this in the best way?

var dashboards = new GlideRecord('x_431699_dashboard_items');
dashboards.addNotNullQuery('dt_dashboard.name');
dashboards.orderBy('dt_dashboard.name');
dashboards.query();

gs.info('---> ' + dashboards.getRowCount());

var dashboardList = [];
while(dashboards.next()) {
     dashboardList.push(dashboards.dt_dashboard.name.trim());
}

var arrayUtil = new ArrayUtil();
dashboardList = arrayUtil.unique(dashboardList);

gs.info('---> ' + dashboardList.length);

for (var i = 0; i < dashboardList.length; i++) {
     gs.info('---> ' + dashboardList[i]);

}

The report on the dashboard whiteout any filtration looks like below:

The database view for dashboard is configured as below:

Basic need is, I need to query the unique values from the database view and create a report for this.

@Technomonk @Tai Vu @Ankur Bawiskar @Community Alums 

Best regards

Anders

[Ankur Bawiskar]

@AndersBGS 

you should use IS ONE OF and not IS as you are returning multiple sysIds

[AndersBGS]

Hi @Ankur Bawiskar ,

Thank you for you help, even though I made it work with the sysid is:

Unfortunately, it didn't resolve my problem as I thought it would, due to that it's not only showing unique dashboards name, due to that the database view doesn't have any unique value for the dashboard or/and a unique sysid. 

I'm not sure if you have any other good idea on how I can only show unique dashboard name in a listview?

Best regards

Anders

[Tai Vu]

Hi @AndersBGS 

I'm having difficulty grasping your use case.

Based on the provided script and report, it appears that we're retrieving the dashboard sys_id from x_431699_dashboard_items using the getUniqueDashboardNames function.

Subsequently, we create a report within the same x_431699_dashboard_items table (db view), filtering the dashboard sys_id to be one of those returned by the aforementioned function.

Essentially, this implies that applying the function yields the same result. We can construct a report with the following query, which should produce identical results:

dashboards.addNotNullQuery('dt_dashboard.name');

Could you please provide more specific details regarding the business requirement we are aiming to fulfill?

Cheers,

Tai Vu

[AndersBGS]

Hi @Tai Vu ,

Sure, I also came to the conclusion based on the script etc. that I would obtain the same result as the DB view is giving mulitple entries of the same Dashboard in a DB view (this is as intended):

Now, I would like to create a report to show this DB view, but only show unique values in Dashboard column (the others columns doesn't matter).

Best regards

Anders 

[Tai Vu]

Hi @AndersBGS 

I can see from your database view, there are 2 Automated Indicators linked to a Formula Indicator, and 2 Jobs associated with the Automated Indicators. The Formula Indicator is utilized in the IT Manager dashboard.

Therefore, when listing the data, it will show 4 separate records due to the presence of 2 Automated Indicators and 2 Jobs linked to the IT Manager dashboard.

This is expected behavior because of the interconnected data. Attempting to consolidate into one record for the IT Manager dashboard would result in missing other information from the list.

Perhaps we could leverage using a group by on the dashboard field.

Cheers,

Tai Vu