Irregular periods in the columns of a Crosstab

[geo40]

I have just heard from your site, and this is exactly what I'm looking for because CR isnot the most easiest application.

I've the following issue which I want to put in a crosstab ( if possible ):

Each month we report our projectresults/ -turnover to our parentcompany. Our ( monthly ) financial periods don't run parallel with a normal month. For example the first halfyear:

Period 1 is from January 1 till January 28 ( 4 weeks ); Period 2 is from January 29 till February 24 ( 4 weeks );
Period 3 is from February 25 till March 31 ( 5 weeks ).

I have the following lay-out in mind ( columns ):

(1)Projectnr_ (2)Projectname (3)Accumulated results
X XX 100
Y YY 250

previous periods (4)Period 1 (5)Period 2 (6)Period 3
10 5 7
30 15 20

(7) Accumulated results till period 3
122
315

When I input by means of a parameterfield the last date of the financial period ( in this issue March 31 ), the report should generate the above layout, taking account of the financial periods as we use them in our company.

A month later( Period 4, April 1 till April 28, 4 weeks ), the report have to show the following :

(1)Projectnr_ (2)Projectname (3)Accumulated results
X XX 110
Y YY 280

previous periods (4)Period 2 (5)Period 3 (6)Period 4
5 7 10
15 20 10

(7) Accumulated results till period 4
132
325

Can anyone give me a hand to solve this issue ? If there is anything not clear in this issue please tell me .

Regards, Geo40.

 

[dgillz]

I would use a manual cross tab to do this.

Columns 1 and 2 are database field, while columns 3 thru 7 are formulas.

Columns 3 thru 6 would be a formula like this:
If {datefield} in {BegDate} to {EndDate} then {databasefield} else 0.

While column 7 would just add 3 thru 6 togther. Then subtotal this by ProjectNumber, and hide the details to emulate a cross tabs's behavior. You can evan make the column heading be formulas themselves which would be dynamic based on Date Range Paramters that youi supply the report at runtime.

Let me know if you have any questions.
Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[geo40]

Your formula is clear to me however I don't understand the formula for column (3) ( maybe you can clarify ? ).

I tried to solve this with the formula :

If {datefield} <= cdate (dateadd(&quot;d&quot;,-1,{?BegDate} then &quot;Results previous periods&quot;
else
If {datefield} in #01-01-02# to #27-01-02# then &quot;Period 1&quot;
else
If {datefield} in #28-01-02# to #24-02-02# then &quot;Period 2&quot;
else
If {datefield} in #25-02-02# to #31-03-02# then &quot;Period 3&quot;

But this option doesn't work because the column &quot;Previous results&quot; remains empty. What is wrong with my formula ?

Next to it I havt to say that when I refresh my report I've to enter two parameters: {?BegDate} and {?EndDate} ( in this case Period 1 for {?BegDate} and Period 3 for {?EndDate}.

Regards, GEO40