SSRS

Data Source Level Security

(Google when to use data source level security) One decision that must be made early on in the report design process is whether the reporting user to be authenticated by SSRS will be a SQL Server Login or a Windows / Active Directory login. This decision is made in BIDS (Visual Studio 2010 using the Business Intelligence Development addin), specifically during the creation of the DataSource for the project, as noted in the below image. for all data objects (ie tables, views, stored procedures) used in a particular report, the user running the report must have appropriate permissions to access the data and its related object Once the decision is made about which authentication method is used, the next step includes granting the appropriate privileges, whether to a windows user or group or a SQL Server login, for all objects involved in the dataset queries for the report. Of course these grants could include execute permissions on a stored procedure or select permissions for a group of tables and views. Windows Authentication center around ease of maintenance and administration of the user whereas the use of the SQL Server login allows for the avoidance of the double hop issue Kerberos Double Hop is a term used to describe the method of maintaining the client's Kerberos authentication credentials over two or more connections. In this fashion, we can retain the user's credentials and act on behalf of the user in further connections to other servers.

What are the different kind of parameters in SSRS

1. Single Value - 2. Multi-Value - 3. Boolean - 4. Date/Time - 5. ? 6. ?

5 Functions that I have used in SSRS?

1. Switch Function - Lets us evaluate a list of expressions and allow us to do things like alternate colors or sort data in a particular order. so that it appears in a certain way in the key. Switch(bool-expr, result1 [, bool-expr-n, result-n]) The arguments are pairs of expression. When the bool-expr is true the result is returned. bool-expr-n is evaluated until one is results in true then the cooresponding result-n expression is returned. 2. DateDiff - Find the age of customers. 3. Today() Return the current date/time on the computer running the report. 4. IsDate - Boolean value to determine whether a column contains a date. Helps me to make sure I am pulling in the correct data type. 5. IIF - Set an expression on a value. Color code reports based on conditions. (Loan Reports)

What is my SSRS Experience?

1. This is probably my favorite part of being a BI professional. Being able to present complex data to someone that is simple to read and efficient to process. Visual data is so vital to a business being able to function properly and the degree to which that data can be processed and translated can really help to make insightful business decisions that will pay off in the long run. My goal when developing report is to create a beautiful report that can be looked at in a matter of seconds and a user be able to know what they are looking at, what is important, and how to dig into the report to find the answers that they need to find with ease. I want to allow the user to get a broad persepctive and a granular perspective. 2. I use KPI's to help give the user the most important information. 3. I also use conditional formatting to make the information much easier to read and personalize the report data to the end users needs. 4. The tools I use to help with granularity are Cascading parameter, drill through reports, and drilldown reports. 5.Give examples of the charts that I have built and give examples of how I used them applying them to my recent job 6. Subscriptions delivered through Sharepoint and Delivery with Daily, Weekly, and Monthly subscriptions -Email -Sharepoint -FTP Server 7. Security (Simple overview) I am familiar with setting up security on reports so that the proper people have the proper roles when accessing the reports. I know how to set Security and Access at the Report Server Level and Authentication and Permission at the data source level. 8. I also have the ability to step in when needed to help create Business Requirements Documents be helping the end users identify the business needs and ask the right questions to the data to find the solutions to those needs. 9. Lastly, I am well versed in troubleshooting and optimizing reports.

How do I deliver reports to end users?

After building the report in the Dev environment, I then test it in a testing environment. Once success have been validated i then look to deploy the report. Before deploying I always make sure the report URL is properly configured for that instance. In my experience the most popular way to deploy a report is using the push method (Event driven) to the report server. In Project Properties right click and then do a report build. THen set the desired properties including the correct server location. I am also well versed in the pull method (on Demand) as well Then go back in and select the deploy option. --- (Come up with my own story) In some cases as my experience with Deliv I had to deploy ad hoc reports per individual managers that only needed a small subset of the reports. In this instance I delivered the reports individually. Using report manager I created separate folders for each report as well as the data sets and the datasources. I then confiured each folder and then created a connection for the data and dataset and source folder. Then pointed the report folder to the location of the report RDL file. Then set up security on a as needed bases --- Restore the report from a backup. Restored the report from the back-up --- Created standard subscriptions When I was working with Best Buy I utilized standard subscriptions for particular reports that needed to go to individual managers. Those reports did not need any particular values altered and they did not have any parameter changes. Permissions - I setup my proper permissions. I make sure the correct report is being accessed by the correct people and the I also setup the proper authorizations on the reports. I may use "manage individual subscriptions task" to create, modify, or delete subscriptions to users. Credentials - Next I set up credentials usually using stored credentials via windows account/authentication, but sometimes I let the user supply their own credentials that I have already set up the proper roles on. At best buy we used Windows Integrated Security so windows credentials were authenticated and passed as such Delivery - Now I decide my delivery method. Usually it is an email directly to the proper end users with a url link. Before I could do that though I needed to open up SQL server configuration tools and properly configure the email settings for the user. I also use a windows file share that allows the folder with the report to be accessed on a network. Data Driven subscriptions Reports that were parameterized and had dynamically valued reports and did need to be delivered to a list of users. They needed the reports to be varied based on the indivdual user. I have dilivered reports through windows file share where I copied the reoprt RPL file for archive purposes. I have also used the sharepoint utility. In this case its a good idea to make sure the sharepoint server is set up correctly for report viewing with the sharepoint delivery extension. I have also dilvered reports through email. You need to make sure the SMTP server is properly configured so that the emails can be stored securley on a site and relay it to the proper email domain. I have also used windows file share for a standard subscirption. I set up the proper user credentials and the proper folder share file path. I have set these subscriptions up to be delivered daily, weekly, monthly, quarterly, and yearly. I have also utilized different outputs such as PDF, Word, adn Excel

What is a cascading parameter?

Cascading parameters provide a way of managing large amounts of report data. With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter. Explain process Provide an example

How do I handle Security in SSRS?

In reporting services I would handle security by assigning roles! Roles assignment is a security policy that allows me to manage which users or groups interact with the reports that I build and deploy. the role assignment has fundamentally 3 parts 1. Securable Item for which you can control access. Examples of securable items include folders, reports, and resources. 2. User or groups that can be authenticated using windows security 3. Role definition - a set of tasks that define operations available in a report server. (These can be customized or I can use the predefined roles) 2 kinds of predefined roles 1. System level -System Admin - Has access to site-wide security, can grant access, and perform site-level operations -System User - Only allowed to view site level data such as jobs and shared folders. 2. Item level -Browser - allows users to run reports and browse folders. Usually used by most end users -Content Manager - allows users to manage and define folders and reports and to grant permissions -Report Builder - Allows users to create report builder reports -Publisher - allows users to deploy/upload reports and create folders -My reports - allows users to create and maintain personal MyReports folders (Content manager roles in specific folders) Role assignments are inherited within the folder hierarchy. The role assignment that is defined for a folder is automatically inherited by all reports, shared data sources, resources, and subfolders contained within that folder. You can override inherited security by defining role assignments for individual items. All parts of the folder hierarchy must be secured by at least one role assignment. A system role assignment does not authorize access to items in the folder hierarchy. System and item security are mutually exclusive. For any given user or group, you might need to create both a system-level and item-level role assignment to provide sufficient access to a report server. Example I recently had an executive director wanting to make changes to a report so I simply assigned the proper roles. -Browser - allows users to run reports and browse folders. -Publisher - allows users to deploy/upload reports and create folders https://msdn.microsoft.com/en-us/library/ms156406.aspx

Formatting Layout of a Report

Interactive size and formatting?

3 less common report functions that I have used

Rectangle Text Box Indicators

Report Builder

Report Builder is again a browser based tool that enables in creation of adhoc reports by end users without really knowing much about the underlying Database Details. (do more research on this and give an example of how I have used this)

Report Manager

Report Manager is the one tool which is used by SSRS Admin/Developer to manage the reports, Data Sources, Roles and Schedules etc. There is a Browser based Report Manager apart from the one available from the Management Studio. (Give a example of how I have used this)

Report Server

Report Server can be defined as the Server which has SQL Server Reporting Services Installed. All the reports are generated and processed before rendering on this Server.

Report Manager vs Report Server

The report server is the central component of a Reporting Services installation. It consists of a pair of core processors plus a collection of special-purpose extensions that handle authentication, data processing, rendering, and delivery operations. Processors are the hub of the report server. Report Manager: Report Manager is a web based tool built using ASP.NET application to view/access SSRS reports. It is not available in SharePoint mode.

Report Level Security

Two main roles, System Administrator and System User are predefined. Assignment to these roles is made by clicking on Site Setting in the upper right corner of the report server site; next click on the Security link from the left menu. Local and active directory groups and users can be assigned to either of these roles; however SQL Server logins cannot. System Admin = Execute Report Definitions Manage Jobs Manage Report Server Properties Manage Roles Manage Shared Schedules Manage Report server security System User = Execute Report definitions (run report without publishing to a report server) View Report server properties View shared schedules 5 Predefined roles in SSRS that can be assigned Browser - allows users to run reports and browse folders. Usually used by most end users Content Manager - allows users to manage and define folders and reports and to grant permissions Report Builder - Allows users to create report builder reports Publisher - allows users to deploy/upload reports and create folders My reports - allows users to create and maintain personal MyReports folders most day to day users will only need the Browser role. Most high level power users will need to be assigned to either the Content Manager or Publisher role. Some additional attention needs to directed to how permissions are inherited by subfolders and reports. First, the role assignments use a waterfall methodology. Thus, starting at the home page, all folders, subfolders, or reports underneath the home page in the hierarchy will have the same permission that are assigned to the home page UNLESS the permission chain is broken, either at the folder, subfolder, or report level. Clicking on the Edit Item Security button, as displayed below, will break the permission chain and allow for the customization of the security for that individual folder or report. In addition to using the predefined roles in SSRS, customized roles can be created and used.

What is Kerberos?

http://www.roguelynn.com/words/explain-like-im-5-kerberos/

More Great interview questions

https://msbiskills.com/tag/difference-between-report-server-and-report-manager/

How have I delivered reports to end users?

https://www.simple-talk.com/sql/reporting-services/sql-server-reporting-services-basics-deploying-reports/ https://msdn.microsoft.com/en-us/library/ms159762.aspx