Comparing Data in specific time frames from two sources

[PDAnalyst]

Hi,

I am running CR 11 R2 on a windows XP Pro machine. I have two seperate excel worksheets (one in excel 2003 and other in 2007 format) that has the data for stolen vehicles for Year 2009 (full year and this is in excel 2003 format) and YTD 2010 (YTD values in excel 2007 format, being updated on a daily basis).
I am trying to create a report that will take YTD values for the last years excel sheet (1/1/09 to 7/21/09) and then compare it with this years (1/1/10 to 7/21/10) and then calculate the % change.
I have thought of doing two seperate subreports than bringing them together but this did not work. I am looking for any ideas.

Here is what I am trying to achieve:

January
Beat 2009 2010 Change%
A1 20 30 50
A2 15 15 0

February
Beat 2009 2010 Change%
A1 10 15 50
A2 20 15 -33

etc.

Thanks

Safa

 

[briangriffin]

Create two connections, one for each spreadsheet, and create one command object per connection:

select 
month(date) as month,
beat,
sum(amount) as 2010_Amount

from range

group by month(date), beat

In this example I've named the range of data in the spreadsheet 'Range'. Name the sum in the other connection '2009_Amount'

Then link the commands on the month field and you'll be able to drop the month, beat, 2009 amount, and 2010 amount into the details section. Then add groupings and formulas as needed.

 

[PDAnalyst]

Thanks,

Will try it and get back.

I appreciate it.