| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 22 Apr 2014 at 3:20am |
|
if you just need the prior two years why are you using a parameter?
your select would just use the currentdate to determine the prior years as your select statement. Then you can group on the month part then the year part as indicated earlier.
datepart('yyyy',{GL_DetailPosting.PostingDate}) in [datepart('yyyy',currentdate)-1,datepart('yyyy',currentdate)-2]
|
 IP Logged |
|
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
Posted: 23 Apr 2014 at 1:49am |
|
Here's the issue. I don't know what 2 years they will want to run this report. So it using current date really does not work for me. They might want to run it right after each other for different years. (2013 and 2014) then (2013 and 2012). They also might run it in January 2015 for the years 2014 and 2013.
I wish I could use current date - but then want a date parameter to use...
Is there a way I can do this and still get it to summarize?
Edited by chudok - 23 Apr 2014 at 2:07am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Apr 2014 at 4:17am |
Sorry to confuse the matter. I misread the posting.
so your user can enter 2 dates (1 paramter set to a range).They are supposed to enter 2 full years?
What do you want it to do if it is not 2 full years?
If it is less?
or more?
or say they enter "Feb 10 2010" to "Feb 10 2012" which has 3 actual year values in it? How do you want to compare these?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 23 Apr 2014 at 5:08am |
|
why not just limit them to 1 date to enter...the begin or the end date, and have the report calculate the other date?
just a thought
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Apr 2014 at 5:20am |
I would agree with Lockwelle, but the answer to my questions about what happens when users enter other ranges may exclude that as an option. Also if you are looking at full calendar or fiscal years I would even consider using a single numeric param for a year or a preset drop down for fiscal year selection.
That said, the ability to group on and display summarizations based on the param values should be easy enough. However to do it properly you will need to decide on these possible parameter entry issues and how the report should handle them.
|
|
IP Logged |
|
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
Posted: 23 Apr 2014 at 5:43am |
|
I understand the concern. I decided to use a different reporting platform that I have instead. This has become more complicated that I needed. Also if someone wants to look at current year normally, but say Jan 5th, they want to see end of prior year data they can't because of the the formulas.
thanks for all the help
|
|
IP Logged |
|
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
Posted: 24 Apr 2014 at 7:41am |
|
I do have a quick question. I've tried to research this but I cannot find the answer.
What does the 01 do in the formula below?
Date(year(minimum({?ReportDate})),Month(maximum({?ReportDate})),01)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Apr 2014 at 7:43am |
uses the first day of the month
DATE(yearvalue,monthvalue,dayvalue)
|
|
IP Logged |
|
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
Posted: 24 Apr 2014 at 7:59am |
|
so if I have this formula.....
if {ARN_InvHistoryHeader.InvoiceDate}>=minimum({?ReportDate}) and
{ARN_InvHistoryHeader.InvoiceDate}<=Date(year(maximum({?ReportDate}))-1,
Month(maximum({?ReportDate})),day(maximum({?ReportDate})))
and my data parameters are 01/01/2013 and 04/22/2014..... shouldn't my results be dates between 01/01/2013 and 04/22/13
I am trying to get Prior Year to date total up to the maximum date (minus 1 year) selected
Edited by chudok - 24 Apr 2014 at 8:00am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 Apr 2014 at 8:14am |
so you have one parameter (ReportDate) as a date type that allows for a range to be entered.
For you example, your range in that one parameter is using 01/01/2013 to 04/22/2014
your if formula is evaluating to TRUE if your invoice date is between
01/01/2013 and 04/22/2013
an easier way to do it is
{ARN_InvHistoryHeader.InvoiceDate} in minimum({?ReportDate}) to dateadd('yyyy',-1,maximum({?ReportDate}))
|
|
IP Logged |
|
|
|
