Solved

How to Get a Value to Show Only Once In Report Designer

  • 3 September 2022
  • 8 replies
  • 489 views
C
Rank
Userlevel 4
Badge

Hi everyone,
I would like to know what would be the best way to isolate a value that appear in all the lines. 

I am working on a freight cost report to have SOPackageDetail in its relation. When I add the table, my DocAmt and FreightCost repeat itself based on the number of line in SOPackage,LineNbr. I try to use below formula to filter out but not successful:

=IIF([SOPackageDetail.LineNbr]<>first(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])

=IIF([SOPackageDetail.LineNbr]<>Last(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])

=IIF([SOPackageDetail.LineNbr]<>MAX(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])

These formula work with most cases but there are shipments that does not start with line 1 and value could not be shown. See below screen shot for reference: 

 

I have attached the file for reference. Hopefully someone would be provide insights or directions on how this can be done. 

Kind regards,

Calvin

icon

Best answer by BenjaminCrisman 13 September 2022, 19:25

View original
1 Attachment

8 replies

R
Rank
Userlevel 6
Badge +5

Hello @Calvin , let me repeat to make sure I understand your issue correctly.

You want all the invoice lines be shown once, right?
 

 

Just an idea
You probably can define a  counter on detail section#
 

 

Then you can use the visibility control

Show the lines only when counter=1


 

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@Calvin Usually for this issue the best way to get around the extra lines printing is to have them appear in a Grouping section instead of a Detail section.

You can also use a variable and try defining the variable and then displaying this in the Detail section.

Can you provide the reason for adding the SOPackageDetail table if you are trying to not see all the package details?

Joins like this create a one-to-many type ratio and will always display one of each of the results from the SOPackageDetail table for each result on SOOrder (or whichever table you’re joining)

C
Rank
Userlevel 4
Badge

Hi @ray20,

Thank you for the suggestion. The idea itself is great, but the only issue is that it does not limit the amount of lines that are run int he background, cause the Net Sales and Freight Cost field to duplicate multiple times.

Hi @BenjaminCrisman,

The reason why I need SOPackageDetail table is to get the number of package per shipment and also how the shipment is being ship out as (box or skid). I was able to use the [SOShipment.PackageLineCntr] function to get the number of packages in most shipment that is less than 10 packages. I do notice most package over 10 pieces and randomly few shipment are counted wrong compare to what it is in the system. Below will be examples of the wrong count:

  • 10 pieces of boxes shipped but the report return 11 lines counted
  • 9 boxes shipped but report return 12 lines counted
  • 15 boxes shipped and somehow counted correctly of 15 in report

 

Would you have an idea how I might be able to fix this? I understand that PackageCount field in shipment is a calculated field and it return blank when I try to use it. I can not think of any other way to get the package count other than SOPackageDetail.LineNbr, and even with that it might not always be correct. 

Ultimately I am trying to create a report that is able to tell me the below in a quick glance:

  • How the order was shipped (Box or Skid)
  • Who shipped the shipment (UPS, FedEx etc...)
  • How many boxes or skids in this shipment
  • How much was the order in Net Sales
  • How much is the Freight Cost
  • What is the % of Freight compare to Net Sales

Thank you,

Calvin Law

 

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@Calvin Have you tried doing a Count() function on the BoxID instead of using LineCntr?

I’m not sure I’ve seen your method used before, but I have seen a successful iteration of this using Count() on either BoxID or LineNbr.

Have you already tried that?

C
Rank
Userlevel 4
Badge

Hi @BenjaminCrisman,

I have added the SOPackageDetail table and use the count function on [SOPackageDetail.BoxID] & [SOPackageDetail.LineNbr] & even [SOShipment.PackageLineCntr], it would work on counting it correctly, but then it don’t solved the problem of multiple line of Net Sales and Freight, causing incorrect sum total. 

 

 

I tried using VisibleExpression to limit the out come on Net Sales and it just not showing properly at all. I used =[SOPackageDetail.LineNbr]=1 and =Count(SOPackageDetail.LineNbr])=1 but it doesn’t work as I hope it would. 

 

 

I am not sure what other expression I can try to limit the multi-line issue from the Join Table. 

Kind regards,

Calvin

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@Calvin For this issue there won’t be a way to accomplish this with Visibility Expressions and grouping, it will always SUM the amounts which are appearing in the group.

One way around this is using a subreport to bring in the details which pulls the relative data off the shipment and sends it to the subreport which will return just one line.

Have you looked into using a subreport?

C
Rank
Userlevel 4
Badge

Hi @BenjaminCrisman,

I have not try to use a subreport as I do not know how. I am beginner in Report Designer and don’t really understand the help guide on subreport. Would you have suggestion on how this might be done?

Kind regards,

Calvin

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@Calvin It can definitely be confusing to use subreports, they can tricky too.

I’ve found that a good way to implement the subreport is to make the subreport and verify it can return the results needed.

To do this just create a new report, add in the tables needed and make sure that there is related tables in the main report (like SOShipment).

After making the new report add it to the site map and give it a spot on the UI so you can quickly and easily test it using the same parameters you use to print the original report.

Once you see the package data you’re looking for in the Details section of the subreport, add the subreport box to the main report where you want this data to appear.

In the parameter collection box add the parameter you want to pass to the subreport in order for it to know which shipment you’re dealing with (ShipmentNbr in this case, otherwise make sure it is key linking fields like DocType/RefNbr):

As long as the subreport also has this exact parameter then it will pass the shipment number used to run the report and then return the results of the subreport into the main report.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved