Converting Crystal Reports 8.5 XML output to RDL....

All--

We have several dozen Crystal (8.5) Reports that we would like to port to SSRS 2005. I believe that I can save these reports as XML data files with an accompanying schema. We are not interested in the data, but would like to be able to preserve the reports' layout from one reporting platform to another. I believe I might be able to write an XSLT transformation to convert the Crystal XML output into RDL, but this is not a trivial task. (We would like to avoid, at all costs, manually redesigning the reports in SSRS, but, if it comes to that, we will have to bite the bullet.) Has such an XSLT transformation already been created, and, if so, would anyone care to share their work?

TIA,

mattyseltz in NY

You could try this service for $25 per report:

http://rpttosql.com/

cheers,

Andrew

sqlsql

Converting Crystal Reports 8.5 XML output to RDL....

All--

We have several dozen Crystal (8.5) Reports that we would like to port to SSRS 2005. I believe that I can save these reports as XML data files with an accompanying schema. We are not interested in the data, but would like to be able to preserve the reports' layout from one reporting platform to another. I believe I might be able to write an XSLT transformation to convert the Crystal XML output into RDL, but this is not a trivial task. (We would like to avoid, at all costs, manually redesigning the reports in SSRS, but, if it comes to that, we will have to bite the bullet.) Has such an XSLT transformation already been created, and, if so, would anyone care to share their work?

TIA,

mattyseltz in NY

You could try this service for $25 per report:

http://rpttosql.com/

cheers,

Andrew

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

Convert varchar to decimal

Hello, I am running a SELECT statement on a varchar 50 field. I would like
to convert the output to a numeric field with 2 decimal places. I tried a
CAST but that didn't work. Any suggestions would be appreciated. Thanks,
Pancho."Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.
Not knowing the largest decimal value you might wish to display, you could
do something like this:
SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
FROM TableName
This will allow a total of 10 digits with 2 on the right of the decimal
point.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Pancho,
What didn't work?
Did you receive an error message?
If so, what?
If not, show us your code.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.|||Rick,
I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
and got:
'dec' is not a recognized function name
Got the same error using 'decimal'. I am running a successful
SELECT CONVERT (CHAR (8), Field8, 112) AS Field8Text but the dec didn't work
.
"Rick Sawtell" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
> Not knowing the largest decimal value you might wish to display, you could
> do something like this:
> SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
> FROM TableName
>
> This will allow a total of 10 digits with 2 on the right of the decimal
> point.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi Raymond,
I got the msg "Unable to convert varchar field" when using CAST. I am
taking data from a varchar and trying to format it to a numeric field, 10
wide with 2 decimal spaces. Pls see code above in my reply to Rick. Thanks
,
Pancho.
"Raymond D'Anjou" wrote:

> Hi Pancho,
> What didn't work?
> Did you receive an error message?
> If so, what?
> If not, show us your code.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
>|||> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work.
What does "didn't work" mean? What did you try? Are you sure all of the
values are really numeric and can be converted? If so, why are you storing
them in a VARCHAR(50) column?|||> I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
> and got:
> 'dec' is not a recognized function name
Did you try using the right syntax?
NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)|||> I got the msg "Unable to convert varchar field" when using CAST.
Well, because you decided to use a VARCHAR(50) to store decimals, invariably
you will get varchar data that is not a decimal.
You shouldn't be really surprised by this.
A start would be to filter out the rows where ISNUMERIC(column_name) = 0.
However, ISNUMERIC() is not perfect either, see http://www.aspfaq.com/2390
Then, try cleaning up your data and fixing the data type. When you do that,
you won't need to do a convert at all.|||You got me. I didn't design this DB; a vendor did. I would not have stored
a field which has cash values as a varchar either. I'm trying to convert it
from one vendor to another.
Thanks everyone for your comments. I'm closing this one now.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "didn't work" mean? What did you try? Are you sure all of the
> values are really numeric and can be converted? If so, why are you storin
g
> them in a VARCHAR(50) column?
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OfuNFSyXGHA.4388@.TK2MSFTNGP03.phx.gbl...
> Did you try using the right syntax?
> NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)
>
Ooops.. Got it backwards. Thanks Aaron.
Rick

convert stored procedures returned output to varchar from int?

I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(

Syntax error converting the varchar value 'ABC' to a column of data type int.

SQL file

======================

DECLARE @.S AS VARCHAR(1000)

EXEC @.S = PDT.DBO.SP_RPT
'ABC'

SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email

EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S

======================

Stored Procedure

======================

CREATE procedure sp_RPT
( @.array varchar(1000) )
AS

DECLARE @.content AS VARCHAR(2000)

SET @.content = 'RPT: ' + @.array + ' loaded successfully '

SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)

GO

try

cast( @.array as varchar(1000))

Hope this help

|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).

convert sql selects into xml format

hai all,

I need some help and is very urget. I wan to convert the output of the sql query into xml format but i don hv any idea on how to do it. I'm using vb.net for this application.

Hope your guys out there can help me on this.

Regards,

KYY

Read up on the FOR XML clause that SQL Server supports: http://msdn2.microsoft.com/en-us/library/ms191268.aspx

CONVERT Money Char(20)

I have a sql query listed below that I would like to only output values like
'6024', the Bonus values are converted to char(20) from Call_Movement table.
SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
Call_Movement
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
Please help me complete this task.Joe K. wrote:
> I have a sql query listed below that I would like to only output values like
> '6024', the Bonus values are converted to char(20) from Call_Movement table.
>
> SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
> Call_Movement
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> Please help me complete this task.
Please post with table structure and simple data and desired output.
if you are not using wildcard then no need to use like in the query
just use =.
Also you can not put = after like and you have to put like operator in
where clause.
Regards
Amish Shah

CONVERT Money Char(20)

I have a sql query listed below that I would like to only output values like
'6024', the Bonus values are converted to char(20) from Call_Movement table.
SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
Call_Movement
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
Please help me complete this task.Joe K. wrote:

> I have a sql query listed below that I would like to only output values li
ke
> '6024', the Bonus values are converted to char(20) from Call_Movement tabl
e.
>
> SELECT cast(cast(Bonus as money) as char(20)) like = '6024' from
> Call_Movement
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> Please help me complete this task.
Please post with table structure and simple data and desired output.
if you are not using wildcard then no need to use like in the query
just use =.
Also you can not put = after like and you have to put like operator in
where clause.
Regards
Amish Shah

Convert Money as String $XX.XX in output

How can I convert or format a Money defined field to look like $xx,xxx.xx?
My data is stored like 50000.99 but I'd like to see $50,000.99 in the query
results. I tried using CAST (SELET CAST (MyField As Money) As MyField FROM
MyTable...) but that didn't seem to change anything.
Thanks!Try this:
SELECT '$' + convert(char(9), MyField , 1)
FROM MyTable
It is not perfect because it will have space between $ and the digit if the
amount is small, and you need to make sure your money amont is less then
100.000. Try it and make more modification as you need.
Perayu
"ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the
> query results. I tried using CAST (SELET CAST (MyField As Money) As
> MyField FROM MyTable...) but that didn't seem to change anything.
> Thanks!
>|||ShaneFowlkes,
See function "convert" in BOL.
Example:
select '$' + convert(varchar(25), cast(50000.99 as money), )
go
AMB
"ShaneFowlkes" wrote:

> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the quer
y
> results. I tried using CAST (SELET CAST (MyField As Money) As MyField FRO
M
> MyTable...) but that didn't seem to change anything.
> Thanks!
>
>|||This one is better:
SELECT '$' + convert(varchar(15), MyField , 1)
FROM MyTable.
Perayu
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eN62z16bGHA.5116@.TK2MSFTNGP02.phx.gbl...
> Try this:
> SELECT '$' + convert(char(9), MyField , 1)
> FROM MyTable
> It is not perfect because it will have space between $ and the digit if
> the amount is small, and you need to make sure your money amont is less
> then 100.000. Try it and make more modification as you need.
> Perayu
> "ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
>|||"ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the
query
> results. I tried using CAST (SELET CAST (MyField As Money) As MyField
FROM
> MyTable...) but that didn't seem to change anything.
General note though is this is typically something you want to do in the
front end, not the back end.
But for quick dirty stuff, what others said should work.

> Thanks!
>

Convert Money as String $XX.XX in output

How can I convert or format a Money defined field to look like $xx,xxx.xx?
My data is stored like 50000.99 but I'd like to see $50,000.99 in the query
results. I tried using CAST (SELET CAST (MyField As Money) As MyField FROM
MyTable...) but that didn't seem to change anything.
Thanks!Try this:
SELECT '$' + convert(char(9), MyField , 1)
FROM MyTable
It is not perfect because it will have space between $ and the digit if the
amount is small, and you need to make sure your money amont is less then
100.000. Try it and make more modification as you need.
Perayu
"ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the
> query results. I tried using CAST (SELET CAST (MyField As Money) As
> MyField FROM MyTable...) but that didn't seem to change anything.
> Thanks!
>|||ShaneFowlkes,
See function "convert" in BOL.
Example:
select '$' + convert(varchar(25), cast(50000.99 as money), )
go
AMB
"ShaneFowlkes" wrote:
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the query
> results. I tried using CAST (SELET CAST (MyField As Money) As MyField FROM
> MyTable...) but that didn't seem to change anything.
> Thanks!
>
>|||This one is better:
SELECT '$' + convert(varchar(15), MyField , 1)
FROM MyTable.
Perayu
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eN62z16bGHA.5116@.TK2MSFTNGP02.phx.gbl...
> Try this:
> SELECT '$' + convert(char(9), MyField , 1)
> FROM MyTable
> It is not perfect because it will have space between $ and the digit if
> the amount is small, and you need to make sure your money amont is less
> then 100.000. Try it and make more modification as you need.
> Perayu
> "ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
>> How can I convert or format a Money defined field to look like
>> $xx,xxx.xx? My data is stored like 50000.99 but I'd like to see
>> $50,000.99 in the query results. I tried using CAST (SELET CAST (MyField
>> As Money) As MyField FROM MyTable...) but that didn't seem to change
>> anything.
>> Thanks!
>|||"ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the
query
> results. I tried using CAST (SELET CAST (MyField As Money) As MyField
FROM
> MyTable...) but that didn't seem to change anything.
General note though is this is typically something you want to do in the
front end, not the back end.
But for quick dirty stuff, what others said should work.
> Thanks!
>