crystal reports viewers, crystal reports schedulers, view crystal reports, report analyzers, burst reporting, report scheduler
 
view crystal reports, rpt viewer, crystal reports viewers, crystal reports schedulers, report analyzers, burst reporting, report scheduler
desktop viewer, crystal reports viewers, crystal reports schedulers, report analyzers, burst reporting, report scheduler

Crystal Reports Tools: Improve Performance While Saving Time and Money
  Resources  
Best sellers:
cView
Report Analyzer
cViewSERVER
 ReCrystallize
 


Articles:
 Administration
Advanced
Basic
 Crystal eNL
Database
Financial
Problems Solved

 Books:
CR Books
Database Books
Developer Books
 
Tools:
Analyzers
Bestsellers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs
ReCrystallizePro


Add'l:
About us
Contact Us
cViewSUITE Ppt
Support
 

CrystalReports
on Steroids

Crystal Reports:
Missing the last day’s data from your record selection

Often you need to select a range of records from the report using a date range, so you would use a record selection of:

{Table.DateField} >= Date(2001,6,1) and {Table.DateField} <= Date(2001,6,15)

So you look at your Crystal Report and discover there are no data in your report for the 15th of June, but you are sure there are data for that day in your database.

Why does Crystal Reports exclude those data from the report? The problem occurs when you have a time component in your date field. The data field really stores 15-June 8:16am. The selection formula above does not specify a time component, so it is assumed to be at 0:00 (midnight), and anything that happens later that day is after midnight--and thus is excluded from your record selection.

So the formula should really be:

{Table.DateField} >= Date(2001,6,1) and {Table.DateField} <= DateTime(2001,6,15,23,59,59)

If you are using a start date and end date parameter to you could also use:

{Table.DateField} >= {?StartDate} and {Table.DateField} <= DateTime({?StartDate},Time(23,59,59))

 

But what if you're missing the month?

Crystal displays data that are present. Unless you have data for all months, some months won't display--unless you manufacture the missing months. The first formula you need will decide if there are missing months:

Missing Month: This formula checks to see if the next month Order Date is sequential. We must also check that we are still on the same customer.

if month({Orders.Order Date})+1 <> month(next({Orders.Order Date}))

and {Customer.Customer ID}= next({Customer.Customer ID}) then

{@MonthList} //invoke the formula below

This formula is placed in a Group Footer Section by itself, and the section is formatted "Suppress Blank Section"

More, below....

 

 
 

Month List: This formula builds up a list of the names of the months:

local numbervar y; //Counter for year loop

local numbervar m; //Counter for month loop

local numbervar m1; // 1st month for the month loop

local numbervar m2; // final month for the month loop

local stringvar s:=""; // Result string to print out at the end

for y:= year({Orders.Order Date}) to year(next({Orders.Order Date})) do ( //for every missing year

if y= year({Orders.Order Date}) then m1:=month({Orders.Order Date})+1 else m1:=1;

if y= year(next({Orders.Order Date})) then m2:=month(next({Orders.Order Date}))-1 else m2:=12;

if m1<=12 and m2>=1 then // Cannot be before January or after December

for m:=m1 to m2 do (

s:=s + totext(date(y,m,1),"MMM-yyyy")+ chr(10) + chr(13)); //build the string

); s //print the result out

 

Sorry, this method is available only for CR version 8 users because it has For loops. Good luck.

 

How easy it would be to modify the formula above for missing days or missing weeks?

The missing days formula is a lot easier to write as you are just looking for successive days.

Missing Day: The first formula you need is to decide if days are missing:

if {Orders.Order Date}+1 <> next({Orders.Order Date}) and

{Customer.Customer ID}= next({Customer.Customer ID}) then

{@Day List}

 

This formula is placed in a Group Footer Section by itself, and the section is formatted "Suppress Blank Section"

 

Day List: This formula builds up a list of the names of the months:

local numbervar d; //Number of missing days

local numbervar i; //Counter for loop

local stringvar s:=""; //String to print out the result

d:= next({Orders.Order Date})-

{Orders.Order Date}-1; //Calculate the number of missing days

for i:= 1 to d do (

s:=s + totext({Orders.Order Date}+ i,"dd-MMM-yyyy")+ chr(10) + chr(13));

s

 

Missing weeks is a little bit more complicated. You first need a formula to calculate the start of each week:

Start of Week:

{Orders.Order Date}-DayOfWeek ({Orders.Order Date})+1

Then your Missing Weeks formula is as follows:

if {@Start of Week}+7 <> next({@Start of Week}) and

{Customer.Customer ID}= next({Customer.Customer ID}) then

{@Week List}

And the list of missing weeks is: Week List

local numbervar w; //Number of missing weeks

local numbervar i; //Counter for loop

local stringvar s:=""; //String to print out the result

w:= (next({@Start of Week})-

{@Start of Week})/7-1; //Calculate the number of missing weeks

for i:= 1 to w do (

s:=s + totext({@Start of Week}+ (i*7),

"dd-MMM-yyyy")+ chr(10) + chr(13));

s

 

 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.