Dates in Fiscal Year in Report Studio (Relational)

[gosoccer]

Hi folks,

I have to get a prompt to the user to enter a fiscal year and retrieve the data from the DB based on the fiscal year for the last year. So if they select 2017, the query needs to go return the data based on our date field from last Oct 2016 to current date.

The report is just need to retrieve the data based on the Fiscal Year. The Year in our database is based on Calendar Year.

Any example is greatly appreciate it.  8) 8)

[Stepharia]

I would create a data items that extract the month number ([Month]) and year ([Year]) then use those in a case statement to create a fiscal year data item.
case
when ([Month] between 10 and 12)
then ([Year] -1)
else ([Year])
end

[gosoccer]

That's interesting. So, if I may understand your logic Stepharia, since 10,11,12 falls into previous year, you are simply subtracting one from the present year. So, the Year and Month is Extracted from the actual Fact table Date Field that
exists for every row and the Data Item is performing the filtration in this case.

Is my assumptions correct? Sorry, it's midnight over here.

Thanks a lot!

[Stepharia]

Yes, that's correct gosoccer. Creating these data items should end up giving you a fiscal year for each line that you can then filter by.
Glad I could help.