We run Reports based on 2 Fiscal Years.
There is a Date Prompt for ?p_EndDate? which helps calculate
the Current [Fiscal Year] Start Date, plus
the Previous [Fiscal Year] Start Date, and
the Previous [Fiscal Year] End Date
Using 9/30/2018 (which was the End Date for [Fiscal Year] 2018] as a Test Date
as an example we have a Data Item ...
G_CFY_StartDate, which has an Expression Definition of ...
Case
When (extract(year,?p_EndDate?) = [Fiscal Year]) Then
_Make_timestamp (extract (year, _add_years(?p_EndDate?, -1)) ,10,1)
End
That results in ...
10/1/2017 12:00:00 AM
Our Data Sources include dates as
10/1/2017 as [Month Begin Date]
For the Query to work, it requires a "Date"
such as 10/1/2017
not a Timestamp like 10/1/2017 12:00:00 AM
Although hard coding is not, IMHO, good for a programmer,
I am going to test hard coded Dates to ensure the Query works.
How does one calculate a "True Date", such as 10/1/2017 from ...
Case
When (extract(year,?p_EndDate?) = [Fiscal Year]) Then
_Make_timestamp (extract (year, _add_years(?p_EndDate?, -1)) ,10,1)
End
TIA, Bob
Did you understand what the Case statement is meant to do? The answer is right there..
It's like saying ' I am bald and hence I wear a wig '. Can somebody tell me how can I look bald ?
Thank you. Bald or not, the Mission is to make a True Date.
Google searches found similar discussions w/ hitting a brick wall.
I am unaware of a _make_datestamp, or a Visual Basic-type
command to Format a _make_timestamp as a Date.
There has to be a way to do this?
You said that your data source has a [Month Begin Date] which is a Date and that's exactly what you want. So you already have it !
What you are trying to do is reverse engineering.
There is also a 'cast' function available in Report Studio which you can use with your date parameter..
Report Studio online documentation is better than google search !
Thank you.
True ---> You said that your data source has a [Month Begin Date] which is a Date and that's exactly what you want. So you already have it !
We are trying to work w/ a Standalone Calculation in our 3rd Party App. The Bolded hard code date below is needed to determine which [Month Begin Date]s we need, and similar for [Month End Date].
I hope I haven't done communicated poorly, which is possible.
A typical Data Item is ...
Case
When (([Q_TPRR].[Month Begin Date] >= Cast('2017-10-1',Date) and [Q_TPRR].[Month End Date] <= ?p_EndDate?) and [BusinessGroup] = 'AAA') Then [Q_TPRR].[Cases]
End
This is hard coded now until we can calculate a True Date based on a Date Prompt selection.
This ties in w/ the Table and Singletons Topic (I authored) and are trying. That WILL work as long as we can use "trusted" Dates (be they Case When dates, or hard coded).
Your thoughts please. TIA, Bob