Currency Formatting

I have a report that uses the following xml dataset via a DPE I got from Teo
Lachev:
<dsDisplay>
<Table1>
<AreaGroup>Orange County </AreaGroup>
<Col1>96090.8100</Col1>
<Col2>310241.9500</Col2>
<Col3>95732.6400</Col3>
<Col4>123224.3500</Col4>
<ShowFilter>show</ShowFilter>
<V1>-214151.1400</V1>
<V2>-27491.7100</V2>
</Table1>. . . . . . . .
I am specifying Currency formatting for the textboxes having numeric amounts
but it doesn't format - they still appear as they are in the dataset.
Why would this not work?
Thanks,
TI suspect that your report data is being treated as a string. If that is
correct the currency formatting will affect how your report data is
displayed.
What you need to do is to convert the data to a numeric format and then
apply the currency formatting. The textbox value expression you will need
should be similar to this:
=CDbl(Fields!<SomeFieldName>.Value)
Please see the VB Type Conversion discussion at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tina" <tinamseaburn@.removespamexcite.com> wrote in message
news:OegSX3shEHA.1156@.TK2MSFTNGP10.phx.gbl...
> I have a report that uses the following xml dataset via a DPE I got from
Teo
> Lachev:
> <dsDisplay>
> <Table1>
> <AreaGroup>Orange County </AreaGroup>
> <Col1>96090.8100</Col1>
> <Col2>310241.9500</Col2>
> <Col3>95732.6400</Col3>
> <Col4>123224.3500</Col4>
> <ShowFilter>show</ShowFilter>
> <V1>-214151.1400</V1>
> <V2>-27491.7100</V2>
> </Table1>. . . . . . . .
> I am specifying Currency formatting for the textboxes having numeric
amounts
> but it doesn't format - they still appear as they are in the dataset.
> Why would this not work?
> Thanks,
> T
>|||I forgot to include the schema info in the xml file. Once I did that it
worked just fine.
As Anna Roseanadana would say, uh, never mind :)
T
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:OeHsUMthEHA.3916@.TK2MSFTNGP11.phx.gbl...
> I suspect that your report data is being treated as a string. If that is
> correct the currency formatting will affect how your report data is
> displayed.
> What you need to do is to convert the data to a numeric format and then
> apply the currency formatting. The textbox value expression you will need
> should be similar to this:
> =CDbl(Fields!<SomeFieldName>.Value)
> Please see the VB Type Conversion discussion at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Tina" <tinamseaburn@.removespamexcite.com> wrote in message
> news:OegSX3shEHA.1156@.TK2MSFTNGP10.phx.gbl...
> > I have a report that uses the following xml dataset via a DPE I got from
> Teo
> > Lachev:
> > <dsDisplay>
> > <Table1>
> > <AreaGroup>Orange County </AreaGroup>
> > <Col1>96090.8100</Col1>
> > <Col2>310241.9500</Col2>
> > <Col3>95732.6400</Col3>
> > <Col4>123224.3500</Col4>
> > <ShowFilter>show</ShowFilter>
> > <V1>-214151.1400</V1>
> > <V2>-27491.7100</V2>
> > </Table1>. . . . . . . .
> >
> > I am specifying Currency formatting for the textboxes having numeric
> amounts
> > but it doesn't format - they still appear as they are in the dataset.
> >
> > Why would this not work?
> > Thanks,
> > T
> >
> >
>

Culture in MSSQL 2005 express edition

i am trying to add xml to the xml data type column. The xml contains both
english and hebrew characters. English characters are saved well, but hebrew
saved as "''?" how can i fix it?Try converting the xml to varbinary(max) before inserting. From BOL:
Text Encoding
SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved from
the server comes out in UTF-16 encoding. If you want a different encoding,
you have to perform the required conversion on the retrieved data.
Sometimes, the XML data may be in a different encoding. If it is, you have
to use care during data loading. For example:
a.. If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to
an XML column, variable, or parameter without any problems.
b.. If the encoding is not Unicode and is implicit, because of the source
code page, the string code page in the database should be the same as or
compatible with the code points that you want to load. If required, use
COLLATE. If no such server code page exists, you have to add an explicit XML
declaration with the correct encoding.
c.. To use an explicit encoding, use either the varbinary() type, which
has no interaction with code pages, or use a string type of the appropriate
code page. Then, assign the data to an XML column, variable, or parameter.
Example: Explicitly Specifying an Encoding
Assume that you have an XML document, vcdoc, stored as varchar(max) that
does not have an explicit XML declaration. The following statement adds an
XML declaration with the encoding "iso8859-1", concatenates the XML
document, casts the result to varbinary(max) so that the byte representation
is preserved, and then finally casts it to XML. This enables the XML
processor to parse the data according to the specified encoding "iso8859-1"
and generate the corresponding UTF-16 representation for string values.
SELECT CAST(
CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY
(MAX))
AS XML)
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"michael" <michael@.discussions.microsoft.com> wrote in message
news:19B65A8D-E246-46EB-A85B-C517D1830BAD@.microsoft.com...
> i am trying to add xml to the xml data type column. The xml contains both
> english and hebrew characters. English characters are saved well, but
hebrew
> saved as "''?" how can i fix it?|||Thanks a lot
"David Barber [MS]" wrote:

> Try converting the xml to varbinary(max) before inserting. From BOL:
> Text Encoding
> SQL Server 2005 stores XML data in Unicode (UTF-16). XML data retrieved fr
om
> the server comes out in UTF-16 encoding. If you want a different encoding,
> you have to perform the required conversion on the retrieved data.
> Sometimes, the XML data may be in a different encoding. If it is, you have
> to use care during data loading. For example:
> a.. If your text XML is in Unicode (UCS-2, UTF-16), you can assign it to
> an XML column, variable, or parameter without any problems.
>
> b.. If the encoding is not Unicode and is implicit, because of the sourc
e
> code page, the string code page in the database should be the same as or
> compatible with the code points that you want to load. If required, use
> COLLATE. If no such server code page exists, you have to add an explicit X
ML
> declaration with the correct encoding.
>
> c.. To use an explicit encoding, use either the varbinary() type, which
> has no interaction with code pages, or use a string type of the appropriat
e
> code page. Then, assign the data to an XML column, variable, or parameter.
>
> Example: Explicitly Specifying an Encoding
> Assume that you have an XML document, vcdoc, stored as varchar(max) that
> does not have an explicit XML declaration. The following statement adds an
> XML declaration with the encoding "iso8859-1", concatenates the XML
> document, casts the result to varbinary(max) so that the byte representati
on
> is preserved, and then finally casts it to XML. This enables the XML
> processor to parse the data according to the specified encoding "iso8859-1
"
> and generate the corresponding UTF-16 representation for string values.
>
> SELECT CAST(
> CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY
> (MAX))
> AS XML)
>
> --
> David Barber [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "michael" <michael@.discussions.microsoft.com> wrote in message
> news:19B65A8D-E246-46EB-A85B-C517D1830BAD@.microsoft.com...
> hebrew
>
>

CTE XML Output

I have a organizational table with location->Department->group. Location has many departments and department has many groups. I have only one table to reprasent the above

Id, Name, Parent

Parent is the foreign key tieing back to Id.

Is there any way to use CTE and output an xml like the following?

<Location id="1">
<Name>Location name</Name>
<Departments>
<Department id="2"><Name>Department1</Name></Department>
<Department id="3"><Name>Department2</Name></Department>
</Departments>
</Location>

Thanks,
-keasv

With a fixed hierarchy depth you should be able to use nested selects

select t1.id as "@.id",
t1.Name,
(select t2.id as "@.id",
t2.Name,
(select t3.id as "@.id",
t3.Name
from mytable t3
where t3.parent=t2.id
for xml path('Group'),root('Groups'),type)
from mytable t2
where t2.parent=t1.id
for xml path('Department'),root('Departments'),type)
from mytable t1
where t1.parent is null
for xml path('Location'),type

|||

If the nesting level is not known apriori, you can write a recursive SQL UDF. See the FOR XML Whitepaper at http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp.

Best regards

Michael

|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here at http://msdn2.microsoft.com/en-us/library/ms345137.aspx

So can anyone suggest any alternate method here?

CTE XML Output

I have a organizational table with location->Department->group. Location has many departments and department has many groups. I have only one table to reprasent the above

Id, Name, Parent

Parent is the foreign key tieing back to Id.

Is there any way to use CTE and output an xml like the following?

<Location id="1">
<Name>Location name</Name>
<Departments>
<Department id="2"><Name>Department1</Name></Department>
<Department id="3"><Name>Department2</Name></Department>
</Departments>
</Location>

Thanks,
-keasv

With a fixed hierarchy depth you should be able to use nested selects

select t1.id as "@.id",
t1.Name,
(select t2.id as "@.id",
t2.Name,
(select t3.id as "@.id",
t3.Name
from mytable t3
where t3.parent=t2.id
for xml path('Group'),root('Groups'),type)
from mytable t2
where t2.parent=t1.id
for xml path('Department'),root('Departments'),type)
from mytable t1
where t1.parent is null
for xml path('Location'),type

|||

If the nesting level is not known apriori, you can write a recursive SQL UDF. See the FOR XML Whitepaper at http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp.

Best regards

Michael

|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here at http://msdn2.microsoft.com/en-us/library/ms345137.aspx

So can anyone suggest any alternate method here?

CTE and XQuery

I'ev been playing around with CTE's and trying to query a table that has the following structure

tbl Projects{
id (int),
Project (xml)
}

sample data within the project column is
id = 100
Project =
'<Projects>
<SubProjects>
<id>150</id>
<id>160</id>
<id>170</id>
</SubProjects>
</Projects>'

basically i'm trying to set up a recursive query to get all sub projects for a given project.. this is PART of a bigger query ..

WITH MySubProjects(SubProjects)
AS
(
SELECT TblProjects.*, P.SubProjectIDs.value('(.)[1]','int') AS SubProjects
FROM RB_Projects TblProjects
CROSS APPLY Project.nodes('/Projects/SubProjects/id') AS P(SubProjectIDs)
where id = 100
)

but i keep getting the following error
Incorrect syntax near ')'.

does CTE's not like xquery functions within it's expression?n/m

guess i gotta use the CTE after definine it...

"A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE

statement that
references some or all the CTE columns."
http://msdn2.microsoft.com/en-us/library/ms175972.aspx