0

Group By option for grids

I have this report that shows allocation details for sales invoices, and I'm having a problem with transactions that cancel each other out; when we reverse a transaction in our system, it creates a new voucher with all the same details as the original one, the only thing that will be different will be the amount and the record ID- so Pyramid will group both the transactions together:

 I can add the recid (SettleRecId) into my grid to make it show as separate lines like the user wants:

But I don't really want to have the recid showing in the rows- I tried putting it in filters as combined elements, but that still groups everything together

So, I'm wondering if there is a way to group a grid by a particular dimension, without having that dimension shown in the grid itself (like if I was writing a sql query I would have the recid in the group by clause but not in the select clause)

8 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    Unfortunately I can see no way to achieve this. In order to see the individual transactions, then the transaction id must be present  in the report, otherwise Pyramid will aggregate the values by the next level of nested dimension, as you have discovered.

    The only way I can see to accomplish this is to be able to hide the transaction column in the rendered grid. Again, though, unfortunately, currently the columns can only be minimised, not hidden, which means you can still see them as they are 2 or 3 characters wide.

    If this type of report is critical to your business, then please make a product enhancement request to be able to hide grid columns / rows.

    Hope this helps.

    Ian

      • Sophie_durrant
      • 2 yrs ago
      • Reported - view

      Ian Macdonald thanks for your reply. When you say I should make a product enhancement request, do you mean I should post in the Product Ideas forum?

    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    Yes. I think ability to hide rows / columns would be useful in  a number of scenarios.

    But, I do have an alternative solution for you.

    If you drop the transaction id into the Color drop zone, it will split out the transactions by colour (I selected background), but not include the Transaction dimension in the report:

    It does make the report rather colourful, so to elminate that, you could create a new Theme where all the colours in the palette are set to white:

    Set the report to that theme and hide the legend:

    Bingo! Sort of... :) You can probably play around with the Theme in other ways to change the overall appearance and effect of the Transaction dimension in the colour Drop Zone.

    Hope that helps.

    Ian

    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Actually, it is easier to override the background colour drop zone formatting using the grid formatting options:

     

      • Sophie_durrant
      • 2 yrs ago
      • Reported - view

      Ian Macdonald I can't find the grid formatting options! Can you please point me in the right direction?

    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    What version of Pyramid are you using?

    The formatting panel as  in my screenshot is available in 2020.20 and onwards. You access it by clicking on the format pointer, or the formatting Panel on the right:

    If you have a release prior to 2020.20, then the formatting options are in the Grid submenu:

    Select the Column / Row you want to edit by clicking on the row or column header, click on the Data drop down and select the background colour.

    Hope that helps.

    Ian

      • Sophie_durrant
      • 2 yrs ago
      • Reported - view

      Ian Macdonald Hi, I have managed to format the data in the grid as you said, but I can't work out how to change the formatting on the totals row- is it possible? I'm on version 2020.18.055

    • Senior Director of Product
    • Ian_Macdonald
    • 2 yrs ago
    • Reported - view

    Hi Sophie,

    I used background formatting rather than foreground as I found it easier to override the background then the foreground. I also found that using the Transaction in the Color Drop Zone, didn't affect the Totals when using Background.

    If it does, then try using / setting the Totals Formatting button in the Grid sub-menu bar.

    Ian

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 8Replies
  • 50Views
  • 2 Following