Building out sums inside of a table (SSRS 2000)

Hi all,
I am converting some Crystal reports... and replacing formulas which
are automatically grouped in Crystal. Essentially, I am comparing two
different date ranges, and need to be able to know how many items were
shipped for each customer/item combination within each date range.
I have created calculated fields to hold the quantity shipped if that
row falls in the appropriate date range... this just adds the quantity
shipped in the field if it falls within the appropriate date range.
Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
and
Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
I need to build out a table that looks close to the following example:
Cust No
Item No Total for the Period
{need total quantity here}
PD1 {Qty2ShipPD1}
PD2 {Qty2ShipPD2}
I created a matrix control and dragged cus_no, item_no into the rows,
with item_no in the columns, and then summed the Qty2ShipPD1 in the
Details...
This works, in the sense that I am actually getting grouped
information which is correct for the customer/item combinations...
But the format won't work, and I have no idea how to do the same thing
in a table. Every time I do it in a table, I just get the same,
entire sum for the entire query for the two calculated fields.
Help?
THANKS!
JoshAlternatively - is there a way I can use a Matrix Control with only 1
column? If so, I might be able to live with that.
Thanks,
Josh
On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> Hi all,
> I am converting some Crystal reports... and replacing formulas which
> are automatically grouped in Crystal. Essentially, I am comparing two
> different date ranges, and need to be able to know how many items were
> shipped for each customer/item combination within each date range.
> I have created calculated fields to hold the quantity shipped if that
> row falls in the appropriate date range... this just adds the quantity
> shipped in the field if it falls within the appropriate date range.
> Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> and
> Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> I need to build out a table that looks close to the following example:
> Cust No
> Item No Total for the Period
> {need total quantity here}
> PD1 {Qty2ShipPD1}
> PD2 {Qty2ShipPD2}
> I created a matrix control and dragged cus_no, item_no into the rows,
> with item_no in the columns, and then summed the Qty2ShipPD1 in the
> Details...
> This works, in the sense that I am actually getting grouped
> information which is correct for the customer/item combinations...
> But the format won't work, and I have no idea how to do the same thing
> in a table. Every time I do it in a table, I just get the same,
> entire sum for the entire query for the two calculated fields.
> Help?
> THANKS!
> Josh|||Doh! Got it.
In my
Sum statement, I was referencing the entire dataset, and not just the
group. I just didn't know I could reference the group.
*This* works...
=Sum(Fields!qty_to_ship.Value, "table1_Group1")
*This does not (well, it gives me values for the entire dataset)
=Sum(Fields!qty_to_ship.Value, "devdbds")
FYI. Hopefully this will help someone else.
THANKS
On Apr 24, 9:44 am, rumplyminz <squa...@.gmail.com> wrote:
> Alternatively - is there a way I can use a Matrix Control with only 1
> column? If so, I might be able to live with that.
> Thanks,
> Josh
> On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> > Hi all,
> > I am converting some Crystal reports... and replacing formulas which
> > are automatically grouped in Crystal. Essentially, I am comparing two
> > different date ranges, and need to be able to know how many items were
> > shipped for each customer/item combination within each date range.
> > I have created calculated fields to hold the quantity shipped if that
> > row falls in the appropriate date range... this just adds the quantity
> > shipped in the field if it falls within the appropriate date range.
> > Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > and
> > Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > I need to build out a table that looks close to the following example:
> > Cust No
> > Item No Total for the Period
> > {need total quantity here}
> > PD1 {Qty2ShipPD1}
> > PD2 {Qty2ShipPD2}
> > I created a matrix control and dragged cus_no, item_no into the rows,
> > with item_no in the columns, and then summed the Qty2ShipPD1 in the
> > Details...
> > This works, in the sense that I am actually getting grouped
> > information which is correct for the customer/item combinations...
> > But the format won't work, and I have no idea how to do the same thing
> > in a table. Every time I do it in a table, I just get the same,
> > entire sum for the entire query for the two calculated fields.
> > Help?
> > THANKS!
> > Josh