Interview Questions on .Net and Msbi Technologies

OverviewIn an earlier section we covered Business Intelligence Development Studio (BIDS), the tool that developers use to create reports. BIDS provides a Report Designer which allows you to define every aspect of your report. In addition BIDS provides a Report Wizard which will guide you through the steps to build a simple report.

With the Report Designer you start out with an empty canvas; you define every aspect of the report yourself. When you are getting started with Reporting Services, the Report Wizard should be your first choice. Once you've built a report with the wizard, you can continue customizing it with the Report Designer. When you reach the point where you are comfortable defining every aspect of your reports and you want to build sophisticated reports and dashboards, then you should use the Report Designer.

In this section we will use the Report Wizard to build a simple report. We will walk through the following steps:

Launch the report wizard
Create a shared data source
Design a query
Select a report type
Design a table
Choose the table layout
Complete the wizard

Launch the Report Wizard

OverviewThe Report Wizard guides you through the steps to build a simple report. In this section we will launch the report wizard and get going with creating our first report.

ExplanationIn the earlier section on Projects and Solutions, we created a blank solution and added a Report Server project to the solution. If necessary open the solution by clicking File, Open, Project/Solution from the top-level menu and navigating to the solution file; e.g. MSSQLTips_SSRS_2008_Tutorial.sln. The BIDS Solution Explorer shows our Report Server project along with Shared Data Sources and Reports nodes as shown below:

Right click on the Reports node and select Add New Report to launch the Report Wizard as shown below:

Note the checkbox "Don't show this page again". You way want to click it as there isn't much need to see this dialog every time you want to design a new report using the wizard. Click the Next button to create a data source for your report which we will cover in the next section.

Create a Shared Data Source

Overview
The Data Source contains the information necessary to retrieve the data we want to render on our report. Reporting Services can access data in relational databases, OLAP databases, and just about other any data source for which you have an ODBC or OLE DB driver.

When we create a Data Source we can specify it as shared which means it can be used by any report in the same project. As a general rule you probably want to create Shared Data Sources. If a Data Source isn't shared it just means that its definition is stored inside the report and it cannot be shared with another report.

In this section we will walk through the details of creating a Shared Data Source.

ExplanationAfter launching the Report Wizard you will be presented with the Select the Data Source dialog as shown below:

Our project does not have any Shared Data Sources yet so we do not have the option of selecting an existing Shared Data Source for our new report. Instead we will have to define a Data Source. We will have to supply the following:

Name - choose a descriptive name for this Data Source; we'll use AdventureWorksLT. Do not put any spaces in the name; if you do you will get an error message when you complete the wizard and your Data Source will not be created
Type - select from the available options in the dropdown list; the default value of Microsoft SQL Server is the correct one for the AdventureWorksLT database that we are using
Connection String - enter the connection string for your Data Source; usually you will want to click the Edit button to enter the details and have the connection string created for you
Edit button - rather than entering the connection string, click this button to display the Connection Properties dialog where you can enter some information and have the connection string created for you
Credentials button - click this button to display the specify Data Source Credentials dialog where you can specify the credentials to use when connecting to your Data Source
Make this a shared data source checkbox - click this checkbox to create a Shared Data Source; any report in the same project can the use this Data Source

Click the Edit button to display the Connection Properties dialog; fill in your Server name and select the AdventureWorksLT database as shown below:

The Server name is the one where your SQL Server database is deployed. If you are running a named instance of SQL Server, you will need to specify the Server name as SERVERNAME\INSTANCENAME. If you are running SQL Server locally you can specify localhost in place of SERVERNAME. You should click the Test Connection button to verify that you are able to connect to the database then click OK to close the dialog.

Click the Credentials button to display the Data Source Credentials dialog as shown below:

The default selection Use Windows Authentication (Integrated Security) is okay for our purposes. This means that Reporting Services will connect to the Data Source using the Windows credentials of the person running the report. Note that when your report is complete and you deploy the report and Data Source for others to use, you can select a different option if necessary. For now we'll stick with the default.

After completing the above steps, the Select the Data Source dialog will look as shown below:

Click Next to move on to the Design the Query dialog which we will discuss in the next section.

Design the Query

Overview
The Design Query step of the Report Wizard allows us to specify what data we want to retrieve from our Data Source and render in our report. In this section we will walk through the details of defining a query to retrieve the data to render on our report.

ExplanationThe Design the Query step in the Report Wizard will display the dialog as shown below:

You can click the Query Builder button to graphically build your query or you can type your query directly into the Query string textbox. The following is an example of a query that you could type in:

SELECT
  c.ParentProductCategoryName
, c.ProductCategoryName  
, SUM(d.LineTotal) Sales
FROM SalesLT.Product p
JOIN SalesLT.vGetAllCategories c 
  ON c.ProductCategoryID = p.ProductCategoryID
JOIN SalesLT.SalesOrderDetail d 
  ON d.ProductID = p.ProductID  
GROUP BY  
  c.ParentProductCategoryName
, c.ProductCategoryName
ORDER BY
  c.ParentProductCategoryName
, c.ProductCategoryName

The above query will provide us with a sales summary broken down by product category. Copy and paste the query into the Query string textbox in the Design the Query dialog. Alternatively you could click the Query Builder button and graphically design the same or a similar query. The Query Builder dialog displays the Query designer that you are accustomed to seeing in products like Microsoft Access.

Select the Report Type

Overview
The Select Report Type step of the Report Wizard allows us to choose from a Tabular or Matrix type of report. In this section we will discuss the details of these available report types.

ExplanationThe Select Report Type step in the Report Wizard will display the dialog as shown below:

The tabular report type is the traditional report with page headings, column headings, subtotals and totals running down the page. The matrix report type is one where we define the fields that go on the columns and rows. In addition the matrix report type provides interactive drilldown capabilities. We'll create a tabular report as this will provide a simple and familiar example.

Design the Table

Overview
The Design Table step of the Report Wizard allows us to layout the available fields on our report choosing between Page, Group, and Details.

ExplanationThe Design Table step in the Report Wizard will display the dialog as shown below:

The Available fields list is populated based on the query that you defined in the previous step in the Report Wizard. Click on a field then click on the appropriate button where you want to put that field. Fill in the dialog as shown below:

The following is a description of the buttons in the above dialog:

Page - put a field in this list when you want to begin a new page when the value of the field changes; e.g. to start each ParentProductCategory on a different page
Group - group by the fields in this list
Details - the fields in this list appear in each row of the report

Choose the Table Layout

Overview
The Choose Table Layout step of the Report Wizard allows us to choose a stepped or blocked layout and whether to include subtotals and enable drilldown.

ExplanationThe Choose Table Layout step in the Report Wizard will display the dialog as shown below:

The default of Stepped displays the groupings as shown above. Block moves things in a little closer which saves space, but it disables drilldown. Include Subtotals will provide intermediate totals based on groupings. Enable drilldown will initially hide details and allow expanding with a click on the plus icon.

Fill in the dialog as shown below:

Choose the Table Style

Overview
The Choose Table Style step of the Report Wizard allows us to choose from a number of different styles. This is a purely cosmetic choice; each selection provides a different color scheme.

ExplanationThe Choose Table Style step in the Report Wizard will display the dialog as shown below:

Choose a style from the list of available options the click the Next button to move on to the Completing the Wizard dialog which we will discuss in the next section.

Complete the Wizard

Overview
The Completing the Wizard step of the Report Wizard displays a summary of our choices from the previous dialogs.

ExplanationThe Completing the Wizard step in the Report Wizard will display the dialog as shown below:

You should provide a descriptive name for your report in the Report Name textbox; e.g. ReportWizardExample. You can click the Preview report check box if you would like to see what your report will look like. Scroll through the Report summary to review your choices. If there is something you want to change, click the Back button to revisit the completed dialogs.

Click the Finish button to have the wizard generate your report. You will now see your report in the Solution Explorer as shown below:

The report will also be displayed in the Report Designer. Click on the Preview tab to render your report. A portion of the report is shown below:

We're going to make a couple of changes to the report. Click on the Design tab; you will see the following:

We're going to put spaces between the words in the heading, make the columns a little wider, and change the format of the sales numbers. Here are the steps:

Click in the report heading between the t and W; add a space; do the same for d and E
Click in the ParentProductCategory cell, an Excel-like grid will appear, click between the cells at the top of the grid and drag to widen them; you will see the cursor change to <--> when you hover between the cells
Click inside the [Sum(Sales)] column, locate Format in the Properties window and type C0; this will format the cell as currency with no decimals. If you don't see the Properties window, click the top-level View menu then select Properties Window of click F4. Repeat for the [Sales] column.

After making these changes the report design should look like this: