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.
This article explains how to use SAMEPERIODLASTYEAR function in SSRS to achieve the same result as like in Power BI.
The challenging part is we don’t have SAMEPERIODLASTYEAR function in SSRS so we need to look for another function which should give the same result.
We have “Previous” function in SSRS, we are going to use that function to achieve the result. Let us get started.
First, let see the result of Power BI – SAMEPERIODLASTYEAR
SAMEPERIODLASTYEAR = CALCULATE(sum(DemoSales[Sales]),
SAMEPERIODLASTYEAR(DemoSales[SalesDate]))
Let us replicate the same result in SSRS.
First, understand the functionality of “Previous” function.
It will give the result of the previous row as the line below.
We need to tune the previous function to get the result as same as SAMEPERIODLASTYEAR in Power BI.
Follow the below steps.
=Previous(sum(Fields!Sales.Value,”Day”),”Year”)
Warning:
The result of the previous function will work only on the current data displayed. For example, if you create a parameter for year and filter for the year 2019 then it will not show any value for previous value.
In my previous post, I have explained the possibility of using SSRS shared dataset as a source for Power BI report. Refer my previous post here.
In this post, I am giving you the detailed step by step procedure to work with the shared dataset in Power BI.
Follow the below steps,
Step 1: Open Power BI Report Server portal URL. For example, the URL is like below.
http://localhost/Reports
Step 2: Add the following extension with the above URL. /api/v2.0/datasets. It will list out all the datasets with ID as like below.
http://localhost/Reports/api/v2.0/datasets
Step 3: Filter the dataset with ID. Here I used the second dataset id.
http://localhost/Reports/api/v2.0/datasets(datasetid)/data
My URL, http://lab/ReportsPBI/api/v2.0/datasets(ca502b81-c88d-4646-af32-ca08d9b4a1f4)/data
Step 4: Open Power BI Desktop and choose OData feed data connector
Step 5: Pass the above URL as like below.
Step 6: Choose advance and select “Include open type columns” checkbox to get all the columns of the dataset.
Step 7: Click Ok and select Edit Option on next screen.
Step 8: Select the icon near the “More Columns” as like below. It will list out all the columns.
Step 9: Uncheck the “Use original column name as a prefix” and click ok. Finally click “Close & Apply”.
Step 10: Choose the fields and create a report.
Thanks. Let me know if you face any issues.
Nowadays, most of the organisations are using Azure Virtual Machine to host SQL Server Reporting services. By default, we can access the reporting service manager with-in same network. It won’t be possible to access the reporting services outside of network without setting up below configuration.
This is not only for SSRS in Azure VM. The same setup is applicable for the on-premises server.
Follow the below steps,
Step 1: Go to virtual machine resource group and select Network Security Group.
Step 2: Create a new inbound security role with service as HTTP, it will take 80 as a port.
Step 3: Create a same inbound rule inside the virtual machine. Use the below power shell script to create a rule.
New-NetFirewallRule -DisplayName “Report Server (TCP on port 443)” -Direction Inbound –Protocol TCP -LocalPort 80
Step 4: Enable DNS if not configured already. Click Public IP in Virtual Machine overview section in the portal. Set up the DNS name. It will look like below.
Step 5: Make sure report server is configured and working without an issue.
Step 6: Pass the DNS name with extension in your local machine as like below.
Step 7: Pass the credentials and it will show your reports.
Share your comments below.
SQL Server Reporting Services has pre-defined roles for security.
Roles are,
Each role has set of access which explored below. We can customise these roles or create a new role by connecting reporting services in SQL Server Management Studio.
This article explains you the possibilities of passing the parameters in mobile report URL.
When we run the mobile report in report manager, usually it will open a URL with folder and report name as like below,
https:////
We have many scenarios where we need to build the shared dataset with one or multiple parameters. To filter the data either we can add the selection list in mobile report and pass the selected item value to shared dataset or we can control through mobile report URL
Scenario 1
Single Parameter
We can pass the parameter to the above URL followed with “?”. The syntax will be like below,
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername>=value
Scenario 2
Multiple Parameters
If you have multiple parameters in your dataset then you need to add “&” symbol followed by your first parameter.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value&<datasetname>.@<parametername2>=value
Scenario 3
Single Parameter with Multiple values -Repeated
To pass multiple values to single parameter then we need to repeat the same parameter with different values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1&><datasetname>.@<parametername1>=value2
Scenario 4
Multiple Parameter with Multiple values -Repeated
To pass multiple values to multiple parameters then we need to repeat the parameters with different values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1&<datasetname>.@<parametername1>=value2&<datasetname>.@<parametername2>=value1&<datasetname>.@<parametername2>=value2
Scenario 5
Parameter with Multiple values with Comma separated
We can pass multiple values with just comma separated. It eliminates the scenario of repeating the parameter with different values.
To get this solution, need to set up the shared dataset to support multiple values.
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=value1,value2,value3<datasetname>.@<parametername2>=value1,value2,value3
Scenario 6
Stored Procedure with custom option
The reason for using the stored procedure is to pass “ALL” values instead of trying all values with comma separated.
Limitations,
https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=ALL<datasetname>.@<parametername2>=value1
Microsoft SQL Server 2016 Reporting Services has multiple new features. One of the features is Branding. It enables us to customise the Report Manager Portal with our preferred colours, logo and custom colours for mobile reports, etc.
You need two files to make your own branding.
1. colors.json
2. metadata.xml
The template for the above files are available in online. You can download here and start to customise it.
There is a possibility that you can get the below error.
Actually, this error is self-explanatory and the solution is very simple.
Need to zip the above 2 files and upload that in Report manager branding section.
The reason for Error:
If you are stored the files in a folder and zip the whole folder and try to upload in branding then you will get the above error message.
Solution:
Select those files and right click and choose to send to ->Compressed. Now you can upload the file.
The drillthrough option is more familiar to us as it is one of the options in SSRS and we use this option in almost all reporting projects.
We have a same drillthrough option available in the latest release of SSRS 2016 Mobile reports. The mechanism of the option is same for both the cases (RDL Report & Mobile Report).
We need two reports. We can pass the value from one report to another to filter the data in the second report.
This article explains that how we can configure drillthrough in Mobile report.
Pre-Requisites:
Considering above pre-requisites are fulfilled.
Follow the below steps to configure DrillThrough
Step 1: Open a mobile report publisher
Step 2: Drag and drop the report elements and add the data for those elements
Step 3: Choose the report element where you need to apply the drill through.
Step 4: Click “Drillthrough target”. You can see two options, 1. Mobile report 2.Custom URL
Step 5: Choose Mobile report option. It will show all your mobile reports in report manager
Step 6: Choose the report which has the parameter on it.
Step 7: Choose the field of the first report as an input parameter for the second report.
Step 8: Save the report and open in report manager.
Step 9: Click the value from the drillthrough column. It will redirect to the second report with filtered value.
To remove or edit the drill through, click the same Drillthrough target.
Additional Information:
There is no drillthrough option available for below report elements