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.

fORMAT eXCEPTION

Hi
I am continuously getting the error:

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

Line 57: {
Line 58: Connection.Open() ;
Line 59: ThreadResult = ThreadCommand.ExecuteNonQuery() ;
Line 60: }
Line 61:

in the following part of the code :

protected void PostClick(object sender,EventArgs e)
{
SqlConnection Connection ;
SqlCommand ThreadCommand ;
SqlCommand GetThreadCommand ;
SqlCommand MessageCommand ;
SqlDataReader ThreadReader ;
string InsertThread,InsertMessage ;
string GetThread,Message ;
long ThreadID ;
int ThreadResult ;
Connection = new SqlConnection("server=HT; Integrated Security= SSPI; Database=Forums");
Feedback.Text = "" ;
InsertThread = "Insert Into Threads(ThreadName,TopicId) VALUES(@.ThreadName,@.TopicId)" ;
ThreadCommand = new SqlCommand(InsertThread, Connection) ;
ThreadCommand.Parameters.Add(new SqlParameter("@.ThreadName", SqlDbType.VarChar,50)) ;
ThreadCommand.Parameters.Add(new SqlParameter("@.TopicId", System.Data.SqlDbType.Int));
ThreadCommand.Parameters["@.ThreadName"].Value = ThreadText.Text ;
ThreadCommand.Parameters["@.TopicId"].Value = Request.QueryString["TopicId"] ;
try
{
Connection.Open() ;
ThreadResult = ThreadCommand.ExecuteNonQuery() ;
}
catch (System.Data.SqlClient.SqlException excp)
{
if (excp.Errors[0].Number == -105121349)
{
Feedback.Text = "<font color=red>*** There is already a thread with the name"+ThreadText.Text+". Please choose a different name and click Post.</font><br><br>" ;
}
else
{
Feedback.Text = "<font color=red>*** "+excp.Errors[0].Message +"<br><br>" ;
}
}

I would be very grateful for any suggestions.I expect the problem is here:

ThreadCommand.Parameters["@.TopicId"].Value = Request.QueryString["TopicId"] ;

Perhaps Request.QueryString["TopicId"] is null, or not a value that can be converted into a number? Debug the application and check this value, or alternately, do a Response.Write("TopicID: " + Request.QueryString["TopicId"] );|||Hi
Yes Mr.Douglas you are right. but the problem lies in this statement only in this page. Actually i think its in the following line in the other page :

<a href="http://links.10026.com/?link=threads.aspx?TopicId=<%=Request.QueryString["TopicId"]%>&TopicName= <%=Request.QueryString["TopicName"]%>"
Actually on printing TopicId, i noticed its '1' (with comma) instead of being just 1 due to the courtesy of above line. Can you please tell me how can i remove the comma thats being appended. Thanks a lot|||I presume when you say comma, you really mean the single quotes ' and ' and if so, change to this:

=<%=String.Trim(Request.QueryString["TopicId"],"'".ToCharArray()%
The best bet would be to determine why, on the previous page, it is putting the single quotes in.|||Yes you are right , i can use the trim method but Mr. Douglas why is the following statement attaching a comma.

<%=Request.QueryString["TopicId"]%
in

<a href="http://links.10026.com/?link=threads.aspx?TopicId=
<%=Request.QueryString["TopicId"]%>
&TopicName=<%=Request.QueryString["TopicName"]%>"
Thanks a lot and Mr. Douglas i would like you advise on a designing tool . Which one do u use since yesterday i was trying to do things with Html Table but thats a HeadAche and i can't even arrange the data the way i like.|||I have no idea what the value of Request.QueryString["TopicID"] is, but my guess is that somehow, it has the apostrophes.

I tend to use a mix of tables and Cascading Style sheets, but honestly, my page design skills are less than perfect.