Report to compare current year with last year based off parameters 1

[dodge20]

CR XI

I have a report that has input parameters for a start and end date which is working fine. On this report I also need to pull in last years data for the same date range. For example if I put in start and end dates of 2/3/2008 - 2/9/2008 I need the report to show the data for for 2/4/2007 - 2/10/2007. This report can be run weekly, monthly, or yearly. Do I make a subreport for this? And how do I make sure that it pulls the same date range, whether it is Sunday - Saturday or monthly or yearly.

Thanks




Dodge20

 

[highsmith]

What is your data source and how are you getting at it?
With a command statement?

I always write SQL commands to a SQL server DB and I would just pull all the data from both years with 1 query.
For SQL server guys, this site has a wonderful function for doing date math:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

I use it for everything.

cheers
--
See and search my pics at willhighsmith.com

 

[dodge20]

It is a SQL Server DB. I just used the database expert and the selection formula to grab the data. I have never done a command statement in CR, but I will check it out. Is this the best approach to take? Would a subreport where they input last year's dates work?

Dodge20

 

[highsmith]

Yeah, you could do it with a subreport, but I generally use subreports for different data sets.

What is your selection formula?

You can probably do it all there with the addition of 1 parameter to specify the number of years worth of data to pull.

cheers
--
See and search my pics at willhighsmith.com

 

[dodge20]

Here is the select formula

{RtChargeEntryElem.CategorySubCode} = 1 and
{RtChargeEntryElem.PublishedFlag} = true and
{AoAdType.Name} = {?Ad Type} and
{RtChargeEntryElem.EffectiveDate} >= {?Run Date} and
{RtChargeEntryElem.EffectiveDate} <= {?EndDate} and
{AoOrderCustomers.OrderedBy} = true and
{AoProducts.Name} = {?Product} and
not(
{AoOrderCustomers.CustomerTypeId} <> 3 and
{AoPlacements.Name} = "Co-op Ads"
)

Dodge20

 

[dodge20]

Any other thoughts on this? I think i need to create a sub report to make this happen. The sub report will be identical, but I only want the information in group footer4 to show. If I suppress everything in the subreport except group footer4 where do I place the subreport so the data matches with the master report?

Dodge20

 

[kskid]

You can use dateserial to pick up last year data

{RtChargeEntryElem.EffectiveDate} >= dateserial(year({?Run Date}-1,month({?RunDate}),day({?RunDate})) and
{RtChargeEntryElem.EffectiveDate} <= dateserial(year({?EndDate})-1,month({?EndDate)},day({?EndDate}))

 

[dodge20]

So this is what I put in the subreport? I want to keep this year's and last years data separate, but on the same report.

Dodge20

 

[kskid]

Just insert a subreport and just select your main report and probably insert it into the report footer. Then pass the parameters as subreport links.

Edit the subreport record selection criteria with the above dateserial and that should do it.

-lw

 

[dodge20]

Thanks for your help

Dodge20