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
> >
> >