Solved

Serial Lot Numbers listing across the vertically rather than horizontally

  • 26 October 2023
  • 4 replies
  • 48 views
W
Rank
Userlevel 4
Badge

Our company has serialized numbers for completed items with each location have a unique sequence of numbers. In some cases the number of ordered items may be 400, 500 or 100 and rather than have the numbers going down the page, if there is a way to have them listed across the page (as a subreport) so as to save the number of pages that are printed such as:

100422KS0033728  100422KS0033729  100422KS0033730  100422KS0033731  100422KS0033732

… the rest of the 400 sequential numbers rather than

100422KS0033728

100422KS0033729 etc.

In SSRS there is a way to do this as a subreport using a matrix report style with SQL query code that involves using the following formula’s:

SELECT iif(a.ser_lot_no IS NULL, b.ser_lot_no, a.ser_lot_no) AS ser_lot_no
    ,ROW_NUMBER() OVER (
        ORDER BY a.ser_lot_no
        ) AS RowNumber
    ,(
        ROW_NUMBER() OVER (
            ORDER BY a.ser_lot_no
            ) - 1
        ) / 5 AS ColumnNumber etc. and using parameters of @OrderNumber, @LineNumber, @OrderType linked back to the main report.

icon

Best answer by lauraj46 26 October 2023, 18:04

View original

4 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @wmatthews1877 ,

I think you could accomplish this by using a variable and concatenating each serial number in the Detail section of the report, then printing the value in the group footer.

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
W
Rank
Userlevel 4
Badge

I’m not sure of what you mean by using a variable, then concatenating each serial number in the Detail section of the report, then print the value in the group footer. The subreport in SSRS looks like the screen shot below and maybe that can spark some ideas? The two formula’s that I included in the inital question make up the SQL query for the “Columns” and “Rows” that are part of the matrix report.

Thoughts?

 

lauraj46
Rank
Userlevel 7
Badge +7

Hi @wmatthews1877 ,

I’m familiar with the matrix functionality in SSRS.  In Acumatica you can create what’s called a Tabular report, but I’m not sure if a tabular report will render properly as a subreport:

The idea for the variable is to build (either in the main report or a subreport) a string with all of the serial lot numbers.  The variable would be incremented in the Detail section for each record, and the final result would be printed in the footer.  Variables can be defined and calculated from the Variable collection in the properties of each report section.

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
W
Rank
Userlevel 4
Badge

I find that this needs to be revisted. Using the =Next[SOShipLineSplit.LotSerialNbr] is fine if there is just a few serial numbers, but in our case there may be up to 400 or 500 serial numbers for one line item (pumps). I’ve tried using a variable $LotSerial, and either my variable is not listed correctly or something is going on. All of the serial numbers will be listed in a subreport. The main report has DocType, RefNbr and I’ve tried using LineNbr but still not working as needed. The formula for the $LotSerial is being done in the detailSection1, =Concat($LotSerial, [SOShipLineSplit.LotSerialNbr], ‘,’) and there is another variable LineNbr with a value of =LineNbr+1. I also think that there should be more parameters passing from the main report such as InventoryID. I can attach the main report and subreport if needed.

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