Skip to Content
0
Dec 13, 2022 at 03:28 PM

Using ReportName() in date

119 Views

Hello, I am wanting to make a dynamic variable but seem to come off short.

In my report I have them named after the months of the year (January, February etc)

By doing =ReportName() I do get the Month as expected.

Doing =ToDate("01/" + ReportName() + "/2022"; "dd/mm/yyyy") gives me the result 01/01/2022 (All good so far!)

So I made these two Variables to get the first day and last day of the month

[PromptFrom]=ToDate("01/" + ReportName() + "/2022"; "dd/mm/yyyy") (Result: 01/01/2022)

[PromptTo]=LastDayOfMonth(ToDate("01/" + ReportName() + "/2022"; "dd/mm/yyyy")) (Result 31/01/2022)

All Good So far Until this one...

[Status]=If [Leaving Date:People] < [PromptFrom] Then "Already Gone"

ElseIf [Leaving Date:People] Between([PromptFrom];[PromptTo]) Then "Leaver"

ElseIf [Join Date Merge] Between([PromptFrom];[PromptTo]) Then "Joiner"

ElseIf [Join Date Merge] <= [PromptFrom] Then "Current"

ElseIf [Join Date Merge] > [PromptFrom] Then "Future Employee"

The results show in the table, however I am unable to filter the results as it shows no values inside. If I replace ReportName() with the name "January" it works! Could someone help me as I need it to go by the ReportName() otherwise I will have to make 96 variables for the year!

thanks in Advance.