Creating a Custom Report

In the Report management page, click
Create New Template
on the right side.
Give the template a name and select a data source.
Click
Next: Template Builder
.The interface shows five elements labeled:
The query Explorer, located on the left of the screen on the Template Builder.
The query editor, located on the right of the query Explorer.
The query results, located on the right of the query editor.
the Documentation Explorer, located to the right of the query results.
The Transformation Template editor, located at the bottom of the Template Builder.
Once the required information has been entered, click
Save
Optionally, you may associate an Excel template as well. Select
Yes
from the 'With Template' dropdown and choose the file that you wish to associate.
Click
Create Template
to finish.

The data is requested using the GraphQL query.
The data is received and transformed with a transformation template.
The data is output to an Excel template, or optionally to a CSV or JSON file.

The GraphQL Query: An Overview

{
  costLineItems (
    period: { eq: 202011 }
    ccCustomerTotalCostConverted: { gt: 10 }
  ) {
    providerName
    assetType
    category
    ccCustomerTotalCostConverted
    quantity
    serviceStartDate
  }
}

{
  costLineItems

{
  costLineItems(
    period: { eq: 202011 }
    ccCustomerTotalCostConverted: { gt: 10 }
  )

{
  costLineItems(
    period: { eq: 202011 }
    ccCustomerTotalCostConverted: { gt: 10 }
  ) {
    assetType
    category
    ccCustomerTotalCostConverted
    providerName
    quantity
    serviceStartDate
  }
}

{
  "data": {
    "costLineItems": [
      {
        "assetType": "Amazon Elastic Compute Cloud",
        "category": "Compute",
        "ccCustomerTotalCostConverted": 47.205000461,
        "providerName": "aws",
        "quantity": 9442,
        "serviceStartDate": "2020-11-01T00:00:00.000Z"
      },
      {
        "more": "costLineItems..."
      }
    ]
  }
}

The GraphQL Query: In Depth.

{
  costLineItemsWithDRGs(
    group: [
      { field: "allTags.DeliveryPlatform" }
      { field: "allTags.Toolname" }
      { field: "allTags.RFS-Invoiceable" as: "Invoiceable" }
    ]
  ) {
    grouped_data
  }
}

{
  "data": {
    "costLineItemsWithDRGs": [
      {
        "grouped_data": {
          "DeliveryPlatform": "Prod",
          "Invoiceable": "Yes",
          "Toolname": "MyToolName"
        }
      },
      {
        "grouped_data": {
          "DeliveryPlatform": "Dev",
          "Invoiceable": "No",
          "Toolname": "MyToolName"
        }
      },
      {
        "more": "costLineItemsWithDRGs..."
      }
    ]
  }
}

{
  costLineItemsWithDRGs(
    group: [
      { field: "providerName" }
    ]
    sum: [
      { field: "allTags.CPU-Max" as: "Cores" }
      { field: "allTags.Memory-Max" as: "Memory" }
      { field: "ccCustomerTotalCostConverted" as: "Cost" }
      { field: "quantity" as: "instanceQuantity" }
    ]
    count: [
      { field: "providerAssetId" as: "assetCount" }
    ]
    min:[
      { field:"invoiceStartDate" }
    ]
    max:[
      { field:"invoiceEndDate" }
    ]
  ) {
    grouped_data
  }

{
  costLineItemsWithDRGs(
    group: [
      { field: "providerName" }
    ]
    count: [
      {
          field: "allTags.Environment"
          as: "DevServers"
          if: { field: "allTags.Environment" in: ["DEV", "Dev"] }
      }
      {
          field: "allTags.Environment"
          as: "ProdServers"
          if: { field: "allTags.Environment" in: ["PRD"] }
      }
      {
          field: "allTags.Environment"
          as: "BareMetalServers"
          if: { field: "allTags.Environment" in: ["BMTL"] }
      }
    ]
  ) {
    grouped_data
  }

  {
  costLineItemsWithDRGs(
    let: [
      {
        name: "assetSubType"
        search: "providerAssetId"
        default: "unknown"
        cases:[
          {
            if: { regex:"i-" }
            value: "instance"
          }
          {
            if: { regex:"vol-" }
            value: "volume"
          }
        ]
      }
    ]
  ) {
    providerName
    assetSubType
  }

{
  costLineItemsWithDRGs(
    steps: [
      {
        group: [
          { field: "providerName" }
          { field: "providerAssetId" }
        ]
        sum: [
          { field: "ccCustomerTotalCostConverted" as: "Cost" }
        ]
      }
      {
        let:[
          {
            name: "assetSubType"
            search: "providerAssetId"
            default: "unknown"
            cases:[
              {
                if: { regex:"i-" }
                value: "instance"
              }
              {
                if: { regex:"vol-" }
                value: "volume"
              }
            ]
          }

        ]
      }
      {
        group: [
          { field: "providerName" }
          { field: "assetSubType" }
        ]
        sum: [
          { field: "Cost" }
        ]
      }
    ]
    ) {
      grouped_data
    }

The Transformation template

{
  "data": {
    "costLineItems": [
      {
        "assetType": "Amazon Elastic Compute Cloud",
        "category": "Compute",
        "ccCustomerTotalCostConverted": 47.205000461,
        "providerName": "aws",
        "quantity": 9442,
        "serviceStartDate": "2020-11-01T00:00:00.000Z"
      },
      {
        "more": "costLineItems..."
      }
    ]
  }
}

data.{MyReportName: costLineItems}

The advanced transformation template shown above filters out any data where category equals ```All```.

It also pulls specific values from the ```allTags``` array into named columns. For example, the ```DataCenter``` column in the transformation template is populated by a value from the allTags array where the tag key ```== 'DataCenter'.

data.{MyReportName: costLineItemsWithDRGs.{
  DeliveryPlatform: grouped_data.DeliveryPlatform,
  Invoiceable: grouped_data.Invoiceable,
  Toolname: grouped_data.Toolname
}}

data.{
  sourceDataCosts: myQueryA[].{
    category: grouped_data.category,
    cost: grouped_data.ccCustomerTotalCostConverted
  },
  sourceDataAssets: myQueryB[].{
    category: grouped_data.category,
    cost: grouped_data.ccCustomerTotalCostConverted
  }
}

The Excel Template

Report tab
Source Data tab

Upload the Excel template when you save your custom report.
On the reports list page, select
[Generate Report]
from the 3-dot overflow menu to the right of the report you just created.
Check the bell icon at top of page for a notification that your report is ready.
Download and open your report -- the Excel document should have one or more new tabs that contain source data.
The formulas/macros should correctly populate the Report tab(s) from the Source Data tab(s).

The Explorer and Docs

Deciphering Cost fields

Special Filter Fields

Special Project fields

InstanceCount: group(get: "instanceQuantity")

providerLocation: concat(values: ["providerName", "location"], sep: "-")

providerCountry: substr(field: "location", start: 0, end: 1)

serverName: jmespath(search: "allTags.Name")