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.

Rename files in a folder using VBScript

Like a lot of you, I take a lot of pictures with my cell phone which I like to upload to my desktop computer. Besides pictures from the cell phone, I also have a Canon PowerShot camera that I like to use as well. When I upload the pictures, I like to have easier control of how the files are named for sharing purposes. Below is a VBScript I use to rename an entire folder's worth of pictures. For ease-of-use, I use textboxes to enter the folder path and the filename prefix. The script then adds unique numbers, with leading zeroes, to preserve the current sort. It's written to handle up to 9999 files in a folder, which for all practical purposes, is enough.

Option Explicit

Dim folderPath
Dim filePrefix
Dim FSO
Dim FLD
Dim file
Dim oldFilename
Dim newFilename
Dim LeadingZeroString
Dim count
Dim uniqueNumber

On Error Resume Next
folderPath = InputBox("Enter the folder path where your files are located:") 
filePrefix = InputBox("Enter the new filename prefix:") 

' path needs to end with a path separator
If Right(folderPath,1) <> "\" Then
folderPath = folderPath & "\"
End If

' Replace any characters, illegal or otherwise. 
filePrefix = Replace(Replace(filePrefix," ","_"),"\","_")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder(folderPath) 
If Err.Number < 1 Then
' set the correct # of leading zeros based on number of files
If FLD.Files.Count > 999 Then 
   LeadingZeroString = "0000"         
Elseif FLD.Files.Count > 99 Then 
   LeadingZeroString = "000" 
Else
   LeadingZeroString = "00" 
End If

'loop through the file collection, renaming files
For Each file in FLD.Files
   oldFilename = file.Path
   count = count + 1
   uniqueNumber = Right(LeadingZeroString & CStr(count), Len(LeadingZeroString))
   newFilename = folderPath & filePrefix & "_" & uniqueNumber & "." & FSO.GetExtensionName(oldFilename) 
   'rename the file
   FSO.MoveFile oldFilename, newFilename
Next

Set FLD = Nothing
Set FSO = Nothing
Else
MsgBox (Err.Description)
End If

Recently I updated a few MS Access reports that used various grouping; by day, by week and by month. In the group footers I needed to add two textboxes to display visit counts by campus.  Below is the report, group by week.

To show the first day of each week, the following expression was placed in the group header:

=Format([Time_In]-Weekday([Time_In],2),"dd-mmm-yyyy")

To show the count for the campuses, I included the following expressions:

=Count(IIf([Campus]="Pecos" Or IsNull([Campus]),1,Null))
and
=Count(IIf([Campus]="Williams",1,Null))

I look for null value because the Williams campus is a recent addition and there exists records where campus is null. I could have also summed the results as with the following expression:

=Sum(IIf([Campus]="Pecos" Or IsNull([Campus]),1,0))

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>

Displaying the first day of the week in CoolSigns content

The following javascript can be used to display the first day of the current week in content created for CoolSigns.

var d = new Date();
var day = d.getDay();
var diff = d.getDate() - day + (day == 0 ? -6:1);
var monday = new Date(d.setDate(diff));

var month = monday.getMonth() == 0 ? "January":
monday.getMonth() == 1 ? "February":
monday.getMonth() == 2 ? "March":
monday.getMonth() == 3 ? "April":
monday.getMonth() == 4 ? "May":
monday.getMonth() == 5 ? "June":
monday.getMonth() == 6 ? "July":
monday.getMonth() == 7 ? "August":
monday.getMonth() == 8 ? "September":
monday.getMonth() == 9 ? "October":
monday.getMonth() == 10 ? "November":"December";

return month + " " + monday.getDate() + ", " + monday.getYear();

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

Using PowerShell to find the GUID of an SPField instance

In SharePoint, I occasionally need to find the GUID of a field in a custom list. The following PowerShell will spit out the field's GUID:


$web=get-spweb https://mysite.com/mysite/
$list=$web.lists["My List Display Name"]
$list.fields["MyFieldName"].id