Power BI Paginated Reports with Custom Code (VB Code)

[AbhiSSRS]

With the buzz around Power BI Premium Per User Licensing going to General Availability at just $20 in April, this may be a time to witness an acceleration in Power BI Paginated reports development as it is included in PPU licensing and permits to host them in the PPU workspace.

Power BI paginated reports are built using “Power BI Report Builder” which helps build the pixel perfect reports ready for printing or PDF exports having headers/footers and often running into multiple repetitive pages. While the tool provides a host of functions (image below) often there may be a need to use your own code to address some of the business needs.

So here we would try adding our own custom code (written in VB) to the Power BI Paginated report to split and array of strings and reverse it using Custom code. Lets start with creating a paginated report in Power BI Paginated Report Builder :

->Here we create an EnterData data source to simply create a new table with couple of Array String Values

->Next Step is to add the dataset and enter values. You may use a different data source as available.

->Next add a Table ,populate and map a column with dataset values and save the rdl.

->Now open the rdl in VS or any tool where you can edit its XML and add the VB code adding a Code section altogether that would split the string and reverse it. Please add it as last tag in Report XML for easy visibility.(Its just an example. There may be better ways to write/do this string reversal 😊 )

Public Function StringArrayReverse( Byval MainString as String) As String
    Dim TestArr as Array
    Dim Result as String
    Dim i as Integer
    Dim j as Integer
    Result=Nothing
    TestArr= MainString.Split("|")

    j=UBOUND(TestArr)

    For i =   0 to UBOUND(TestArr)

    Result = Result+TestArr(j-i)


    Next
    Return Result

    End Function

->Reopen the file in Paginated report builder and you are ready to use this custom code stub!

->Lets run the report now ! See ! 😊

This ability to add custom code without having to worry about using custom libraries provides with immense flexibility in Power BI Paginated reports to achieve business needs !

And yes , it does work perfectly in the PPU Workspace. Deploy the paginated report to a worspace that you have assigned as Premium or Premium Per User and run it there to check results.

There are immense possibilities with using the custom code , It may even help to handle missing fields altogether to avoid Datset missing field errors on reports . eg below code can be used to pass whole field in argument and catch the error in case its missing. 

public function GetValue(ByVal dataElement as Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field) as String


If dataElement.IsMissing Then
Return " "
Else
Return dataElement.value
End If

End Function