Skip to Content
0
Jun 22, 2020 at 11:15 PM

Crystal Reports 2016 - Crosstab with transactional data & static data

48 Views Last edit Jun 23, 2020 at 04:55 AM 3 rev

I need to build an Item usage report that summarizes inventory transactions by month and by item #. Crosstab seems to be the answer but i have 2 issues:

#1: require last 24 months of data but current month cannot be included in the row Grand Total; current month must appear in a column on its own;
So far i've got
Rows = Item#, Columns = Month, Summarized Field = Qty. All these records coming from table Inventory_Transaction.
Date query: Transaction date in (dateserial(year(currentdate)-2,month(currentdate)+1,1)) to today

#2: For the same item#'s need to display in that same report a "Qty on Hand" column which is calculated field found in another table "Items". Items.Qty_On_Hand

Report looks something like:


Thanks

Attachments

capture.jpg (22.3 kB)