Explore topic-wise InterviewSolutions in .

Power Bi: Microsoft's Power BI is a data analysis and reporting tool. It's a cutting-edge CORPORATE analytics tool with a plethora of intelligent and user-friendly features. Data connectivity, data translation, data preparation, data modelling, reporting, and dashboarding are just a few of the capabilities that Power BI offers. In addition, additional features such as DAX formulae, bespoke graphics, and so on are available in Power BI.

Following are the differences between SQL Server Reporting Services and Power Bi:-

• SSRS requires a SQL Server licence, whereas Power BI is free. Power Bi Report Server (an on-premises report server that includes a web portal for viewing and managing reports and Key Performance Indexes), on the other hand, necessitates the purchase of a Power BI Premium licence.
• SSRS is a server-based database, while Power BI is cloud-based.
• Power BI is more graphical and modern. SSRS is a traditional reporting and analysis system that requires manual effort and time.
• In Power BI, you MAY use data from the cloud. Only on-premise data can be used by SSRS.
• The successor to SSRS, Power BI Report Server (PBIRS), contains additional functionality than SSRS. For one thing, unlike SSRS, it can use and display both interactive PBIX and analytical XLSX reports.
• Cortana has been integrated with Power BI for AI-based natural language, Q&A about your data, and reports. This feature is not available in SSRS.
• To create reports, Power BI includes a graphical INTERFACE with drag-and-drop functionality. Working with unstructured data, modern rendering, PUBLISHING, INTEGRATING, and collaborating across platforms is simple.

SQL Server Reporting Services (SSRS) Documentation

SSRS Download

SSIS Interview

Microsoft Interview

Reports with MANY parameters are known as cascaded parameterized reports.

Some parameters, on the other hand, will have values or value sets that are DEPENDENT on other parameters. The order of the parameters can affect the report's optimization. For example, if we have a million results for a customer parameter, we MAY filter it by ADDING a GENDER parameter first, which will cut the customer parameter results in half. We have a subcategory parameter, for example, but its values aren't filled in until the category parameter is selected.

After an SSIS package has completed a data load, it is frequently NECESSARY to be able to transmit an SSRS report in Excel, PDF, or another format to different users. To begin, you MUST first create a subscription to the report. From the Report Manager, you can create an SSRS report subscription. You can specify the report format and the recipient's EMAIL address when subscribing to a report. A SQL SERVER Agent Job is established when you set a schedule for an SSRS report. You may run the SSRS report subscription from SSIS by using sp_start_job and giving the required job name.

RDL stands for Report Definition Language. It is an XML representation of a SQL Server Reporting Services report definition. The data retrieval and layout information for a report is contained in a report definition. RDL is MADE up of XML elements that follow a Reporting Services-specific XML language. By accessing code assemblies within report definition FILES, you can add your own custom functions for manipulating report item values, styles, and formatting.

RDL encourages commercial reporting product interoperability by creating a common schema that allows report definitions to be exchanged. RDL can be used with any XML-based protocol or programmatic interface. In other words, RDL is :

• An XML schema for report definitions.
• A business-to-business and third-party interchange format.
• An extensible and open architecture that provides support to ADDITIONAL namespaces and custom elements.

Following are the VARIOUS components of an RDL file:-

• Data: This section holds the dataset that may be used to write QUERIES and is always linked to the data source.
• Design: You can create a report in the shape of tables, matrices, or other formats. All you have to do now is drag and drop the data you wish to use.
• Preview: After the run command has been executed, it is used to examine the report preview.

Following are the use cases of Snapshot reports in SQL Server Reporting Services (SSRS):-

• History of the REPORT: You may create a reported history that displays how data changes over time by taking a series of report snapshots.
• Consistency: When you WANT to give consistent RESULTS for multiple users who must work with the same data sets, use report snapshots. An on-demand report can offer different results from one minute to the next when dealing with VARIABLE data. A report snapshot, on the other hand, allows you to compare data from the same point in time to data from other reports or analytical tools.
• Performance: You can lessen the processing effect on the report server during core business hours by SCHEDULING large reports to execute during off-peak hours.

Following are the advantages of using SQL Server AUTHENTICATION:-

• It enables SQL Server to work with older APPLICATIONS and third-party apps that require SQL Server authentication.
• It enables SQL Server to work in mixed-OS situations where all users are not authenticated through a Windows domain.
• It enables users to connect from domains that are unknown or UNTRUSTED. For example, an application that allows established clients to check the progress of their orders using assigned SQL Server logins.
• It enables SQL Server to support user-created identities in Web-based applications.
• It enables software developers to deploy their programs utilising a complicated permission hierarchy based on SQL Server logins that are known and pre-configured.

Following are the disadvantages of using SQL Server Authentication:-

• Even a Windows domain user with a Windows login and password must provide another user id and password (SQL Server) to connect. Many people find it challenging to keep track of several user IDS and passwords. It can be inconvenient to have to supply SQL Server credentials each time you connect to the database.
• The Kerberos security protocol is not supported by SQL Server authentication.
• Additional password policies are provided for Windows logins that are not accessible for SQL Server logins.
• At the moment of the connection, the encrypted SQL Server Authentication login id and password must be sent over the network. The password is stored on the client by some applications which connect automatically. These are attack spots that are susceptible to ATTACKERS.

You must choose an AUTHENTICATION mode for the Database Engine during setup. Windows Authentication mode and mixed-mode are the two options. In Windows Authentication mode, Windows Authentication is enabled while SQL Server Authentication is disabled. Both Windows and SQL Server authentication is supported in mixed mode. Windows Authentication is anytime enabled and can't be TURNED off.

• You must set up a strong password for the built-in SQL Server SYSTEM administrator ACCOUNT if you choose Mixed Mode Authentication during setup. SQL Server Authentication is used by the system administrator account to connect.
• The setup establishes the system administrator account for SQL Server Authentication if you choose Windows Authentication while setting up, but it is disabled. You must enable the system administrator account if you later switch to Mixed Mode Authentication and WANT to utilise it. As a system administrator, any Windows or SQL Server account can be set up. Since the system administrator account is well-known and frequently targeted by unscrupulous individuals, you should only enable it if it is required by your application.

When a report is divided into COLUMNS, such as columns in a newspaper, they are referred to as LOGICAL pages rendered on the same physical page. They are ordered from left to right, top to bottom, and each column is separated by white space. Each physical page is divided vertically into columns, each of which is regarded as a logical page if the report is divided into more than one column.

Take, for example, a physical page with two columns. The first column and then the second column is filled with the content of your report. If the report does not FIT entirely within the first two columns, the first column is filled first, followed by the second column on the following page. From left to right, top to bottom, columns are filled until all report ITEMS are rendered. The column spacing defaults to zero if you give column sizes that cause the horizontal or vertical WIDTHS to equal zero.

The paper size is the physical page size. The report's rendering is determined by the paper size that you pick. REPORTS that are formatted with a hard page break When printing or viewing a hard page break file format, insert page breaks horizontally and vertically based on the physical page size to provide an ideal reading experience. Soft page break formats are used in reports. Insert horizontal page breaks based on the physical size to provide a better reading experience in a Web browser.

The page size is SET to 8.5 x 11 inches by default, but you can modify it in the Report Parameters window, Page Setup dialogue box, or the Properties pane by setting the PageHeight and PageWidth properties. The report body's contents do not CAUSE the page size to increase or decrease. If you want the report to be printed on one page, all of the text in the report body must fit on that page. If it doesn't fit and you choose the hard page break format, you'll need to add more pages to the report. A page break is inserted horizontally if the report body extends beyond the right margin of the physical page. If the report body extends beyond the physical page's BOTTOM edge, a vertical page break is inserted.

Following things should be kept in mind while creating PAGINATED reports:-

• Determine the report's PURPOSE for the intended audience. A well-designed report offers readers information that leads to understanding and action. The report parameters, report layout design, and report viewing experience are all influenced by the design decisions made during this step.
• Select the query type. Choose between a generalised, shared dataset query and a dataset query tailored to your individual set of reports. A common dataset with a generalised query is simple to maintain for multiple reports, but each report creator must filter the data for their own set of reports.
• Make plans for associated data views. Consider how your report's viewers will interact with it. A helpful technique to dealing with big amounts of data is to use summary reports with the flexibility to drill down into detailed data.
• Permissions must be configured. Prepare a strategy for granting the appropriate level of access. Creating a folder structure on the report server and granting access to reports and related things based on roles and folder security is a popular method.
• Select which data from each data source to use. Define report datasets for each data source. Each dataset has a query that specifies which data should be used. Define a dataset to populate the list of the available values for each parameter if you have report parameters.
• Customize the data as well as the layout. Create a report layout. The report body, data sources, datasets, data regions, text boxes, lines, and images are all included in the report definition. Rectangles are utilised as both layout and graphic element containers. Write expressions to control filtering, grouping, sorting, formatting, and displaying data in each data section. Add report titles, locations, and other IDENTIFYING information to make managing dozens or hundreds of reports easier. To organise the layout elements on the website, use visual elements and containers.
• Think about which parts can be reused. Check to see whether any of the data sources or dataset queries can be reused. Create shared data sources and shared datasets on the report server or SHAREPOINT SITE if this is the case. Check to see if the data areas can be reused as report sections.

Following are the command prompt utilities available in SQL Server Reporting Services (SSRS):-

RS.exe utility: The rs utility is a script host that allows you to run scripted commands. It has its source code in the rs.exe file. Run Microsoft VISUAL Basic scripts to copy data across report server databases, produce reports, create objects in a report server database, and more with this tool. It supports both Native and SharePoint deployment modes.

Powershell cmdlets: Powershell cmdlets are becoming increasingly popular among power users and IT professionals as a PREFERRED language and command-line interface. CMdlets are used to interface with the CLI; in PowerShell, the majority of them are written in C and perform functions that return a.NET OBJECT. Only SharePoint modes are supported, and it installs SSRS SERVICE and proxy servers as well as deploying and managing SSRS apps and proxies.

The following is a list of the most HELPFUL PowerShell cmdlets:

• Get-Command
• Get-Member
• Get-Help
• Get-Process

Rsconfig utility: Rsconfig utility is a script host that is used to perform scripted tasks such as publishing reports, creating items in a report server database, and setting and managing report server database connections. 'rsconfig.exe' is the command file, and it only supports native developer mode.

RsKeymgmt utility: The 'rskeymgmt.exe' command is an encryption key management tool that is used in database recovery procedures. Only the Native development mode is supported. Using the command line, you may also back up, apply, rebuild, and delete symmetric keys.