Grouping Dates

[Dodecahedron]

Hi,

I have developed a report where the user enters a start and end date.

The report is then grouped by month and week.

By week takes the first Sunday of the week i.e. if the user enters 1st Jan to 14 Feb 2007 the weeks would be
Jan
07/01
14/01
21/01
28/01


Feb
04/02
11/02


I now want to display the week labels across instead of down ie.

for Jan

07/01 14/01 21/01 28/01


Feb

04/02 11/02


I cannot use a crosstab as there are additonal columns after the week headings.

Thanks

 

[lbass]

Addidtional columns do not necessarily rule out use of a crosstab. What version of CR are you using? If you want an opinion on whether the additional columns could be added with an inserted crosstab, please explain what they are. Otherwise you will need to insert a group on month, and then create separate formulas for each possible week within a month (up to six formulas) for whatever it is you are using as a summmary field.

-LB

 

[Dodecahedron]

I am using CR10, the problem is I am also using shared variables to add tot he totals for each week as well

 

[lbass]

So you have subreports that are contributing values? Can't really help without more detailed information.

-LB

 

[synapsevampire]

Try posting example data and the expected output.

You show us the weeks, but no summaries, and no indication of what the data looks likes that makes up the summaries, or why you are using subreports (this is assumed because you posted that you're using shared variables, those are only needed for subreports).

-k

 

[Dodecahedron]

Apologies for being vague

The report runs of two date parameters start and end, the user can enter any two dates but they will be in the same year i.e. 01/01/2006 to 31/12/2006.

The first part of the report consists of 3 subreports which I have placed in the report header. The measure data for months of the year as below.

Jan-06 Feb-06 Mar Apr May to Dec Forecast
No of calls 10 4 3 2 4 8

There are about 10 more rows of different stats.

The formula for the month name 'Jan-' & right (totext (year({?start}),0,""),2) - obviously repeated for each month

To work out number of calls - if month({datetimefield}) = 1 then 1 else 0 - repeated for each month - then use sum to total them up. Repeated for all the calculations on the report.

The other 2 subreports follow the same pattern, but bringing in data from 2 different servers to the report. I then have calculations on the main report thet used shared variables from all the subreport to do further calculations.

The next part of the report I want the same information as above broke down into weeks for that month

i.e. so instead of 'Jan-' & right (totext (year({?start}),0,""),2) for month name - I need a formula that will give me the week heading for Jan i.e.

01/01/06 08/01/06 15/01/06 22/01/06 29/01/06

This will be repeated for a the each month that the user chooses with the date parameters.

I would then need a formula for
if month({datetimefield}) = 1 then 1 else 0

i.e. for Jan I would need something like

if date >= 01/01/06 and <=08/01/06 then 1 else 0

repeated for each week selected

Cheers

 

[lbass]

If you are grouping on month, then you could use formulas like the following for your detail section:

//First create a {@weekno} formula:
datepart("ww",{table.date})-datepart("ww",{table.date}-day({tabledate})+1)+1

Then reference it in detail formulas like:

//{@1stweekJan}:
if month({table.date}) = 1 and
{@weekno} = 1 then 1

//{@2ndweekJan}:
if month({table.date}) = 1 and
{@weekno} = 2 then 1

//etc.

Then for labels, you could use formulas like:

//{@Jan1stwklabel}:
if month({table.date}) = 1 and
{@weekno} = 1 then
{table.date}-dayofweek({table.date})+1

Insert a maximum on the label formulas at the month group level and then drag the result into an insert GH1b section to be used for the labels.

-LB

 

[Dodecahedron]

Thanks, that worked, perfectly cheers