IBM Cloud customers that have multiple departments or applications that share an IBM Cloud account often need to report on the usage by an Organization or Resource Group. This can be handy or required to understand consumption of credits as well as chargeback to each organization.
The Usage Dashboard page (https://cloud.ibm.com/billing/usage) has a “Filter by group” dropdown which allows you to select Organizations or Resource Groups. The page is then refreshed showing the total usage for the selected Orgs and Resource Groups along with the Services.
Pivot Table on exported CSV file
On that same Usage Dashboard page, there is an “Export CSV” button. You can click on that button and select “Instances” to export all the instances to a CSV file. You can add an Excel filter on the Headings row, this allows you to filter on columns such as “Resource Group Name” or “CF Org”.
A common request by customers is to get a report that sums up all instances by Org or Resource Group. You achieve this via Pivot Tables in Excel. Here are the steps for that:
- This may vary by Excel version, but under the “Insert” tab, there is a “Table” button. Click on that “Table” button and Excel should detect the range of your data rows and columns along with headers. Leave the “My table has headers” checkbox checked. Click OK, and your data should now appear in an Excel Table format similar to this:
- Click on the “Recommended Pivot Tables” button, Excel then provides several suggestions on Pivot Tables. If any of the suggestions are what you are looking for, select it and click OK. I usually select the first recommendation, “Sum of Original Cost by Resource Group ID”, as I will modify the Pivot Table later. This will generate a Pivot Table on a new Worksheet.
- Now you can modify the Pivot Table as needed by clicking the field check boxes and dragging to the lower sections. Here is an example of calculating the Sum of Usage Cost by CF Org:
You can optionally add another row subtotal such as CF Space like the following:
To generate the same Pivot Table for Resource Groups, select the “Resource Group Name” field instead of “CF Org”.
The Export CSV reports are currently only for a single month so if a trend report is desired over a period of several months, you can download each monthly report, add it a larger single table and then include the Month as another Rows field in the Pivot Table.
The rows that are (blank) are due to instances either belonging to Orgs or Resource Groups, not both. As an example, Watson instances belong to Resource Groups, so it will have a blank CF Org value.