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.
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.
Check the below video to know details steps and what are all possible with viewer access with build permission.
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.
This issue occurs in Power BI Desktop report. This issue will occur when you use the direct measure (implicit measure) in the visual.
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.
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.
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.
I got a requirement to show a layout like below in Power BI Paginated Report.
|Amount – Div1||Amount – Div2|
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.
|Amount – Div1||Amount – Div2|
|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.
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?
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.
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.
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.
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.
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.
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.
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.
If anyone from Microsoft team see this, please let me know – is this a bug or known issue?
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.
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
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.