SSRS

Power BI Report using Shared Dataset [Detailed Step by Step]

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.

Access Report Manager outside of Network

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.

SSRS Security Roles

SQL Server Reporting Services has pre-defined roles for security.

Roles are,

• Browser
• Content Manager
• My Reports
• Publisher
• Report Builder

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.

SSRS Mobile Report URL

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,

1. Can pass ALL to all the parameters
2. Other than ALL, only single value is supported to other parameters

https://<report manager>/<folder>/<report name>?<datasetname>.@<parametername1>=ALL<datasetname>.@<parametername2>=value1

SSRS 2016 Branding

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.

DrillThrough in SSRS Mobile Reports

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:

1. Mobile Report Publisher
2. Mobile report with parameterized shared dataset

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

1. Pie Chart
2. Funnel Chart
3. Tree Map

SSRS Mobile Report with Cascading Parameter

Configuring a cascading parameter for SSRS report is easy. We used to play with multiple datasets. To make it simple, if we have two parameters in a report, the second parameter values are based on first parameter selected value.

Example,

I have following two parameters in a report.

1. Country
2. City

Country Parameter will show all the available country and city parameter will show all the cities irrespective of country. In a business scenario, this needs to be fixed as users will expect to see only cities relevant to the countries.

If user Selects India, then the city should show only the cities from India.

Check out my below post for cascading parameter in SSRS report.

Cascading Parameter in SSRS

The same logic can be applied in SSRS mobile report but with different procedure.

Pre-Requisites:

1. Shared Dataset
2. Mobile Report Publisher

In this scenario, I am taking AdventureWorksDW database for better understanding.

Follow the below steps,

Step 1: Create a shared Dataset and deploy to report manager.

Step 2: Open Mobile report publisher and add the shared dataset. In this case dataset from AdventureWorksDW database.

Step 3: Drag and Drop two selection list. You can maximise or minimise to show only one value at a time as like below.

Step 4: Go to Data and Add data. Here I added a shared dataset which contains Product Category Name and Product Subcategory Name fields.

 Step 5: Rename the Selection list 1 to Category and selection list 2 to Sub Category

Step 6: Choose our dataset for both the selection lists. Make sure to choose Category Key and label for selection list 1 and Subcategory Key and Label for selection list 2.

Step 7: Add the checkbox on “Filter these dataset when selection is made”. Choose the Product Category.

Step 8: Click the options on subcategory data, choose Category check box under “Filtered by”

Step 9: By default “Allow select all” option is ON, now off for both the selection list.

Step 10: Preview the report. You can check the sub category shows only Bike information. If we choose some other value in category then it will show respective sub category results.

SSRS Shared Dataset with Parameter

As you know, a dataset is the direct input component for SQL Server Reporting Services. These datasets are part of rdl files. Reports can use the datasets as an embedded dataset or shared dataset.

But in a real time, we mostly used embedded dataset for reports. Now, shared dataset is the mandatory option for SQL Server 2016 Reporting Services mobile reports.

There are plenty of articles available to show how to use the Shared Dataset for mobile report publisher. This article explains to you that how to create a shared dataset.

Shared dataset can be created any of the following tools.

1. Visual Studio Data Tools
2. Report Builder

Let us see the steps to create a shared dataset in Visual Studio Data Tools.

1. Create a Reporting Services Project in Visual Studio
2. Create a Shared Data source Connection under Solution Explorer
3. Right Click on Shared dataset Folder and choose “Add New Dataset”
4. Specify the name of the dataset and choose your data source.
5. Under Query Type, you have 3 options. Text, Table & Stored Procedure. In our case, choose Text
6. Pass your T-SQL Query. It can be simple or complex queries
7. Make sure the query is correct and click Ok.
8. Right click on your dataset and click deploy.
9. Check the deployed dataset in datasets folder in report manager URL.

Let us discuss another scenario.

Scenario: Need to pass the parameter in shared dataset.

Follow the below steps,

1. Add the parameter in your T-SQL Query as like below,

Where <columnname> = @<parametername>

In the above syntax, you can pass any condition as like regular T-SQL script. Instead of value pass parameter name with @ symbol.

2. Once you added the above line, go to parameters section.
3. You can see your parameter there as like below or you can add one new parameter using Add option.
4. Select the check boxes based on your requirement but you need to pass one default value
5. Deploy the dataset.

Share your comments below.