Thanks, Jeff. I will try that. As for the version, I was convinced it was SSRS 2016 or newer but I am not so sure now. This is the only...

Yes, it does show with time - a bunch of 00:00:00s in the unformatted field lol.

Thank you for the explanation. This makes sense in terms of resource-efficiency. I will convert :).

Hi Jeffrey,

The data type is date time because when I use query wizard to put filter and choose a date - the SQL code says:

WHERE

pr_time_attendance.h_date >= N'2020-05-10T00:00:00'

OMG! Thank you so much. I had come at this via the wizard which by default had created all these row groups, even when I took them out (as seen...

When I filter by employee number 1722, it does give me all the records (instead of just 1). And as you can see from the second screenshot in the original...

What was your solution though - it seemed more elegant and one that possibly could get nested. Unsure how many times I can nest With CTE etc.

I think I was...

Hi Jeffrey,

Sorry for asking a noob question but why should I change the code if it is working for me? Is Jeff's more resource-efficient?

Also maybe my Microsoft Report Builder is...

• This reply was modified 3 years, 4 months ago by  fahad.kazi.

Very similar to what you mentioned but looks like I had to rely on CTE for it to work. Here is the below code.

With CTE as
(
SELECT
 ...

Of course, please note that the example I had shared was a simplistic one. Here is the SQL'ing I had done.

With CTE AS
(
SELECT
  pr_time_attendance.a_employee_number
  ,pr_employee_mast.a_name_first
...

• This reply was modified 3 years, 4 months ago by  fahad.kazi.
• This reply was modified 3 years, 4 months ago by  fahad.kazi.
• This reply was modified 3 years, 4 months ago by  fahad.kazi.
• This reply was modified 3 years, 4 months ago by  fahad.kazi.
• This reply was modified 3 years, 4 months ago by  fahad.kazi. Reason: Added Fiscal Period of Fiscal Month complexity

Hi pietlindin,

This is not working because I may not be putting the starting parenthesis in the right area or maybe it doesn't work in Microsoft Report Builder.

I read somewhere to...

• This reply was modified 3 years, 4 months ago by  fahad.kazi.
• This reply was modified 3 years, 4 months ago by  fahad.kazi.

Thank you all for the quick response, despite it being a long weekend in some countries. A blend of these has certainly addressed my issue.

You guys rock!

If I am understanding you correctly, you are suggesting I make a second table which has all dates and fiscal years (maybe also fiscal periods or fiscal months) and do...

• This reply was modified 3 years, 4 months ago by  fahad.kazi.

Thank you so much, pietlinden. And no you are not estupidez at all, you are a saint for helping dumb old me.

Quick follow-up question - where is the start of...

Here is the file just in case.