Date Comparison

[mgerard802]

I have a table with transactions over a range of time. I want the user to enter a date range, then have the report sum the data by employee, have 1 column for the date range entered, and 1 column for the date range minus 1 year. For example
Employee Amount Date
1 10 1/1/03
2 20 2/1/03
2 30 3/1/03
2 40 9/1/02
1 50 5/1/02

User enters date ragne of 1/1/03 to 12/31/03
Result:
Employee Current Prior
1 10 50
2 50 40
Any help would be appreciated.
Thanks

 

[nagornyi]

Select data by the date range, group by employee and find totals. So far can be done with report wizard. Suppress detail section. For the previous year, use supreport that receives employee and entered date range. Inside subreport, select and sum up data for the emplyee and data range minus 1 year (formula). Show the subreport total only. Place subreport in the main report group footer alongside with total for the current date range.

 

[lbass]

First, use the following as a record select:

{table.date} in dateadd("yyyy",-1, minimum({?date range})) to maximum({?date range})

Then insert a crosstab and add {table.employeeID} as the row and sum of {table.amount} as the summary. Then create a formula {@period}:

if {Orders.Order Date} in dateadd("yyyy",-1,minimum({?date range})) to minimum({?date range})-1 then "Prior" else
"Current"

Then insert {@period} as the column field. If you don't want to display the totals, you can go to customize style and check "suppress row grand totals" and "suppress column grand totals." You can also eliminate the grid by selecting "format grid lines"->uncheck "show grid lines."

That should give you the display you want. The crosstab should be placed in the report header or report footer.

-LB