Crystal Reports® Tips and Tricks: Creating a Green Bar Effect

Do you remember green bar paper? It was wide continuous feed paper meant for giant dot matrix printers used by accounting departments. The paper was printed with alternating white and light green bars. The purpose of the green bars was to make it easier to follow a single line of data across the printed sheet.

I personally prefer this method over adding grid lines to the report. In this post, I'll show you how to simulate that green bar effect in Crystal Reports®.

Here is a down and dirty report that I created from the AdventureWorks database. Very simple, it gives the last name, first name and phone number from the Contact table. As you can see it gets difficult to know which phone number belongs to which name.

 

What we need to do now is add our green bars. With the report in design mode within Crystal Reports®, right click and select section expert.

Select the details section.

Now select the color tab.

Select the formula editor button (x+2) to bring up the format formula editor and enter the following line of code:

If remainder(RecordNumber,4) in [2,3] then Color (228, 255, 223) else crNoColor

The formula divides the record number by four. If the remainder equals two or three, it prints a light green bar as a background color for the details section. Save and close, then preview the report.

That's more like it, much easier to read and follow. Hope you liked this tip. If I can answer any questions or you have comments, use the comments button below.

Til next time…

GW

Read more...

UNI/CARE Pro-Filer™ Reporting: Retrieving a Client's Age on the Day of Service from Their Date of Birth

One of the most common tasks handed to the Pro-Filer™ analyst is producing reports based on the age of the client at the time of service.  Since age is not a field within the database, (it really can't be as it is in continuous flux) this value must be calculated.  We could certainly add a formula to each of our reports or queries to do this, but let's write the code just once and then reference it when needed.  For this purpose we are going to add a user function in SQL to our reporting server.

Since we have the client's birth date stored in:

    Client.BDate

We can use a function to return their age by comparing to whatever date we are measuring against.  In this case let's use the recorded service start time which is stored in:

    RECORDED_SERVICE.STARTTIME

So we create our function with the code below:

begin sql----------------------------------------------->

create function dbo.fn_GetAge
(
      @pDateOfBirth datetime
    , @pAsOfDate datetime
)
returns int
as
begin

declare @vAge int

    if @pDateOfBirth >= @pAsOfDate
     
       return 0

    set @vAge = datediff(YY, @pDateOfBirth, @pAsOfDate)

    if month(@pDateOfBirth) > month(@pAsOfDate) or
      (month(@pDateOfBirth) = month(@pAsOfDate) and
       day(@pDateOfBirth)   > day(@pAsOfDate))

    set @vAge = @vAge - 1

return @vAge
end
go

<-------------------------------------------------end sql

DESCRIPTION
Our function as created accepts two inputs, the date of birth and the comparison date. 

The first if statement is just an error check to ensure that the date of birth is prior to our second date.  If not, it returns a 0.

The set statement starts the process of computing the age for us.  We can't just use a straight datediff function without the remaining if clause however.  Datediff simply returns the number of years between two dates.  So, if today is April 11, 2010 without checking for date, any birth date in 1990 would return an age of 20 even if the birth date has not yet passed for 2010.

USAGE
Let's create a list of clients in the system that were under age 5 at the time of service.  In this case we want the earliest age at which they received services:

begin sql----------------------------------------------->

select

      c.lname + ', ' + c.fname as ClientName

    , c.id as ClientID

    , min(dbo.fn_GetAge (c.bdate, rs.starttime)) as ClientAge

from

               recorded_service_helper rsh

      inner join client c on rsh.client_oid = c.oid

      inner join recorded_service rs on rsh.recorded_service_oid = rs.oid

where       dbo.fn_GetAge (c.bdate, rs.starttime) <= 5
        and rs.recorded_service_voided_moniker is null
        and rs.service_status_moniker is null

group by c.id,c.lname,c.fname

<-------------------------------------------------end sql

Hopefully this will help make your job just a wee-bit easier.

Til next time...

GW

Read more...

UNI/CARE Pro-Filer™ Reporting: The Enigma of the OIDs – What they are, what they do, how to create them and why there is no need to fear them.

It doesn't take long after you begin your Pro-Filer™ implementation that you become aware of the word "OID".  OID is an acronym for Object IDentifier.  In the Pro-Filer™ database OIDs are used as a means of linking data through primary and foreign key relationships.  On both the primary key and foreign key exists a matching OID.  This is how Pro-Filer™ knows that one record is related to another.  It is critical then that every OID within a system be unique.  The first column of every table in Pro-Filer™ is the OID column.  Every row in every table of a Pro-Filer™ database has its unique OID existing in that first column of the table.

An OID is an identifier standard that UNI/CARE uses within Pro-Filer™. The intent of using OIDs is to enable distributed systems to uniquely identify information without any central coordination. Thus, each UNI/CARE Pro-Filer™ installation can create an OID and use it to identify a record within the database with reasonable confidence that the identifier will never be unintentionally used by anyone for anything else. Therefore, information labeled with OIDs can be later combined into a single database without needing to resolve name conflicts.

An OID is a 128 bit number displayed as a 32 character hex-decimal:

    18F55E86905C413280551BE264FB8D0B

128-bits is big enough and the generation algorithm is unique enough that if 1,000,000,000 OIDs per second were generated for 1 year the probability of a duplicate would be only 50%. Or if every human on Earth generated 600,000,000 OIDs there would only be a 50% probability of a duplicate.

We can generate an OID within Microsoft SQL by using the following code:

     select newid()

Which will return the result:

    18F55E86-905C-4132-8055-1BE264FB8D0B

As you can see this is in the form of a standard UUID (Universally Unique Identifier) or GUID (Globally Unique Identifier) with the dashes in place.  UNI/CARE strips the dashes from the UUID format in Pro-Filer™ to create an OID.  The code to create an OID without the dashes is shown below:

     select cast(replace(newid(),'-','')as varchar(32))

Which will return the result:

    18F55E86905C413280551BE264FB8D0B

Now, when a user asks you "What the heck is an OID?"; you have the answer.

Til next time...

GW

Read more...

Crystal Reports® Tips and Tricks: Brian Bischof - Crystal Reports Subreports: Troubleshooting Shared Variables

Shared variables can be a real bear to troubleshoot.  Brian's article will give you a good head start in solving those seemingly inexplicable issues.  Brian is the author of a passle of  Crystal Reports® books.

Crystal Reports Subreports: Troubleshooting Shared Variables

GW

Read more...