BLOGS

ByHariharan Rajendran

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
ByHariharan Rajendran

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.

ByHariharan Rajendran

SQL Server Drop Database User

This blog post explains the issue that we usually face while dropping the users from the database and how can we resolve the issue.

We can create a user for database easily by any of the following user types,

  1. SQL User with login
  2. SQL User without login
  3. User mapped to a certificate
  4. User mapped to a asymmetric key
  5. Windows User

If we want to drop the user then we can write drop statement but it will throw a below error in case the user owned any of the schemas.

Msg 15138, Level 16, State 1, Line 20

The database principal owns a schema in the database, and cannot be dropped.

To resolve the issue, Follow the below steps.

Step 1: Check the schema that owned by the user.

SELECT *

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘Username’)

Step 2: Change the schema back to dbo.

ALTER AUTHORIZATION ON SCHEMA::<schemaname> TO dbo;

Step 3: Drop the user

DROP USER <username>

ByHariharan Rajendran

SharePoint List to Power BI & Extending Power BI to SharePoint

This article explains to you that how we can use SharePoint list as a source for Power BI and also extending the Power BI report to embed on SharePoint.

I am following the same strategy in all my blogs. Reading a big paragraph is tough than a step by step points as it is easy to follow and remember.

Flow,

Pre-Requisites:

  1. SharePoint List URL
  2. SharePoint Credentials (On-premises or O365)
  3. Power BI Account
  4. Power BI Desktop

Follow the below steps,

Step 1: Create a SharePoint list as per your business needs. It can be simple or complex with calculated columns. In my case, I created a simple SharePoint list with some sample content as like below.

Step 2: Get the SharePoint URL where the list is created.

https://xxxxx.sharepoint.com/Training

Step 3: Open Power BI Desktop and click Get Data.

Step 4: Choose SharePoint Online List, if you use O365. Click Connect

Step 5: Pass the O365 SharePoint URL and credentials.

Step 6: Choose the list and click Load.

Step 7: It will load all the columns from the SharePoint list. Choose the visualization and map with the respective fields.

Step 8: Save and publish the report to Power BI online services

Step 9: Login to app.powerbi.com with your credentials.

Step 10: Open your report from your workspace where you published.

Embed the Report in SharePoint

So far, we created a report using SharePoint list. Now we are going to embed the report in SharePoint.

Follow the below steps.

Step 1: Open the report in app.powerbi.com

Step 2: If we have O365 E series license then we can use “Embed in SharePoint Online (preview)” option otherwise choose the below option.

Step 3: It will ask you to publish.

Step 4: Finally, it will generate URL and iframe code to place on any web page of SharePoint.

Step 5: Go to SharePoint page and edit where you want to embed this report.

Step 6: Click Insert and choose the Embed Code

Step 7: Copy and paste the iframe

Step 8: Save the page.

Share your comments below.

Related Post:

Embed Power BI Report in Web page

ByHariharan Rajendran

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.

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

Share your comments below.

 

ByHariharan Rajendran

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.

ByHariharan Rajendran

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:

  1. Azure Account Subscription with Credit
  2. 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.

ByHariharan Rajendran

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,

Basic Errors in Stretch Database

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