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]),
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.
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.
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.
Step 3: Filter the dataset with ID. Here I used the second dataset id.
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.
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,
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
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
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
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
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
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
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.
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.
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.
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.
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.
There is no drillthrough option available for below report elements
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.
I have following two parameters in a report.
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.
The same logic can be applied in SSRS mobile report but with different procedure.
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.