Pages

Tuesday 12 January 2016

MDX REAL TIME INTERVIEW QUESTIONS AND ANSWERS

MDX:

1. MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language, Explain
MDX in SQL server 2005 Analysis services offers CASE and SCOPE statements. CASE returns specific values based upon its comparison of an expression to a set of simple expressions.

2. Explain about cross join in mdx?

3. How can we do Process Update through AMO?

4. How can identify, how many DB’s are in SQL Server through AMO?

5. What is the difference between a derived measure and a calculated measure?

6. What MDX functions do you most commonly use?

7. How in MDX query can I get top 3 sales years based on order quantity?

8. You want to use a perspective in an MDX query. How do you select the perspective?

9. What is Multi Dimensional Expressions?

10. What is MDX query and SSAS?


SSAS (COMMON QUESTIONS)

1)                  What is difference between Process Add and Process Update?

2)            What is the main use of attribute relationship?

3)            If we use process update, how ssas database identify particular record will be update?

4)            What are the storage modes? How we describe that?

5)            What is a RAGGED hierarchy?

6)            What are the roles of an Analysis Services Information Worker?

7)            What are the different ways of creating Aggregations?  

8)            What is WriteBack? What are the pre-conditions?

9)            What is processing?

10)          Name few Business Analysis Enhancements for SSAS?

11)          Where do you put calculated members?

12)          How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

13)          How do you extract first tuple from the set?

14)          Symbol philosophy?

15)          What is a measure group?

16)          User wants only to display only pdf as export option in report Manager. How to acheive this?

17)          What type of objects can u have in a DSV?

18)          How do you implement security in SSAS?

19)          If there are 5 facts and 50 dimensions tables tell me various scenarios who u link that and which is best method? How will u analyze which method will be perfect how u choose on which criteria’s?

20)          What is SMDL?

21)          Could you explain one task which you struggle a lot in your current project? And how did you achieve that task?


SSAS REAL TIME INTERVIEW QUESTIONS AND ANSWERS

SSAS
1. What is new with the Analysis Services (SSAS) in SQL Server 2005?
SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality through a combination of server and client technologies, further reinforced through the use of a specialized development and management environment coupled with a well-defined object model for designing, creating, deploying, and maintaining business intelligence applications. The server component of Analysis Services is implemented as a Microsoft Windows service. Clients communicate with Analysis Services using the public standard XML for Analysis (XMLA), a SOAP-based protocol. Let us see the enhancements of made to SSAS.
•         Supports up to 16 instances of Analysis Services Service.
•         As discussed above, the Analysis Services service fully implements the XML for Analysis (XMLA) 1.1 specification. All communication with an instance of Analysis Services is handled through XMLA commands in SOAP messages.

2. How to schedule SSAS Database backup.
Steps to schedule SSAS database backup:
•             Open SSMS
•             Right click the Job folder of the SQL Agent node and choose New job.
•             Enter a job name.
•             Go to steps page and click New button.
•             Enter a step name and select SSAS command as job type.
•             Enter the Server name and the following command
•            
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>DatabaseName</DatabaseID>
</Object>
<File> DatabaseName.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<Password>password</Password>  </Backup>
3. What is SQL Server 2005 Analysis Services (SSAS)?
SSAS gives the business data an integrated view. This integrated view is provided by combining online analytical processing (OLAP) and data mining functionality................

4. What are the new features with SQL Server 2005 Analysis Services (SSAS)?
It offers interoperability with Microsoft office 2007. It eases data mining by offering better data mining algorithms and enables better predictive analysis...................

5. Explain the purpose of synchronization feature provided in Analysis Services 2005.
Synchronization feature is used to copy database from one source server to a destination server. While the synchronization is in progress, users can still browse the cubes.................

6. What is slicing and dicing in business objects?
Slicing and dicing of business objects is used for a detailed analysis of the data. It allows changing the position of data by interchanging rows and columns.............

7. Data Sources
Data source is where the data comes from in data warehousing. The data collected from various sources and is cleaned.............

8. Fact Tables
Data in a warehouse comes from the transactions. Fact table in a data warehouse consists of facts and/or measures. The nature of data in a fact table is usually numerical. e.g. If I want to know the number..............

9. Database roles
Database level roles are used to manage the security of the database. The role can be either fixed or flexible...............

10. What are fact tables?
     A fact table is a table that contains summarized numerical (facts) and historical data.
This fact table has a foreign key-primary key relation with a dimension table. the fact table maintains the information in 3rd normal form.

11. Types of facts?
          1. Additive:-able to add the facts along with all the dimensions
               -discrete numerical measures.
               -Ex:-retail sales in $
          2. Semi additive:-snapshot taken at a point in time
              - Measure of intensity
              -not additive along time dimensions
              ex:-account balance, inventory balance
         3.non-addItive:-numerical measures that can't be added across any dimensions.
               -Intensity measure arranged across all dimension
               ex:-room temperatures, averages.

12.  Data warehouse?
          A data ware house is a collection of data marts representing historical data from diff operational data sources (OLTP).
The data from these OLTP are structured and optimized for querying and data analysis in a data warehouse.

13.  Data mart?
          A data mart is a subset of a data warehouse that can provide data for reporting and analysis      on a section, unit or a department like sales dept, hr dept.

14.  Diff   b/w star schema and snowflake?

          STAR SCHEMA                                                                         SNOWFLAKE
_________________________________________________________________________
1.centrally located fact table                                       1.centraly located fact table
surrounded by de normalized                                    surrounded by the normalized
Dimensions.                                                                       Dimension table.
2. All dimensions will be link                                        2.all dim link with each other (or)
directly with fact table.                                                  1- N relationship with other table.
3. It is easy to understand by                                      3.It is diff to understand.
End user or tech people                                                               
4.We can easily retrieve data parsing                      4.It is diff to retrieve the data while
the query against the facts n dim
By passing the simple queries.                                  
5. Increase the query                                                     5.more joins.
performance because It involve less
Joins.

15. What are Star Schema and Snowflake schema? Which is better ?
     Star Schema:
             The star schema is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables.
Example of a star schema would be Sales Fact Table with foreign key id’s such as Product id and Location id. Product Table would be the dimension table with the attributes of the product in that table. Location Master would be the other dimension table .
   Snowflake Schema:
            The snowflake schema is a variation of the star schema used in a data warehouse.

            The snowflake schema (sometimes called snowflake join schema) is a more complex schema than the star schema because the tables which describe the dimensions are normalized.
            Example of snow flake Schema: Using the above example mentioned for star schema if product master is further normalized to two tables. For example product master would be one table contain product category id .The product category master would have the product id as the foreign key and product category id as the primary key.
             Star schema is better for data warehousing and snowflake schema is better for transaction systems as in star schema the data retrieval is faster due to lesser joins and snowflake reduces redundancy and better suited for Transactions.

16. Staging area?
              It is a temporary data storage location, where various data t/r activities take place.

17. Fact grain(granularity)?
          The grain of fact is defined as the level at which the fact information is stored in a fact table.

18. What is a fact less fact table?
            The fact table which does not contain facts is called as fact table.
            Generally when we need to combine two data marts, then one data mart will have a fact less fact table and other one with common fact table.

19. Which port no ssas uses?

20. What is the difference between SQL Server 2005 and 2008?

21. Features and concepts of Analysis Services

22. What is Analysis service repository?

23. What is Analysis Services? List out the features?

24. What is UDM? Its significance in SSAS?

25. What is the need for SSAS component?

26. Explain the TWO-Tier Architecture of SSAS?

27. What are the components of SSAS?

28. What is FASMI ?

29. What languages are used in SSAS ?

30. What are Data Sources and Data Source Views?
31. What is a named Calculation?

32. What is a named Query?

33. What is a metadata?

34. What is Many-Many relationship in SSAS?

35. What is the difference between SSAS 2005 and SSAS2008?

36. What is star, snowflake and star flake schema?

37. What is SSAS processing task ?

38. What is Performance Tuning ?

39. Difference b/w SSAS and AS 2000.

40. What is the difference between Star schema and Snowflake schema?

41. What is star schema explain in real time which u done in your project?

42. What languages are used in SSAS?

43. Tell me some scenarios in your project where u use star schema and how u implemented it?
44. Tell me scenarios how u used snowflake schema in your project?

43. How Primary keys and foreign keys are linked amongst tables in Star schema consider employee table and explain?

OLAP & OLTP
1. OLAP database objects
The following are the OLAP database objects:
Cubes: Data in cubes are persisted in a summarized version that helps to analyze data quickly. The data is persisted, through which reporting can be done easily.................

2. Explain the concepts and capabilities of OLAP.
Online analytical processing performs analysis of business data and provides the ability to perform complex calculations on usually low volumes of data. OLAP helps the user gain an insight on the data.............

3. Explain the functionality of OLAP.
Multidimensional analysis:- OLAP helps the user gain an insight on the data coming from different sources. OLAP helps faster execution of complex analytical and ad-hoc queries...............

4. What are MOLAP and ROLAP?
Multidimensional Online Analytical Processing and Relational Online Analytical Processing are tools used in analysis of data which is multidimensional..................

5. What is Hybrid OLAP?
In a Hybrid OLAP, the database gets divided into relational and specialized storage. Specialized data storage is for data with fewer details while relational storage can be used for large amount of data..............

6. Explain the shared features of OLAP.
OLAP product by default is read only. If multiple access rights are required, admin needs to make necessary changes..................

7. Compare Data Warehouse database and OLTP database.
Data Warehouse is used for business measures cannot be used to cater real time business needs of the organization and is optimized for lot of data, unpredictable queries. On the other hand, OLTP database is for.................

8. Diff b/w OLTP AND OLAP?

             OLTP                                                                              OLAP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1.transactional processing                                            1.query processing
2.time sensitive                                                                2.history oriented
3. Operator & clerks view                                             3.Managers, CEOs, PM’s views
4. Organized by transaction                                         4.organized by subjects
(Order, input, inventory)                                              (product, customer)
5.relatively smaller DB                                   5.large DB size
6.volatile data                                                                    6.non-volatile
7.stores all data                                                                7.stores relevant data
8. Not flexible                                                    8.flexible

9. What is the difference between ETL tool and OLAP tool?
ETL is the process of Extracting, loading and transforming data into meaningful form. This data can be used by the OLAP tool for to visualize data in different forms. ETL tools also perform some cleaning of data..................

10. What is the difference between OLAP and DSS?
Data driven Decision support system is used to access and manipulate data. Data Driven DSS in conjunction with On line Analytical Processing.....

11. What is OLAP?
         OLAP stands for online analytical processing. It uses databases tables (fact and dimension table) to enable multi dimensional viewing, analysis and querying of large amount of data.

12. What is OLTP?
         OLTP stands for online transactional processing. Except data warehouse databases the other databases are OLTP.
These OLTP uses normalized schema structure. These OLTP databases are designed for recording the daily operations and transactions of a business.

13. What are the fixed measure and calculated measure?
Normally we used fixed measures in SSIS mainly for calculating measures.
Where as calculated measures uses in SSAS, while creating cube we can mention this cmeasure in the OLAP.

14. What are measures?
a) Measures are numeric data based on columns in a fact table.

15. How do you improve performance of a SSAS OLAP DB?

16. What is the diff b/w OLAP AND OLTP?

17. What are various storage modes of OLAP?



Cubes:

Cubes   A data cube stores data in a summarized version which helps in a faster analysis of data. The data is stored in such a way that it allows reporting easily................

1. What are cubes?
             Cubes are data processing units composed of fact tables and dimensions from the data warehouse. They provided multidimensional analysis.

2. What are virtual cubes?
              These are combination of one or more real cubes and require no disk space to store them. They store only definition and not the data.

3. After process the cube, if I want to see the data in iexplore then what can I do?

4. How Cubes are implemented in SSAS?

5. While creating a new calculated member in a cube what is the use of property called non-empty behavior?

1.       What is your approach when you need to do changes in the cube on customer request?

7. How many cubes your product has?

8. Explain your cube in detail.

9. What are the types of cube processing?

10. What are the basic things that you consider before designing a cube?

11. What are various tabs in cube?

12. What is Perspective Cube?

13. What is CUBE and why we implement it and how it uses in your project explain in brief with real-time example u performed in your project?

14. How can identify, how many measures are in cubes through AMO?














KPIS:

1. What are perspectives?

2. Explain about calculated members and drill through actions?

Partitions:
1. What is a partition?

2. Why we are creating partitions? Is it improves performance?

Dimensions:
1. What are dimensions?
        Dimensions are categories by which summarized data can be viewed. For example a profIt summary fact table can be viewed by a time dimension.

2. What are conformed dimension?
           The dimensions which are reusable and fixed in nature. Example: customer, time, geography   dimensions.

3. Confirmed Dimension?

4. What is Degenerated dimensional table?

5. What are role-playing dimensions?

6. What are the different relationships used in dimension usage?

7. How do you process a single partition without processing other dimensions and measures in production?

8. What is fact-less-fact dimension? When do you use such dimension?

9. How can I setup default dimension member in Calculation script?
















SSRS REAL TIME INTERVIEW QUESTIONS AND ANSWERS

SSRS
1.       What is SSRS?
SQL Server Reporting Service is one of the server-based software systems that generate reports developed by Microsoft. It is used for preparing and delivering interactive and variety of printed reports. It is administered through an interface that is web based. Reporting services utilizes a web service interface for supporting and developing of customized reporting applications. It can be competed with Crystal Reports and other business intelligent tools.

2.       Explain the architecture of SQL Reporting Service?
Reporting Services runs as a middle-tier server, as part of your existing server architecture.
SQL Server 2000/2005 should be installed for the database server, and Internet Information Services 6.0 as a Web server.
The report server engine takes in report definitions, locates the corresponding data, and produces the reports.
Interaction with the engine can be done through the Web-based Report Manager, which also lets you manage refresh schedules and notifications.
End users view the report in a Web browser, and can export it to PDF, XML, or Excel.
Reporting Services has a quite complex architecture. The full Reporting Services architecture includes development tools, administration tools, and report viewers. There are a number of ways to get to Reporting Services programmatically, including SOAP and WMI interfaces.
Report Server is the core engine that drives Reporting Services.
Report Manager is a Web-based administrative interface for Reporting Services.
Report Designer is a developer tool for building complex reports.
Report Builder is a simplified end-user tool for building reports.
The Report Server database stores report definitions. Reports themselves can make use of data from many different data sources.

3.       Describe SQL Server Reporting Services lifecycle. ?
The phases of the Reporting Life Cycle involve:
• Report authoring
This stage involves creation of reports that are published using the Report Definition language. RDL is an XML based industry standard for defining reports.
• Report management
This involves managing the published reports as a part of the web service. The reports are cached for consistency and performance. They can be executed whenever demanded or can be scheduled and executed.
In short Report Management includes:
- Organizing reports and data sources,
- Scheduling report execution and delivery, and
- Tracking reporting history.
• Report delivery
Reports can be delivered to the consumers either on their demand or based on an event. Then they can view them is a web-based format.
• Report security.
It is important to protect reports as well as the report resources. Therefore, Reporting Services implement a flexible, role-based security model.

4.       What are the ways to tune Reporting Services?
Following are a few ways you can tune up Reporting Services:
You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.
You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.
The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads.


5.       What is new with the Reporting services in SQL server 2005?
SQL Server 2005 Reporting Services is a key component of SQL Server 2005 that provides customers with an enterprise-capable reporting platform. This comprehensive environment is used for authoring, managing, and delivering reports to the entire organization. SQL Server 2005 reporting services have some major changes when compared with the previous version.
•         Changes to the core functionality of the Reporting services in the design of the report, processing, and interactivity
•         Better Integration with other components – Enhanced integration with other components within SQL Server 2005 like SSIS, SSAS and SQL Server Management studio
•         Report Builder – A new reporting tool that enables business users to create their own reports.

6.       What new data source types were added in SSRS 2005?
In addition to the data source types available in SSRS 2000 (SQL Server, Oracle, ODBC, OLE DB), the following have been added in SSRS 2005:
•             SQL Server 2005 Analysis Services
•             SQL Server 2005 Integration Services
•             SQL Server 2005 Report Builder Models
•             XML (thro ugh URL and Web services)
•             SAP (available through download)

7.       What are the functional & architectural differences between business objects and Web Intelligence Reports?
Functional differences - Business objects, for building or accessing reports, needs to be installed on every pc. On the other hand, Web intelligence reports needs a browser and a URL of the server from where Business...................
Explain the Dashboard in the business intelligence.
A dashboard in business intellgence allows huge data and reports to be read in a single graphical interface. They help in making faster decisions by replying on measurable data seen at a glance.

8.       What new data source types were added in SSRS 2012?
In addition to the data source types available in SSRS 2005 (SQL Server, Oracle, ODBC, OLE DB), the following have been added in SSRS 2008: SQL Server 2005 Analysis Services SQL Server 2005 Integration Services SQL Server 2005 Report Builder Models XML (through URL and Web services) SAP

9.       Will running SSRS on Windows XP limit the number of users?
Yes, but not because of SSRS. The Internet Information Services (IIS) component of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS, this would prevent more than a few people from using SSRS at once. Also, the only edition of SSRS that will install on Windows XP is the Developer Edition. This edition can not be used for production use. You need Standard or Enterprise Edition for production use, which requires a Server OS to install on (Windows 2003 Standard, Windows 2008 Standard, etc).

10.   How has the integration between Reporting Services and Analysis Services improved since the release of SQL Server 2000?
SSRS 2005 has added the following enhancements:
•             A graphical MDX query designer embedded in the report design tool
•             Report server support for server-calculated aggregates
•             Report server support for server-supplied extended properties such as formatting, fonts, and color
•             Automatic generation of valid-values queries from the parameters in the base MDX query
•             Automatic generation of Report Builder semantic models from Analysis Services databases
•             Drill-to-report drillthrough actions in Analysis Services
11.   What’s the use of custom fields in report?


12.   What can SQL Server Reporting Services do?
With Reporting Services,
•             You can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
•             You can publish reports, schedule report processing, or access reports on-demand.
•             You can create ad hoc reports based on predefined models and interactively explore data within the model.
•             You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports.
•             You can view the reports created over a Web-based connection or as part of Windows application.

13.   What tools can I use to manage SSRS 2005?
Report Manager, the Web-based management tool, is still available in SSRS 2005. In additions, all of the Report Manager functionality is now available in SQL Server 2000 Management Studio, a Windows-based console that gives you a single place to manage all components of SQL Server. Finally, a new Reporting Services Configuration tool allows you to manage various aspects of your report server, including service identities and database connections.

14.   How have you addressed the problem of installing on a nondefault Web site?
SSRS 2005 provides an option in setup to install but not configure the report server. It also provides the Reporting Services Configuration tool that lets you create a virtual directory on any Web site.

15.   Can you import Microsoft Excel data to SSRS?
Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. I will assume that you're asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You'll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.



16.    Do I need Visual Studio 2005 to design reports?
No. Unlike SSRS 2000, which required users to purchase Visual Studio 2003, SQL Server 2005 comes with Business Intelligence Development Studio, which provides the Visual Studio shell and the business intelligence projects including Report Designer and Report Builder Model Designer. If users already have Visual Studio 2005, these projects are installed along with language projects.

17.   What is Report Designer?
Report Designer is a collection of graphical query and design tools that are hosted within the Microsoft Visual Studio environment.
Report Designer provides a Report Data pane to organize data used in your report, and tabbed views for Design and Preview so that you can design a report interactively.
Report Designer also provides query designers to help specify data to retrieve from data sources and the Expression dialog to specify report data to use in the report layout.
When the report is complete, you can use Report Designer to preview the report and publish it directly to the report server.

18. Where can u publish the report?
a) By using report designer or publish reports on report server.

19. Describe different Processing Modes offered by SSRS?
Local Processing Mode:
Processes reports in the client application.
Remote Processing Mode:
Renders server reports that are processed on a SQL Server Reporting Services report server.

20. What are the Reporting Services components?
Reporting services components assist in development. These processing components include some tools that are used to create, manage and view reports. A report designer is used to create the reports. a report sever is used to execute and distribute reports. a report manager is used to manage the report server.

21. Why should you not overwrite a shared data source in production?
You should not overwrite a production-shared data source because the administrator has probably already changed some connection string properties.

22. What mechanisms do you identify to reduce the overhead of Reporting Services data sources?
Snapshots and cached reports can help reduce the processing pressure on data sources and improve report response time.

23. What are different types of roles provided by SSRS?              
1.      Browser
2.      Content Manager
3.      My Reports
4.      Publisher
5.      Report Builder        

21.   How would you go about developing a SSRS report?
General development methodology for a SSRS report is to start by creating a data source. Based on the data source create one or multiple datasets as needed for parameters and the body of the report. Add required controls from the toolbox which would act as a container for the fields in the dataset. Format the controls added to the report body.  Verify and validate the report and finally deploy the report.

22.   What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.

23.   Can we assign multiple data sets to a single data region?

24.   What is a parameterized dataset?

25.   Would you store your query in a SSRS report or a Database server? State the reason why?
Storing SQL queries directly in text format in the dataset, should be avoided. Ideally it should be stored in a stored procedure in the database server. The benefit is that the SQL would be in a compiled format in a SP and brings all the benefits of using an SP compared to using an ad-hoc query from the report.

26.   What is Data Source Definition?
 Every report should have atleast one data source definition that explains how to connect to a data source.

27.   Name a few data sources supported by SSRS 2008.
 Microsoft SQL Server, Microsoft SQL Server Analysis Services, Oracle, SAP NetWeaver BI, Hyperion Essbase, Teradata, OLE DB, ODBC, and XML.

28.   What is a Query Designer in SSRS 2008?
 Report needs a dataset to report the information, to retrieve this dataset from database, typically you need a query. A query designer is the tool in SSRS that helps to design this query.

29.   What is a shared data source in SSRS 2008?
 If a data source is referenced by more than one report or model or data driven subscription, it is called shared data source.



30.   What are advantages of shared data source?
 According to MSDN online, Shared data sources provide an easy way to manage data source properties that often change over time. If a user account or password changes, or if you move the database to a different server, you can update the connection information in one place.

31.   What all does shared data source contain?
 Name, Description, Connection type, Connection string, Credential type.
What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML

32.   How can you create the dynamaic Datasource. Can a Shared Datasource be Dynamic?

33.   Difference between Logical Page an Physical Page in SSRS.
Logical page breaks are page breaks that you insert before or after report items or groups. Page breaks help to determine how the content is fitted to a report page for optimal viewing when rendering or exporting the report.
The following rules apply when rendering logical page breaks:
Logical page breaks are ignored for report items that are constantly hidden and for report items where the visibility is controlled by clicking another report item.
Logical page breaks are applied on conditionally visible items if they are currently visible at the time the report is rendered.
Space is preserved between the report item with the logical page break and its peer report items.
Logical page breaks that are inserted before a report item push the report item down to the next page. The report item is rendered at the top of the next page.
Logical page breaks defined on items in table or matrix cells are not kept. This does not apply to items in lists.

34.   What are data regions?
Data regions are report items that display repeated rows of summarized information from datasets.

35.   What are the different kinds of SSRS Reports?
Reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report. Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized, linked, snapshot, cached, etc...

36.   What are various ways to enhance the SSRS report? Explain.

37.   How to send a SSRS report from SSIS?
Steps to send a SSRS report from SSIS:
•             Create a subscription to the report from Report Manager.
•             At subscription level, mention the report format and email address of the recipient user. When a schedule is created for SSRS report, an SQL Server agent job is created.
•             Execute the SSRS report subscription using sp_start_job with the appropriate report name.
38.   How do you configure a running aggregate in SSRS?
You can use the Running Value function to configure a running aggregate.

39.   How to configure SSRS for Disaster recovery?
Depends on daily backup technique.

40.   How do we write custom code in reports? and ways? and how will you call them in reports?

41.   How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will find the options for custom code.

42.   What are the three SSRS command-line utilities and their primary functions?
RSConfig.exe is used to define the connection properties from the SSRS instance to the Report Server database; RSKeyMgmt.exe performs encryption key operations and scale-out deployment setup; RS.exe runs Report Server Script files that can perform report deployment and management operations.

43.   Name few Endpoints exposed by SSRS 2012?
Name few Endpoints exposed by SSRs 2008.
 Management Endpoints
Execution Endpoint
 SharePoint Proxy Endpoints

44.   How can you access the Code in You report. Describe the Static and intance based method with example.
Static methods within a custom assembly are available globally within the report. You can access static methods in expressions by namespace, class, and method name
The following example calls the method ToGBP, which converts the StandardCost field value from dollar to pounds sterling:
=CurrencyConversion.DollarCurrencyConversion.ToGBP(Fields!StandardCost.Value)
Instance-based methods are available through a globally defined Code member. You access these by referring to the Code member, and then the instance and method name. The following example calls the instance method ToEUR which converts the StandardCost field value from dollar to euro:
=Code.m_myDollarCoversion.ToEUR(Fields!StandardCost.Value)

45.   Name and Describe few console utilities for SSRS.
RSConfig.exe             Configuration of connection properties between the Report Server to the repository database.
RSKeyMgmt.exe              Management of encryption keys via command-line
RS.exe  Scripting of report deployment

50. How to add custom Assemblies to Report.
1.      On the Report menu, click Report Properties
2.       On the References tab, do the following:
a.       In References, click the add (...) button and then select or browse to the assembly from the Add Reference dialog box.
b.       In Classes, type name of the class and provide an instance name to use within the report.
3.       http://msdn.microsoft.com/en-us/library/ms252130(VS.80).aspx
Modify SessionTimeout and SessionAccessTimeout system properties.
Here is a sample script for rs.exe which will set these values for you:
Public Sub Main()
    Dim props() as [Property]
    props = new [Property] () { new [Property](), new [Property]() }

    props(0).Name = "SessionTimeout"
    props(0).Value = timeout

    props(1).Name = "SessionAccessTimeout"
    props(1).Value = timeout

    rs.SetSystemProperties(props)
End Sub
You can run this script with the following command:
rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout="6000"The timeout is expressed in seconds, so this example sets the SessionTimeout and SessionAccessTimeouts to about an hour and a half.

51. Which of the following are valid options for deploying a report? (Choose all that apply.)
 a. With BIDS
b. With the computer management console
c. With the .nET START command
d. WITH SSMS
e. With Report Manager
the correct answers are a and e, BIDS and Report Manager.

52. How to deploy an SSRS Report?
 Configure project properties (for multiple environments) and deploy from bids, upload manually or use rs.exe for command line deployment.

53. Have you used the Report Viewer control / web part? What are the limitations?
The report viewer control / web part is the basic control to integrate SSRS reports with external applications. Using these interface applications can link and display SSRS reports within the application. The parameters toolbar in these controls have a limited programmable interface to decorate and blend it with the theme of the application.

54. What is the file name extension for an encryption key backup?
Encryption key backups have an .snk file name extension.

55. When storing the credentials of a data source in the server, are those credentials safe?
Yes, the data source credentials are safe because Reporting Services encrypts them and stores them in the Report Server SQL Server database.

56. What Factors need to take into consideration while designing a international report (Localization).

57. What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.

58. How to find slow running reports?
 Check Report Execution table.

59. What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all report server component and also get the report deployed on report server.

60. Can you use data mining models in SSRS?
Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.

61. What are the drawbacks of reporting in SSRS?
For many years, Microsoft had no direct solution for reporting with the SQL Server besides Crystal Reports. Now, they have SQL Server Reporting Services, but it does have several drawbacks. It is still complex to understand the complete functionality and structure of this new component, and many users are still relying on the reporting application they are more familiar with, which is Crystal Reports. Also, components in SSRS like Report Builder and Report Designer are meant for different users for different aspects of the report process, yet complete understanding and exposure to both is important to utilize both functions fully and extensively. There are also issues when exporting very large reports to Microsoft Excel, as it can lead to a loss of data.

62. Are there issues when exporting SSRS reports into Microsoft Excel?
When my users are trying to export a SSRS report into Microsoft Excel, one or two columns in the report appear to merge together. Why might this be? Exporting from SSRS is not always perfect, even if you stay within the Microsoft range of products. If you have extra resources, you could splurge for an add-on that offers much better control over exporting to Excel, such as Office Writer. From my experience, though, it is usually headers or footers that cause exporting issues. If any of these headers or footers overlap with data columns in your report, you will find that the exported version of the report has merged cells. Also, check columns next to each other to make sure that there is no overlap, as well.

63. for generating reports which is used like RDBMS OR CUBE?
a) Depends on data

64. How do you integrate the SSRS reports in your application?

65. How can u create a job?
a) Job is a group of reports.
The reports can be processed either sequentially or parallel.
66. What is Batch?
a) A batch is defined as group of sessions. Those are 2 types.
1. Parallel batch processing
2. Sequential batch processing
(OR)
Batch processing can be used to schedule reports. Objects can be also be used for batch processing. Batch processing can be used to also select the objects to be processed................

67. How do you provide different color to alternate records?

68. Have you used cubes in your project?

69. How many collections available in report? What are they?

70. How do you get row by row group in 2008?

71. How do you implement page breaking for Metrix reports?

72. Do you have any idea about markers in ssrs?

73. How can we implement optional parameters in ssrs?

74. We are getting blank pages in report after exporting to PDF, how do you avoid?

75. Is there any option to hide the parameter(Filter) based on conditions?

76. A report is performing poorly. What steps you would take to troubleshoot the Issue.

77. What are the reports did you create in your reporting project?

78. What is the use of SSRS and where is it used mostly?

79. What needs to be done to get row numbers?

80. How u can group the table data?

81. How can u navigate to different reports within a project using a text box?

82. How u can define report parameters?

83. How u can pass parameters for stored procedures in reports?

84. Embedded Vs Shared

85. Difference between Sorting and Interactive Sorting?

86. How to pass parameter from Report Viewer Control to sub report?

87. How to open another report in a new window from existing report?

88. How to pass parameter from chart to Table in same report?
89. Can we have Table within a Table in SSRS report?

90. How to apply style sheet to SSRS Reports

91. Dynamic sorting, Dynamic Grouping in SSRS

92. Different types of Filters

93. Difference between Filter and Parameter? Which one is better?

94. Optimization of Report

95. I have 'State' column in report, display the States in bold, whose State name starts with letter 'A' (eg: Andhra pradesh, Assam should be in bold)

96. In which scenario you used Matrix Report

97. Crosstabs in SSRS

98. What is a Shared Dataset

99. How do u display the partial text in bold format in textbox in Report? (eg: FirstName LastName, where "FirstName" should in bold fornt and "LastName" should be in normal font.)

100. Place the table in report header and how to repeat on each page

101. parent grouping, child grouping in SSRS

102. A main report contain subreport also. Can we export both main report and subreport to Excel?

103. how to convert PDF report from Portrait to Landscape format?

104. How to Combine Datasets in SSRS (1 Dataset gets data from Oracle and other dataset from Sql Server)

105. Tell me on which criteria you create reports?

106. How you create a report modelling?

107. What is self service report?

108. What is report modelling and how you implemented in your project?

109. How can you build sql query in ssrs?







Reporting Authoring

1.       What is a Report Item in SSRS?
A Report Item is one of the subsequent types of items: Rectangle, Textbox, Image, Sub report, Line, Custom Report Item, List, Table, Matrix or Chart.

2.       Types of reports?
3.       What are the Matrix reports and what are their uses?
   Matrix reports are the reports which used to generate data dynamically i.e. table structure is not static and it can be changed at run time.

4. What are the necessity things for creating matrix report?
a) Page, column, row, details

5. What is Tablix ?
A Tablix can be seen as a control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings.

1.       What are the drilldown reports? Give one example.
   Drill down reports is the reports which can collapse or expand as per requirement i.e. information can be drilled down in to minor level information if required. For example when you group by the data at particular group. At the group level you can get the sum or any other information which you want to see. When you expand it you will get the row level data. For drill down there will be + sign on the row through which you can drill.

2.       What is drill through report?

3.       Difference between drill down and drill through report?
Types of parameters
1.            Single value parameters
2.            Multi value parameters,
3.            Cascading parameters (one parameter is dependent on another parameter selection)

4.       What are internal & hide parameters?

5.       Which report is better in performance?


6.       What is the extension of linked report?

7.       What is drill-through in SSRS?
Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report

8.       What is the main difference between a Matrix report item and a Table report item?
The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.

9.       What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns charts and various other options.
3d charts are also available in reporting services.

10.   What is use of List data region?

11.   How do you prepare chart report?

12.   How do you prepare pivot & unpivot in SSRS?

13.   What is a Gauge in SSRS 2008 ?
 Usually, the Key Performance Indicators (KPIs) in an organization are represented using Gauge where the an indicator points to a specific value in a data range. Common example of a gauge is car speedometer.


14.   What are sub reports and how to create them?
Sub reports are the reports which can be called in main report and can be generate through main report.
Parameters can be passed from main report to sub report and basis of that report can be generated.

15.   How do you prepare dash boards in SSRS?

16.   What is bookmark link in SSRS?
Bookmark is a link which a person clicks to shift to a different area or page in a report. We can insert bookmarks links only to textboxes and images.

17.   SQL Server Reporting Services vs Crystal Reports.
Crystal reports are processed by IIS while SSRS have a report server. Caching in Crystal reports is available through cache server. On the other hand, caching in SSRS is available for Report history snapshots. Crystal reports have standards and user defined field labels. SSRS allows only user defined field labels.

18.   How to fine tune Reports?
To tune-up the Reporting Services, follow the below mentioned ways: - Expand the Server or utilizing the reporting services of another database server. For better embedding of report contents, report application’s logic and characteristics can have a duplicate copy of data. - Replication of data continuously. Using nolock, the issues of locking can well be resolved and the performance of the query can be improved. This can be done by using dirty read at the time of duplicating the data is unavailable.

19.   How to implement group by in report?
   Group by can be implemented by adding a group on row in table data part. Adding group will two group rows for this row on above and below of this particular row. Below row can be used for subtotal of this group which is specified in above row. By repeating the same operation on group row can created a group on group.

20.   How to create Dynamic Columns in SSRS as per given user input?

21.   How to display text message when report does not fetch any data?

22.   You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?

23.   Create a text box item, set the action to Go To URL, and then configure the URL.

24.   You want to include an image in a report. How do you display the Image Properties dialog box?

25.   When you drag an image item from the Toolbox window to the Report Designer, the Image Properties dialog box automatically opens.

26.   You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?
No, the Report Wizard lets you create only tabular and matrix reports. you must create the chart report directly by using the Report Designer.

27.   Which rendering formats are affected by the PageSize properties?
Because only the Adobe PDf file, Word, and Image rendering extensions use physical page breaks, they are the only formats that are affected by the PageSize properties.
Rendering formats - PDF, PIFF, HTML, MHTML, Excel, CSV etc

28.   Which layer is handling for rendering reports?

29.   Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

30.   How to get displayed parameter value in report, passed by user at runtime?

31.   How to identify current user in SSRS Report?
 User!UserID.

32.   What is the RDL file?
   RDL File stands for report definition language. It contains the report definition which located on report server. It contains the report layout and data source and datasets.

33.   What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.

34.   Which language rdl files made of?
RDL files are written in XML.

35.   Difference between RDL and RDLC?

36.   What sorts of capabilities were added to the Report Definition Language in SQL Server 2005?
Hidden parameters, multivalue parameters, dynamic connection strings, end-user sort, and fixed headers were the major features added to report processing in SSRS 2005


37.   What is Query parameter in SSRS?
Query parameters is mentioned in the query of the data sources that are to be included into the SQL script’s WHERE clause of the SQL that can accept parameters. Query parameters begin with the symbol @.The name should not contain spaces and cannot begin with numeral. For clarity, we use only letters.

38.   How will you provide parameters?

39.   How to create "alternate row color"?
 Use Row Number and Mod function.

40.   How will you change the colors of three rows?

41.   How to display columns to rows and rows to columns?


42.   What is expression?

43.   Write an expression in SSRS to get two decimal values in number field

44.   Write an Expression to perform a Division of two Integers fields and  to avoid NAN error.

45.   How to create "dependant" parameter "Make, Model, Year"?
 They need to be in correct order, and previous parameter should filter next parameter dataset. For instance Model dataset should be filtered using Make parameter.

46.   What is parameter variable filter?


47.   What are parameterized reports? What are cascading parameters in SSRS reports?
Reports that accept parameters from users to fetch and report data conditionally are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.

48.   Could you explain how do you create parameter report?

49.   Explain the Dashboard in the business intelligence.
A dashboard in business intelligence allows huge data and reports to be read in a single graphical interface. They help in making faster decisions by replying on measurable data seen at a glance. They can..............

50.   SAS Business Intelligence.
SAS business intelligence has analytical capabilities like statistics, reporting, data mining, predictions, forecasting and optimization.........

51.   What is Command parameter in SSRS?
A Command parameter is used to forward protocol to the Report Server regarding the item that has been retrieved. Command parameter is also used for rearranging a user’s session information.

52.   What is Format parameter in SSRS?
Format parameter is used to control report output. Every delivering format on Report Server has to pass through this parameter.

53.   How to pass multi-valued parameter to stored procedure in dataset?
 Join function in SSRS and split function in T-SQL

54.   How to pass cascaded parameters in report?
   Cascaded parameters are the parameters which are depended on other parameters. These parameters can be created in parameter section in upper window while creating the report.

55.   What is the main purpose of a report parameter?
The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.

56.   What is the main purpose of a query parameter?
The main purpose of a query parameter is to filter data in the data source.

57.   Difference between report and query parameter. Why do we need different type of parameter?

58.   You want your users to select a parameter from a list of values in a list box. How should you configure the parameter?

59.   You should create a data source that contains the possible values and then bind the data source to the parameter.

60.   What is the main benefit of using embedded code in a report?
The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.

61.   What programming language would you use to create embedded functions in SSRS?
An SSRS report supports only visual Basic .nET embedded code.

62.   What are embedded functions in SSRS?


63.   How do you reference an embedded function in a report expression?
Use the Code prefix and the name of the function to reference an embedded function in a report expression.

64.   What are the rendering extensions of SSRS?
Rendering extensions manage the category of document produced when a report is processed. Rendering Extensions are: HTML, MHTML, EXCEL, CSV, IMAGE, PDF, and XML.

65.   Which functions are used to pass parameter to MDX query?
 StrToMember and StrToSet

66.   How can you make calendar available in report parameter?
 Set parameter data type to date.

67.   How to change parameter value inside the report?
 Set action. "Jump to itself" and pass different value for the parameter.

68.   I have 3 parameters say A,B,C. I want that when I select values in A and B only then C comes alive. I am using cascading parameter in SSRS 2005.

69.   I know that in cascading parameters, one parameters passes a value to other one and it comes alive. But I want that 2 parameters pass the value to a single one.
Please dont say to use multi select as multi select is used only when we have to multi select from one parameter.
Also one thing how can I use radio buttons in SSRS 2005 and how to hide null checkbox when allowing null values in calender control.


70.   How to find a value in another dataset based on current dataset field (SSRS 2008 R2)?
 Use lookup function.

71.   Can we use datagrids for our report in SSRS?
I've got an ASP.NET project that populates a datagrid. I'd like to use the datagrid as my datasource for my report using SQL Server Reporting Services. Is this possible? The simple answer is no. However, nothing's ever simple. A set of reporting controls was added in Visual Studio 2010 allowing you to report in a dataset, on data that was supplied by you. So, if you retrieved your data into a dataset, bound the datagrid to the dataset so it had data to display, you could then use that dataset as the datasource for the reporting controls. These are then client-side reports, not server reports though.

72.   You want a report to display Sales by Category, Subcategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report?
Group the Sales information by Category, Subcategory, and Product. Hide the Subcategory group and set the visibility to toggle based on the Category item. Hide the Product category group and set the visibility to toggle based on the Subcategory item.

73.   How can you monitor the report Usage?
You can query the Report Server database for this information as follows:
SELECT
ex.UserName, ex.Format, ex.TimeStart, cat.Name,
ex.Parameters,
CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate
FROM ExecutionLog AS ex, Catalog AS cat
where ex.ReportID = cat.ItemID
ORDER BY ex.TimeStart DESC
1)      Modify SessionTimeout and SessionAccessTimeout system properties.


74.   Here is a sample script for rs.exe which will set these values for you:
Public Sub Main()
    Dim props() as [Property]
    props = new [Property] () { new [Property](), new [Property]() }

    props(0).Name = "SessionTimeout"
    props(0).Value = timeout

    props(1).Name = "SessionAccessTimeout"
    props(1).Value = timeout

    rs.SetSystemProperties(props)
End Sub
You can run this script with the following command:
rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout="6000"The timeout is expressed in seconds, so this example sets the SessionTimeout and SessionAccessTimeouts to about an hour and a half.
(via http://blogs.msdn.com/b/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx)
2) Change Report Execution Timeout via Report Manager Open Report Manager At the top of the page, click Site Settings. This opens the General Properties page of the site.
Report Execution Timeout Specify whether report processing times out after a certain number of seconds. (via http://msdn.microsoft.com/en-us/library/ms181194.aspx)
3) Set the HttpRuntime ExecutionTimeout
a. Open the ReportServer’s Web.config file by going to Start -> Administrative Tools -> Internet Information Services. b. From there, expand Web Sites -> Default Web Site, and click on ReportServer. On the right-hand pane, right-click ‘Web.Config’ and select ‘Open’. c. Locate the HttpRuntime parameter. If it doesn’t exist, you will have to create it within the section. d. Set the executionTimeout value to ‘10800’ (3 hours) as shown below:
<system.web>
<httpRuntime executionTimeout = "10800" />
</system.web>
5) Set the report to never time out on the server
1.            Open your web browser on the server and go to http://localhost/Reports
2.            Navigate to the Report location and click on the problem report(s).
3.            On the left-hand pane, click on Properties.
4.            In the ‘Report Execution Timeout’ click the ‘Do not timeout report execution’

75.   You want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web?
No, you cannot create the same experience with SSRS. you can, however, use Excel to create such an experience.

76.   How to make conditional sum in SSRS?
 IIF condition true then SUM else nothing.

77.   How to create percentile function?
Custom code is required.

78.   How to create median function?
 we can use custom code function to calculate median of field set.

79.   Latest report prepared by you ? Explain

80.   What are the Challenges You Faced while making of Reports?

81.   What you will do when your Report is not Fetching Right Data?


82.   What is the time taken to run your report?

83.   How many reports you have prepared so far?

84.   I have Country table and State table if I click on Country I need that country         States? Will it possible in ssrs?
Yes

85.   How?
 By using Cascading Parameters

86.   Can we implement multiple parameters in Report?

87.   How to enable multiple parameters in a Report?

88.   How do you normally create reports (wizard/manually)?

89.   What languages have you used to query data for SSRS Reports?

90.   What types of graphs do you normally use and what effects do you apply to them?

91.   Can you update report data using SSRS?

92.   What is the formatting code (format property) for a number with 2 decimal places?

93.   Which Reports Are Being Run, How Long Are They Taking, and Who Is Viewing
Them?
94.   How analytics Process Your Request When you Create your Requests.

95.   From where you Get the Logical Query of your Request?

96.   Major Challenges You Faced While Creating the Reports?














Deploying Reports

97.   How do you deploy a SSRS report? Where can u change the deployment URL?

98.   How many ways are there for deploying the report?

99.   Different ways of Deploying reports

100.                        Can we deploy SSRS reports on our personal website?

101.                        How do u access a Report, which is deployed

102.                        What are different option to deploy report from Dev to Prod.

103.                        What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report format and also the report import types. Report service config reside at ISS.

104.                        What is 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.
105.                        What are the main components of reporting services?
The main components of Reporting Services are
1.            Report designer
2.            Report server
3.            Report manager
4.             Report user

106.                        Different databases in Reporting Services?
• ReportServerTempDB
Reporting Services uses a temporary database for storage of objects which are, well, temporary. For example, report snapshots which are associated with a particular user session as the result of a live execution. Cached report snapshots are also stored here because they begin life as a result of a live report execution. This means that in systems where there are a lot of live report executions happening, we are churning through a lot of data in the ReportServerTempDB. As your system scales out or up, you will undoubtedly experience a problem where you will begin to saturate the IO subsystem hosting the ReportServerTempDB.
Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database.
Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.
If you back up the temporary database and subsequently restore it, you should delete the contents. Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents.
• Report Server Database
The report server database is a SQL Server database that stores the following content:
Items managed by a report server (reports and linked reports, shared data sources, report models, folders, resources) and all of the properties and security settings that are associated with those items.
Subscription and schedule definitions.
Report snapshots (which include query results) and report history.
System properties and system-level security settings.
Report execution log data.
Symmetric keys and encrypted connection and credentials for report data sources.

•  http://ReportServer (Web Services)<ComputerName>/reportserver
A report server is a stateless server that uses the SQL Server Database Engine to store metadata and object definitions.
It is a web service that SSRS uses to perform report management, publishing, and rendering operations is located in the Report Server Virtual Directory. By default, the Virtual Directory is named ReportServer, but this can be changed if an instance that uses the Virtual Directory already exists or if your application requires a name that is directly related to the application.
•  http://Report Manager(Web Application)<ComputerName>/reports
Report Manager, which is the Web-based management tool for Reporting Services to set up directories and manage security on the Web site. Configure this in the same way you configured ReportServer. The default name for the Virtual Directory for Report Manager is Reports.

107.                        Report Designer vs Report Builder
Report Designer can create reports of any complexity that Reporting Services supports, but requires you to understand the structure of your data and to be able to navigate the Visual Studio user interface.
Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business users rather than developers. Report Builder requires a developer or administrator to set up a data model before end users can create reports. Report Builder presents a simplified view of the report-building process and is intended for business analysts and other end users.

108.                        What is Reporting Services?      
SQL Server’s Reporting services offer a variety of interactive and printed reports managed by a web interface. Reporting services is a server based environment.
You can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
You can publish reports, schedule report processing, or access reports on-demand.
You can create ad hoc reports based on predefined models and interactively explore data within the model.
You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports.
You can view the reports created over a Web-based connection or as part of Windows application.

109.                        You want to configure an amount to display the value in a currency format. Which property do you use?
To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c.

110.                        You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?
You can right-click the project in Solution Explorer and then change the Target-Server URL property.

111.                        What is SQL Server 2005 Reporting Services?
SQL Server 2005 Reporting Services is a server-based enterprise reporting environment, managed through Web services, that delivers a variety of interactive and printed reports.

112.                        What’s new in SQL Server 2005 Reporting Services?
SQL Server 2005 retains the core functionality of SQL Server 2000 Reporting Services. The product contains several key additions and enhancements, including the following:
•             Report Builder (end-user reporting)
•             Report Controls (ships with Microsoft Visual Studio 2005)
•             Direct client printing (first shipped in SQL Server 2000 Service Pack 2)
•             SharePoint Reporting Web Parts (first shipped in SQL Server 2000 Service Pack 2)
•             End-user sort
•             Multivalued parameters
•             Custom report items
•             Report Designer improvements
•             Analysis Services Query Designer
•             Management Studio integration
Detailed explanations of Reporting Services enhancements are available at the Microsoft SQL Server TechCenter page for Reporting Services.

113.                        How can I add Reporting Services reports to my application?
Visual Studio 2005 (Standard and Enterprise editions) contains a set of freely redistributable Report Viewer controls that make it easy to embed Reporting Services functionality into custom applications. Two versions of the Report Viewer exist, one for rich Windows client applications and one for ASP.NET applications.

114.                        Do I need a report server to run reports in my application?
In addition to publishing reports to a report server, you can build reports using the Report Designer that is directly integrated with Visual Studio language projects. You can embed reports directly in any Windows Forms or ASP.NET Web application without access to a report server. The data access in embedded reports is a natural extension of the Visual Studio data facilities. Not only can you use traditional databases as a source of data for your reports, you can use object collections as well.

115.                        What do you need to restore report server database on another machine?
 SSRS Encryption key             

116.                         How do I upgrade from SQL Server 2000 Reporting Services to SQL Server 2005 Reporting Services?
You can upgrade most installations using the SQL Server 2005 setup program. All report server content and settings will be fully functional after the upgrade process.
In cases where you have customized your report server deployment (added extensions, changed virtual directory settings, or encrypted the ASP.NET account user name in the registry), you will need to migrate your Reporting Services installation. In this case, you install a new instance of SSRS 2005 and use the Reporting Services configuration tool to migrate settings.
If you deployed custom extensions for Reporting Services 2000, you may need to rebuild these extensions so that SSRS 2005 can use them. Consult SQL Server Books Online for specific instructions on what you need to do to move to SSRS 2005.

117.                         Will my existing reports work with SQL Server 2005 Reporting Services?
Yes, all reports will continue to work as they did in SQL Server 2000 Reporting Services. If you want to take advantage of new report functionality, you will need to convert the reports to the new report definition format in the SQL Server 2005 Report Designer.

118.                         I built an application using the report server. Will it still work after I upgrade?
Yes. SSRS 2005 provides backward compatibility for Simple Object Access Protocol (SOAP) as well as URL access–based applications. You will not have to change your applications to start using SQL Server 2005. Note, however, that the new functionality available in SSRS 2005 is not available in the backward compatibility SOAP endpoint.

119.                        Difference between report manager and report server?

120.                        How to upload a report to report server
Report Server Configuration Files

121.                        What is report server project?
   Report Server Project contains the RDL file and it need to be deployed on report server to view the report files to application and user. It a solution where we design our reports. You can add it by going into BIDS clicking on new item and then selecting reports server project. Once the solution is created you can start creating reports.

122.                        How many types of deployments in ssrs?

123.                        How would you deploy SSRS Reports using out-of-box functionality and how can you automate SSRS report deployment?
Business Intelligence Development Studio is generally used to deploy SSRS reports. There is no out-of-box support in SSRS to automate reports deployment, but free third-party products like RSScripter can be used for this.

124.                        What are different option to deploy report from Dev to Prod.
From Solution Explorer by giving Target Sever URL
Uploading the .rdl from Report manager.

125.                        How would you access SSRS reports deployed on report server?
Reports Manager is the most straight-forward way to access SSRS reports deployed on report server. Apart from executing the reports, it is the main administration console for SSRS server to manage reports.
Increase the script timeout on the report server
1.            Go onto your Reporting Server and open up Internet Information Services; right-click on the ReportServer and select Properties.
2.            Go to the Options tab, and set the ASP Script timeout to 300 seconds (this didn't really work for me).
     131.                   Describe the role of Encryption key in Report Server Configuration manager?
To encrypt credentials, connection strings.  Can be taken backup and restore when required from configuration manager.

126.                        In which processing mode of a report viewer control can you use the full functionality of your report server?
You should use the remote processing mode to use the full functionality of your report server.


127.                        How can you manage reports from your application if the report server is deployed in SharePoint integrated mode?
Use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.

128.                        Can we deploy SSRS reports on our personal website?
Your reports can only be deployed on a reporting services site. Your only option for viewing them from other sites is an HTTP link. Some tools, like SharePoint offer controls allowing you to view reports in the context of the other websites, but the report is still deployed to and hosted from reporting services

129.                        Any other way for report deployment?

130.                        Where can u configure SSRS for connecting to a database? Email settings as well.
Report Manager and Delivery

131.                        What is Report manager?
 A web based tool provided by Microsoft for managing and accessing reports.
                                              (OR)
Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features.
You can use Report Manager to browse the report server folders or search for specific reports. You can view a report, its general properties, and past copies of the report that are captured in report history.

132.                        How does the report manager work in SSRS?
Report manager is a web application. In SSRS it is accessed by a URL. The interface of this Report manager depends on the permissions of the user. This means to access any functionality or perform any task, the user must be assigned a role. A user with a role of full permissions can entire all the features and menus of the report. To configure the report manager, a URL needs to be defined.

133.                        If you want to disable one of the rendering options in the Save As drop-down list when viewing a report through Report Manager, where do you do that?
The visibility property for any of the rendering devices can be changed by modifying the RSReportServer.config file and locating the <ExtensionName="RenderDevice"/>  tag for the specific device.
134.                        Problems faced while rendering report to excel from report manager?
140.                How to open report manager in SSRS?
1. Open Microsoft Internet Explorer 6.0 or later.
2. In the address bar of the Web browser type the Report Manager URL. By default the URL
Role of Report Manager
is http://<ComputerName>/reports.

135.                        Which of the tools and utilities described in this lesson can change or create the virtual directories for the report server and Report Manager after installation?
Only Reporting Services Configuration Manager can enable and name the virtual
directories for the report server and Report Manager.

136.                        What is Report Builder?
 Report Builder is a self-service tool for end users. Report Builder is a business-user, ad-hoc report design client that allows users to design reports based on the business terms (Report Builder model) they are familiar with, but without needing to understand database schemas or how to write SQL or MDX queries. Report Builder works with both SQL Server and Analysis Services data sources.

137.                         How does Report Builder support Analysis Services cubes?
Report Builder supports relational SQL and Analysis Services data sources in SQL Server 2005. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

138.                        In which SQL Server version report builder introduced?              
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.

139.                        What permission do you need to give to users to enable them to use Report Builder?
"Report Builder" role and "system user". Report builder should also be enabling in report server properties.

140.                        Can you edit a report that an end user created by using Report Builder in BIDS?
Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.

141.                        How do users use Report Builder with SQL Server data sources?
While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio–based development shell.
142.                        How do I get Report Builder to generate a parameter that can be set by users viewing the report?
In the filter dialog box, click the name of the criteria that you would like to prompt the user for when viewing the report. For example, for the criteria Order Year=2000, click Order Year. Select the Prompt option in the drop-down list.

143.                        How does Report Builder support Analysis Services cubes?
Report Builder supports relational SQL and Analysis Services data sources in SQL Server 2005. To create a model for Analysis Services cube, go to Report Manager or Management Studio, create a data source for your Analysis Services database, and then select the Generate Model option to create the model.

144.                        How do users use Report Builder with SQL Server data sources?
While models that provide access to SQL Server Analysis Services are automatically generated on the report server, the Report Builder Model Designer can be used to generate or modify the models that are built on top of SQL Server relational databases. These model-building projects are a new type of project within a Visual Studio–based development shell.

145.                        Name a few new features in Report Builder 2.0.
                Enhanced data layout,on-demand rendering and Rich-format text.

146.                        Can reusable templates and styles including images be created for Report Builder?
Support for templates, styles, and images are frequently requested and will be considered for a future release.

147.                        What is Linked Report?
A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

148.                        Can you edit the .rdl code associated with a linked report?
No, because a linked report has no .rdl code of its own. It refers to the .rdl code of the base report.

149.                        What are data driven subscriptions?
Reporting Services provides data-driven subscriptions so that you can customize the distribution of a report based on dynamic subscriber data. Data-driven subscriptions are intended for the following kinds of scenarios:
Distributing reports to a large recipient pool whose membership may change from one distribution to the next. For example distribute a monthly report to all current customers.
Distributing reports to a specific group of recipients based on predefined criteria. For example send a sales performance report to the top ten sales managers in an organization.

150.                        What can you do if your query with properties for a data-driven subscription does not provide values for all of the subscription properties?
If your query with properties for a data-driven subscription does not provide values for all of the subscription properties, you can use text and default values instead. These values are then used for parameters of all subscriptions you get from the query.

151.                        When to Use Null Data driven Subscription?
Create a data-driven subscription that uses the Null Delivery Provider. When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination and uses a specialized rendering extension called the null rendering extension. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure through a subscription definition.

152.                        What is report subscription?     
  Report subscription is to schedule the resource on particular time and to send a mailer to particular users. You can find this functionality on report server for each report.

153.                        Can you create subscription using windows authentication?
No.

154.                        What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value?
If you do not specify a parameter value in a subscription and the parameter does not have a default value, the execution of the report will fail.

155.                        You want to create a subscription to a report. However, when you right-click the Subscription subfolder of the report, you notice that the new Subscription option is dimmed. What is wrong?
When the new Subscription option is dimmed, the report probably does not use stored credentials for accessing the data. SSRS needs these credentials stored in its own Report Server database to execute a report on a schedule.

156.                        How Many Report Subscriptions Have Been Created and Did Any of Them Fail?



157.                        What is a cache in SSRS?
Report server can lay up a copy of processed report in a memory and return the copy when a user opens the report. This server memory is known as cache and the process is called caching.

158.                        Can you always create a cache of a report?
No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.

159.                        Can a parameterized report be cached?
Difference between Cached Report and Snapshot Report

160.                        What caching options do you have for reports on report server?
 Do no cache, expiry cache after x minute, on schedule.


161.                        Snapshot & caching reports?

162.                        Where is snapshot report saved?           

163.                        When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset?
From a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.

164.                        What is Snapshot parameter in SSRS?
When a report is saved in a snapshot history, it is allocated a time or date to uniquely identify that report. Snapshot parameter is used to get back these historical reports by passing this time or date to get proper report.

165.                        What is report snapshot in SSRS?
Report snapshot is a report which contains layout information and a dataset that is extracted at a particular point of time. When the new snapshot is created the previous report snapshot will be overwritten.

166.                        How do you improve the performance of a report?

167.                        What is log shipping?

168.                        What is the report model project?
   Report model project is for creating Adhoc reporting. You can create the adhoc reports through report builder. Report model project can be created on bids or report server. This model can have simple view.
User want only to display only pdf as export option in report Manager .. Describe Steps to perform this .
using System.Reflection;
using Microsoft.Reporting.WebForms;
public void DisableUnwantedExportFormats()
{
foreach(RenderingExtension extension in serverReport.ListRenderingExtensions())
{
if(extension.Name == "XML" || extension.Name == "IMAGE"
|| extension.Name == "MHTML")
ReflectivelySetVisibilityFalse(extension);
}
}

169.                        Have you used Conditional Formatting in Report? Explain.

170.                        How the users Created Differs From RPD/Answers/Dashboards Level?

171.                        What kind of data does your project has?

172.                        Explain me about your project and your role in it?

173.                        How to deliver the given report?

174.                        What are options there in property tab in report manger?

175.                        How do you create shared schedule in SSRS?

176.                        How can you add a new report User to report manager.

177.                        What types of roles are available in SSRS 2008, and what are their purposes?

178.                        What role should be assigned so that client can’t manage report?          

179.                        Can a user or group belong to more than one item-level or system-level role?
Yes, in SSRS 2008, a user or group can have more than one association to a system-level or an item-level role.
180.                        Is SSRs 2008 dependent on IIS? if not how it perform the operations.

181.                        Error handling in Report

182.                        Have u worked on any 3rd party Report Tools

183.                        Explain the Report Model Steps

184.                        How do u secure a Report

185.                        Where we show reports to end users?

186.                        How can u configure then can u explain briefly?

187.                        How do you restrict the user from viewing the report?

188.                        How does your SSRS maintain security?