| Author |
Message |
SteveDLC
Newbie
Joined: 27 Jan 2016
Location: Australia
Online Status: Offline
Posts: 21
|
   Topic: Dynamic Date range in RF subreport
Posted: 14 Apr 2016 at 8:10pm |
|
Hi,
I'm working on a P&L report. It will be populated with subreports. Each subreport collects values based on account numbers. Very simple formula's like a+b=c which are placed into the report footer. The select expert is set to only look at the account numbers I need. At the moment I have a date range in the select expert as well which gives me the information I need no problem.
I'd like to place this date range into the formula but have it dynamically. So the information collected automatically calculates selected values for March 2016 (in this case).
Would appreciate help!
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Apr 2016 at 2:30am |
|
are you asking about parameters? You can create parameters in the main report and also pass the values the sub-reports if needed.
In the main report you can either create one date parameter set to allow a range or create two date parameters and use them in your select
example
Table.date in {?start} to {?end}
|
|
IP Logged |
|
SteveDLC
Newbie
Joined: 27 Jan 2016
Location: Australia
Online Status: Offline
Posts: 21
|
Posted: 18 Apr 2016 at 1:12pm |
|
Sorry, just looking at what I wrote doesn't make a lot of sense.
In a subreport and in the Select Expert I can enter a date range against a field.
In the Formula Workshop under Record Selection I can see this information.
(eg.) - {gltran.tran_date} in Date (2016, 03, 01) to Date (2016, 03, 31)
I'd like to alter this so the dates are dynamic. I know I can replace the '03' with something like Month(CurrentDate)-1 to get it to look in March. However this will be a problem in January since there is no month '00' (which is meant to be December).
I also need to change the '31' to pick up the last day of the month. I've tried a few things like Maximum(Day(Month(CurrentDate))) but having problems.
Hope this is more useful.
Steve.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Apr 2016 at 2:44am |
|
Do you mean you want the user to select a date range at run time or do you mean you want the report to dynamically adjust itself based on today (the date the report is run)?
|
|
IP Logged |
|
SteveDLC
Newbie
Joined: 27 Jan 2016
Location: Australia
Online Status: Offline
Posts: 21
|
Posted: 19 Apr 2016 at 1:05pm |
|
No user interaction.
I want the report to dynamically adjust itself.
At the moment it is reporting on July 1st, 2015 to March 31st, 2016. When May 1st comes around I want it to automatically adjust itself to July 1st, 2015 to April 30th, 2016.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 20 Apr 2016 at 5:56am |
|
I think what you are asking is how to make the report, at run-time, automatically include only FY records through the end of last month where the fiscal year start on 7-1.
There are a number of ways to do this.
Here is one way to get the FY dates
create a 'startdate' formula
date((if month(today) > 7 then year(today) else year(today)-1),7,1)
Create an 'enddate' formula
dateserial(year(today),month(today),1-1)
you can then use these in your select
table.date in {@startdate} to {@enddate}
|
|
IP Logged |
|
SteveDLC
Newbie
Joined: 27 Jan 2016
Location: Australia
Online Status: Offline
Posts: 21
|
Posted: 20 Apr 2016 at 5:13pm |
|
Thanks for this information.
I've tried a few different things but just couldn't nail the correct syntax with combining functions.
I'll try these out and play around a bit.
- Update -
Successfully got it to work and figured out some other solutions as well.
Your startdate formula though I think should be '> 6' otherwise it will make August the first month.
Anyhow, just wanted to say thanks !
Edited by SteveDLC - 20 Apr 2016 at 8:50pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Apr 2016 at 3:30am |
|
Note - I intentionally used ">7" on the startdate.
IF you use >6 then you will get 0 results for all of July. This means you will never see the full FY data and the report is essentially useless for one full month.IF you use 7 you will get the full prior FY data for a full month that would otherwise return no data.
If you would rather have July return something else you would need to decide what that is in order to determine how to write the formula.
|
|
IP Logged |
|
|
|
