Calculating required work hours by number of weeks selected in date slicer

[KMacauley]

Hi All,

I have been at this issue for the last three days and have not got anywhere.

The issue:
I have a two datasets of payroll data, one with hours worked on a daily basis and the second one the employee details and the number of hours expected to work in a given week.

This data is in PowerPivot, and I have created a Pivot Table that shows the sum of the Hrs Worked. The user has a slicer on Date so they can select the required date/s (anywhere from one day to n months).

The issue is I need to show in the Pivot Table the sum of Hrs Target and the difference between Sum of Hrs Worked and sum of Hrs Target.

Note: The calculation for sum of Hrs Target is NETWORKDAYS(<First Date Selected>, <Last Date Selected>)/5 * Weekly Hrs Target (of each employee)

Once I show these columns I then need to filter (possibly with a slicer) the records that have a difference of <0.

I've been able to calculate the NETWORKDAYS(<First Date Selected>, <Last Date Selected>)/5 from the slicer and store it in a named range, but I cannot work out how to pass that value back to the PowerPivot. If I knew that, I would create a calculation on each employee in the Hours Worked table.

Example Data:
Hours Worked
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Emp ID[/TD]
[TD]Date[/TD]
[TD]Hrs Worked[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31/3/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/4/2014[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]31/3/2014[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/4/2014[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/4/2014[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]31/3/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/4/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/4/2014[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Employee Data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Emp ID[/TD]
[TD]Weekly Hrs Target[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]37.5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]37.5[/TD]
[/TR]
</tbody>[/TABLE]

Any help will be appreciated.

Thank you on advance.

 

[KMacauley]

Can I just claim this is a requirements problem?

I mean, what does it mean to look at any specific date... wrt to a weekly goal?

I am fairly confused on your option 1 though... it's like you are saying the date slicer is not working? Are you using a separate calendar table?

Hi Scott,

The resulting table needs to sum up the hours worked, and then compare them against the target hours for the time period, and show the difference between the two.

To the answer to your question, no I'm not using a separate calendar table. If a softcopy of the spreadsheet is required, I can provide that (once I strip out sensitive information).




All examples use the data in the below table.


Example 1:
Employee enters the below entries.
If I select the date range 31/3 to the 6/4, the expected outcome is this employee to show Sum of Hours as 39.5 and the target as 40 with a difference of -0.5 hours. So if I select "Missing Time" as "No" then I should see this employee with an aggregated result for the week.

If I use Option 1, the report returns correctly.
If I use Option 2, the report returns this employee with a Sum of Hours Worked Per Day = 7.5, Sum of Target Hours Per Day = 8, and Difference = -0.5.
If I use Option 3, the report returns this employee with a Sum of Hours Worked Per Day = 7.5, Sum of Target Hours Per Day = 8, and Difference = -0.5.


Example 2:
Employee enters the below entries.
If I select the date range 1/4 only, the expected outcome is this employee to have Sum of Hours as 8 and the target as 8 with a difference of 0 hours. So if I select "Missing Time" as "No" then I should not see this employee.

If I use Option 1, the report shows the employee because another date for this employee is flagged as missing time.
If I use Option 2, the report returns correctly.
If I use Option 3, the report returns correctly.


Example 3:
Employee enters the below entries.
If I select the date range 7/4 to the 13/4, the expected outcome is this employee to show Sum of Hours as 40 and the target as 40 with a difference of 0 hours. So if I select "Missing Time" as "No" then I should not see this employee.


If I use Option 1, the report returns this employee with a Sum of Hours Worked Per Day = 40, Sum of Target Hours Per Day = 40, and Difference = 8.88178E-16.
If I use Option 2, the report returns this employee with a Sum of Hours Worked Per Day = 7, Sum of Target Hours Per Day = 8, and Difference = -1. This is because the employee did an extra hour on the Wednesday, and left early on the Friday.
If I use Option 3, the report returns this employee with a Sum of Hours Worked Per Day = 7, Sum of Target Hours Per Day = 8, and Difference = -1. This is because the employee did an extra hour on the Wednesday, and left early on the Friday.


Example 4:
Employee enters the below entries.
If I select the date range 14/4 to the 20/4 (same results if 21/4 to the 26/4 is selected), the expected outcome is this employee to show Sum of Hours as 40 and the target as 40 with a difference of 0 hours. So if I select "Missing Time" as "No" then I should not see this employee.


If I use Option 1, the report returns this employee with a Sum of Hours Worked Per Day = 40, Sum of Target Hours Per Day = 40, and Difference = 0.
If I use Option 2, the report returns correctly.
If I use Option 3, the report returns correctly.


Example 5:
Employee enters the below entries.
If I select the date range 27/4 to the 30/4, the expected outcome is this employee to show Sum of Hours as 24 and the target as 24 with a difference of 0 hours. So if I select "Missing Time" as "No" then I should not see this employee.


If I use Option 1, the report returns this employee with a Sum of Hours Worked Per Day = 24, Sum of Target Hours Per Day = 24, and Difference = 0.
If I use Option 2, the report returns correctly.
If I use Option 3, the report returns correctly.


Example 6:
Employee enters the below entries.
If I select the date range 31/3 to the 30/4, the expected outcome is this employee to show Sum of Hours as 183.5 and the target as 184 with a difference of -0.5 hours. So if I select "Missing Time" as "No" then I should see this employee with an aggregated result for the month.

If I use Option 1, the report returns correctly.
If I use Option 2, the report returns this employee with a Sum of Hours Worked Per Day = 14.5, Sum of Target Hours Per Day = 16, and Difference = -1.5. I don't know why.
If I use Option 3, the report returns this employee with a Sum of Hours Worked Per Day = 14.5, Sum of Target Hours Per Day = 16, and Difference = -1.5. I don't know why.



[TABLE="class: grid, width: 403"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Emp ID[/TD]
[TD]Date[/TD]
[TD]Hours[/TD]
[TD]Project[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31/03/2014[/TD]
[TD]4.5[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31/03/2014[/TD]
[TD]0[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31/03/2014[/TD]
[TD]1[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]31/03/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/04/2014[/TD]
[TD]6[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/04/2014[/TD]
[TD]7[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4/04/2014[/TD]
[TD]5[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7/04/2014[/TD]
[TD]2.5[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7/04/2014[/TD]
[TD]1.5[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]1[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]2[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/04/2014[/TD]
[TD]5[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14/04/2014[/TD]
[TD]0[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14/04/2014[/TD]
[TD]8[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]4.5[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]2.5[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]0[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]0[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16/04/2014[/TD]
[TD]6[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16/04/2014[/TD]
[TD]0[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]18/04/2014[/TD]
[TD]8[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21/04/2014[/TD]
[TD]8[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]22/04/2014[/TD]
[TD]5[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]22/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]22/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]22/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]23/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]23/04/2014[/TD]
[TD]5[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]23/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]24/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]24/04/2014[/TD]
[TD]6[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]24/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]24/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]25/04/2014[/TD]
[TD]8[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28/04/2014[/TD]
[TD]3[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28/04/2014[/TD]
[TD]0[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-01.01[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]29/04/2014[/TD]
[TD]0[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30/04/2014[/TD]
[TD]4[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-10.10[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30/04/2014[/TD]
[TD]2[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30/04/2014[/TD]
[TD]1[/TD]
[TD]NZ1-30.83[/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

Pretty interesting problem.

A few things:

1. Very likely you will want a separate calendar table. This allows thinks like, putting Year/Month on rows, using YTD/QTD/MTD functions that DAX provides, cleaning handling IsWeekend, etc. But we can probably ignore that for now.
2. Somewhere above, I said something crazy like

   Very likely you will want a separate calendar table. This allows thinks like, putting Year/Month on rows, using YTD/QTD/MTD functions that DAX provides, cleaning handling IsWeekend, etc. But we can probably ignore that for now.

   But as we get into this problem deeper... I think we are seeing the downfall of calculated columns. they are just "too static". If you only wanted to think about this problem in terms of WEEKS, I think it would be ok. But your table is by DAY (not week), so the delta's would have to be day based as well in the calculated column. But then... it becomes really unclear what the yes/no "mean" when you select say... 3 days.
3. Let's see if we can't work this from the other angle... with measures (calculated fields in 2013 terminology)?
4. I warn you, this is gonna get crazy. I admit to wondering if I over-thought this...

Some measures:

[HoursWorked] := SUM('Hours Worked'[Hrs Worked])
[TargetHours] := (SUM('Employee Data'[Weekly Hrs Target]) / 5) * DISTINCTCOUNT('Hours Worked'[Date])
[HourDelta] := [TargetHours] - [HoursWorked]
[IsMissingHours] := IF( [HourDelta] > 0 , 1, 0)

So, this gets us pretty far. You can use a date slicer, and put employees on rows of your pivot table (which MUST come from the lookup table -- Employee Data, not Hours Worked) and everything "works", including the Grand Totals.

Filtering is... problematic at this point. We don't have a column in our table that has the yes/no (or IsMissingHours, I guess).

This is where things get a bit crazy. I created a new table:

id caption
1 Show All
2 Show Only Missing Hours

It's just hanging out there. PowerPivotPro calls them "disconnected slicers".

Then, I defined a new measure:
[ShouldDisplay] := IF([IsMissingHours] || MIN(DisplayFilter[Id]) = 1, 1, 0)

Then you can drag the caption onto a slicer... and this measure will only return 0 if they are missing hours AND the slicer is set to "Show Only Missing Hours" (id=2).

So, then, on your pivot table you would use the Value Filters... to only show rows where ShouldDisplay = 1. And you would just hide your ShouldDisplay column in the end.

And now I am thinking I should clean this up and put it on the blog...

Let me know what you think.

 

[KMacauley]

Hi Scott,

Thank you very much. The above reply was perfect.

Kim.