Requirement: Allow users to enter comments after they review results from a Power Pivot model and then show the comment text in the Pivot Table report results. Here’s a quick example of the final result. My example uses Flintstones characters in-place of employee or customer names and a simple measure value. After reviewing the results, the user moves to another worksheet and enters a comment for the date and person with an expiration date for the comment. After refreshing the report, each cell with a comment is indicated with a little “dog ear” and the comment is displayed by clicking the cell.
In practice, the data model could be in a server-hosted SSAS Tabular model, Office 365/Power BI or SharePoint-hosted Power Pivot workbook, or a desktop Excel file. In one real world application, if entries don’t meet company standards, the person reviewing the data entry can enter comments about these late entries and other policy violations. A comment has an expiration date and in the production solution, the comment expiration is also dependent on a calculated “severity level” which can be baked into the expiration logic.
My first thought when considering the need to incorporate text strings into a semantic model was that it might not be a good idea to store large text strings in a Power Pivot or Tabular model. After all, the tabular model technology is really optimized to handle numeric and redundant data. Upon carefully talking through the requirements, we’re pretty comfortable that the number of columns will be fairly low and the length of the comment strings should be conservative. The VertiPaq/VertiScan engine is actually pretty forgiving about having “stuff” added to a model that it will ignore except when it’s being used in a calculation or query. So, that’s good – my comment rows shouldn’t get in the way of core features like measure aggregation and dimension navigation. I’m really curious to find out how viable this is in a larger-scale application so if you end-up doing this with a lot of data or have the opportunity to test the limits of this technique, please share your results.
With that in mind, I took the plunge and built a simple proof-of-concept solution; which is what I’m demonstrating here. If the storage and query engine are OK coexisting with these new column fields, the big question that remains is how will the query engine react when we start to send it some unconventional long string results. Will these long values just get crunched through the query engine expressions along with the usual measures without making a ruckus.
Now remember that this is an über-simplified model and that the real one contains about 30 tables, but the major components are the same. The main fact table (tbl_fact) contains keys related to some outside tables and a measure called “Sum of Sum Value” which aggregates the SomeValue column for every Person in a pivot table report. So, here’s the solution:
The Comments table (tblComments, which I’ll call “Comments”) is essentially fact table related to Dates and People:
The source for the Comments table is a worksheet/table. Here are some of the comments:
In the production solution, we want the comment to show up until the expiration date. Rather than waiting for the system date to change, I’ve added a table of EffectiveDate values to use as a slicer for prototyping. The DAX measure expressions are added to the calculation area in the Comment table.
I’ll start with a simple version of the the CurrentComment measure before adding the expiration logic:
Current Comment:=IF(HASONEVALUE(tbl_Comments[Comment]), VALUES(tbl_Comments[Comment]))
it’s important to test the comment for only one value using the HASONEVALUE function to avoid an error. The VALUES function just returns the column value as a string. Now, I’ll add the expiration logic which uses the first measure:
Comment Until Expiration:=
CALCULATE( [Current Comment],
FILTER( ‘tbl_Comments’, [Date] < FIRSTDATE( tblEffectivedate[EffectiveDate] )
&& tbl_Comments[ExpirationDate] > FIRSTDATE( tblEffectivedate[EffectiveDate] )
)
)
Once this is all tested and working, we can just substitute “TODAY()” in-place of “FIRSTDATE( tblEffectivedate[EffectiveDate] )” to use the system date.
Now, to add the Excel comments. This is the fun part (as if it hasn’t been exciting enough thus far!)
Add a pivot table to a new sheet name “Report”. Add Dates and People on rows and the “Some of Some Value” measure on columns (which actually adds it to the VALUES). Add the “Comment Until Expiration” to columns as well. Select the last column added to the pivot table and hide it. In my example, this is column D.
You’ll need to have macros enabled and trust VBA in Options > Macro Settings.
Use Alt+F11 to open the Visual Basic for Applications editor and enter the following code into a code module (usually Modules > Module1):
‘***********************************************************
‘ Paul Turley, 9-22-14
‘ Dynamically add comments to Pivot Table value cells based
‘ on an adjacent hidden column.
‘***********************************************************
Dim ws As Worksheet
Dim pt As PivotTable
Const iPTRowOffSet As Integer = 3 ‘ Pivot Table starts on this row
Const iPTHeaderRows As Integer = 2 ‘ Number of Pivot Table header rows
Const iPTClearSafetyRows As Integer = 100 ‘ Number of rows beyond the last PT row that get cleared (in-case the filtered table shrinks by a large # of rows)
Dim iRows As Integer
Dim iRow As Integer
Dim CommentRng As Range
Dim ValueRange As Range
Sub SetObjects()
Set ws = Worksheets(“Report”)
Set pt = ws.PivotTables(1)
End SubSub btnCreateComments()
SetObjects
ClearAllComments
CreateComments
End SubSub CreateComments()
For iRow = (iPTRowOffSet + iPTHeaderRows) To pt.RowRange.Rows.Count + iPTRowOffSet
Set CommentRng = pt.ColumnRange(iRow, 2)
If CommentRng.Cells(0).Value <> “” And iRow >= iPTRowOffSet Then
Set ValueRange = Worksheets(“Report”).Cells(iRow + 1, 3)
If ValueRange.Comment Is Nothing Then
ValueRange.AddComment (CommentRng.Cells(0).Value)
End If
ValueRange.Comment.Visible = False
End If
Next
End SubSub ClearAllComments()
SetObjects
For iRow = (iPTRowOffSet + iPTHeaderRows) To (pt.RowRange.Rows.Count + iPTRowOffSet + iPTClearSafetyRows)
Set ValueRange = Worksheets(“Report”).Cells(iRow + 1, 3)
If Not ValueRange.Comment Is Nothing Then ValueRange.Comment.Delete
Next
End Sub
Note that there are some assumptions made in this code. I don’t think the pivot table needs to start at cell B3 but that’s where mine is. Note the constants at the top that are used to skip the header cells. These values will need to be adjusted if you make changes.
Next, open the code module for the workbook (named ThisWorkbook) and add the following code for the SheetCalculate event (three lines added between the generated Private Sub & End Sub lines):
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
SetObjects
ClearAllComments
CreateComments
End Sub
Now, let the magic happen. Every time the sheet is calculated (this event fires every time it is connected, filtered, sliced or the pivot table is changed), the code iterates through the measure column cells and removes all the Excel comments and then iterates through and adds new Excel comments using the values in the adjacent hidden column. The hidden column contains the calculated “Comment Until Expiration” measure text values.
You can add or remove comment text in the Comments sheet, move to the report sheet and use Data > Refresh to update the model. Use slicers to change the EffectiveDate and filters to add or remove row values. With each action, you should see comments come and go. To view a comment, click the cell and hover the mouse pointer over the dog-ear in the upper-right corner of the cell. There are more comment options on the Review ribbon. Make sure you save this as a macro-enabled workbook (.xlsm) file.
As always, I’m interested in feedback, thoughts and suggestions.
– Paul