Hi Roshani,
Seeing all three 3 criteria directly on a single report including all groupings and aggregations is tricky as it would require a 3D cube. Human readable reports are normally 2D, and additional dimensions must be included via some trick. Multi-level pivot tables can do the trick. But before ending with the multi-level pivot table report, let's check what kind of value simper types of reports still can deliver.
--------------------------------------------------------------------------------
I. A simple list report
Let's start with an ordinary List report on the table: Requested Item
Beware, there are two Configuration Item field on the Requested Item, one with the column name cmdb_ci, this is the out-of-the-box field inherited from the Task table, and the second with the column name configuration_item, this is the own column of the Requested Item table. You probably mean configuration_item.
1) Name your report, choose the Source type = Table and select Requested Item table:
2) Choose the List type of the report
3) Select desired columns into the report:
Number, Created, Item, and Configuration Item (configuration_item):
4) Save the report and run it, you can use the filter in a standard way to work with all 3 criteria:
Here for example all RITMs created on April 2022:
Value this simple report can provide:
- Simple and straightforward (you can even consider using a list view of the Requested Items insted of this report, teh result will be the same)
- You can construct a filter of any complexity to show exactly what you mean - in the filter you can combine all you 3 criteria, filter building functionality makes it quite easy
--------------------------------------------------------------------
II. Trend report
And now to something more sophisticated - Trend report
This type of report is suitable for showing data over time - this is probably what you are after, showing data per months. Considering Months as one criterion, the next two: Catalog Item and Configuration Item can be added as primary and additional Group by.
1) The same first step: name your report, choose the Source type = Table and select Requested Item table:
2) Now choose the type Trend
3) Now it is a bit trickier, you need to specify what fields will be used for grouping, let's choose Item as primary Group by and Configuration Item as Additional group by, the you need to choose Created as Trend by, and select Month as "per", like this:
4) Save the report and run it, the result will look like this
Now, you can:
- Change the Group by - you can use Item or Configuration item
- Show the details of the color sections of the bars - you can see for instance number of
- Drill down into the details by clicking the the color sections of the bars - it will show you the
- Share the report a standard way with other users, use it on dashboards, schedule it and automatically distribute, etc.
The value of the Trend report:
- It nicely aggregated data per months - it this is your preferred grouping, this type of report might be a good option for you
- It helps user to specify "filter" for additional two criteria: Item & Configuration Item, just by clicking you can specify what Items or CIs you want to see or filter out (unfortunately you cannot do that together for both)
- The drawback of the previous point can be mitigated by additional usage of a standard filter, it is available also in this report so combining it with a filter specified by clicking Group by section (either Items or CIs) you can easily create a filter using all 3 criteria
--------------------------------------------------------------------------------
III. Multi-level pivot table
If you want to see really all 3 criteria at a single report (in the previous bar/trend report you have to change the group by to switch between Item and Configuration Item criteria), the multi-dimensional report is needed, multi-level pivot table can do the job:
1) Start of the report configuration will be the same, only choose Multi-level pivot table as a report type:
2) In the Configure section you need to decide where you want to see Created (columns or rows?), let's assume it will be columns (for longer period the report will be wider), the Item and Configuration should stay together in the "opposite" dimension, if Created is in Columns then put them both in Rows. And next decision is about what will be first and what second in the rows, first should be the more significant for the reporting, let§s assume it's Item (what kind of the Catalog Item it is).
The configuration will look like this:
Save and run the report and the result will look like this:
The report will start grouping Columns - Created once there will be more then max number of groups specified (set to default 50).
The value of the Multi-level table report:
- There are really all 3 criteria presented directly on the report
- You can easily hide unwanted details
- You can very quickly get to RITMs meeting all 3 criteria
- The drawback is a higher complexity, using this report efficiently might require some training
More about Multi-level pivot table reports:
I hope this helps.
Regards,
Jan
