Use different dimension for display other than the one for grouping

[mnifakram]

Problem

How can I use another dimension for display?

Related Cube.js schema

cube(`Participations`, {
  sql: `SELECT * FROM public.participations`,

  joins: {

  },

  measures: {
    count: {
      type: `count`
    }
  },

  dimensions: {    
    stepDefinitionId: {
      sql: `activity_id`,
      type: `string`
    },

    id: {
      sql: `id`,
      type: `string`,
      primaryKey: true
    },

    participantName: {
      sql: `participant_name`,
      type: `string`
    },

    stepDefinitionName: {
      sql: `activity_name`,
      type: `string`
    },

    participantId: {
      sql: `participant_id`,
      type: `string`
    },
  }
});

Cube.js JSON query

{
      measures: ["Participations.count"],
      timeDimensions: [],
      dimensions: [
        "Participations.participantId",
        "Participations.activityId"
      ],
      filters: [],
      order: {}
    }

I'm wondering if there is a way to select the participantName and activityName instead of participantId and activityId. In other words use participantId and activityId for grouping and use others for display purpose.

Thanks

[RusovDmitriy]

Hey @mnifakram! Please see following links about ungrouped option:

• https://cube.dev/docs/@cubejs-backend-server-core#options-reference-allow-ungrouped-without-primary-key
• https://stackoverflow.com/questions/58264957/how-to-remove-cubejs-default-group-by-clause-from-query

Also you could add these fields as dimensions and config display in your client code:

{
      measures: ["Participations.count"],
      dimensions: [
        "Participations.participantId",
        "Participations.activityId",
        "Participations.participantName",
        "Participations.stepDefinitionName"
      ]
}

[mnifakram]

Hey @RusovDmitriy! Thank you for the quick response

• If I add these fields as dimensions they will be included in the grouping which is something that will get false results as I may have two participants named Jhon Doe but with different id. So this is not an option, I think.
• Ungrouped as far as I understand it will remove groupBy from the generated SQL, Is there a way that I can add my own group by clause?

[RusovDmitriy]

two participants named Jhon Doe but with different id - it is no problem, because participantId and activityId also included in the grouping.

If the possible situation with diff name but the same id - you could use aggregation funcs and rewrite base SQL for cube schema:

SELECT 
 id
 participantId,
 activityId,
 STRING_AGG(participantName) as participantName,
 STRING_AGG(stepDefinitionName) as stepDefinitionName
FROM public.participations
GROUP BY id, participantId, activityId

[mnifakram]

Got it, thank you so much for your help @RusovDmitriy.