Formatting totals with the table footer

Here's a scenario that i'm trying to figure out.

In the table details, i return order number, item, manufacturer, and total cost of the order.

This is what it originally looked like:

Order Number Item Manufacturer Total Order Cost

1 portable DVD Company A $100

1 portable DVD Company B $100

1 portable DVD Company C $100

2 portable DVD Company B $100

2 portable DVD Company D $100

2 portable DVD Company F $100

Grand Total $600

I can get the table to look like this after hiding duplicates:

Order Number Item Manufacturer Total Order Cost

1 portable DVD Company A $100

1 portable DVD Company B $100

1 portable DVD Company C $100

2 portable DVD Company B $100

2 portable DVD Company D $100

2 portable DVD Company F $100

Grand Total $600

The problem is the grand total. It should be $200 but it takes in the all total costs in the row because I have:

=FormatCurrency(Sum(Fields!TotalCost.Value)) in the footer and it'll sum up all.

I'm stumped here. Any suggestions are greatly appreciated.

Thanks a lot for taking the time to read.

Hiding duplicates will invariably give you wrong values especially when adding. I have had the same scenario and I found the answer here on this fourm long time back. You need to do a sum of TotalOrderCost for distinct values of OrderNumber. In the Code block (available from the Report Properties dialog), you would add:

Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal TotalOrderCost As Obect) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + TotalOrderCost
orderIDs.Add(orderID, TotalOrderCost)
End If
MyFunc = total
End If
End Function

3.) In your report, you add a hidden textbox with the value expression to compute the value:

=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!TotalOrderCost.Value))

4.)In the footer of the table, you add a textbox with the value expression:

=Code.MyFunc(Nothing, Fields!TotalOrderCost.Value)

to return the total value.

Hope this helps....

|||

Thanks for your help.

I'll give it a try.

formatted invoice line item report

I am trying to make a report that show invoice line items using a table with
groups. We what the table to fill the page no matter how many line items are
on the invoice. For example the invoice my only have three items on the last
page so the group footer ends up in the middle of the page. The group footer
has the invoice total. How can we place group footer at the bottom of the
last page? Even in the case of a small invoice that only has one page ?I'm going to take a stab at this but I hope someone comes up with a better
way. I could use it myself. Anyway here goes:
Insert a row in your above your totals in the group footer.
Figure out the number of line items that fit on a page.
Make the row height of the just inserted row an expression equal to
(MaxLineItemsPerPage - (LineItemCount MOD MaxLineItemsPerPage)) *
LineItemRowHeight)
This will give you the number of "blank" lines on the last page of the
invoice and make your empty footer row that height to fill in the space. The
expression will get a little more complicated if pages 2 on have more rows
than page 1 (i.e. no invoice header) but I think the calculation can be done.
Let me know if this works...Or if someone comes up with a better way!
"DJJIII" wrote:
> I am trying to make a report that show invoice line items using a table with
> groups. We what the table to fill the page no matter how many line items are
> on the invoice. For example the invoice my only have three items on the last
> page so the group footer ends up in the middle of the page. The group footer
> has the invoice total. How can we place group footer at the bottom of the
> last page? Even in the case of a small invoice that only has one page ?

Format of tables

Hello,

I have the following problem; In my application I have certain items that
have properties.

Item Property
--- --------
ToyBear Hairy, Soft, Brown
ToyCar Brown, Plastic, Wheels
ToyBall Round, Soft, Brown, Plastic

As you might expect I want to do queries on the properties; so property
Brown should yield all items listed above and Plastic should yield only
ToyCar and ToyBall.
The amount of properties is limitless, so making a separate field per
property seems to be madness. My question is how can I make a (or more)
tables that will enable me to search for items by applying one or more
properties?? What structure do I need to accomplish this?

Kind regards, Darius Blaszijk"Darius Blaszijk" <dhkblaszyjk@.zeelandnet.nl> wrote in message
news:41060ef3$0$13577$fb624cd1@.morenews.zeelandnet .nl...
> Hello,
> I have the following problem; In my application I have certain items that
> have properties.
> Item Property
> --- --------
> ToyBear Hairy, Soft, Brown
> ToyCar Brown, Plastic, Wheels
> ToyBall Round, Soft, Brown, Plastic
> As you might expect I want to do queries on the properties; so property
> Brown should yield all items listed above and Plastic should yield only
> ToyCar and ToyBall.
> The amount of properties is limitless, so making a separate field per
> property seems to be madness. My question is how can I make a (or more)
> tables that will enable me to search for items by applying one or more
> properties?? What structure do I need to accomplish this?
> Kind regards, Darius Blaszijk

The obvious thing would be to have two tables - TB_Item and TB_Property.
TB_Property has a foriegn key referencing into TB_Item.

Therefore, you're definition would look something like this:

CREATE DATABASE TB_Item (
name VARCHAR (30),

CONSTRAINT PK_TB_Item
PRIMARY KEY (name)
)

CREATE DATABASE TB_Property (
itemName VARCHAR (30)
propName VARCHAR (30)

CONSTRAINT FK_TB_Item_name
FOREIGN KEY (itemName)
REFERENCES TB_Approver_Type (name)
)

Then, to populate the database for the example of ToyBear given above, do:
INSERT INTO TB_Item (name) VALUES ('ToyBear')
GO
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Hairy')
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Soft')
INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Brown')

Hope that is clear, and answers your question,

Rowland.|||"Rowland Banks" <banksr0@.hotmail.com> wrote in message
news:ce544k$6mp$1@.sparta.btinternet.com...
> "Darius Blaszijk" <dhkblaszyjk@.zeelandnet.nl> wrote in message
> news:41060ef3$0$13577$fb624cd1@.morenews.zeelandnet .nl...
> > Hello,
> > I have the following problem; In my application I have certain items
that
> > have properties.
> > Item Property
> > --- --------
> > ToyBear Hairy, Soft, Brown
> > ToyCar Brown, Plastic, Wheels
> > ToyBall Round, Soft, Brown, Plastic
> > As you might expect I want to do queries on the properties; so property
> > Brown should yield all items listed above and Plastic should yield only
> > ToyCar and ToyBall.
> > The amount of properties is limitless, so making a separate field per
> > property seems to be madness. My question is how can I make a (or more)
> > tables that will enable me to search for items by applying one or more
> > properties?? What structure do I need to accomplish this?
> > Kind regards, Darius Blaszijk
> The obvious thing would be to have two tables - TB_Item and TB_Property.
> TB_Property has a foriegn key referencing into TB_Item.
> Therefore, you're definition would look something like this:
> CREATE DATABASE TB_Item (
> name VARCHAR (30),
> CONSTRAINT PK_TB_Item
> PRIMARY KEY (name)
> )
> CREATE DATABASE TB_Property (
> itemName VARCHAR (30)
> propName VARCHAR (30)
> CONSTRAINT FK_TB_Item_name
> FOREIGN KEY (itemName)
> REFERENCES TB_Approver_Type (name)
> )
> Then, to populate the database for the example of ToyBear given above, do:
> INSERT INTO TB_Item (name) VALUES ('ToyBear')
> GO
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Hairy')
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Soft')
> INSERT INTO TB_Property (itemName, propName) VALUES ('ToyBear', 'Brown')
> Hope that is clear, and answers your question,
> Rowland.
ADDENDUM:
I just read through your post again and I missed a bit. To extract the
information, use somethign similar to:

SELECT i.name
FROM TB_Item AS i, TB_Property AS p
WHERE i.name = p.itemName
AND p.itemName = 'Hairy'

hope that helps,

Rowland