How to format numerics with the sign on the right?

I've got an amount field in a report where I want the values to have the sign
(if negative) on the right. But I also want the numbers to line up properly.
I can't seem to find a way to format this. In Excel, you'd use a format like
#,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
see you can use custom formats and the syntax is similar to Excel but not
quite the same. I tried:
#,##0.00 ;#,##0.00-
but RS seems to ignore the whitespace. I also tried:
#,##0.00' ';#,##0.00-
also to no avail.
Any suggestions?What about using double quotes: " " ?
"virtualfergy" wrote:
> I've got an amount field in a report where I want the values to have the sign
> (if negative) on the right. But I also want the numbers to line up properly.
> I can't seem to find a way to format this. In Excel, you'd use a format like
> #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> see you can use custom formats and the syntax is similar to Excel but not
> quite the same. I tried:
> #,##0.00 ;#,##0.00-
> but RS seems to ignore the whitespace. I also tried:
> #,##0.00' ';#,##0.00-
> also to no avail.
> Any suggestions?|||Tried it. No luck I'm afraid. Works the same as single quotes. Any other
advice out there?
"Albert" wrote:
> What about using double quotes: " " ?
> "virtualfergy" wrote:
> > I've got an amount field in a report where I want the values to have the sign
> > (if negative) on the right. But I also want the numbers to line up properly.
> > I can't seem to find a way to format this. In Excel, you'd use a format like
> > #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> > see you can use custom formats and the syntax is similar to Excel but not
> > quite the same. I tried:
> >
> > #,##0.00 ;#,##0.00-
> >
> > but RS seems to ignore the whitespace. I also tried:
> >
> > #,##0.00' ';#,##0.00-
> >
> > also to no avail.
> >
> > Any suggestions?

How to Flatten ?

Is there an easy to understand method of bringing this into a flat structure
(all elements related to a line item on one line) ? I do not care if the
xsl is "optimal" with respect to efficiency, I just want it to work with as
little complexity as possible. Please note the multiple line items. I
have seen and understand examples where there is a one to one relationship
between the header and the detail line, but not when there are 3 levels
involved and multiple detail lines.
Thanks,
Rob
<Invoices>
<Invoice id="ABC">
<Supplier>123</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>2.88</NetAmount><Codes><Code1>ABC</Code1><Code2>DEF</Code2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>1.88</NetAmount><Codes><Code1>VBH</Code1><Code2>LKJ</Code2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
<Invoice id="DEF">
<Supplier>568</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>7.00</NetAmount><Codes><Code1>VPP</Code1><Code2>LRE</Code2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>9.00</NetAmount><Codes><Code1>ZAQ</Code1><Code2>WIJ</Code2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
</Invoices>
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:%KB7d.14101$yp.4743@.bignews1.bellsouth.net...
> Is there an easy to understand method of bringing this into a flat
structure
> (all elements related to a line item on one line) ? I do not care if
the
Rob,
Does this work for you:
DECLARE @.xml varchar(2000)
set @.xml =
'<Invoices>
<Invoice id="ABC">
<Supplier>123</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>2.88</NetAmount><Codes><Code1>ABC</Code1><Code2>DEF</Cod
e2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>1.88</NetAmount><Codes><Code1>VBH</Code1><Code2>LKJ</Cod
e2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
<Invoice id="DEF">
<Supplier>568</Supplier>
<InvoiceItems>
<InvoiceItem
line="1"><NetAmount>7.00</NetAmount><Codes><Code1>VPP</Code1><Code2>LRE</Cod
e2></Codes>
</InvoiceItem>
<InvoiceItem
line="2"><NetAmount>9.00</NetAmount><Codes><Code1>ZAQ</Code1><Code2>WIJ</Cod
e2></Codes>
</InvoiceItem>
</InvoiceItems>
</Invoice>
</Invoices>'
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc output, @.xml
SELECT * FROM OPENXML(@.hdoc, '/Invoices/Invoice/InvoiceItems/InvoiceItem',
2)
WITH (
InvoiceId char(3) '../../@.id',
Supplier varchar(20) '../../Supplier',
line char(1) '@.line',
Code1 char(3) 'Codes/Code1',
Code2 char(3) 'Codes/Code2',
NetAmount numeric(9,2) 'NetAmount')
EXEC sp_xml_removedocument @.hDoc
sql

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
--
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIA
Data Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am no
t able to know
which line is giving the problem. How do I make QA show me the offending lin
e.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIAsql