Re: Group total only in a report

GriffAdmin · 05-18-2022 03:30 AM

My table called MaterialsUsed has the following basic fields: OrderId (related to my Orders table); MaterialUsedId, ItemNum (related to a Materials table); Qty. There can be several entries in the MaterialsUsed table for the same ItemNum on the same Order so I need to create a report that lists only the sum by ItemNum for a given Order.

Do I need to create the total as a VC in the MaterialsUsed table to calculate the total by ItemNum for each order and then use that in the report, or is there a way to do the total by ItemNum by Order within the Start statement in my report?

Joseph_Seddik

You can put the relevant expression in the template directly without needing to have a virtual column in your table.

Suvrutt_Gurjar

I think you should be able to do the needful in the report. If you could share more details about say existing start expression etc. of the report , meaning if the report is for all orders or single order etc., then probably we can opine better on feasibility.

Robbo

Thanks for the confirmation that it can be done. The report I am trying to create is one email per Order. I know how to list all the individual associated MaterialsUsed records, but I have no idea how to sum them

JeofryDC

have you tried this option? You just have to group per order then it will generate the sum of your order on top of the table group beside the group name

Robbo

I just saw your suggestion - sorry I missed it earlier. Doesn't that just give the view on screen? I need to email my in my email body

JeofryDC

Ah yes, then you will have to create an VC on your header table for the sum of the orders.

Joseph_Seddik

As per @Suvrutt_Gurjar's advice, you need to share more details about your template. Please do.

Steve

<<Start: FILTER(
  "MaterialsUsed",
  (
    [_ROWNUMBER]
    = MAX(
      SELECT(
        MaterialsUsed[_ROWNUMBER],
        AND(
          ([_THISROW-1].[ItemNum] = [ItemNum]),
          ([_THISROW-1].[MaterialUsedId] = [MaterialUsedId])
        )
      )
    )
  )
)>>

<<SUM(
  SELECT(
    MaterialsUsed[Qty],
    AND(
      ([_THISROW-1].[ItemNum] = [ItemNum]),
      ([_THISROW-1].[MaterialUsedId] = [MaterialUsedId])
    )
  )
)>>

Sayad

Hi @Steve

Can you explain this expression better? Can you tell me which column is this expression for grouping.

GriffAdmin

Thanks for the support. Sorry, but I was at a meeting till late last night and arrived home too late to provide a reply with more details. I see Steve has provided a template for me for which I am grateful.

If I understand it correctly, the first step creates a filtered list of unique MaterialId's and the second step adds the Qty by ItemNum.

When I tried running the code, I got the message "MAX(SELECT(MaterialsUsed[_ROWNUMBER], AND( ([_THISROW-1].[Item Number] = [Item Number]), ([_THISROW-1].[MaterialUsedId] = [MaterialUsedId]) ) ) ) ))' is invalid due to: Error in expression '[_THISROW-1].[ItemNum]' : Unable to find column 'ItemNum'.".

I spent about 6 hours yesterday trying to come to grips with this and got the following code to run, but it provided incorrect results. Please note that I abbreviated my field names in the question but the following code uses my actual field names. With the test data I was using, the first two lines were correct, but the rest was distorted.

<<START: UNIQUE(
SELECT(
MaterialsUsed[Item Number],
AND(
[Day Sheet Id].[OrderId]=[_THISROW-1] ,
[Item Number]<>”.Other”,
[Item Number]<>”” ,
ISBLANK([Day Sheet Id].[InvoiceNum]
)
)
)
)>>
<<SUM(
SELECT(
MaterialsUsed[Qty],
AND(
[Charge Basis]<>”Metre”,
[Item Number]<>”.Other” ,
[Item Number]<>””,
[Day Sheet Id].[OrderId]=[_THISROW-2],
[Item Number] = [_THISROW-1].[Item Number],
ISBLANK([Day Sheet Id].[InvoiceNum]
)
)
)
)>>

GriffAdmin

Unfortunately I just can't seem to figure out how to get this to work. I know I've been asked for more information, but I don't know what to provide.

Because I am running a report for each row of the Orders table, I can get it to display details of the order quite successfully. And when I insert the following code in a table , it lists the records I want perfectly.

<<Start: OrderBy(SELECT([Related MaterialsUseds][MaterialUsedKey],AND([Item Number]<>".Other",ISBLANK([Day Sheet Id].[InvoiceNum]))), [Item Number])>><<[ Item Number]&” - “&[Description]>><<End>>

I just can't seem to figure out how to get it to list a summary of the items (other than Item Number = ".Other") with total by item.

Suvrutt_Gurjar

Thank you. Please try an expression something like below. The additional part that is expected to calculate quantity is highlighted in blue.

<<Start: OrderBy(SELECT([Related MaterialsUseds][MaterialUsedKey],AND([Item Number]=".Other",ISBLANK([Day Sheet Id].[InvoiceNum]))), [Item Number])>>

<<[ Item Number]&” - “&[Description]>>

<<IFS(

ANY(SELECT([Related MaterialsUseds][MaterialUsedKey],AND([Item Number]=".Other",ISBLANK([Day Sheet Id].[InvoiceNum]))))=

INDEX(SELECT([Related MaterialsUseds][MaterialUsedKey],AND([Item Number]=".Other",ISBLANK([Day Sheet Id].[InvoiceNum]))),1),

SUM(SELECT([Related MaterialsUseds][Qty],AND([Item Number]=".Other",ISBLANK([Day Sheet Id].[InvoiceNum]))))

)>>

<<End>>

GriffAdmin

Hi, thanks Suvrutt.

My report will need to list each Item with Item Number = ".Other" individually as these are materials not in the Material file so listing those is easy. I changed your code to summarise by Item Number for Materials with Item Number <> ".Other" and ran it. It seems to list the items but the total it is giving is the same total for each product.

Let me give an example to show what I need. Let's say my order has the following entries

Qty Item Num Descritpion

3 1234 Widget

2 1246 Gidget

4 1234 Widget

The summary I need would show

Qty Item Num Descritpion

7 1234 Widget

2 1246 Gidget

Suvrutt_Gurjar

Hi @GriffAdmin ,

What is the meaning of [Item Number]=".Other" in the expressions? In example you have numeric item numbers 1234 etc. Could you update the difference?

GriffAdmin

It's a misnoma coming from the MYOB accounting system we use. Items in MYOB have a key field called Item Number, but it's actually a text field. The table I use for materials is downloaded from MYOB.

When a user adds an item with Item Number = ".Other" to an Order, the app will ask them to enter a description and some other details so the report I create needs to list these individually with the supplied description. That's easy.

The report also needs to list other entries in the order summarised by item number. We use this report to create an invoice so, using the example supplied above, our invoice includes one line with 7 widgets rather one line with 3 widgets and another line with 4 widgets. That's the part of the report I am having difficulty creating

GriffAdmin

I'm making progress but need help to work out how to get the following to only list one of each of the Item Numbers when there are several records with the same Item Number.

UNIQUE() will only give me unique MaterialUsedKeys

If I change the Select statement to: <<START: OrderBy(SELECT([Related MaterialsUseds][Item Number]... the resulting recordset only give me access to records from my Materials table, not Related MaterialsUsed thereby precluding me from accessing to the Qty field.

<<START: OrderBy(SELECT([Related MaterialsUseds][MaterialUsedKey],AND([Item Number]<>”.Other”, ISBLANK([Day Sheet Id].[InvoiceNum]))) , [Item Number])>>

GriffAdmin

Thanks to everyone for your input. I ended up getting some off-line help that made it clear I was asking too much of a report, so I created 2 VC's and a Slice and generated the report just the way I needed it from that. Not sure how I should close out this question, but I have it working.