Fiscal Year Column Reporting

[CTekGirl]

I am creating a report based on view of joined tables for a fiscal year report July03- June04; each column only needs to have data for a single month. (DATA is hours worked that month. The DATES are period end dates.
ie.
June-03 --- through June04
DATA DATA

What is the best method to do this, I tried using the Select Expert DATE =06/30/2003 or DATE IN or DATE BETWEEN (listing all dates)but it the results will only provide data for 1 MONTH - the rest of months are zeroes.

Or should I script some type of code for each column. I also tried defining the date in the column, but it would only report results for one month. Any suggestions would be appreciated.

 

[Madawc]

Try

totext(DatePart("yyyy", {your.date})) & "/" &
DatePart("m", {your.date})

to give you a year/month group. Split by that group, or maybe do a crosstab.

If you want to use the month name, try totext({your.date}, "MMMyy"). But this would sort alphabetically and isn't suitable for grouping.

Note that M for month display means month, while m means minute. But for DatePart, m means month and minutes are n. So it goes.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)

 

[dgillz]

The record selection formula should be

{YourDateField} in Date(2003,7,1) to Date(2004,6,30)

I never use the select expert personally, I just write the formula. What is your formula now?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[lbass]

Use dgillz' record selection formula and then insert a crosstab in the report header or footer. Use {table.date} as the column. In the crosstab expert, then click on the column field {table.date} and go to "group options" and choose "print on change of month". Use whatever field you want summarized as the summary field.

-LB

 

[CTekGirl]

Sorry I didn't include the version; 8.5

 

[CTekGirl]

I tried the crosstab and it did not give me exactly what I need. So perhaps I did not provide enough information. This report is based on 2 views and 1 table joined. In the report design there are 3 groups(rows) based on depts and sub-grps within the department and dept#. Reporting data only in the 3rd grp.

The report must maintain this format.

For example.
JUNE04 JULY04 AUGUST04 SEPT04
DEPT A
DEPT A1
A100 10 35 22 45
A115 42 45 34 45
DEPTA2
A200 10 35 22 45
A215 42 45 34 45

sub total dept A1
sub total dept A2
total DEPT A

The numbers represent the total hrs worked(THW) for each dept each month.

This is based on two formula fields.

JUNE04 formula:[ {tableK.PERENDDATE} = DateTime (2004, 06, 30, 00, 00, 00)[

JUNE04_THW formula:[ if {@june04} = true then
{tableK.HOURS}]
The formula field JUNE04_THW is used for the column. These formulas were cloned for each month.

Problem is I cannot get the report to give data for all the months at the same time. Variations of date ranges only give me the first column of data.

The crosstab solution puts a box at the end of this formatted report does not report all the months and is not in a format I can use. It also increases an 11 page report to 35+ pages.

Hopefully this additional information will be helpful to any that respond.

 

[Madawc]

You may need to do a 'mock crosstab', in which you define running totals for all twelve months in your range and display them. This could be done using runnning totals which test for month, using one of the methods described above. You'd probably need 36 distinct running totals, to get the different levels. Long-winded but I can't see an easier method.

In Crystal 8.5, you can cut-and-paste to a dummy Crystal report as a short-cut to making large numbers of very similar fields. Change the name on the dummy and then copy back.

Madawc Williams (East Anglia, UK)

 

[lbass]

Please try the crosstab one more time. You should place it in the Group #2 Header_b section. Insert the crosstab and use your subgroup field as the row (whatever field you use that results in A100, A115, etc.), and add{tableK.PERENDDATE} as the column field and click on it so that "Group options" becomes available. Click on "Group options" and choose "print on change of month". Then add sum of {tablek.HOURS} as the summary field. You can format the month names to display as you wish.

-LB

 

[CTekGirl]

I will attempt this again. I was looking for a method that wasn't so time consuming. It looks like I will not be able to use the report I created. I will try to build this from scratch. I will post if I have more questions.

 

[lbass]

An inserted crosstab is much simpler than the method you were initially trying, which was a manual crosstab. It should only take minutes to see if an inserted crosstab meets your needs. You could save your existing report under a different name, remove your date formulas from the detail section, and insert a crosstab in the group #2 header as noted above.

-LB