Monthly Archives:August 2018

<
ByHariharan Rajendran

Power BI Enter Data in Power BI Service | Create Static Table in Power BI Service

This article explains that how to use Enter Data option in the Power BI Service and use it across report datasets as a live connection (shared dataset).

This feature is already available in Power BI Desktop already.

Enter Data in Power Desktop

Power BI Desktop has an option called “Enter Data” which helps to create some static data and use it for reporting purpose. There are multiple use cases with this “Enter Data”.

Let me explain one use case for better understanding, you can create “Enter Data” table and use it for placing all the report measures.

Name the table name is “_Measures” and leave with column1 and click Load. We will delete this column later.

Add the measures under the _Measures table.

Delete the Column1 from _Measures after adding one measure.

Enter Data in Power BI Service

Let’s come to main point. You can create this Enter Data or static table in Power BI Service. If you want to use the same table in multiple reports then you can use this option. Create it one place and use it multiple reports via live connection.

Warning – We can’t modify the values in the service once created the table.

Follow the below steps

Step 1 – Go to app.powerbi.com

Step 2 – Go to workspace where you want to create table.

Step 3 -Click Create option and you will see an option called “Paste or manually enter data” as like below.

Step 4 – Once you click the option, it will open a Power Query window where you can define the columns and values and also define the data type of the columns. Once added then name the table. This window is just for Enter Data so you will not see any other transformation as like Power Query Editor in Desktop or Power Query online in Dataflow.

Step 5 – In the bottom, you will see below option. If you want to create this as a dataset then choose “Create a dataset only” option or if you want to create a report out of it then choose the other options.

Step 6 – After the table will be created as dataset. You can go to the workspace and search the table name and you will see a dataset with that name.

Step 7 – As it created as a regular dataset, we can use this in another datasets. This does not request to map with gateway and setup a schedule refresh.

How to modify the value?

If you need to modify the value then download the dataset as PBIX file and update the values and publish it back.

I hope you like this option to create a static table in service without using Power BI Desktop.

Happy Learning!!

ByHariharan Rajendran

Report Share + Viewer Access in Power BI

This blog post explains how Power Bi Admin can allow users with viewer access share their reports with others.

Power BI has 4 different levels of access with different options enabled and disabled.

  1. Admin
  2. Member
  3. Contributor
  4. Viewer

When workspace admin assign a viewer access to user then user can see only the reports and features with in the report. User can’t share the report with other as the viewer access doesn’t allow user to see the share report option.

If user wants to get the share option on top of the viewer access then admin can enable the share option to the user. Follow the steps to enable the share option.

  1. Choose the report which you want to allow user to share with others
  2. Click the share option and enter the user email address who already added as a viewer in the workspace.
  3. If user wants to share all the reports then admin should repeat the above step for all the reports.

Check the below video to know details steps and what are all possible with viewer access with build permission.

ByHariharan Rajendran

[Solve] – Fix the column names in Excel export from Power BI Desktop Report

This blog post explains you that how to fix the column name mismatch in the Power BI visual export. This is not a major issue for most of us but sometimes user might complain about the column name mismatch when they directly compare the UI result with excel export.

How to reproduce it?

This issue occurs in Power BI Desktop report. This issue will occur when you use the direct measure (implicit measure) in the visual.

Scenario

I have table called Dummy where I have 2 direct columns – Department & Sales. I am just creating a simple and visual and exporting the visual data into csv using export data option in Power Bi Desktop itself.

 

The result of csv file will have proper column names.

Try publishing the same report to service and export there into excel and we will get the below column names.

The name of Sales column is changed into “Sum of Sales”. The default summarization of this column is SUM so the name of column has changed.

Note- The names will be same if we export into csv both in Desktop and Service.

Solution

The solution is very simple. Create an explicit measure using DAX and use that measure in your visual.

Created a new DAX measure called SalesValue and used that measure in visual. If we try to export into excel then we will have proper name as like below.

Happy Learning. Comment if you have any other approach.

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.