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

[Anonymous]

Hello @AbhiSSRS - This seems to be exactly what I am looking for (well, at least VBA part of it).

However, can you expand on the VBA side of things from above.  Meaing will this work:

=Code.GetValue(SomeFieldName)

Do I have to add 

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel

To the references/assemblies?

My main attempt here is to do a lookup of a value from a dataset that is a Key/Value pair table.  This Key/Value pair table SQL is parameterized and narrows down the set of data from thousands of rows to just the 200 or so that deal with my widget.  What I need to do is run a function (since I have not found a WHERE clause in a built in function) that takes that Key/Value DataSet and gives me the Value field for the given Key.

If I can do this without a custom VBA code that would be great. But I am not sure how to do it either way.

Please Help!

[AbhiSSRS]

Hi! I think you may use the built in Lookup function instead of custom code. 

Use Lookup to retrieve the value from the specified dataset for a name-value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region.

=Lookup(Fields!SaleProdId.Value, Fields!ProductID.Value, Fields!Name.Value, "Product")

[Anonymous]

@AbhiSSRS Thanks for the response.

I have tried that as well. This is in a Textbox so there is no scope. But I have

=Lookup("TBN", Fields!TestValueDescription, Fields!TestValue, "PsAndCs")

This returns "#Error" on the report with no more detail.  Where TBN is one of the values in the TestValueDescription field and I want the TestValue field from that row within the PsAndCs Dataset.