Re: weekly reports 1

[ecsrs]

I am trying to run a yearly report (manual crosstab) that is broken down into weeks. I have to parameters "begdate" and "enddate". The report will start with the begdate and run each week until the enddate. It is summing unit cost. Basically I want it to look like this:

Week Cost
04/05/04 - 04/11/04 96.24
04/12/04 - 04/18/04 192.84
04/19/04 - 04/25/04 144.36
etc.

I just can't seem to get it to work. I used datediff to figure out the total number of weeks and dateadd to get each weekly period. The table I am pulling from has a date associated with each cost (servicedate). I am not sure how the begdate, enddate and service date all fit in the formulas.

 

[lbass]

Your example is not really a manual crosstab. To get the display you show, you would use a record selection formula of:

{table.servicedate} in {?begdate} to {?enddate}

Then group on {table.servicedate} and choose "customize group name"->use a formula as group name->x+2 and enter:

totext({table.servicedate}-dayofweek({table.servicedate})+1,"MM/dd/yyyy")+" - "+
totext({table.servicedate}-dayofweek({table.servicedate})+7,"MM/dd/yyyy")

Insert a summary (sum) on {table.cost} to get the group subtotal, drag it to the group header, and then suppress the details.

This solution would not show every week of the period if there were no service dates during that week, however.

-LB

 

[ecsrs]

I am sorry I should have been more specific. The reason I said manual cross tab is because there are different services.

Week Service 1 Service 2 Service 3
4/05-04/11 cost cost cost
04/12-04/18 cost cost cost

I just figured if you told me how to do one I could figure out the other ones. Does this change anything? I also plan to do YTD totals in the last two columns for each service.

 

[lbass]

What does this mean?

"I also plan to do YTD totals in the last two columns for each service."

Are you planning to insert columns between services? The YTD column might be one, but what is the other? I think I would just use a running total for the YTD column using the running total expert: Select {table.cost}, sum, evaluate based on a formula:

{table.service} = "Service 1"

Reset never. Place this in the group footer for the date (week) group. Repeat for each service.

As far as the service columns go, for each column you would need to create a formula like:

//{@svc1}:
if {table.service} = "Service 1" then {table.cost}

//{@svc2}:
if {table.service} = "Service 2" then {table.cost}

Then you would insert summaries on these formulas and suppress the detail sections. The summaries would appear for each weekly group.

-LB

 

[ecsrs]

I hate to be pain but maybe I am not explaining myself clearly. The report works but I need it to group by week instead of by day. It looks like this:

04/04/2004 - 04/10/2004 (representing 04/04)
04/04/2004 - 04/10/2004 (representing 04/05)
04/04/2004 - 04/10/2004 (representing 04/06)
04/04/2004 - 04/10/2004 (representing 04/07)
etc.

I really appreciate your help...I am working overtime to get this done and don't have much time left.
Thanks again!!

 

[lbass]

Sorry, I meant to add that when you group on {table.servicedate}, choose the option "the section will be printed for each week". To change your setting, go to report->change group expert->servicedate group->options and make the change to weekly.

-LB