| Author |
Message |
JJ
Guest
|
 Posted:
Wed Nov 02, 2005 5:51 pm Post subject:
Formatting numbers in DataView web part |
|
|
I'm trying to determine the best way to do things in Sharepoint for us.
I need to get some data back from our accounting system, so I set up two
DataView web parts.
The data comes from a SQL query like this:
select Customer, sum(InvoiceAmt) as Amount from AR_Invoices
where ...
group by Customer
One DataView web part gets the data from a view out of a SQL Server database
and the data appears like this:
Customer 1 123,456.78
Customer 2 90,434.00
Customer 3 400.89
Customer 4 1,444.10
As you can see the amounts are formatted with commas and two decimal places
(although not right justified).
As an alternative method, I created a ASP.NET web service to return the same
data as a .NET DataSet. This works (after manually removing the diffgr:id
and RowCount columns), but the data is unformatted:
Customer 1 123456.78
Customer 2 90434
Customer 3 400.89323
Customer 4 1444.1
There are no commas and there are varying numbers of decimal places. Additionally,
if I enable the column header sort capability in the DataView, the sort on
the amount column doesn't work properly, either because it doesn't think
the column is a number or because of the varying lengths/decimal places of
numbers (not sure which).
I'd really like to use the web service method - is there a way to use the
DataView web part to properly format data from the service? Ideally, I'd
like to add commas, always display two decimal palces, and right justify
within the column. I suppose I could return it formatted from the web service,
but is that kosher |
|
| Back to top |
|
 |
Daniel Larson (www.portal
Guest
|
| Posted:
Wed Nov 02, 2005 5:51 pm Post subject:
RE: Formatting numbers in DataView web part |
|
|
JJ,
You should be able to. Do you have FrontPage? That will help you
tremendously (I'd actually be quite impressed if you were using the dataview
without FrontPage!). You can get into the raw XSLT as well, just change your
view to split (code and design).
Daniel Larson
"JJ" wrote:
| Quote: | I'm trying to determine the best way to do things in Sharepoint for us.
I need to get some data back from our accounting system, so I set up two
DataView web parts.
The data comes from a SQL query like this:
select Customer, sum(InvoiceAmt) as Amount from AR_Invoices
where ...
group by Customer
One DataView web part gets the data from a view out of a SQL Server database
and the data appears like this:
Customer 1 123,456.78
Customer 2 90,434.00
Customer 3 400.89
Customer 4 1,444.10
As you can see the amounts are formatted with commas and two decimal places
(although not right justified).
As an alternative method, I created a ASP.NET web service to return the same
data as a .NET DataSet. This works (after manually removing the diffgr:id
and RowCount columns), but the data is unformatted:
Customer 1 123456.78
Customer 2 90434
Customer 3 400.89323
Customer 4 1444.1
There are no commas and there are varying numbers of decimal places. Additionally,
if I enable the column header sort capability in the DataView, the sort on
the amount column doesn't work properly, either because it doesn't think
the column is a number or because of the varying lengths/decimal places of
numbers (not sure which).
I'd really like to use the web service method - is there a way to use the
DataView web part to properly format data from the service? Ideally, I'd
like to add commas, always display two decimal palces, and right justify
within the column. I suppose I could return it formatted from the web service,
but is that kosher?
|
|
|
| Back to top |
|
|
JJ
Guest
|
| Posted:
Wed Nov 02, 2005 9:51 pm Post subject:
RE: Formatting numbers in DataView web part |
|
|
Thanks for the reply. I took your suggestion and changed the XSLT and I
now have the numbers formatting properly.
However, they still are not sorting properly. I have it configured to do
a descending sort on the amount, but it appears to be doing an ASCII sort
- any amount that starts with a "9" appears together, whether it is 9, 99,
999, or 999999999999. Then any amount that starts with 8, etc. They are
not sorting based on their value.
When I look at the XML that comes out of the web service, the embedded XSD
(remember, the web service is returning a .NET DataSet) defines the column
as a "double".
| Quote: | JJ,
You should be able to. Do you have FrontPage? That will help you
tremendously (I'd actually be quite impressed if you were using the
dataview without FrontPage!). You can get into the raw XSLT as well,
just change your view to split (code and design).
Daniel Larson
"JJ" wrote:
I'm trying to determine the best way to do things in Sharepoint for
us. I need to get some data back from our accounting system, so I
set up two DataView web parts.
The data comes from a SQL query like this:
select Customer, sum(InvoiceAmt) as Amount from AR_Invoices
where ...
group by Customer
One DataView web part gets the data from a view out of a SQL Server
database and the data appears like this:
Customer 1 123,456.78
Customer 2 90,434.00
Customer 3 400.89
Customer 4 1,444.10
As you can see the amounts are formatted with commas and two decimal
places (although not right justified).
As an alternative method, I created a ASP.NET web service to return
the same data as a .NET DataSet. This works (after manually removing
the diffgr:id and RowCount columns), but the data is unformatted:
Customer 1 123456.78
Customer 2 90434
Customer 3 400.89323
Customer 4 1444.1
There are no commas and there are varying numbers of decimal places.
Additionally, if I enable the column header sort capability in the
DataView, the sort on the amount column doesn't work properly, either
because it doesn't think the column is a number or because of the
varying lengths/decimal places of numbers (not sure which).
I'd really like to use the web service method - is there a way to use
the DataView web part to properly format data from the service?
Ideally, I'd like to add commas, always display two decimal palces,
and right justify within the column. I suppose I could return it
formatted from the web service, but is that kosher?
|
|
|
| Back to top |
|
|
|
|
|
|
FAQ
Memberlist
Register
Profile
Log in to check your private messages
Log in