1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Business Intelligence Template Projects

Discussion in 'SQL Server' started by MinalS, May 15, 2015.

  1. MinalS

    MinalS New Member

    Joined:
    Jul 8, 2014
    Messages:
    138
    Likes Received:
    30
    Trophy Points:
    0
    Report Server project template

    The report server project creates two blank folders as Shared Data sources and reports. User can start creating the report.

    In the Shared Data Sources folder, the following options are available.
    1. Add New Data Source: It provides the shared data source window where user can start configuring the shared data source
    2. Add, New Item: It provides user with the report project category. The add new item report category consists of report wizard, report and data source templates
    3. Add, Existing Item: It allows user to browse the file system for .rdl and .rds file types
    In the Reports folder, the following options are available.
    1. Add New Report: A new report wizard is available
    2. Add, New Item: It provides the report project category consisting of the report wizard, report and data source templates
    3. Add, Existing Item: It helps user to browse the file system for .rdl or .rds file types
    Report authoring

    In the process of authoring, the data sources are connected to the databases and the queries are executed against the database. The data retrieval, filtering, sorting, applying aggregations are applied before the data is displayed. The Query designer tool helps for the data retrieval.

    Data sources in reports

    There are two ways for defining the connection to the data sources. They can be embedded or shared. The connection and management of data is different in both the sources. The wizards are available for the creation of data sources for connection set up.

    Embedded data sources

    The embedded data source connection is report specific. The information is embedded in the report definition or is in the internal of the description. The Report designer provides the embedded connection.

    Click Reports, Add new report for the wizard startup. Click Next Button. Select a Data Source page of the wizard opens.

    [​IMG]

    The previously defined shared data source is the default shared data source. A new data source is enabled.

    Shared data sources

    The shared data source is shared among the number of reports. They are defined inside or outside the Studio application. The default data source type option is shared. A .rds file is added to the shared data source folder in the report server project.

    Report Data and Query Designer

    The query to be bound to the report is finalized; the data used in the report is available from the Report data window. The view menu helps accessing the report. There are built in fields in addition to the database fields. The folder contains report properties, folders for report parameters, and report images.

    Report designer

    For creating the report server project, add a report, the design interface is displayed. It is also known as report designer. It consists of report related items, design and preview panes, a report menu. Click on Reports to get the report menu items.

    The report building begins by placing controls for displaying data on the body of the report. The report items can be dragged and drop into the controls used for binding the data.

    The report item toolbox contains following components.
    1. Table: They are added as data regions of the report and links to the data set.
    2. Matrix, List, Chart, Gauge: The Table, Matrix and List are known as Tablix. They call the data source wizard once placed on the report body.
    3. Textbox: The plain text as column header is added in it. The data or built in fields can be added.
    4. Image: They are links embedded, external or database referenced image.
    5. Line and Rectangle: They are the graphical elements used for improving the design. The data regions can be present in the rectangle
    [​IMG]

    Expression Builder

    The report menu items can be added to the headers and footers. They contain textboxes which can contain page number, execution time, etc. These can be dragged into the textboxes.

    The Expression menu item is where user needs to build an expression consisting built in fields, database fields, etc. User can concatenate the two fields together and provide a value to be placed in the textbox for expression creation. The expression can be set by selecting the Category, Item and values option.

    Creating a report server project using Report server wizard project

    Start the Visual Studio 2008 application and select the business intelligence project.
    1. Click File, New Project, select report server project wizard from the visual studio installed templates. Add an appropriate name to the project, location, and click ok

      [​IMG]
    2. Click Next button. The Select Data Source page opens as shown below:

      [​IMG]
    3. Choose the new data source from the drop down list. Accept the default data source type and click Edit button
    4. In the connection properties window, click refresh button. Select the server name. Use the Windows Authentication.

      [​IMG]
    5. Enter the database name in the dropdown list. Click on Test connection button for checking the information. A test connection message success is displayed.
    6. Click Ok for the test results window and connection properties window
    7. The Select data source page contains the connection information in the connection string field
    8. Once the connection is established and the data to be retrieved, user needs to show it in a report
    9. Click Next button. The Query designer window opens for the user. User can either use the query builder or add the statements directly.
    10. In the pane diagram/objects, right click and select Add Table.

      [​IMG]
    11. Close the Add table button and click Close button
    12. Place a checkmark in the columns from the student table. The query builder configures the statement and the grid is filled up.
    13. Right click in any of the panes and select Execute SQL option. The executed SQL statement and the result appear in the result pane.

      [​IMG]
    14. Click on the Next button. The select report type page of the wizard appears
    15. In the design table page, select the studid by clicking the page button. Select the available fields by clicking the Group button. And use the Details button to add the remaining fields. Click Next button.

      [​IMG]
    16. In choose the table layout pane, accept the default choice steeped. Add checkmarks for the include subtotals and enable drilldown. Click Next button
    17. In the choose table style page, select the style and click Next button
    18. In the deployment page, delete the entries in both the report server and deployment folder textboxes. Click Next button
    19. In the completing wizard page, add a new name to the report. Click Finish button.
    20. The program starts running and the value is generated.

      [​IMG]
    Report Model

    The data present in the backend of the database is relational database with data in tables. The business user does not have knowledge of the data in the database. The report model creates an easy way for the business users for making decisions using the report model.

    The report model is created such that the report author does not need to understand the data connectivity, queries execution, filtering, sorting, and using parameters. The report model is a data representation used for describing the data in business.

    Creating a report model using Visual Studio

    For creating the report model project, the built in template of the project is used. The wizards are used for driving the model. The steps for creating a report are as mentioned below:

    1. Click Start, All Programs, Microsoft SQL Server, SQL Server BIDS
    2. Click on File, New, Project
    3. In the visual studio installed templates, click report model project

      [​IMG]
    4. Add a new name to the project, click ok
    5. Right click on the DataSources folder and click add new data source
    6. Click Next button. Select how to define the connection window opens
    7. Click New button. The connection manager window opens.

      [​IMG]
    8. Select the appropriate server name, select windows authentication, click select or enter database name. Select the demo database from the list
    9. Click Ok. Click Next button on the select how to define the connection page
    10. In the complete wizard page, change the default data source name to a new one. Click Finish
    11. Right click on the Data source views folder. Click Add new data source view.
    12. The Welcome to the data source view wizard page to the source gets displayed
    13. Click Next. Select the data source wizard displays.

      [​IMG]
    14. Click Next, select the tables and views page of the wizard is displayed.

      [​IMG]
    15. From the available objects, select the objects and click on the >> button and add them to the Included objects
    16. Click Next. In the completing wizard page, change the default name to different one.
    17. Click Finish button. The .dsv file gets added to the data source views.
    Defining the report model

    The report model hides the complexity of the data through the user friendly interface. The following steps are used for creating a user friendly one.
    1. Right click report models folder, select add new report model
    2. Click Next button. In the select data source view, the created data source view is displayed
    3. Click Next button. Accept the defaults and click next button
    4. Accept the default choice and click next button
    5. Add the name of the database and click Run. Click Finish
     

Share This Page