Attachment template: Trying to report from multiple days with empty values

[rapmachado]

Good evening,

I want to generate a report that seems like:

I already have a table that has the following columns: [Production ID],[Date of production],[Employee],[Process],[Produced Units].

And I’m using the following expression for the first and second columns (name and production from the 1st of the moth):
<<Start: orderby(SELECT([Related Productions][Production ID], [Process]=Process 1, [Date]=Date(moth([Date of production])&"/1/"&year([Date of production])))),[Employee])>><<[Employee]>><<[Produced units]>><>

That gives me:
Employee 1 2
Employee 3 4

So, when I do similarly for the 2nd of the month, I get:
Employee 1 3
Employee 2 5

As you might see, the Issue is that I need to run the expression with all the “active” employees from that period in order to be able to fulfill the 0’s where needed.

I mandatorily need to filter by date, which will modify the employees on the result. I got to a dead end.

I’ll appreciate any ideas in order to solve this situation.

[Steve]

Based on what I’ve been able to determine, I suggest you use these tags:

<<Start:ORDERBY(
  SELECT(
    [Related Productions][Production ID],
    AND(
      ("Process 1" = [Process]),
      (EOMONTH(TODAY(), -1) = EOMONTH([Date of Production], 0)),
      (
        [_ROWNUMBER]
        = MAX(
          SELECT(
            [Related Productions][_ROWNUMBER],
            ([_THISROW-1].[Employee] = [Employee])
          )
        )
      )
    )
  ),
  [Employee]
)>><<Employee>>

In your report template here:

Then, use this tag:

<<SUM(
  SELECT(
    [Related Productions][Produced units],
    AND(
      ([_THISROW-1].[Employee] = [Employee]),
      ((EOMONTH(TODAY(), -2) + 1) = [Date of Production])
    )
  )
)>>

In your template here:

Repeat that same tag here:

but replace (EOMONTH(TODAY(), -2) + 1) with (EOMONTH(TODAY(), -2) + 2). Do the same in the 3 column ((EOMONTH(TODAY(), -2) + 3)), the 4 column ((EOMONTH(TODAY(), -2) + 4)), and so on.

Put an <<End>> tag in the last cell of that same template row.

[rapmachado]

Dear @Steve,

After a few days trying to figure everything out and perfecting my report, I was finally able to achieve my goal. I just wanted to thank you and provide with my solution so coming readers can use it as reference.

For the unique Employee per Process I used this:
<<Start: ORDERBY(SELECT([Related productions][Production ID],
and([Process]=“Process 1”,
[_ROWNUMBER]= MAX(SELECT(Productions[_ROWNUMBER],
AND([Employee] = [_THISROW-1].[Employee],
[Process] = [_THISROW-1].[Process])))))
, [Employee])>>
<<[Employee]>>

Then, for the Produced units, this is what I did (example for 1st of the month):
<<SUM(SELECT(Productions[Produced units],
AND([Employee]=[_THISROW-1].[Employee],
[Process]=[_THISROW-1].[Process],
[Date]=EOMONTH([_THISROW-1].[Date], -1)+ 1)))>>

And increased the last “+1” while moving right.

We have to be careful with something that I suffered a lot to solve. On the inner SELECT from te first expression, as well as on the one used to solve the Produced units cell, we have to call the proper Table (not the [Related table] field). I don’t know if it was just on this case, but it’s what made my template work.

I hope this information is helpful for the community.

[Steve]

Well done!