Monthly Archives:March 2014

<
ByHariharan Rajendran

Display Zero When No Rows in Dataset – Power BI Paginated Report Tip -1

In recent times, I had a chance to work with Power BI Paginated Reports which is same as SQL Server Reporting Services (SSRS). I have worked with SSRS long back and the same knowledge was helped me to achieve any sort of solution in Power BI Paginated reports. I can see people are started using Power BI paginated reports heavily so I thought it will be good for developers if I share the tips and tricks that I know. I am going to post multiple article in this series so keep follow.

Tip – 1 – Show the value 0 is there is no data. This looks simple but sometimes difficult to achieve. If you have some data then you can write IIF statement and show 0 but if there is no rows in your dataset then your expression will not show anything.

Scenario

I got a requirement to show a layout like below in Power BI Paginated Report.

  Amount – Div1 Amount – Div2
Production 200 100
HR 400 300
IT 500 250
Marketing 600 150

 

In the above table – Column header and row values are hard-coded value except the numbers (200, 400 ,….) which is coming from dataset.

If we have proper value then we can achieve the above result easily. If there is no rows in dataset then need to show the below layout.

Expected

  Amount – Div1 Amount – Div2
Production 0 0
HR 0 0
IT 0 0
Marketing 0 0

 

Actual

  Amount – Div1 Amount – Div2

 

If you try to write any expression in the cell then it will not display and you will get like above. How to get the expected layout.

Why we are not getting the rows?

All the Tablix should be mapped with dataset and if there is no data then nothing will be displayed in the rows and will be hidden.

There are two methods to you can achieve the expected result.

Method 1 – if there is no data in dataset then you can add UNION with your existing script and can add one empty or zero values. If you have data then you can remove the dummy row in the expression else the dummy row only will be available as a dataset result and your table will show all the rows and you will get the expected result.

Method 2 – Since the layout is hard-coded or static layout, we can insert many header rows and achieve the result. To make it clear, we have header and data row. Header row will not show option to choose the dataset column but data row will show the option to choose the column. Here, we need to delete the data row and insert multiple header and write expression you will get the expected result.

 

ByHariharan Rajendran

How to overcome the Limitations of Paginated Report ODBC/OLEDB connectors in Power BI Report Server

This article covers how the ODBC and OLEDB connectors can be effectively used and overcome the limitations. The ODBC and OLEDB connectors are only supported in on-prem version of report builder as of now.

What is the use of ODBC and OLEDB in Report Builder?

As you know, we have more connectors support in Power BI Desktop and the list is keep extending on a monthly basis but the data source support for report builder is limited. If you take Power BI Report builder then very less connectors are supported as of now. It doesn’t support ODBC or OLEDB data source. The same is available in Microsoft Report Builder (on-prem version) and gives the flexibility to connect many data sources through ODBC and OLEDB. For example, if you want to create paginated report using SAP HANA as a source then you just have only one option which is ODBC (if you are looking for on-prem solution).

Info – SAP HANA with Power BI cloud, you can use Power BI Desktop report or use Power BI Datasets as a source for Power BI Builder report.

The list of supported data sources from Microsoft Report Builder.

 

How to use ODBC or OLEDB in Report builder?

First, you need to have answer for “what is the data source you are trying to connect from report builder through ODBC”?

Let us take an example, you want to connect SAP HANA then you need to install 32 bit of SAP HANA ODBC client and then you need to create a DSN in your system to connect and the same needs to be created in Report server machine. The same should be followed if you want to connect with other ODBC supported data sources.

Attached some sample screenshots,

Enter the connection details and create the DSN.

We need to choose ODBC from report builder data sources and can choose the created DSN.

To connect through OLEDB, you need OLE DB provider for the data sources. If you have installed the OLEDB driver then you can choose and create the connection.

What are the primary limitations?

  1. To define the parameter, we need to use “?”. In case, if you have multiple parameters in a dataset then it will difficult to find the order of the parameters.
  2. It will not support multi-value parameters in the report.

Error – 

Cannot add multi value query parameter ‘?’ for dataset ‘DataSet1’ because it is not supported by the data extension.

—————————-

An error has occurred during report processing.

How to overcome multi-value parameter value limitation?

We can overcome the limitation using below steps.

  1. Use Join Condition in the parameter expression to convert the list into comma separated values.
  2. Use Database function to convert the comma separated values into row by row in the dataset query.

Step by Step Procedure

Scenario – I have a report where I connected Oracle through OLEDB data source. I need to pass the account type values into the parameter which is multi-value enabled and need to show the result in the report.

Step 1 – Create a parameter as like below and enable the “Allow multiple values” checkbox.

Step 2 – Build your query as like below. It should have IN operator as we need to pass multiple value. The initial query will be like below.

Step 3 – We have “regexp_substr” function in oracle which will convert comma separated values into row by row. Added the statement below. To test this statement, you can replace the “?” into hard-coded value and see the result.

(SELECT trim(regexp_substr(?, ‘[^,]+’, 1, LEVEL)) str   FROM DUAL

CONNECT BY instr(?, ‘,’, 1, LEVEL – 1) > 0)

Step 4 – Need to add the above statement in the step 2 dataset query.

Step 5 – There will two parameters created, need to map with already created parameter.

Step 6 – Need to convert the @AccType into expression to join the multi-value into comma separated. Use the below expression for both the parameters.

Step 7 – Add a simple table to show the result and run the report. Report will run and show te exprect result.

Alternate Approach

We do have other approach using combined query (query expression) in dataset to achieve the same result.

Create a query field in fields section for all the select columns.

The above expression will work and get the expected result.

Happy Learning!! Share your comments below.

ByHariharan Rajendran

[First Impression] Automate Power BI Export File with Power Automate

Here, I am covering what are the options available in latest actions in Power BI Connector in Power Automate. Those 2 actions are most wanted from most of the Power BI professionals.

Most of the customers are wanted to subscribe their reports and they want to get the file in their inbox or network shared drive. As of now, we have option for email but not for network shared drive. There was a gap in the product and it got resolved when Power BI team released the REST API support for export the files but we it was possible by creating custom connectors with Azure APP client details in Power Automate. Now, we have direct connector to achieve the same result in an easy way.

You know, I have recently written an article where I compared the Power BI Paginated report with Power BI Report Server Paginated report. I have considered this as an one of the feature comparison since there was no support at that time. I may need to update my post with this latest information.

We had only 2 actions under Power BI but now, we have two more.

Check out the pre-requisites and other details here – https://powerbi.microsoft.com/en-us/blog/power-automate-actions-for-exporting-power-bi-and-paginated-reports-now-available/

Export to File for Power BI Reports supports only PPTX, PDF & PNG as same in the service. We have additional option like Include hidden pages & Bookmarks with limited functionality.

Just tested with One Drive as a destination and it worked well.

Export To File for Paginated Reports – This action has variety of export formats, RLS and Parameter value support.

 

I will be covering the detailed blogpost with all the use cases with this two actions.

Happy Learning!!

ByHariharan Rajendran

Power BI with Teradata Report Considerations

In recent times, I am using Teradata Database and connecting with Power BI Desktop and Report Builder. I want to share my experience so that it might be helpful to others.

My setup – I am connecting the Teradata database through VPN and I don’t have access to use windows authentication to connect with Teradata from my system. We need to use LDAP authentication to connect from system to Teradata database.

Quick Bytes – LDAP is nothing but “Lightweight Directory Access Protocol” and it is helping us to access the directory information services over an Internet Protocol (IP) network. You can learn more information in online.

Power BI Desktop

From Power BI Desktop, we can connect to Teradata either using Windows Authentication or Database Authentication.

If you see my above scenario, i can’t connect with windows or database. Teradata DBA has enabled LDAP for any users who want to connect the database using VPN. LDAP is one of the authentication which is not available as default. We need explicitly make LDAP enable for Power BI Desktop.

Need to install Teradata client driver and then need to run the below command in command prompt.

setx PBI_EnableTeradataLdap true

The will enable LDAP as one of the authentication method in Power BI Desktop as like below.

We can use LDAP to connect with Teradata and can import the tables into Power BI.

Power BI Desktop with Teradata via Direct Query.

As we all know, Direct query is something that will run the query against database when user run the report, query will be triggered and run against database through Gateway. My understanding was, we can use any authentication to connect with database from Power BI Desktop and when we publish to service it is always use the account which we use on the gateway data source.  The below scenario gave me the new understanding on Direct Query with Teradata again via LDAP.

I have used Direct query using LDAP and report and  was working fine in the Power BI Desktop. Once I published the report to Service and mapped with Gateway Service account data source, we got the below error message.

When we see the error message, we can understood something that there is an issue with Gateway service account access but really not.

The file which I use same LDAP with Import mode is working fine on schedule refresh but only direct query is not working in the service. We have done many analysis and finally found that LDAP method which is used on the PBIX file is the problem. It means the Power BI Desktop file with Taradata LDAP is not working in the service with Direct Query.

Finally, we got the database account and it resolved the issue.

Conclusion – We need to make sure to choose proper default authentication method in Power BI Desktop when we deal with direct query in Power BI Service.

If anyone from Microsoft team see this, please let me know – is this a bug or known issue?

Happy Learning!!

ByHariharan Rajendran

Paginated Report works in Power BI Service but Fails in Power BI Report Server

This post explains a tip when you build the paginated report for Power BI Service and Power BI Report Server. Both the version of paginated reports are .rdl files.

Let me start with simple question.

 If you have rdl file published in Power BI service and working there, will the same rdl work in Power BI report server?

Almost, all of us will say YES because we know that there is no difference in RDL file on both the version. This blog post explains, there are certain cases the same RDL will not work in Power BI report server.

I have a paginated report with Oracle as a source and it is working fine in Power BI Paginated Report builder and Power BI Service. I have a data parameter with below name and the same is used in the dataset.

:Start & :End – If you use Oracle in report builder, you need to use “:” for parameters.

The same rdl with Oracle is not working and it ended up with below error in web portal.

Error – An error has occurred during report processing (rsProcessingAborted). Query execution failed for dataset XXXX (rsErrorExecutingCommand).

It shows that there is an error in the dataset. To investigate further, we need to use Microsoft Report Builder (Optimized version of Power BI Report Server / SSRS).

I started to run the query in Dataset – Query Designer and I got the below error.

Error – An error occurred while executing the query. ORA-01745: Invalid host/bind variable name.

The above error conveys something that there is an issue with the parameter / variable which we defined. I have renamed the parameter as :StartDate and :EndDate and it worked.

Published the updated RDL in report server and it worked there also without an issue.

Conclusion – The Power BI Report Builder and Microsoft Report builder is different in-terms of deployment and data source settings also the built-in system codes with data sources. Some of the words are considered as keyword in Microsoft Report Builder but not in the Power BI Report Builder.

ByHariharan Rajendran

Paginated Reports from Power BI Service and Report Server (On-Prem)

This article explains that on-prem report server is the best option for paginated reports as of now as per my experience and I am also recommending you to use the hybrid approach wherever it is possible. You can maintain a hybrid structure like, Power BI Desktop reports in Power BI Service and Paginated reports in On-prem server. This is something that you can prefer where you will get the more stable version of product with more features.

I am sure, Power BI team will make the Power BI Paginated Report as a stable version with tons of feature in future.

I am highlighting few points in which Report Server is getting more score than the Power BI Service.

License – if your organization has Power BI Premium Capacity node then you can get the report server product key and use them for on-prem installation or you can get it if you have SQL Server Enterprise Edition with Software Assurance.

Report Render Time-Out issue  – We often encounter this issue in one of my client. We are using SQL Server and the database and we are handling large tables. If we run the query, it will take at least 5 to 6 mins due to relationship with multiple tables and conditions.

We have done all level of performance optimization but still same performance. If we use the same script in Report Builder it is taking more than 10 mins and same in service taking more than 10 mins and after that it end up with an error. When we check with Microsoft support engineer, they were highlighted that the issue is because of Authentication token expiration but not sure and there is no fix as of now. We tried all the approaches to overcome the issue but finally we end up with Report Server as an alternate solution for these kind of time-out issues. In Report Server we do have control to setup the time-out settings.

Subscription – Our customer had requirement to get the report’s excel export into their email or network drive. Power BI Service has Email option but no network drive as an out of the box feature. We need to use Power Automate to achieve the result which will be difficult for business users. This network file share is one of the default option in on-prem Report Server. Also we do have data driven subscription.

Gateway  – Gateway is one of the component in Power BI Service which helps to connect Power BI reports with on-prem data sources. We always need to map the report with gateway data sources. If you have Power Bi Desktop reports then you can map the dataset once with Gateway datasource and you can replace them every time whenever you do the changes on the report and you no need to map the data source again and again for the same report where as you need to map with data source again and again whenever you overwrite the paginated report (rdl).

Security – In terms of security, we have to leverage only the pre-defined access levels which are Admin, Member, Contributor & Viewer in Power BI Service. These pre-defined access levels are more than enough for most of the cases but on few cases we may need to limit certain access. We have same kind of access levels in report server but we do have control to create our own access with defined controls. It will give more control to manage the access levels.

If you have PII data and you dont want to move the data outside of your network then report server is one of the best option.

Data Source Support – This is one of the feature that I highlight Power BI Report server is best. Power BI Paginated report support only limited data sources where as we have few more additional data sources like ODBC, OLEDB and etc supported in Report server paginated report.  ODBC and OLEDB can cover many data source connections possible with Report Server report. One of the highlight of Power BI Paginated report, it supports Power BI datasets as a source which is not supported in report server.

Management – Power BI Service has workspace concept to maintain the reports for different teams and groups and etc. If you have multiple sections under one main group then we need to maintain multiple workspaces. In Report Server, all are folders, we can create folders, sub folders and can control the access to root folder or sub folder or report.

Additional Features – Power BI Report Server supports below features which is not fully available in Power BI Paginated reports yet.

1. History Snapshot

2. Cache control

3. Central Data Sources and Datasets

4. KPIs

5. Document Map

6. Drill-through reports

7. Linked Reports

Development Tools – Power BI Report Builder is 32 bit and light weight tool and we have same kind of report builder for report server. Both of the tools hanging when we handle report with huge number of rows as a result. If you choose report server then you can use SQL Server Data Tools with Visual Studio and you can create reports and deploy. The development experience will be good when you choose Visual Studio.

Share your thoughts here.

ByHariharan Rajendran

On-Prem SharePoint Folder as a Source in Power BI – Part 2 SharePoint.Contents

Check my previous blog post where I have explained how to connect SharePoint On-Prem folder as a source to access excel or csv files using SharePoint.files function in Power BI Desktop.

On-Prem SharePoint Folder as a Source in Power BI

This article explains how to use SharePoint.Contents function in Power BI to achieve the same function. It is kind of Part 2 version of my previous blog post.

Why you need to care about SharePoint.Contents?

I have used SharePoint.Files on some of my clients to access there excel files in Power BI Desktop, it is working good for few of them but not for others. I got the below error message.

 

I have tried different option to fix the issue, but it was not working so I tried with SharePoint.Contents and it worked well. I may suggest you use SharePoint.Contents first when you access SharePoint files.

Follow the below steps.

Step 1 – Get your SharePoint Site URL where you have your folder.

Step 2 – Go to “Get data” and choose Blank Query.

Step 3 – Click “Advanced Editor”

Step 4 – Get your SharePoint site name and replace it in below script.

let

Source = SharePoint.Contents(“http://<siteurl>.com/sites/<sitename

in

Source

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – It will load all the objects from SharePoint site as like below.

Step 7 – Check for your folder name under Name column and click Table under content column. For example, if my folder name is “Power BI Files” where I have all my excel files are there.. Click the Table and it will expand all the sub folders and then if we click Table again then it will show the list of files from the folder as like below. We need to navigate to multiple levels based on the folder structure.

Step 8 – Filter for .xlsx under Extension column, if you have other documents also placed on the same folder.

Step 9 – Rest of the steps are same as discusses in my Part 1.

The above method is works well when you have multiple files in a folder. It will use the automatically created parameter and function.

What if you want to access only one excel file from the folder? Can I use the same method? Yes. You can use the same method and you can filter only that specific file and follow the rest of the steps.

Sometimes you think that parameters and functions are not required when you just access one single. Yes, you are correct. Use the below Approach to access the single file which will avoid creating and using parameter and function.

Follow the below steps.

Step 1 – Get your SharePoint Site URL where you have your folder.

Step 2 – Go to “Get data” and choose Blank Query.

Step 3 – Click “Advanced Editor”.

Step 4 – Get your SharePoint site name and replace it in below script.

let

Source = SharePoint.Contents(“http://<siteurl>.com/sites/<sitename>/”)

in

Source

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – It will load all the objects from SharePoint site as like below.

Step 7 – Check for your folder name under Name column and click Table under content column. For example, if my folder name is “Power BI Files” where I have all my excel files are there.. Click the Table and it will expand all the sub folders and then if we click Table again then it will show the list of files from the folder as like below. We need to navigate to multiple levels based on the folder structure.

Step 8 – Get the below details.

  • Excel file Name
  • Excel Sheet Name

Step 9 – Open Advanced Editor and you could see few lines of script which you performed above.

Step 10 – Remove the “in” section and add comma (“,”) at the last line of the script then add the below lines. Replace the below red highlighted text into your previous step name. Your step name may be like in the following format #”Path”. Just copy and paste it below script.

#”Filtered Rows” = Table.SelectRows(PreviousStepname, each ([Name] = “YourExcelFileName.xlsx”)),

Navigation1 = #”Filtered Rows”{0}[Content],

Excel = Excel.Workbook(Navigation1 , null, true),

Completions_Sheet = Excel{[Item=”YourExcelSheetName”,Kind=”Sheet”]}[Data],

#”Promoted Headers” = Table.PromoteHeaders(Completions_Sheet, [PromoteAllScalars=true])

in

#”Promoted Headers”

Step 11 – Your final script will look like below. You may have extra lines at the top based on your folder path level.

Let me know if you have any doubts. Happy Learning!!!

ByHariharan Rajendran

On-Prem SharePoint Folder as a Source in Power BI

This blog post explains you the problems and solution with SharePoint on-prem source in Power BI Desktop.

The list of connectors in Power BI Desktop is keep increasing every month. Thanks to Power BI team who working hard to integrate new connectors with Power BI desktop.

Let me explain you the problem or issues that I see every time in forums and community questions.

Problem– Not able to access excel or csv files from On-Prem SharePoint folder. How to access it?

Yes, the question is simple and you may think that we have different methods to access it. Let me specify the methods below and issues.

SharePoint Folder – We can see SharePoint Folder as a one of the source under Get Data which can be used to connect both online and on-prem SharePoint but sometime it is not working properly with on-Prem SharePoint folders.

Have you tried it? If not then try. You may get this below error.

Error – “We encountered an error while trying to connect. Details: “We found extra characters at the end of JSON input'”

Folder – UNC method – This is very good workaround to connect with SharePoint folders. We need take the SharePoint folder Web URL and convert them into local folder type URL by changing the slash (“\”). This works very well in my case. If you are user and you can access your SharePoint folder as windows explorer folder. As you know, you need to choose Folder as a connection type while configuring data source in gateway. It will work when you use your account for configuring the credentials.

In real-time, we will not use individual account on the gateway data source and it will be a service account or the windows account where the gateway clusters are installed. I have received an error when I configure a folder data source with gateway machine account credentials.

Proper access was given to the gateway account but still got the above error message. This might be working for you. If yes, leave your comment below.

Web – If you have single file stored in SharePoint folder then get the file URL and use Web as a source and connect with the excel file. It is very good option when you have single file. How to handle multiple files?

I have tested with multiple clients and above methods are not working because their on-prem SharePoint servers are not a latest version and few other reasons.

How to solve this issue?

I mean how to access the on-prem SharePoint folder files from Power BI Desktop? Solution is simple. Don’t use GUI for this scenario. Use M query function in blank query.

We have below function in Power BI related to SharePoint.

  • SharePoint.Contents
  • SharePoint.Files
  • SharePoint.Tables

SharePoint.Tables is specifically for SharePoint list. We have SharePoint Online List and SharePoint List as two separate connectors. Use one of them and connect with your SharePoint list and check the advanced editor then you will see the SharePoint.Tables function.

Let us come to the topic now. What are the other 2 SharePoint functions and what is the use of those functions and how those function will solve the on-prem SharePoint folder issues?

I will answer for all the questions below.

Let us start with SharePoint.Files function.

Follow the below steps.

Step 1 – Get your SharePoint Site URL where you have your folder.

Step 2 – Go to “Get data” and choose Blank Query.

Step 3 – Click “Advanced Editor”

Step 4 – Get your SharePoint site name and replace it in below script.

let

    Source = SharePoint.Files(“http://<siteurl>.com/sites/<sitename>/”)

in

Step 5 – Copy and paste the above script in advanced editor window and click done.

Step 6 – This will list out all the files from SharePoint site.

Step 7 –  You can see the last column folder path and filter your path where you have all your excel files and filter .xlsx and .xls on the extension column based on your file extension.

Step 8 – Choose Content column and right click and remove other columns.

Step 9 – Expand the content column and it will take some time to open your excel file and sheet.

Step 10 – It will create a group with sample file and parameters and the same will be used in the query as like below.

Step 11 – Finally, the table will be loaded with all the columns and you can use it and perform further transformations as per the requirement.

You also can create function and invoke them instead of using automatically created function and parameter.

I will cover the SharePoint.Contents in my next article. Thanks. Happy Learning.