Frequently Asked Questions - Technical Information
The Crystal RecordSelectionFormula.
The Crystal RecordSelectionFormula is merged into the SQL Where clause if it is not too complex. This makes the record selection faster and the result-set smaller because the record filtering is being done on the Database Server and not on the Client (Report Writer).
If you see a message in the log that says: "Crystal RecordSelectionFormula converted to DataSet Selection Filter", it means that either:
  • The statement is a Crystal 'Command' statement or a Stored Procedure reference, or;
  • The RecordSelectionFormula contains a Crystal VB expression that references language elements E.g. formulas using variables, custom functions, Loops etc.

Use the conversion log to examine the Crystal RecordSelectionFormula and the converted DataSet Selection Filter expression to determine if any modifications to the SQL statement are needed. Click on the Filter tab in the Dataset Properties Window in Visual Studio to modify the DataSet Selection Filter expression.
This technical article from Microsoft has more information.

Expressions based on PageNumber or TotalPages do not work the same.
SSRS does not allow references to page numbers within the body of the report, because page numbers are assigned during rendering I.e. Page Headers/Footers are produced after the body of the report is produced.

This issue may be easy to resolve, by simply moving the page number reference into the page header or footer. However if it is being used to alternate page background colors then other solutions can be found by doing an internet search.
We encapsulate the crystal page expressions into functions in the code section simply to eliminate Build/Compile errors.

SSRS does not execute expressions in items that are hidden.
This issue is one of the main reasons why complicated reports using variables do not produce the expected results immediately after conversion. The problem manifests itself when the expression changes a variable like in the following example.

Function AddABC(name as String) as Integer
   If name <> "ABC" then CntABC = CntABC + 1
   return CntABC
End Function

The item value expression is: =Code.AddABC(Fields!AreaName.Value)

You can see that the CntABC variable would never be changed if the item has Visibility:Hidden='True'. One solution is to make the expression part of the logic that hides the Item. I.e. If the Row or Item Visibility:Hidden is 'True' (Hide), then change the Function to return True and make the reference to the function in the visibility expression.

Function AddABC(name as String) as Boolean
   If name <> "ABC" then CntABC = CntABC + 1
   return True
End Function

You can also place all expressions like this from a group or row that is hidden, into a single Function that will execute them all before returning True.

Case Sensitive (SSRS) vs. Insensitive (Crystal) String compares
Consider the following Visual Basic statements:
  If "Abc" = "ABC" then Return true
  If "B" < "b" then Return true

In Crystal the first statement evaluates to 'true' and the second to 'false'. but in SSRS/VB they are 'false' and 'true'. In many cases this does not make a difference, because what is usually being compared are two database fields, or a field compared with a literal. And most databases store data as uppercase, or at least in a consistent case.

However Crystal also ignores trailing spaces so that the following statement also evaluates as true in Crystal, but false in SSRS:
  If "Abc   " = "ABC" then return true

We provide a StrEquals function and a StrCompare function that upper-cases and trims trailing spaces from both parameters before doing the comparison. So the statements above are converted to:
  If StrEquals("Abc", "ABC") then Return true
  If StrCompare("B", "b") < 0 then Return true
  If StrEquals("Abc   ", "ABC") then Return true

However if you find that your report is inconsistent or displaying wrong information when a string comparison is involved, then look for this issue. Also if you modify the report and add string comparisons then please consider whether to use the provided functions, or the Visual Basic native comparison operators (= < > <= >= <>).

Aggregates - E.g. Sum, RunningValue, Count
Aggregates are calculated before any report rows are processed.

Consider the following example where we have two variables 'a' and 'b' defined in code with the same 'Get' and 'Increment' function wrappers and both start at an initial value of 10:

Private a as Integer = 10
Public Function Get_a() as Integer
   Return a
End Function
Public Function Incr_a() As Integer
   a = a + 1
   Return 1
End Function

Private b as Integer = 10
Public Function Get_b() as Integer
   Return b
End Function
Public Function incr_b() as Integer
   b = b + 1
   Return 1
End Function

Then we design a report where the increment functions are used in an expression - one outside the SUM aggregate function, and the other inside it:

When we run the report on 8 database records we see these values:

It shows that the value of variable 'a' correctly increases by one for each row (from 11 to 18), but variable 'b' is already 18 before the report has processed the first row. This means that the dataset must have been pre-processed to evaluate the expression inside the SUM and therefore complicated aggregate expressions converted from Crystal may not work the same.

This also means that our implementation of PREVIOUS/NEXT will not work inside an aggregate, because we are not able to obtain these values before the preprocessing occurs i.e. the SUM occurs before we have a value for PREVIOUS/NEXT.

Previous/Next Functions - Crystal vs. SSRS
This example will demonstrate the differences between Crystal Previous and Next functions and SSRS Previous function (Next function not available) and our implementation of these functions.

We will select rows from a table that contains ID and COUNTRY fields.

  Select ID, COUNTRY
From Addresses
Order by COUNTRY, ID

The above SQL statement returns the following data:

We created a Crystal report that displays record #, Current/Previous/Next Country and ID.
You can download the Crystal report here.

This is what the Crystal output looks like.

We then convert the Crystal Report to SSRS and run it.

The only differences between the two are highlighted in red. These are just the Row/Record #s, and it just illustrates that SSRS produces the group header line when it is on the last record of the current group (above the detail lines), rather than the first record of the current group like Crystal does.
This is not relevent to our Previous/Next implementation but is worth understanding.

Let's now compare this to the Native SSRS implementation of the Previous function. You can download the native SSRS report here.
The areas in red are missing I.e. no Next column data and no Previous data in anything but detail rows.

Some notes on our Next/Previous function implementation:

  • It does have some memory and processing overhead, the extent of which is dependent on how many rows are being selected from the database.
  • We will use the SSRS Previous function if only the Previous function is used (not Next), is only referenced in detail rows, and only has Fields as parameter (not an expression).
  • We will use our implementation of Previous/Next functions if the Next function is used, or Previous is referenced outside of detail rows, or has an expression as a parameter.