Converting Crystal Report to SQL Code

Question

I'm converting a Crystal Report into SQL code and need help/direction on how to implement expressions created in the reporting tool in sql code. Below is an expression that returns 1, 2, or 3 depending on how it is evaluated by the run time program. Any assistance in converting this expression into SQL code is appreciated.

Code starts here ***************

if IsNull({WorkAssignment.StartDateTime}) or IsNull({WorkSet.ScheduledReadDate}) then
(
3
)

else
(
if (Date({WorkAssignment.StartDateTime}) <= Date({WorkSet.ScheduledReadDate}) and
({WorkAssignment.WorkFilterName} <> 'DNRs' and {WorkAssignment.WorkFilterName} <> 'Type 2s/3s')) or
(val(right({WorkSet.WorkSetID}, 2)) < 50 and mid({WorkSet.WorkSetID}, len({WorkSet.WorkSetID}) - 3, 1) = "0") then
1

else if (Date({WorkAssignment.StartDateTime}) <= Date({WorkSet.ScheduledReadDate}) and
({WorkAssignment.WorkFilterName} = 'DNRs' or {WorkAssignment.WorkFilterName} = 'Type 2s/3s')) or

((val(right({WorkSet.WorkSetID}, 2)) >= 50 and val(right({WorkSet.WorkSetID}, 2)) <= 69) and
mid({WorkSet.WorkSetID}, len({WorkSet.WorkSetID}) - 3, 1) = "0") then
2
else
3
;
)
;

Code ends here *******************************

The expression is used to group records based on how it is evaluated. Record that belong to group 1 are put into group 1, record that are evaluated as 2 into group two and so on.

---

Rob Seminario

Answer 1

One solution is creating modular views. Threre are usually some views that called in crystal report. You can create a main view (equal to the report), that uses those views.

---

sqldevelop.wordpress.com

Answer 2

Hi Rob,

In SQL Server Reporting Services (SSRS), we can use IIF() and Switch function to converting the Crystal expression to SSRS expression. In SSRS, we can group data by a single field, or create more complex expressions that identify the data on which to group.
Reference: http://technet.microsoft.com/en-us/library/bb630426.aspx

If you want converting the Crystal expression to T-SQL, we can use CASE expression to get the same effect. It is evaluates a list of conditions and returns one of multiple possible result expressions. Please refer to the article below:
http://technet.microsoft.com/en-us/library/ms181765.aspx

Regards,
Alisa Tang

---

Alisa Tang
TechNet Community Support

Answer 3

Alisa,

Thanks for that direction, however, I am not trying to accomplish this in a reporting tool but in an SQL script. Therefore, I need the code in an expression that gets evaluated as the script is running and determines that a record is not code 1 and excludes it from the group or bucket that is being counted. Like the following: IIF(XYZ = TYPE 1, 1, 0) A running counter will only accumulate if it is true.

So, when my expression is true it includes those record or not.

---

Rob Seminario

Answer 4

You might be better off posting this question in a TSQL Forum, as this forum is specifically for SQL Server Reporting Services and Reporting within that space.

Google TSQL Case statement it may do what you need.