By using this website, you agree to our Terms of Use (click here)
I am building a report which has a field that should display the total line items.
=count([SOShipment.ShipmentNbr])
Is there a way in which we can display count of only unique ShipmentNbr in the column?
Also is there a way where i can omit the 0's and give a count.
=count([SOLine.Qty])
Can you explain more about what data is in the report? I'm trying to picture it. Even better, if you could attach the .rpx file...
This is the report for Shipment details by customer
The formula =count([SOShipment.ShipmentNbr]) counts all the shipment number for that customer, I want it to count only unique shipment nbr.
And for Total lines ordered/shipped column, i want to write a formula that will not count 0's
Ah, I see. What you really need is a DISTINCTCOUNT function like in Power BI, but there isn't such a function currently in Report Designer. You might want to add your vote to this:
https://feedback.acumatica.com/ideas/ACU-I-583
To get around this, you could use a variable, but I'm not sure you could display the result in the groupHeaderSection1 area of your report. I think you would need to put it in the groupFooterSection1 area because that's when the variable would actually have a value.
But I'm not a big fan of variables because they can be slow. I prefer to use a Subreport. You could build a Subreport that only includes Shipments, not Shipment Lines. Then pass the customer as a parameter to the Subreport and have the Subreport return the Shipment count. Even though it's a Sub "report' it's really acting like a Sub "field".
Okay!
Can you give me an example of how to use variable in report designer that would give me the count of lines?
This is the sub report that I created but it is still showing total line count.
And it is not visible on the Main report too.
Attaching the Main report and Sub Report.
I created a sub report for the shipments, but if there are 4 shipments for that customer it shows number 4 on 4 lines
4
4
4
4
How can i limit it to only one line?
How can i create a sub report for Total lines ordered/shipped column, so that will not count the line which has a zero value?
You'll need to get the Subreport working first. Then you can drop it in the main report.
To get the SO6210001.rpx Subreport working, you need to group by the CustomerID, then only make the group header visible so you see the total value rather than the detail lines. Like this:
It gives correct result only for current month.
If I change the Date to last month the Total Shipments in not able to give correct values, though the subreport shows correct values for last month if ran separately.
Do I have to add any parameters in the main report?
Did you make changes to the reports? If so can you attach the current versions?
In the Parameters field on the Subreport in the Main report, you are passing =[SOShipment.CustomerID] like this:
Try changing =[SOShipment.CustomerID] to =[Customer.BAccountID]