Business Intelligence

Introduction
Business intelligence (BI) is defined as the ability for an organization to take all its capabilities and convert them into knowledge, ultimately, getting the right information to the right people, at the right time, via the right channel.
This produces large amounts of information which can lead to the development of new opportunities for the organization. When these opportunities have been identified and a strategy has been effectively implemented, they can provide an organization with a competitive advantage in the market, and stability in the long run.

To solve problems with searchability and assessment of data, often BI applications use SSRS (SQL Server Reporting Services)

SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications.

With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. Reports can include rich data visualization, including charts, maps, and sparklines. You can publish reports, schedule report processing, or access reports on-demand. You can select from a variety of viewing formats, export reports to other applications such as Microsoft Excel, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. You can also create data alerts on reports published to a SharePoint site and receive email messages when report data changes.

Benefits of Reporting Services Report

You can use Reporting Services report solutions to:

• Use one set of data sources that provide a single version of the facts. Base reports on those data sources to provide a unified view of data to help make business decisions.
• Visualize your data in multiple, interconnected ways by using data regions. Display data organized in tables, matrices or cross-tabs, expand/collapse groups, charts, gauges, indicators or KPIs, and maps, with the ability to nest charts in tables.
• View reports for your own use or publish reports to a report server or SharePoint site to share with your team or organization.
• Define a report once and display it in a variety of ways. You can export the report to multiple file formats, or deliver the report to subscribers as e-mail or to a shared file. You can create multiple linked reports that apply separate parameter sets to the same report definition.
• Use report parts, shared data sources, shared queries, and subreports to define data visualizations for re-use.
• Manage report data sources separately from the report definition. For example, you can change from a test data source to a production data source without changing the report.
• Design reports in a free-form layout. Report layout is not restricted to bands of information. You can organize data display on the page in a way that promotes understanding, insight, and action.
• Enable drillthrough actions, expand/collapse toggles, sort buttons, Tooltips, and report parameters to enable report reader interactions with the report. Use report parameters combined with expressions that you write to enable report readers to control how data is filtered, grouped, and sorted.
• Define expressions that provide you with the ability to customize how report data is filtered, grouped, and sorted.

One of the Report Designer tool is Report Builder 3.0

Download Report Builder 3.0 from here

Working in Report Design View (Report Builder 3.0) 
The Report Builder window is designed to help you easily organize your report resources and quickly build the reports you need. The design surface is at the center of the window, with the Ribbon above and the Report Data, Grouping, and Properties panes and Report Part Gallery to the left, below, and right. The design surface is where you add and organize your report items. The Ribbon organizes traditional menu items into categories that you can easily find and use. The panes help you to add, select, and organize your report resources, and change report item properties.

The Ribbon
The Ribbon is designed to help you quickly find the commands that you need to complete a task. Commands are organized in logical groups, which are collected together under tabs. Each tab relates to a type of activity, such as inserting report items or formatting text.
In report design view, the Ribbon is divided into the following tabs: Home, Insert, and View. If you cannot find a task on the Ribbon, some Ribbon groups have a related dialog box that you can open by clicking the arrow in the lower right of the group. You cannot minimize or delete the Ribbon or replace it with toolbars and menus.
In run mode, the Ribbon has just one tab, Run.

Home tab
The Home tab is a collection of commonly used commands focused on the appearance of items within your report. From the Home tab, you can access the run, font, paragraph, border, number, and layout commands. When you click an item on the tab, the selected item on the design surface changes. When you click Run, the report is rendered in HTML so that you can see how the contents of the report will appear when published, and you see the Run tab instead of the Home tab. The Home tab is the default tab displayed when you first create a report.

Insert Tab
The Insert tab is a collection of commands commonly used for adding report items to the report. From the Insert tab, you can use wizards to add a table, matrix, chart, or map. You can also add these items without using a wizard, and add other report items such as sparklines, indicators, text boxes, images, rectangles, subreports, and report headers and footers. After you insert an item, Report Builder automatically switches back to the Home tab.

View Tab
The View tab is a collection of commands that control what is displayed within the Report Builder window. You can change display options for the ruler and the Grouping, Report Data, Report Part Gallery, and Properties panes.


Run Tab
When you click Run on the Home tab, you run a preview of the report in the HTML Viewer, and you see the Run tab instead of the Home tab.
The Run tab contains a collection of commands that you can use after the report is rendered. You can print the report, navigate through the report pages, export the report to another file format, view the document map or parameters (if the report has them), and locate items within the report.

Report Design Surface

The Report Builder report design surface is the main work area for designing your reports. To place report items such as data regions, subreports, text boxes, images, rectangles, and lines in your report, you add them from the Ribbon or the Report Part Gallery to the design surface. There, you can add groups, expressions, parameters, filters, actions, visibility, and formatting to your report items. 

You can also change the following:

The report body properties, such as border and fill color, by right-clicking the white area of the design surface, outside any report items, and clicking Body Properties. 

The header and footer properties, such as border and fill color, by right-clicking the white area of the design surface in the header or footer area, outside any report items, and clicking Header Properties or Footer Properties. 

The properties of the report itself, such as page setup, by right-clicking the blue area around the design surface and clicking Report Properties. 

The properties of report items by right-clicking them and clicking Properties. 

Report Data Pane

From the Report Data pane, you can define the report data and report resources that you need for a report before you design your report layout. For example, you can add data sources, datasets, calculated fields, report parameters, and images to the Report Data pane.

After you add items to the Report Data pane, drag fields to report items on the design surface to control where data appears in the report. 

You can also drag built-in fields from the Report Data pane to the report design surface. When rendered, these fields provide information about the report, such as the report name, the total number of pages in the report, and the current page number. 

Some things are automatically added to the Report Data pane when you add something to the report design surface. For example, if you add a report part from the Report Part Gallery, and the report part is a data region, the dataset is automatically added to the Report Data pane. 

Report Part Gallery

The easiest way to create a report is to find an existing report part, like a table or chart, on the report server or a report server integrated into a SharePoint site. You search for report parts to add to your report in the Report Part Gallery. You can filter the report parts by all or part of the name of the report part, who created it, who last modified it, when it was last modified, where it’s stored, or what type of report part it is. For example, you could search for all charts created last week by one of your coworkers. 

Properties Pane

Every item in a report, including the report body itself, data regions, images, and text boxes, has properties associated with it. For example, the BorderColor property for a text box shows the color value of the text box's border, and the PageSize property for the report shows the page size of the report. 

These properties are displayed in the Properties pane. The properties in the pane change depending on the report item that you select.

To see the Properties pane, on the View tab, in the Show/Hide group, click Properties.

In Report Builder, you can change the properties for report items several ways:

• By clicking buttons and lists on the Ribbon.
• By changing settings within dialog boxes.
• By changing property values within the Properties pane. 

The most commonly used properties are available in the dialog boxes and on the Ribbon.

Depending on the property, you can set a property value from a drop-down list, type the value, or click to create an expression. 

Grouping Pane

Groups are used to organize your report data into a visual hierarchy and to calculate totals. You can view the row and column groups within a data region on the design surface and also in the Grouping pane. The Grouping pane has two panes: Row Groups and Column Groups. When you select a data region, the Grouping pane displays all the groups within that data region as a hierarchical list: Child groups appear indented under their parent groups.

You can create groups by dragging fields from the Report Data pane and dropping them on the design surface or in the Grouping pane. In the Grouping pane, you can add parent, adjacent, and child groups, change group properties, and delete groups. 

The Grouping pane is displayed by default but you can close the pane by clearing the Grouping pane check box on the View tab. The Grouping pane is not available for the Chart or Gauge data regions.

Running a Report with Parameters

When you run your report, it is processed automatically. If the report contains parameters, all the parameters must have default values before the report can run automatically. If a parameter does not have a default value, when you run the report you need to choose a value for the parameter, and then click View Report on the Run tab.

Print Preview

When you preview a report in run mode, it resembles a report produced in HTML. The preview is not HTML, but the layout and pagination of the report is similar to HTML output. You can change the view to represent a printed report by switching to print preview mode. Click the Print Preview button on the Run tab. The report will display as though it were on a physical page. This view resembles the output produced by the Image and PDF rendering extensions. Print Preview is not an image or PDF file, but the layout and pagination of the report are similar to the output of those formats.

Multilevel Cascading With Select All

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.


To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.
I used Northwind database (datasource) for testing.
The steps are as follows:
1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.


For Complete Solution  Click Here