Adding together multiple records and selecting last 12 months

[kpetree10]

Hello, here's my issue. I'm creating a report that will show how many pieces of an item were shipped, right now it looks like this...

Item Qty_shipped
1246 25
1246 108
1246 226
1286 10
1303 3
1303 3
1303 3

As you can see it has a record every time it is shipped. Is there a way that I can add together every record so that what appears is the total of all shipment records. Also for reference the field name for qty_shipped is {oelinhst_sql.qty_to_ship}.

My other question is that I only want to see the shipments from the last 12 months. There is a field {oelinhst_sql.shipped_dt} that contains the ship date. How can I select it so it only includes shipped dates from today to 12 months ago.

Thank You!

 

[briangriffin]

1. Insert a group on the item number (Insert: Group, then select the item number field).
2. Create a summary field on the ship quantity (right click on the quantity field, then choose Insert:Summary. Select the Sum function). Make sure it goes into the group #1 footer.
3. Move the summary field from the group footer to the group header.
4. Section Expert: Hide or suppress the details section, and the group #1 footer.

That should give you the summary report that I think you are asking for.

To only return the last year's records, go to the Select Expert and click the show formula button. Add the formula:

{oelinhst_sql.shipped_dt} >= currentdate - 365

There are numerous other ways to do the date filtering, but this should get you started.