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
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.
Latest URL will be like below,
Note: Open the report using report viewer. This is not applicable when you directly open a report from report manager.
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.
Client with IP address XXX.XXX.XX.XX is not allowed to access the server.
To fix the above issue, we have two ways.
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
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’
[ ; ]
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.
Don’t waste your monthly credits and if you have some you can always spin it up with Azure virtual machines to try the SQL Server 2016 RC3 Evaluation on Windows Server 2012 R2.
As you can see in the below Azure portal screen, I am using New => Virtual Machine and then clicked on “See all”, to see all available options and also to search on what I need to search for. The prebuilt images are already available for all of us to try and you can see the highlights that I have made.
Then I got the below screen to confirm on what I was going to create and enable me to create it either with Resource Manager or Classis Deployment Model. In newer version Resource Manager is chosen as default for all deployment models.
In below screen I have given basic options with Name, username, my subscription, resource group, location. Once you have chosen all your settings till 5, then you will get your fully functional SQL Server 2016 RC 3 virtual machine in 10 to 15 minutes. How cool is that.
Following the download of my ISO file from my earlier article, I am going to show you the step by step instructions on installation of SQL Server RC3.
Step 1 is to run the exe file and you get to the Planning tab. Below screenshot, shows the options available in next Installation tab. You can then click on first option which is “New SQL Server stand-alone installation or add features to an existing installation”.
Step 2: Here you have option to evaluate the free edition or enter the product key that you purchased already. Here I chose the Evaluation which is the default screen and then I clicked on next.
Step 3: In the License Terms screen, you have full terms that you can read through to accept the license from Microsoft before start installing.
Step 4: I got error message in the product updates section, as I did not had internet connection in my virtual machine as I had problems in bridging the connection during this setup process. You can click on “Check again” button if you have got the same error screen and still have the internet connection. Then you can click on Next or you can do the product updates later and you can directly click on Next button.
Step 5: Then the system starts installing the necessary setup files and rules. In my below screen you can see the warning messages that I got. As long as it is not failing, we are good to go. So click on Next.
Step 6: Here you will get the options to choose the features. As I am doing main testing on R, SSRS, Polybase, Analysis services, I have ensured to check them all here. You can check/uncheck based on what you need to install.
Step 7: Here I got one task failed for not having Oracle JRE 7 Update 51 to support the polybase installation. After installing this seperately, I was able to continue my installation. Note: till this setup is not installed, the Next button won’t be enabled. Once I have this installation done, I made sure to click on “Re-run” button to test the latest and then this enables me to click on Next button. Here, no need to cancel and then no need to go through all above 6 steps.
Step 8: Then I get the option to configure the name of the instance and I chose it to reflect the RC3 here.
Step 9: As this is the setup in my standalone laptop, I chose the SQL Server as standalone polybase enabled instance.
Step 10: In Server configuration section, it shows all the relevant account names that will be created and the type of startup services (Manual or Automatic). When it is set to automatic, this will then start the services whenever you system is booted. If it is Manual, then you have to start it manually via the services or configuration manager.
Step 11: In the database engine configuration, I have chosen to opt for Mixed Mode to keyin my preferable password, and this also helps me to recover the instance if my current user gone cranky. Also I have added the current user YSMUser1 from the “Add Current User” button. I also have option to choose the data directories, tempdb and filestream.
In here, you also have the new feature of trying to have more than one tempdb and you can get it configured in this place.
Step 12: In Analysis Services Configuration tab, I have chosen to use the Multidimensional and Data Mining server mode and also added the current user to get admin rights.
Step 13: In reporting services configuration, I then have option to choose whether to install and configure or just install only. You still can configure at the later stage, however in regards to Reporting services, I always choose to get it configured as well as you can see below.
Step 14: For the distributed replay controller, I again choose my current user to give the admin permissions to manage it.
Step 15: Then you get to the place where you have option to choose where your controller name, working and result directories should be placed. I have not made any changes here and left it to reflect the default installation folder.
Step 16: This is very new specifically for SQL Server 2016 and you have to accept to download Microsoft R Open and install and also to agree to accept patches and updates for the software. This feature enables us to work on R side from Microsoft itself.
Step 17: I then get this screen to choose the installation path for the Microsoft R Open and Microsoft R Server.
Step 18: I have provided below install path for Microsoft R Open and Microsoft R Server to be installed.
Step 19: This is the last part where you can see the summary of installation on what necessary information you have provided to verify before installation. You still have option to go back to any configuration and make changes and then click on Next.
Step 20: This is the final screen where it says the installation was successful and advise to restart to complete the installation process.
Have good findings in your SQL Server 2016 journey.
You can now download the SQL Server 2016 Release Candidate 3 from TechNet Evaluation Center. In the below screenshot, I have chosen to download it as ISO copy. This will enable me to attach it as disk drive in my Hyper-V Virtual Machine.
Based on few browser plugin issues and local settings, the download may not start immediately. Hence you can click on Manual download again in case if the above chosen ISO file does not download.
I am looking to find if all the previous issues that I faced with previous releases have been fixed in this new Release Candidate 3 or not.
Have a good journey in this SQL Server 2016 as we have now come to the last RC3 before we get the full SQL Server 2016.