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.
- Visual Studio Data Tools
- Report Builder
Let us see the steps to create a shared dataset in Visual Studio Data Tools.
- Create a Reporting Services Project in Visual Studio
- Create a Shared Data source Connection under Solution Explorer
- Right Click on Shared dataset Folder and choose “Add New Dataset”
- Specify the name of the dataset and choose your data source.
- Under Query Type, you have 3 options. Text, Table & Stored Procedure. In our case, choose Text
- Pass your T-SQL Query. It can be simple or complex queries
- Make sure the query is correct and click Ok.
- Right click on your dataset and click deploy.
- 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,
- 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.
- Once you added the above line, go to parameters section.
- You can see your parameter there as like below or you can add one new parameter using Add option.
- Select the check boxes based on your requirement but you need to pass one default value
- Deploy the dataset.
Share your comments below.
Hide SSRS Report Viewer Toolbar
Usually, when we run a report with report viewer we will see the report output with report viewer toolbar options.
To hide the report viewer toolbar, add the following parameter at the end of your report URL.
&rc:Toolbar=false
Latest URL will be like below,
http://XXXXX/Pages/ReportViewer.aspx?%2fReport+Project2%2fReport3&rs:Command=Render&rc:Toolbar=false
Note: Open the report using report viewer. This is not applicable when you directly open a report from report manager.
Host Your SAP Machine in Azure
This article explains to you that how can you host your SAP Machine in Azure. Let us host SAP BW Machine in Azure here. Same steps should be followed for other SAP machine templates.
Prerequisites:
- Azure Account Subscription with Credit
- Account on SAP Cloud Appliance Library
Follow the below steps to host your SAP BW in Azure,
Step 1: Connect to https://cal.sap.com/catalog#/solutions and filter “SAP BW”
Step 2: Choose the instance and click “Create Instance”
Step 3: Log into SAP CAL
Step 4: Choose “Create a new account” in Account section.
Step 5: Choose the “Microsoft Azure” and get subscription id. Visit portal.azure.com and log in with your azure credentials.
Step 6: Fill the details as like below,
Step 7: Click the “Download New Certificate”. The certificate will be downloaded.
Step 8: Visit the below URL to upload the certificate to Azure management portal.
https://manage.windowsazure.com/
Step 9: Upload the certificate.
Step 10: Go to cal.sap.com URL again and click “Test Connection”. It will open the other details.
Step 11: Fill the Instance details and click “Create”
Step 12: Download the “Private key”. A message window will pop up.
Step 13: Check the system status and wait until to complete the activation.
Step 14: Once Status changed to “Active” then click “Connect”.
Step 15: Click “Connect” RDP. Enter the instance password which you defined.
Step 16: Inside RDP, check the guide and start to use BW.
That’s it. It is simple to host any template from cal.sap in Azure as like this.
Share your comments below.
Basic Errors in Stretch Database – Part 2
Beginners Errors in Stretch Database SQL Server 2016 – Part 2
Check out the first basic error that beginners would experience while configuring stretch database in my previous article,
Another common error that usually occurred during the configuration is “Firewall”. If firewall is not able to identify the client IP address then it will throw an error like below.
Check the log to identify the detailed error message.
Error Message:
Client with IP address XXX.XXX.XX.XX is not allowed to access the server.
To fix the above issue, we have two ways.
Way 1:
Add the client IP in Azure SQL Server firewall.
Follow the below steps to add the client ip.
This will be applicable when you have SQL Server (Logical server) in Azure.
Step 1: Go to the SQL Server in Azure Portal
Step 2: Choose “Firewall”
Step 3: Click + Add Client IP and Save
Way 2:
Use the below stored procedure to add the IP Address in master database to allow the firewall.
sp_set_firewall_rule [@name = ] ‘name’,
[@start_ip_address =] ‘start_ip_address’,
[@end_ip_address =] ‘end_ip_address’
[ ; ]
Refer: https://msdn.microsoft.com/en-us/library/dn270017.aspx
Basic Errors in Stretch Database
This post explains that what are the basic errors that you could encounter during the configuration of stretch database in SQL Server 2016 and how can you overcome those errors.
Check the below error,
Check my article on Technet wiki for a solution for the above issue.
Name mismatch (DataSet1) in SQL Server Mobile Report Publisher
This post explains a very simple error message that we may face if any issue with a shared dataset.
For example, take a below scenario
You have a shared dataset deployed to the report manager portal. The name of the dataset is Sales and you need to build a mobile report using Sales shared dataset.
When you use the Sales Shared Dataset from mobile report publisher, you may get an error message if any issue with the query used in Shared dataset or data source issue. The error message usually will say DataSet1 even though we used the proper name for the shared dataset.
It is because the shared dataset by default considering the name ‘DataSet1’ when you create a dataset. It can be identified and modified if you open the dataset file in notepad.
Have you seen this scenario? Share your comments below.
Embed Power BI Report in Web page
This article explains that how can we show your Power BI report to everyone.
We have variety options in app.powerbi.com for the specific report.
The notable options are,
Publish to Web
Embed in SharePoint Online (preview)
Export to PowerPoint (preview)
Let us discuss “Publish to Web” Option
Open your Power BI report and click File option. You can see the options as like below.
Choose “Publish to Web” and click “Create embed Code”
Then click “Publish”, it will create a code for us.
We can share the link to anyone or else can embed the iframe in any web page or in SharePoint.
Check the report,
https://app.powerbi.com/view?r=eyJrIjoiN2UwNzRmYzYtNTAxYy00NTE1LTkxZTAtYzgyZTk1MzFhYWEyIiwidCI6Ijk2ODY3NjIxLTgxNDAtNGRjYS1iZTliLTMyMTkxMmU0NTY2NCIsImMiOjEwfQ%3D%3D
Sachin Tendulkar’s Cricket Data – Embedded Report
[field name=Iframe]
SQL Query – Network Bandwidth Optimization
This article explains that how can we improve the performance when we access the SQL Query via network.
Scenario: I have taken SQL Server installed on Virtual Machine with 7GB of RAM and accessing the SQL Server database from local SQL Server management studio.
Step 1: Up and Run VM with SQL Server installed.
Step 2: Enable the DNS name of the Virtual Machine.
Step 3: Enable the TCP port with 1433 in portal.
Step 4: Create TCP port inside virtual Machine. Type WF.msc in command prompt. Right click on Inbound Rules and choose New.
Step 5: Make sure SQL Server services are up and running.
Step 6: Enable the SQL Server as mixed mode authentication.
Step 7: Create a new user login and check.
Step 8: Access the database from local SSMS.
Performance Improvement with NitroAccelerator
Step 1:
Install the “NitroAccelerator” in local machine, http://nitrosphere.com/trial/?ref=83095
Step 2: Run the query in local SSMS without Nitro Accelerator.
Step 3: Start the accelerator and test the query performance.
