Skip to main content
Solved

Serial Lot Numbers listing across the vertically rather than horizontally


Forum|alt.badge.img+1

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.

Best answer by lauraj46

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

View original
Did this topic help you find an answer to your question?

4 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • October 26, 2023

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


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 122 replies
  • October 26, 2023

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
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • Answer
  • October 26, 2023

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


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 122 replies
  • March 1, 2024

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings