converting Crystal Reports to SSRS

I came across a website that claimed "There are approximately 116 functions in Crystal Reports that do not have a direct equivalent in VB.NET or Reporting Services (SSRS)." Does anyone know what these functions are?I know this is not a direct answer to your question, but we looked into a way to convert Crystal Reports to SQL Reports, but were unable to find a viable solution. There is an application called RPT2RDL that claims to convert some files, but it does not handle a good many functions even though some are supported by SQL Reporting Services. This has left us manually recreating the reports which takes a great deal of time.

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 Crystal Report formula to SSRS

Converting Crystal Report formula to SSRS

I've got a formula written in Crystal Reports that I'm trying to re-do in SSRS 2005. I've just been using Crystal Reports for so long, I've got a mental road-block today.

Here is the formula in Crystal Reports:
IF {V_VIEW.FIELD1} IN ["AAA", "BBB", "CCC", "DDD",
"EEE", "FFF"]
THEN ({V_VIEW.FIELD2}&"*")
ELSE ({V_VIEW.FIELD2})

(It concatonates an asterisk to the end of FIELD2 if FIELD1 contains on of the values in the list.)

In SSRS I'd like to cause an entire row to be bold if FIELD1 contains one of the values in the list.

So in SSRS I'm putting an expression into the FontWeight properties of the TableRow and trying for something (which doesn't work yet) like:
=iif (Fields!FIELD1.Value IN ("AAA", "BBB", "CCC", "DDD", "EEE", "FFF"), "BOLD", "NORMAL")

(SSRS doesn't like the "IN" in the above statement.)

Can anyone offer a suggestion on how to write this for SSRS?

Thanks!
-ErikR

UPDATE

2007-SEPT-17

Ok. I found a workable solution. Does anyone have a better suggestion than the following? The below works but it seems it could be done more simply... Any suggestions?

=iif (Fields!FIELD1.Value = "AAA","Bold",
iif(Fields!FIELD1.Value = "BBB","Bold",
iif(Fields!FIELD1.Value = "CCC","Bold",
iif(Fields!FIELD1.Value = "DDD","Bold",
iif(Fields!FIELD1.Value = "EEE","Bold",
iif(Fields!FIELD1.Value = "FFF","Bold",
iif(Fields!FIELD1.Value = "GGG","Bold",
iif(Fields!FIELD1.Value = "HHH","Bold",
iif(Fields!FIELD1.Value = "III","Bold",
iif(Fields!FIELD1.Value = "JJJ","Bold",
iif(Fields!FIELD1.Value = "KKK","Bold",
iif(Fields!FIELD1.Value = "LLL","Bold",
iif(Fields!FIELD1.Value = "MMM","Bold",
iif(Fields!FIELD1.Value = "NNN","Bold",
iif(Fields!FIELD1.Value = "OOO","Bold",
iif(Fields!FIELD1.Value = "PPP","Bold",
"Normal"))))))))))))))))

Take a look at the VB.NET Switch function for more concise syntax. Alternatively, you can code a custom function that parses the field value any way you want.

Convert numbers to Month Name

I have a stupid field in my database that doesn't hold a date but I have to use it to determine the month in my Chart in SSRS 2005. So for example, check this out:

http://www.webfound.net/chart_months.jpg

Ok, so how can I change those numbers to the Month Name? I tried MonthName() around my field in the expression builder, but that's only for a datetime field.

Write a UDF with input as int month and return it as varchar month. Use 13 CASE statements (12 for 12 months and 1 for error)|||

How about something like convert(datetime,'2006' + <fieldname> + '01') within your SQL statement?

for padding 0's

select convert(datetime,'2006' + fieldname + '01')
from
( select case <fieldname> when > 9 then <fieldname> else '0' + convert(char(1),<fieldname>) end fieldname ) derivedtable1

|||

The function MonthName, in SRSS2005, accepts an Integer parameter, so I'm not sure I understood the question.

|||hmm, then I wonder why that didn't work...MonthName(fieldname)|||

Paulo X , look at the link to my chart above. Those are integer values coming in from my dataset on a field in our DB table called systemmonth. Don't ask me why they did it that way but I need to take those values and convert them to Month Names....hopeing to do this either through SQL or preferably using a function in Reporting Server 2005 as you stated. I tried wrapping the systemmonth like this but it didn't have any affect:

MonthName(myfieldname)

|||

this is what I'm talking about, I put this in as a category group field and created this expression behind it:

=MonthName(Fields!SystemMonth.Value)

|||

What happens when you use MonthName? Is there any error message?
You say that you have used the MonthName function into the group expression. You must used it also in the Label expression! In fact, you may use MonthName only in the Label, you don't need to use it to group data.

Regards

|||

Hi,

Just try

=Monthname(1) or

=MonthName("1")

Is it working. then assign your field values. the second statement also works.

Amarnath

Convert Crystal Field Logic To SSRS 2005?

Hello all!
I am in the process of converting a client's Crystal Reports to SSRS 2005
reports. Some of the crystal report fields have a good amount of logic
behind them to determine what is displayed.
What is the best way to apply this logic in a SSRS 2005 RDL file? This
looks like a lot of logic to have in an expression...Would I want to create a
function and pass in the parameters (it would need about 10+ parameters)?
What implications would this type of logic have on processing time?
Here is a sample of some of the logic:
if {File001.type} = 1 or {File001.type} = 6 then
(
if {File001.category} = "Sold" then
{File001.numeric1}
else
(if ({File001.action11} <> 0 or {File001.action13} <> 0) then
{File001.numeric2} else {File001.numeric3})
)
else if {File001.type} = 5 then
(
if {File001.category} = "Sold" then
{File001.numeric4}
else
(if ({File001.xfield} <> 0 or {File001.yfield} <> 0) then
{File001.numeric5} else {File001.numeric6})
)
else
{File001.numeric7}
Thanks!
-BrianBrian,
I'd look at putting that code in the report's Custom Code, rather than
trying to convert it all to some sort of giant iif expression. The
other alternative would be to put it all in an assembly and reference
the assembly from the report.
Potter|||You could do several things..
Create a VB Function in the Reports->Report Properties->Code section. This
would be a standard VB funtion.
Create a field which includes all the smarts, then you can just refer to
this as a field
or
Do it in SQL ( I personally like SQL, but I am a sql guy)...
The sql might be something like
select ....
Case When File001.Type = 1 or File001.Type = 6 THEN
CASE WHEN File001.Category = 'Sold' THEN File001.numeric1
ELSE
CASE WHEN File001.action1 != 0 or File001.action13 != 0 THEN
File001.numeric2
ELSE File001.Numeric3
END
WHEN File001.type = 5 THEN
CASE WHEN File001.Category = 'Sold' THEN File001.numeric4
WHEN File001.xfield != 0 OR File001.yfield !=0 THEN
File001.numeric5
ELSE File001.numeric6
END
END
ELSE File001.numeric7
END
If you do this in a Field or function, use the switch statement instead.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"brianpmccullough" wrote:
> Hello all!
> I am in the process of converting a client's Crystal Reports to SSRS 2005
> reports. Some of the crystal report fields have a good amount of logic
> behind them to determine what is displayed.
> What is the best way to apply this logic in a SSRS 2005 RDL file? This
> looks like a lot of logic to have in an expression...Would I want to create a
> function and pass in the parameters (it would need about 10+ parameters)?
> What implications would this type of logic have on processing time?
>
> Here is a sample of some of the logic:
> if {File001.type} = 1 or {File001.type} = 6 then
> (
> if {File001.category} = "Sold" then
> {File001.numeric1}
> else
> (if ({File001.action11} <> 0 or {File001.action13} <> 0) then
> {File001.numeric2} else {File001.numeric3})
> )
> else if {File001.type} = 5 then
> (
> if {File001.category} = "Sold" then
> {File001.numeric4}
> else
> (if ({File001.xfield} <> 0 or {File001.yfield} <> 0) then
> {File001.numeric5} else {File001.numeric6})
> )
> else
> {File001.numeric7}
>
> Thanks!
> -Brian
>|||One additional option if you're hitting SQL server 2005 databases is to
write the logic in stored procedures using C# or VB.NET. It is
sometimes easier to embed the logic in the database layer than in a
custom assembly (that needs to be deployed to all report servers), and
you get the flexibility of procedural programming that these languages
offer.
Matt A
Wayne Snyder wrote:
> You could do several things..
> Create a VB Function in the Reports->Report Properties->Code section. This
> would be a standard VB funtion.
> Create a field which includes all the smarts, then you can just refer to
> this as a field
> or
> Do it in SQL ( I personally like SQL, but I am a sql guy)...
> The sql might be something like
> select ....
> Case When File001.Type = 1 or File001.Type = 6 THEN
> CASE WHEN File001.Category = 'Sold' THEN File001.numeric1
> ELSE
> CASE WHEN File001.action1 != 0 or File001.action13 != 0 THEN
> File001.numeric2
> ELSE File001.Numeric3
> END
> WHEN File001.type = 5 THEN
> CASE WHEN File001.Category = 'Sold' THEN File001.numeric4
> WHEN File001.xfield != 0 OR File001.yfield !=0 THEN
> File001.numeric5
> ELSE File001.numeric6
> END
> END
> ELSE File001.numeric7
> END
> If you do this in a Field or function, use the switch statement instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "brianpmccullough" wrote:
> > Hello all!
> >
> > I am in the process of converting a client's Crystal Reports to SSRS 2005
> > reports. Some of the crystal report fields have a good amount of logic
> > behind them to determine what is displayed.
> >
> > What is the best way to apply this logic in a SSRS 2005 RDL file? This
> > looks like a lot of logic to have in an expression...Would I want to create a
> > function and pass in the parameters (it would need about 10+ parameters)?
> > What implications would this type of logic have on processing time?
> >
> >
> >
> > Here is a sample of some of the logic:
> >
> > if {File001.type} = 1 or {File001.type} = 6 then
> > (
> > if {File001.category} = "Sold" then
> > {File001.numeric1}
> > else
> > (if ({File001.action11} <> 0 or {File001.action13} <> 0) then
> > {File001.numeric2} else {File001.numeric3})
> > )
> > else if {File001.type} = 5 then
> > (
> > if {File001.category} = "Sold" then
> > {File001.numeric4}
> > else
> > (if ({File001.xfield} <> 0 or {File001.yfield} <> 0) then
> > {File001.numeric5} else {File001.numeric6})
> > )
> > else
> > {File001.numeric7}
> >
> >
> >
> > Thanks!
> >
> > -Brian
> >
> >