|
|
|
|
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:
|
ID | COUNTRY |
1 | Canada |
2 | Canada |
3 | Canada |
4 | Canada |
8 | Canada |
10 | Canada |
11 | Canada |
12 | Canada |
13 | France |
14 | France |
15 | France |
5 | UK |
6 | UK |
7 | UK |
9 | UK |
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.
|
|
|
|
|
|
|