Friday, September 21, 2018

SSRS - Display data from Hadoop (Hortonworks)


Data Source: First, you need to download the ODBC driver from the Hortonworks add-ons page (http://hortonworks.com/hdp/addons/).

Install and configure the HORTONWORKS ODBC driver on Windows7
The Hortonworks ODBC driver enables you to access data in the Hortonworks Data Platform from Business Intelligence (BI) applications such as SSRS, Microsoft Excel, Tableau, Qlik, Micro Strategy, Cognos, and Business Objects.

You  can see steps into below site:

Once you configure the ODBC driver for same, you can format you connection string in below form:

Data Source(Screenshot from SSRS):

Credentials: Connection will establish only for valid credentials
 

Dataset: (Hadoop Query format) to pull the data from Hadoop HDFS environment for the report:
SELECT   
regexp_replace([ProductID],' "','') AS [ProductID],
regexp_replace([Name],'" ','') AS [Name],
regexp_replace([ProductModel],' "','') AS [ProductModel],
regexp_replace([CultureID],'"  ','') AS [CultureID],
regexp_replace([Description],' "','') AS [Description]
FROM  exlog.main

Dataset Filters: to limit the data for the report
Report Execution:


SSRS – How to implement hovering of data in the report (Mouse Hover Action)


Sometimes, we need to display length text in the report for supporting other attributes e.g.
Column 1 is Attribute Name
Column 2 is the attribute definition of column 1
If this text is just for supporting other attributes in report & not required in any of the exported report then we can utilize the mouse hovering feature of SSRS to show this data, in this way, we can have more space and can show more information in the report. This implementation is useful in dashboard/scorecard reports also where we need to display the summary only.  Below is the sample report where we can see text is taking more space and can be used as hovering data in the report













Steps to implement mouse hovering in the report.
      1)      Right click on the text box where we need implement mouse hover
      2)      Go to text box properties

      3)      Go to Tool tip & map the description column or we can write SSRS expression to map the column


      4)      Now run the report and see the result









                     ----------------------------End of Article-----------------------



Tuesday, September 18, 2018

SSRS – How to give Name to sheet(s) while exporting report to excel


When we export the SSRS report to excel, SSRS export report with its default sheet name e.g Sheet1, Sheet2. Below is the example.
SSRS Report

When we export the same report into excel. It will export in Sheet1 by default.
Exported Excel Report

Steps to give Name to the Sheet(s)
      1)      Go to the property of the tablix by selecting the tablix & press F4 button
      2)      You can see the Page Name property, Give the name that you would to display for sheet(s) while exporting to excel

3)      Now Export the report. You can see the Name of the Page given by you.

This implementation is helpful when you export multiple sheet(s) report. It is helpful in identifying the correct report easily.

--------------------------End of Article-------------------

Friday, September 14, 2018

SSRS Security Model – Role Permission Matrix



Role/Permission Matrix
Following is the Role/Permission matrix for an application that uses SSRS reports for their portal. This matrix consists of 4 roles e.g. Report Admin, Schedule Admin, Report Writer & Report Reader and their associated permission(s).It can be more role(s) based on business requirement.  As SSRS works on Windows Authentication, we can create security group in active directory and assign the role(s) to these security group(s) in this way, we can manage larger of user(s) for Report easily.

Below matrix shows 4 roles e.g. Reader for only viewing the reports, Writer for read/writing/deploying the reports, Schedule Admin for Report Subscription/snapshot/history & Report Admin for system administrative work:
                                                                        
Task
Permissions
Report-Admin
Schedule Admin
Writer
Reader
Consume report
Read Content ,Read Report Definitions & Dataset
Create linked reports
Create Link Reports
Manage all subscriptions
Read, Create Delete & Update Any Subscription
Manage data sources
Create, Delete & Update Content
Manage folders
Create, Delete & Read Folder
Manage individual subscriptions
Read Properties
Create Subscription
Delete Subscription
Read Subscription
Update Subscription
Manage models
Create Model
Manage models

Read, Delete & Update Content
Read, Update Data Sources
Read & Update Model Item Policies
Manage report history
Read Properties
Create, Delete and Execute  Report History
Manage reports
Create Report
Update Parameters
Read & Update  Data Sources
Read & Update Report Definition
Execute Read Policy & Update Policy

Manage resources
Create Delete & Update Content
Set security for individual items
Read & Update Security Policies
View data sources
Read Content & Properties
View folders
Read Execute And View contents

View models
Read Data Sources , Content &Properties
View reports
Read Content/Datasets &Properties
View resources
Read Content/Report Parts/Properties