If you look at the Power BI audit logs for paginated reports you can see which user ran which report and when, but you cannot see any parameter values. This is by-design since parameter values are considered “customer content” and no content like this is included in the Power BI logs.
But if your company has a requirement to log which parameter values were used when running a report then this is something you will need to build yourself.
One possible way of doing this is to add a dataset to your report which does an insert command or calls a stored procedure to insert a record into a custom audit table.
I did this using Azure SQL and creating the following table:
CREATE TABLE [dbo].[ReportExecutionLog]( [ReportName] [nvarchar](255) NULL, [UserName] [nvarchar](255) NULL, [Parameters] [nvarchar](max) NULL, [DateTime] [datetime] NULL )
And the following Stored Procedure:
CREATE PROC [dbo].[sprInsertLogRecord]( @reportName NVARCHAR(255), @userName NVARCHAR(255), @params NVARCHAR(MAX)) as INSERT INTO [dbo].[ReportExecutionLog] ([ReportName] ,[UserName] ,[Parameters] ,[DateTime]) VALUES (@reportName ,@userName ,@params ,getdate())
Then I added a connection to my Azure SQL database in Report Builder and created a new dataset called “Logging” and configured it to call this stored procedure.
Then in the parameters section I set it up to pass in the global ReportName and UserID values
Note: Unfortunately there is currently no way to get the ReportID or WorkspaceID, hopefully this gets added at some point in the future. For the time being this means that you need to make sure that any reports you want to add logging to must have unique names. You could possibly look at adding some unique report ID to your report name to ensure this.
In the screenshot above you can see that there is an expression for the parameter values that I want to capture, this is calling a small custom function. The code in the expression is as follows and I’m just passing in an array of the parameter name that I want to log:
The GetParameterValues function is as follows:
Public Function GetParameterValues( ByVal paramsToLog As Array) As String Dim params as String Dim i as Integer Dim pName as String For Each pName in paramsToLog Dim p as Parameter p = Report.Parameters(pName) Dim pVal as String If (p.Count > 1) Then pVal = Join(p.Value, ",") Else pVal = p.Value End If params = params & pName & "=" & pVal & vbCrLf Next pName Return params End Function
I entered this in the “Code” property for the report by clicking in the dark gray background area to expose the report level properties
Then when you click on the … button to edit the code value I paste the code into the “Custom code” area.
This then produces output like the following.
Note that in my function I am using a newline between each parameter which SSMS does not show in it’s grid output, but you can see them if you build a report over this log table. If you want to a different delimiter between the parameters you could simply replace the vbCrLf value in the function above with something else.
Leave a Reply