Crystal Reports 8.5 grouping

[nicolaeman]

In the field that I want to use for grouping I have this values:
01
02
05
Values 03 and 04 are missing (not in the data); however, I would like them to be included in the grouping.

How can I do this?

Thank you!

 

[CrystalizeCanada]

Hi Nicole,

Create a Group Header a and place the formaula below in it. Choose Suppress if Blank so it will only show when there is a missing group. I tend to use a grey font on this so its visually clear.

We can add to this formaula incase the first and last groups are missing however get this working first.

//PURPOSE OF FORMULA: To determine if there are missing groups
//VARIABLE DECLARTIONS
Local NumberVar c:= {YourField};
Local NumberVar p:= previous({YourField});
Local NumberVar d:= c-p-1;//d stands for difference and the -1 helps make the select case easier
//FORMULA RESULT:
Select x
Case 1: //When one group is missing
c-1
Case is >1: //When two or more groups are missing
c-d & " to " & c-1
Default:
""

Gordon
Crystalize

 

[nicolaeman]

Hi Gordon,
in the previous post I came up with a simple example. I actually have to deal with "the number of week in the year" where the number of week is 1 to 52 and the year is 2009 (I have however a few records where the year is 1980 or 1950 because the data in the database is not very clean).

The above will complicate things big time I assume, right?

Thank you.

 

[nicolaeman]

Forgot to tell you: I need all of week number (1 to 52) in the report; I will make a chart based on this.

Thank you

 

[CrystalizeCanada]

Hi Nicola,

It doesn't complicated it hugely.

How are your grouping the report? If you want to group by week you could use the function datepart - it can extract the week number.

So create a formula called @weekly :
datepart('ww', {your date})
This will give you the week number of your date. Then you can group on this formula.

The first part of the formula in the previous posting would change

numbervar c:= {@weekly};
numbervar p:=previous((@weekly});

With regard to the years 1950 and 1980 these can be dealt with. They could be filtered out. It depends on how you want to treat them

Gordon
Crystalize

 

[nicolaeman]

So:
numbervar c:= {@weekly};
numbervar p:=previous((@weekly});

What about the rest of the formula? please remember that I don't have records for all of the weeks.

Thank you.

 

[CrystalizeCanada]

Hi Nicola,

The rest of the formula remains the same. I added a totext bit that I had left out so the final formula is below:

/PURPOSE OF FORMULA: To determine if there are missing groups
//VARIABLE DECLARTIONS
local numbervar c:= {@weekly};
local numbervar p:=previous((@weekly});
Local NumberVar d:= c-p-1;//d stands for difference and the -1 helps make the select case easier
//FORMULA RESULT:
Select x
Case 1: //When one group is missing
totext(c-1)
Case is >1: //When two or more groups are missing
c-d & " to " & c-1
Default:
""

Gordon BOCP
Crystalize

 

[nicolaeman]

In "select x" ... what's x?

 

[CrystalizeCanada]

well spotted

it should be d



Gordon BOCP
Crystalize

 

[nicolaeman]

I created a formula @FINAL:
local numbervar c:= {@weekly};
local numbervar p:=previous({@weekly});
local numbervar d:= c-p-1;

select d
case 1:
totext(c-1)
case is > 1:
c-d & " to " & c-1
default:
""

I have 2 groupings now; the second one is the one that returns some of the week numbers but not all.

Where do I create the new Group Header? after the second grouping? I tried to create it and placed @FINAL in it and it doesn't work. What's wrong?

Thank you.

 

[CrystalizeCanada]

You need to split the second group (the group by Weeks)into two sections. Right click the group header and choose insert section below. Move this section so the blank section is the a not the b.

Then put your @final formula here in the section a. Next goto the Section expert and make sure the a section has suppress blank section selected.

This should give you what you want

Gordon BOCP
Crystalize

 

[nicolaeman]

I will review tomorrow everything. I understand now the idea. If what you told me doesn't work I will manually create 52 headers and I will make them visible or not based on the value in @weeks.

Other then this I don't have any other ideea.

Thank you so much for your help!

 

[CrystalizeCanada]

If its confusing we can trouble-shoot however as lbass mentioned this need to be done through the forum so others can benefit from the discussion.

Are you getting an error when you place the @final formula. Is it always blank?



Gordon BOCP
Crystalize

 

[nicolaeman]

The formula is always blank.

There is something that I don't understand: the formula that you built will not create new values for missing weeks (weeks that are not in the database for the specified select criteria). The formula will create new headers in the grouping but that's all. Do I miss something?

Thank you.

 

[nicolaeman]

The formula works now and there are additional headers appearing but as I said in the previous post I don't see new values for missing weeks (weeks that are not in the database for the specified select criteria).

Any ideas?

Thank you

 

[CrystalizeCanada]

Hi Nicola,

Could you paste your @weekly formula and @final (if it is different from previous posting.

tx

Gordon BOCP
Crystalize

 

[nicolaeman]

@weekly formula:
----------------------
if year({@Jeff_Date}) < 2009 then
0
else
datepart('ww',{@Jeff_Date});
--------------------------


@final formula:
-----------------------
local numbervar c:= {@weekly};
local numbervar p:=previous({@weekly});
local numbervar d:= c-p-1;

select d
case 1:
totext(c-1)
case is > 1:
c-d & " to " & c-1
default:
""
---------------------------

 

[CrystalizeCanada]

The formuals are good. I literally copied and pasted them into a report and changed @jeffDate to a date field. The formula result was a blank when there was no missing week and the missing week number if there was a missing week.

My report was grouped by @weekly (NOT by @jeffDate - thats important) and I placed the @final into my groupheader.

Check that you are grouping by the right field (@weekly).
Obviously you have to have missing weeks to see any results so filter out @weekly is not one of say 2, 5, 6,7 so you can see results on the first page.

Gordon BOCP
Crystalize

 

[nicolaeman]

I made the modifications based on the above. I can see now a header "1" (didn't have a "1" before ...) etc.

There is only one problem. I can see the header "1" but there is no additional grouping in the data = "1". Do you know what I mean?

My chart will be based on data not on the additional headers.

Do I miss something?

 

[CrystalizeCanada]

Hi Nicola,

It is working in the report but you can't chart it. Unfortunately, the missing columns will show in the report but not in a chart.

If this is the main purpose - to get the missing columns in a chart this technique won't work.

Post again - 'How to add missing week columns to a chart'.

Gordon BOCP
Crystalize