Guidance on Crosstab or DistinctCount 1

[dnayana]

Hi All!

I'm new to Crystal Reports. The version I am using is 9.2. I’ve tried creating a crosstab report which hasn’t worked so I’m completely stuck on how to go about developing this report.

Is a crosstab applicable to this type of report or is there a better way to accomplish this?

Thank you in advance to anyone who can possibly provide assistance/guidance. (I’ve been racking my brain with this)




Summary: I need to create the sample report:
<=10 Days 11-30 Days 31-45 Days 46-60 Days +60 Days Total
System Group


The fields I am working within the table are:

Create_Date
System_Group
Task_ID
System

It is a parameterized report based on the following:
Start Date
End Date
System

The System has Sub-Systems within them (i.e. System_Group).

Each column is to provide the total number of Tasks for each Sub-System group within the specified number of days (i.e column headings). I’m using the DateDiff function to calculate the number of days. If no record exists for the specified system group within the respective column heading, then the column heading should still display with a 0 in that column.

For the number of days, I’m using the following:

if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) <=10) then
Count ({CHG_Task.Task_ID_},{CHG_Task.System_Group})
else
if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) >=11 and (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) <=30)) then
Count ({CHG_Task.Task_ID_},{CHG_Task.System_Group})
else
if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) >=31 and (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) <=45)) then
Count ({CHG_Task.Task_ID_},{CHG_Task.System_Group})
else
if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) >=46 and (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) <=60)) then
Count ({CHG_Task.Task_ID_},{CHG_Task.System_Group})
else
if (DateDiff ("d",{CHG_Task.Create_date},{?EndDate} ) >=61) then
Count ({CHG_Task.Task_ID_},{CHG_Task.System_Group})

 

[lbass]

If you want to show results (0) that don't exist, then you will have to use a manual crosstab. You should create formulas like this if you don't have row inflation:

//{@<=10}:
if DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) <=10 then 1

//{@11 to 30}:
if DateDiff ("d",{CHG_Task.Create_date},{?EndDate}) in 11 to 30 then 1
//{@etc.

Insert a group on system group. Place the formulas in the detail section and then right click on each and insert a sum (not a count) at the group level. Then drag the groupname into the group footer and suppress the details and group header sections.

If you have row inflation, then the formulas would need to be modified.

-LB

 

[dnayana]

Thanks LB!

I was able to analyze your concept.

I have just a couple of questions:

(1) What do you mean by "row inflation"? And if this should be true, how should I modify the formulas?

(2) I created another formula (i.e. TotalGroup) that sums the total for the row (within the Report Footer);

[color]
//@TotalGroup
Sum ({@10Days}) + Sum ({@30Days}) + Sum ({@45Days}) + Sum ({@60Days}) + Sum ({@60+Days})
[/color]

I'm having trouble summing the total for the individual group within the Group Footer.

I tried the following,

[color]
//@SumGroup
Sum ({@TotalGroup},{CHG_Task.Implementor_Group_})
[/color]

but I get the following error:
[color]
The field {@TotalGroup} cannot be summarized
[/color]

(3) Can you recommend any good Crystal Reports Books and/or websites for beginers? (Especially ones that have good examples that even the novice can follow.)

Thanks again for your assistance!

 

[dnayana]

LB --

After searching a little more on the forum, I incorporated the following and it works.


Whileprintingrecords;
numbervar x;

numbervar a:= Sum({@10Days}, {CHG_Task.Implementor_Group_});
numbervar b:= Sum({@30Days}, {CHG_Task.Implementor_Group_});
numbervar c:= Sum({@45Days}, {CHG_Task.Implementor_Group_});
numbervar d:= Sum({@60Days}, {CHG_Task.Implementor_Group_});
numbervar e:= Sum({@60+Days}, {CHG_Task.Implementor_Group_});

x:=a+b+c+d+e;


If you can still provide info re: "row inflation" and any helpful books/websites for a Crystal Report novice, I'd greatly appreciate it!

 

[lbass]

All you need for the group total is:

count({CHG_Task.Task_ID_},{CHG_Task.Implementor_Group})

I'm not sure what group this is, whether it is a higher order or lower order group, but you probably also have a group on System group, and for that group footer, the total would be:

count({CHG_Task.Task_ID_},{CHG_Task.System_Group})

If these formulas do not give you the correct result, then you probably have row inflation, where a taskID appears more than once, and your results are inflated. Let me know if that is the case.

-LB

 

[dnayana]

LB --

Oops .. sorry. Had a typo; Implementor_Group (old field name) is System_Group.

Actual code was:

numbervar a:= Sum({@10Days}, {CHG_Task.System_Group_});
etc.

No; the TaskID only appears once (it is the PK). So, in essence, by row inflation, you're referring to duplicate values of the source field??

BTW, any recommendations for good Crystal Reports Books and/or websites for beginers? (Especially ones that have good examples that even the novice can follow.)

 

[lbass]

Sometimes table joins cause multiple records with duplicate values, yes.

The George Peck books are good (Crystal Reports v.__: The Complete Reference). Ken Hamady has some good resources that he has developed (check out his website). Or just following the threads on Tek-Tips and making good use of the CR Help, which is quite good, should get you going.

-LB