Date Range by Month Only 1

[jestrada101]

Is there a way I can do a RANGE selection by Month only. I'd like the user to only have to input the Month/Year. My field however is Date/Time.

Thanks for any guidance.

JE

 

[vidru]

I'd use 2 parameters, {?Year} and {?MonthRange}.

{?Year}
Value type: Number
Options: Discrete values

{?MonthRange}
Value type: Number
Options: Range values
**Optional, but nice for users**
Hit 'Set default values' and enter the month values (1, 2, 3, etc.), then add the Description for each (January for 1, February for 2, etc.). Set 'Display' to Description, 'Order' to Numerical ascending, and 'Order based on' to Value.

Your record selection formula would look like:

(Year({table.DateTimeField}) = {?Year})
and
(Month({table.DateTimeField}) in {?MonthRange})

**Note - this may not pass through to the server. I can suggest a way around this, but it's database dependant.

-dave

 

[jestrada101]

Thanks! This works perfect. Is there a way to pass these parameters to a subreport?

 

[vidru]

Sure...

In the subreport, define the same two parameters. From the main report, right click on the subreport, click 'Change Subreport Links', drag {?Year} and {?MonthRange} into the 'Fields to Link to' list box, then use the 'Subreport parameter field to use:' dropdown to match each parameter to its equivalent in the subreport.

-dave

 

[jestrada101]

Thanks... I got it passing the parameter.. but I have something kind of weird.. my subreport has to show the "previous month" total.. so is there a way I can manipulate the range to look at the previous month... with the YEAR-MONTH they selected in the main report...

THANKS
JE

 

[vidru]

I'll assume that you're grouping by month (maybe I'm wrong, but stay with me... this should point you in the right direction regardless), and that your subreport is in the Group Footer.

Instead of sending {?Year} and {?MonthRange} to the subreport, create two formulas:

//@PrevMonth
if month({Table.DateTimeField}) = 1 then 
  12 
else
  month({Table.DateTimeField}) - 1

//@PrevYear
if month({Table.DateTimeField}) = 1 then 
  year({Table.DateTimeField}) - 1 
else
  year({Table.DateTimeField})

... where {Table.DateTimeField} is the field that you're grouping on.

Drag the two formulas into the group footer and suppress them (this might be unnecessary, but I can't test from here).

Change the links to the subreport to link @PrevMonth and @PrevYear in the main report to their respective matches in the subreport.

-dave

 

[jestrada101]

Do the formulas go in the MAIN or subreport? I can get the new dates to show in the sub-report, but the report will not query for the new month/year.

 

[vidru]

The @PrevMonth and @PrevYear formulas should be in the main report.

The links to the subreport should be:

Main             Subreport param field
---------        ---------------------
@PrevYear    ->         ?Year
@PrevMonth   ->         ?Month

-dave

 

[jestrada101]

Thanks!!! I got it going. Thanks for all the help.