Using Globally defined arrays in Crystal Reports

In Crystal Reports you can use a globally defined array to determine whether or not any given record will show on a report. Case in point, a record is pulled into your report multiple times due to a join condition. We want to suppress one of the records so only one shows on the report. In our case, we want to keep all records with note # 0033. If any record with note # 0033 is not duplicated with a different note number, that record we want to keep.

To begin, we'll want to initialize our variables in a function. This function is dropped in the Report header:

Global numberVar array ClassNumberArray;
Global numberVar CNCount := 1;
Redim ClassNumberArray[1];

The following function we will want to drop into the Details section of the report. This function runs during the Record Reading pass:

WhileReadingRecords;
Global numberVar CNCount;
Global numberVar array ClassNumberArray;
Redim preserve ClassNumberArray[CNCount];
ClassNumberArray[CNCount] := {RDS_CLASS_VW.CLASS_CLASS_NBR};
CNCount := CNCount + 1;

Our global array is now filled. The following function (we'll call it 'ClassNumber Count' and refer to it later) also gets dropped in the Details section of the report and we'll have it run during the next pass, the Record Printing pass.

WhilePrintingRecords;
Global numbervar array ClassNumberArray;
local numberVar j := 0;
numbervar i;

For i:=1 to Count(ClassNumberArray) do
    if ({RDS_CLASS_VW.CLASS_CLASS_NBR} = ClassNumberArray[i]) then
        j := j + 1;
j;

The variable "j" now holds the number of times that record is duplicated in the set of records.

Now in Section Expert, we'll create a rule for suppressing records:

{REC_CLASS_NOTES_VW.NOTES_NOTE_NBR} = "0033" and {@ClassNumber Count} > 1

// delete any records with note # 0033 that show up two times or more in the array.

That's it. When a record shows up more then once, the record with note # 0033 is the one that is removed.

Exporting Crystal Report to XML

Saving a Crystal Report in XML format, based upon the default Crystal Report schema, along with an xsl transformation, is a great way to  create web-ready content.

Below is a snippet of xml generated by Crystal Reports. While in Crystal, go to Format Field to set a custom Name attribute as shown below:

<?xml version="1.0" encoding="UTF-8" ?>
<CrystalReport xmlns="urn:crystal-reports:schemas:report-detail"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:crystal-reports:schemas:report-detail http://www.businessobjects.com/products/xml/CR2008Schema.xsd">
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Field Name="TERM" FieldName="GroupName ({Command.CLASS_TERM_CD})"><FormattedValue>Spring 2015</FormattedValue><Value>Spring 2015</Value></Field>
<Field Name="TERMCODE" FieldName="{Command.CLASS_TERM_CD}"><FormattedValue>4152</FormattedValue><Value>4152</Value></Field>
</Section>
</GroupHeader>
<Details Level="2">
<Section SectionNumber="0">
<Field Name="CLASSNAME" FieldName="{Command.CLASS_CLASS_NAME}"><FormattedValue>ENG102</FormattedValue><Value>ENG102</Value></Field>
<Field Name="CLASSNBR" FieldName="{Command.CLASS_CLASS_NBR}"><FormattedValue>38090</FormattedValue><Value>38090</Value></Field>
<Field Name="TITLE" FieldName="{Command.CRSE_COURSE_TITLE_LONG}"><FormattedValue>First-Year Composition</FormattedValue><Value>First-Year Composition</Value></Field>
<Field Name="STARTTIME" FieldName="{Command.CLASS_START_TIME1}"><FormattedValue> 4:00PM</FormattedValue><Value> 4:00PM</Value></Field>
<Field Name="ENDTIME" FieldName="{Command.CLASS_END_TIME1}"><FormattedValue> 6:40PM</FormattedValue><Value> 6:40PM</Value></Field>
<Field Name="STARTDATE" FieldName="{Command.CLASS_START_DATE}"><FormattedValue>3/24</FormattedValue><Value>2015-03-24T00:00:00</Value></Field>
<Field Name="ENDDATE" FieldName="{Command.CLASS_END_DATE}"><FormattedValue>5/15</FormattedValue><Value>2015-05-15T00:00:00</Value></Field>
<Field Name="DAYS" FieldName="{@FormattedDays}"><FormattedValue>Tu,Tr</FormattedValue><Value>Tu,Tr</Value></Field>
</Section>
</Details>
...

On the XSL side, make sure you setup your Crystal Reports namespace. In the example below I'm using a 'cr' prefix.

<?xml version="1.0"?><xsl:stylesheet xmlns:cr="urn:crystal-reports:schemas:report-detail" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="/">

<xsl:for-each select="cr:CrystalReport/cr:Group">

<h2 class="ms-rteElement-H2Custom"><xsl:value-of select="cr:GroupHeader/cr:Section/cr:Field[@Name='TERM']/cr:FormattedValue"/></h2><table width="100%" class="courseTableWP">
<xsl:for-each select="cr:Details/cr:Section">

<tr class="courseTableRow"></tr></xsl:for-each></table>
<th width="10%">Course</th><th width="15%">Class Number</th><th width="35%">Title</th><th width="10%">Days</th><th width="15%">Times</th><th width="15%">Dates</th><td><xsl:value-of select="cr:Field[@Name='CLASSNAME']/cr:FormattedValue"/></td>
<td><xsl:value-of select="cr:Field[@Name='CLASSNBR']/cr:FormattedValue"/></td>
<td><xsl:value-of select="cr:Field[@Name='TITLE']/cr:FormattedValue"/></td>
<td><xsl:value-of select="cr:Field[@Name='DAYS']/cr:FormattedValue"/></td>
<td><xsl:value-of select="cr:Field[@Name='STARTTIME']/cr:FormattedValue"/>- <xsl:value-of select="cr:Field[@Name='ENDTIME']/cr:FormattedValue"/></td>
<td><xsl:value-of select="cr:Field[@Name='STARTDATE']/cr:FormattedValue"/> - <xsl:value-of select="cr:Field[@Name='ENDDATE']/cr:FormattedValue"/></td>

</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Using an OR Statement in Crystal Reports to suppress rows in the Details section

I recently created a report where I want to suppress rows in the Details section of a Crystal Report. Using an OR statement along with a conditional IF, besides suppressing based on duplicate ID values, I can also suppress the row based upon a parameter value.

{?Intended Audience} = "Internal Use Only"
OR
 if (not onfirstrecord) then (previous({PERSON.ID})={PERSON.ID})

Multiple if conditions in a Crystal Reports Record Selection

 When using multiple If statements in a Crystal Reports Record Selection, use parentheses to group your if-then-else statements. These need to be terminated with an Else. If no else exists in your logic, use Else True. Multiple If statements are then separated using an And or Or as seen in the example below:


{TERM} = {?Term} And
{INSTITUTION} = {?Institution} And
(
If ({?Organization} <> "%") Then
    {ACAD_ORG} = {?Organization}
Else True
)
And
(
If ({?Dual Enrollment} = "N") Then
    {HS_DUAL_ENROLL} = "N"
Else If ({?Dual Enrollment} = "Y") Then
    {HS_DUAL_ENROLL} = "Y"
Else True
)

Sharing variables between a subreport and the main report in Crystal Reports

In a recent  Crystal Report I was working on, I had a need to share a variable between a subreport and the main report so I could suppress rows based upon the value returned in the subreport. Normally, any value within a subreport is not visible to the Formula Editor. The key is to then to use a shared variable to pull the data into the main report where it's visible within the editor.

Because in the normal processing flow, subreports are exectuted in the 2nd pass, after record retrieval,

Displaying multiple parameter values in a Crystal Report

When a Crystal report user chooses multiple values for a parameter, sometime it's nice to display all those parameter values in the report header. The following code can be added to a function for displaying in the header.

WhilePrintingRecords;
StringVar Array term := {?Term};

StringVar terms := term[1];
Local NumberVar i;

For i := 2 To count(term) Do
(
   terms := terms + ", " + term[i];
);

terms

All (%) in Crystal Reports Formula Editor

When constructing a parameter in Crystal Reports that includes "all" items (%), construct a formula that returns true or false for your report's selection criteria.

In the formula editor, add:


//Selects all academic orgs or specific ones based on parameter
if {?Academic Orgs}="%" then  true
else
     if {?Academic Orgs} = {RDS_CLASS_TBL.CLASS_ACAD_ORG} then true
else
     false

Then for your record selection include your formula:

... and{@Selection Criteria for Academic Orgs} ...

Using the Previous function to supress Detail rows

When your query in Crystal does not return Distinct records, you may want to supress rows in the Details section based upon the previous value of a field or fields. Add the following code to the Supress code area in the Section Expert for the Details section:

 if (not onfirstrecord) then
    previous({RDS_CLASS_VW.CLASS_CLASS_NBR})={RDS_CLASS_VW.CLASS_CLASS_NBR}

SubjectsAll dynamic prompt for Crystal

The prompt group 'RDS SubjAll M' in Crystal Reports requires logic in the Record Selection formula to account for the ' All' value for all subjects. Below is an example of how to construct the query:

if ({?Subject} <> " All") then 

({?Subject} = {RDS_CLASS_VW.CLASS_SUBJECT_CD} and

{?Institution} = {RDS_CLASS_VW.CLASS_INSTITUTION_CD})

else

{?Institution} = {RDS_CLASS_VW.CLASS_INSTITUTION_CD}

If the Subject parameter equals ' All' we simply ignore it.