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.

Formatting question

Hi All, I must be overlooking something obvious, but I could reallt use some
guidance.
I can't find or figure out how to format a string in my report. The
datacolumn is just a string containing numbers like 123456789 and I need to
display it in the report as 123-45-6789 .
From the books on-line I figured I should set the format property of the
textbox to ###-##-#### but that does not seem to work. Can someone please
point me in the right direction?
Thanks
Ed###-##-#### works fine - and i get the right result.
Make sure you select the Custom option button , not the standard one.
and make sure you have a "=" in the value box.
"Ed Richard" wrote:
> Hi All, I must be overlooking something obvious, but I could reallt use some
> guidance.
> I can't find or figure out how to format a string in my report. The
> datacolumn is just a string containing numbers like 123456789 and I need to
> display it in the report as 123-45-6789 .
> From the books on-line I figured I should set the format property of the
> textbox to ###-##-#### but that does not seem to work. Can someone please
> point me in the right direction?
> Thanks
> Ed
>
>|||Ed,
You need to convert the string to a number.
Try =CDec("12345678") in the value then ###-##-### in the format
property.
Chris
Ramani wrote:
> ###-##-#### works fine - and i get the right result.
> Make sure you select the Custom option button , not the standard one.
> and make sure you have a "=" in the value box.
> "Ed Richard" wrote:
> > Hi All, I must be overlooking something obvious, but I could reallt
> > use some guidance.
> >
> > I can't find or figure out how to format a string in my report. The
> > datacolumn is just a string containing numbers like 123456789 and I
> > need to display it in the report as 123-45-6789 .
> > From the books on-line I figured I should set the format property
> > of the textbox to ###-##-#### but that does not seem to work. Can
> > someone please point me in the right direction?
> >
> > Thanks
> > Ed
> >
> >
> >|||That was it Chris, thanks very much!
Ed
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:OMk8dCXgFHA.2444@.tk2msftngp13.phx.gbl...
> Ed,
> You need to convert the string to a number.
> Try =CDec("12345678") in the value then ###-##-### in the format
> property.
> Chris
>
> Ramani wrote:
>> ###-##-#### works fine - and i get the right result.
>> Make sure you select the Custom option button , not the standard one.
>> and make sure you have a "=" in the value box.
>> "Ed Richard" wrote:
>> > Hi All, I must be overlooking something obvious, but I could reallt
>> > use some guidance.
>> >
>> > I can't find or figure out how to format a string in my report. The
>> > datacolumn is just a string containing numbers like 123456789 and I
>> > need to display it in the report as 123-45-6789 .
>> > From the books on-line I figured I should set the format property
>> > of the textbox to ###-##-#### but that does not seem to work. Can
>> > someone please point me in the right direction?
>> >
>> > Thanks
>> > Ed
>> >
>> >
>> >
>

Formatting Problem

Hi,

I'm trying to format the output of a report and can't seem to figure out how use the Matrix control to get what I want. Currently, I have it formatted as:

General Information <-- Head
Tag Number Line Size Line Schedule <-- Information

Service P&ID No. Tightness Requirements

Line No. Area Classification Location

Process Data
Fluid Specific Gravity

Transmitter Spec.
Mounting Type

NEMA Rating Power Supplied Analog Outputs

Ideally I'd like to have it formatted as:

General Information <-- Header centered
Tag Number Line Size Line Schedule

Service P&ID No. Tightness Requirements

Line No. Area Classification Location
__ <-- Divider Line between sections

Process Data
Fluid Specific Gravity
__

Transmitter Spec.
Mounting Type

NEMA Rating Power Supplied Analog Outputs

Any ideas or suggestions would be greatly appreciated. This shouldn't be THIS hard to do.

Thanks,

John.

It'll be a lot clearer to understand if you make up your examples in Excel and then just copy paste that into your post.|||

Adam,

Thanks for taking an interest. Ok, here's what's going on: I'm trying to render information where I have multiple (unknown at design time) Catagories which have multiple Attributes (also unknown at design time). The relationship is One Catagory, Many Attributes.

I'm using a Matrix control because it seems to be the Control to use? If it's not, let me know. What I'd like ideally is to: Add a Line to separate the different Catagories (i.e. General Information, Process Data, etc.), and center the Catagory title. Currently I'm forcing the Matrix to wrap at 3 by setting a property in the List Control's "Edit Details Group..." property to: =Ceiling(RowNumber(Nothing)/3). If you have any ideas on how to solve this through the designer, please let me know.

Here's an example of the data as it's currently being rendered:

General Information

Service

P&ID No.

Tightness Requirements

Tag Number

Line Size

Line Schedule

Line No.

Area Classification

Location

Process Data

Fluid

Specific Gravity

Transmitter Spec.

Mounting Type

Model No.

Support Materials

Accuracy

NEMA Rating

Power Supplied

Analog Outputs

Local Display

Operating Range

Dead Weight

Output Signal

Conduit Connection

Live Weight

Gross Weight

Manufacturer

Weigh Cell Data

Mounting

Temperature Compensation

Power Supplied

|||

On a side note, I've tried adding a Line control to the List, but it renders on every line. I also have no way to detect when the last line of a Catagory is being rendered. I've tried every way I can think of using the tool, but it's just not working for me.

TIA,

John.

|||I would suggest placing the matrix inside a list control. Group the list control by category which will result in a separate matrix being rendered per category. You can then place the category in a textbox above the matrix and also inside the list and center it.|||

Adam,

Mad thanks, that did the trick!!

John.

Formatting Currency (rounding)

I'm trying to show currency values rounded to the nearest thousand (i.e
$797,100.12 would display as $798. I can't seem to figure out the custom
format string. Can someone help me out?Karl wrote:
> I'm trying to show currency values rounded to the nearest thousand (i.e
> $797,100.12 would display as $798. I can't seem to figure out the custom
> format string. Can someone help me out?
Try using "Format" = "P0" for the field.
P as for Percent
0 as for 0 decimals
// Jonas Montonen|||"Jonas Montonen" wrote:
> Karl wrote:
> > I'm trying to show currency values rounded to the nearest thousand (i.e
> > $797,100.12 would display as $798. I can't seem to figure out the custom
> > format string. Can someone help me out?
> Try using "Format" = "P0" for the field.
> P as for Percent
> 0 as for 0 decimals
> // Jonas Montonen
>
That didn't really do the trick. I'm not trying to show the value as a
percentage, I'm trying to show it as currency but rounded to the nearest
thousand dollars.|||I have done this for norwegan format i used # ##0,.# to show 986000 as 986
hope this helps
"Karl" wrote:
>
> "Jonas Montonen" wrote:
> > Karl wrote:
> > > I'm trying to show currency values rounded to the nearest thousand (i.e
> > > $797,100.12 would display as $798. I can't seem to figure out the custom
> > > format string. Can someone help me out?
> >
> > Try using "Format" = "P0" for the field.
> > P as for Percent
> > 0 as for 0 decimals
> >
> > // Jonas Montonen
> >
> That didn't really do the trick. I'm not trying to show the value as a
> percentage, I'm trying to show it as currency but rounded to the nearest
> thousand dollars.|||I have done this for norwegan format, I used # ##0,.# to show 986000 as 986
or 986100 as 986,1 Hope this helps
"Karl" wrote:
>
> "Jonas Montonen" wrote:
> > Karl wrote:
> > > I'm trying to show currency values rounded to the nearest thousand (i.e
> > > $797,100.12 would display as $798. I can't seem to figure out the custom
> > > format string. Can someone help me out?
> >
> > Try using "Format" = "P0" for the field.
> > P as for Percent
> > 0 as for 0 decimals
> >
> > // Jonas Montonen
> >
> That didn't really do the trick. I'm not trying to show the value as a
> percentage, I'm trying to show it as currency but rounded to the nearest
> thousand dollars.

Format of the initialization string does not conform to specification starting at index 0.

can't figure out this error. i think i'm writing something wrong in the connnection string. i'm using SQL Server Express, if that helps.

in my config file:

<connectionStrings>
<add name="OfficialScribblerConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\OfficialScribbler.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

in my aspx file:

Dim DBConnection = New OleDbConnection("OfficialScribblerConnectionString")

DBConnection.Open()

Dim SQLString As String = "SELECT PostedDate From Entries_tbl ORDER BY PostedDate"

Try this way

Dim DBConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("OfficialScribblerConnectionString").ConnectionString)

Hope this will help you

|||thanks for the tip. i will try later on and let you know how it goes.|||

i tried that and now the error says this:

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

|||

Change the connection string as

<

addname="OfficialScribblerConnectionString"connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\OfficialScribbler.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>Change the code to

Dim

DBConnectionAsNew OleDbConnection(ConfigurationManager.ConnectionStrings("OfficialScribblerConnectionString").ConnectionString)

DBConnection.Open()

Hope this will help you.

Let me know if you need any further help