Share
ReportHello All,
I am trying to help my IT group with getting a SSRS report to work properly. We are trying to get the functionality in the smartlist where the transaction date is between Beginning of Period and End Period to work in a SSRS report.
Using this code, stole from SQL queries for fiscal years and periods in Dynamics GP by VICTORIA YUDIN. I tried to see if this could be starting point, but our IT group hasn't had any luck.
SELECT D.PERIODID Period_Number, D.PERNAME Period_Name, D.PERIODDT Starting_Date, D.PERDENDT Ending_Date, D.YEAR1 Fiscal_Year FROM SY40100 D INNER JOIN SY40101 H ON H.YEAR1 = D.YEAR1 WHERE D.FORIGIN = 1 AND D.PERIODID <> 0 and GETDATE() between H.FSTFSCDY and H.LSTFSCDY ORDER BY D.PERIODID
This is the result.
| Period_Number | Starting_Date | Ending_Date | Fiscal_Year |
|---|---|---|---|
| Period 1 | 2020-04-01 | 2020-05-03 | 2021 |
| Period 2 | 2020-05-04 | 2020-05-31 | 2021 |
| Period 3 | 2020-06-01 | 2020-06-28 | 2021 |
| Period 4 | 2020-06-29 | 2020-08-02 | 2021 |
| Period 5 | 2020-08-03 | 2020-08-30 | 2021 |
| Period 6 | 2020-08-31 | 2020-09-27 | 2021 |
| Period 7 | 2020-09-28 | 2020-11-01 | 2021 |
| Period 8 | 2020-11-02 | 2020-11-29 | 2021 |
| Period 9 | 2020-11-30 | 2020-12-27 | 2021 |
| Period 10 | 2020-12-28 | 2021-01-31 | 2021 |
| Period 11 | 2021-02-01 | 2021-28-28 | 2021 |
| Period 12 | 2021-03-01 | 2021-03-31 | 2021 |
What I want to do is to get the start and ending date to be pulled into the parameter of the SSRS based on the server date. For example today is 2020-07-07. The Starting date would be 2020-06-29 and the ending date would be 2020-08-02.
I am a complete novice on SQL and my Google Fu hasn't found anything that has pointed me in the right direction.
Any help would be much appreciated and Thanks in advance.
All responses (
Answers (
150
