Lesson 1

This module discusses the different techniques presented throughout this course, including the grouping techniques and functions.These can be combined to create a query that will produce some very meaningful results. In this module, you will be presented with the class project, in which you will retrieve a listing of sales by sales associate. To make it more challenging, you will only want those sales associates that have sold the product with the highest sales based on overall quantity sold. (SSRS) SQL Server Reporting Services is a server-based report generation software system from Microsoft which is administered by means of a web interface. SSRS can be used to prepare and deliver a variety of interactive and printed reports. SSRS competes with Crystal Reports and other business intelligence tools like Microstrategy and Cognos.

In SQL Server 2022, Reporting Services are provided through SQL Server Reporting Services (SSRS), which continues to be a comprehensive solution for creating, publishing, and managing reports in a variety of formats. SSRS is part of the broader Microsoft BI stack and integrates well with other data services like SQL Server, Azure SQL Database, and more. Here are the main types of reporting services and capabilities available in SSRS:

1. Paginated Reports: These are the traditional, highly formatted reports suitable for printing, such as PDFs. Paginated reports are designed to fit well on a page, displaying all the data in a format that can be easily read or printed out. They can include tables, charts, images, and other report items, and can be exported to various formats like PDF, Excel, and Word.
2. Interactive Reports: These reports allow users to interact with the data presented within the report. Users can drill down into data, expand or collapse sections, sort, and filter to customize the view of the data according to their needs.
3. Mobile Reports: Designed specifically for a mobile-first approach, these reports are optimized for viewing on mobile devices. They allow for a responsive design that adapts to different screen sizes and orientations. Mobile reports can be created using SQL Server Mobile Report Publisher.
4. KPIs (Key Performance Indicators): SSRS allows the creation and management of KPIs directly within the Report Server. These KPIs can be used to display important metrics and goals, and can be viewed in a web portal.
5. Data-driven Subscriptions: This feature allows for reports to be distributed automatically to a dynamic list of subscribers. The list of recipients can be determined based on data retrieved from a database, allowing for personalized report distribution.
6. Report Builder and Report Designer: SSRS includes tools for designing reports. Report Builder is a standalone app designed for business users to create and design reports without needing a deep technical understanding. Report Designer is a more advanced tool integrated into Visual Studio, offering more control and capabilities for developers.
7. Web Portal: The web portal is a web-based interface where users can access, view, and manage reports, KPIs, and mobile reports. It replaces the Report Manager from previous versions of SSRS.
8. Power BI Integration: While not a part of SSRS per se, SQL Server 2022 continues to support integration with Power BI, Microsoft's interactive data visualization tool. This allows for a more comprehensive BI solution, combining the strengths of both platforms.
9. RESTful API: SSRS 2022 provides a fully supported RESTful API, enabling developers to integrate SSRS reporting capabilities into custom applications, and automate report management and execution tasks.

These services and features make SQL Server Reporting Services a versatile and powerful tool for creating a wide range of report types, from traditional paginated reports to interactive and mobile reports, serving the needs of businesses for reporting and data analysis.

Tuning an individual query means examining the structure of the query (and subqueries), the SQL syntax, to discover whether you have designed your tables to support fast data manipulation and written the query in an optimum manner, allowing your DBMS to manipulate the data efficiently. Queries do many things, from

1. adding entirely new records to tables,
2. to updating existing records,
3. to pulling data out of multiple related tables for reporting purposes.

It is this last part that many people think of when discussing optimizing queries, but in fact, a database must be viewed as a whole entity or you risk speeding up one piece while negatively impacting another. As this chapter demonstrates, there are techniques (particularly when optimizing the queries of other people) that are essentially free, while others (specifically creating new indexes) can be applied only at a price.Adding indexes may very well speed up whatever it is you are trying to speed up, but it may cripple some other piece of the application. Deleting an index may speed up one part of the application but cripple another. The intention here is not to cripple you with fear of doing anything because you may cause more problems than you fix, but simply to cause you to always think about the implications of your changes, beyond the area you are working on. Whenever you make a change, particularly on a large system, you need to keep your ears open to users complaining that the application seems slower lately. Where possible, record the changes that you make so that they can be backed out if necessary.