Sunday, February 15, 2009

REPORT STUDIO




















INTRODUCTION

Report Studio is a Web-based report authoring tool.
•Used to create business intelligence (BI) reports that analyze corporate data according to specific information needs.
•To launch Report Studio, click on Report Studiolink in Cognos connection


User Interface
The basic UI of Report studio consist of
• Insertable Objects Pane
• Properties Pane
• Explorer Bar
• Work area

Insertable Objects Pane
The Insertable Objects pane contains objects that you can add to a report.
•TheSource tab :
–contains items from the package selected for the report, such asquery items and calculations.
•TheData Items tab :
–describes the queries created in the report.
•TheToolbox tab :
–contains a variety of objects that you can add to the report, such as text & graphics.

Properties Pane
•The Properties pane lists the properties that you can set for an object in a report.
•You can obtain additional information about a property by selecting it and hitting F1.

Explorer Bar
•Click the Page Explorer button to go to a specific report page or prompt page.
–You can also add a new report page or add a prompt page.
•Click the Query Explorer button to work with queries.
–You use the Query Explorer to create or modify queries and to perform complex tasks, such as defining union joins and writing SQL statements.
•Click the Condition Explorer button to work with variables.
–You use variables to add conditional formatting to a report.

Basic Report Structure
•All reports have two components,
–a layout component that defines the report appearance
–a query component that defines report data.

Layout
•Layout : a set of pages that defines the appearance and formatting of a report.
–present the data in a meaningful way by using lists, crosstabs, charts, and maps
–give the report the appearance you want by adding formatting, such as borders, color, images, and page numbers
•Pages : containers for the layout objects that you use to build a report.
–page header (optional)
–page body (mandatory)
–page footer (optional)

Layout Objects & Queries
•Layout Objects
–List
–Crosstab
–Chart
–Text
–Block
–Repeater
–table
•Queries determine what data items appear in the report.
–Sometimes use a simple SELECT statement.
–Other times you calculate totals or averages etc. using aggregate functions and grouped columns, or apply filters to show only the data you want.

Steps to create a report
•In the Cognos Connection Welcome page, click the Report Studio link.
•Choose a package using the Select a Package page.
•In theWelcome dialog box, choose whether to open a new or existing report
•Choose a template to define your report structure.
•Insert required objects from Insertable Objects Pane

List Reports
•If you have selected List report from template, drag the report items to work area.
•Then you can :
–Group
–Create Sections
–Convert list to Repeater
–Set level of span for columns

Locking & Unlocking Objects
•This facility provides users to manipulate the contents of some objects.
•To toggle between Lock –Unlock :
•Go to Structuremenu & click Lock Page Objects.
•To manipulate the contents of some objects, you must first unlock the object.
•For example, you have a list that contains the column Product Name. You want to insert a graphic inside the Product Name column to show an image of each product.
•Unlocking the list allows you to insert the image object inside a list column.

Repeaters
•Use repeaters to repeat items when you run the report.
•For example, you can use repeaters to create mailing labels, including customer names and addresses.
•To Convert list to Repeater :
–Click any part of the list.
–From the Structure menu, click Convert List to Repeater.
–Click any part of the rotated table.
–Click the select ancestor button in the title bar of the Properties pane and select Repeater Table.
–In the Properties pane, specify how many frames you want to create horizontally and vertically by typing values in the Across and Down underGeneral properties

Setting Group Spans
•You can set the Group level span for a column to show the value of a grouped column or a related column only when the value of the grouped child column changes.
•To set Span Level:
–Click the column for which you want to set the level span.
–In the Properties pane, double-click the Group Span property.
–Select the column you want to appear every time the previously specified column changes value, and click OK.

Creating a crosstab
•Crosstabstreat The values at the intersection points of rows and columns showing summarized information
•To create Crosstab :
–Select a crosstab template.
–Drag desired items to rows & columns structure of crosstab.
–Drag more than 1 item for columns or rows to create a nested crosstab.
–If you have List report, select a report column to pivot it & click on Pivotbutton or through Structuremenu, select Pivot List To Crosstab.

Creating Charts
•Drag Chart from InsertableObjects-Toolbox.
•When chart structure is visible, you can see 3 options for data insertion.
–1.Measures: measure items for Y axis.
–2.Categories: items for X axis
–3.Series: items for Z axis.
•Drag required items to specific fields.

Dealing with Properties
In the Properties pane you can define properties for each objectby specifying values like :
•spacing and breaking
•text flow and justification
•size and overflow
•Floating
•table properties

Working on Data
To make a report easier to read and understand by data specification in the report and how the data looks, you can :
•create additional queries
•filter data
•sort data
•create sections
•add a header or footer
•add a summary
•create a calculation
•format data
•define a prompt

Creating Additional Queries
•By default, one query exists for each report you create, but You can create multiple queries in your report, especially when you want
–to combine different report types in a single report, such as a list and chart, and have different data appear in each
–to improve performance by creating smaller queries rather than having a single large query for the entire report
–to run part of a report
–to add prompts so that each prompt value requires different data

Creating new query
•Click the Query Explorer button, and click the Queries link.
•In theInsertable Objects pane, drag the Query object to the work area.
•Populate it with query items.
•Click the Page Explorer button, and click a report page link.
•When you insert new object from Toolbox, Cognoscreates a blank new Query associated with it. You can relate your existing / custom query to this object.

Filters
•From theData menu, click Filters.
•If you want to create the filter for a specific object click it before you click Filters.
•In the Filters dialog box, decide what type of filter you want to create:
–To add a filter for rows, click the Detail Filters tab.
–To add a filter for groups, click the Summary Filters tab & Click the add button.

Defining a filter
•In the Available Components box, define the filter expression
•Click the validate expression button, and click OK.
•In the Usage box, specify whether the filter is required, optional, or not to be used.
•If you create a detail filter, in the Application box, decide whether to apply the filter before or after aggregation.
•If you create a summary filter, in the Scope box, specify the level to which you want to apply the filter.
•Click OK.

Sorting
•You can organize data values in a report by specifying a sort order
–Click the column on which you want to sort.
–From the Data menu, click Sort Ascending or Sort Descending or Advanced Sorting...


Advanced Sorting
•Unlike simple sorting, advanced sorting sorts specific report item based on some other item.
•This feature comes in handy, when you want to sort certain dimensions based upon their performance. E.g. Sorting Years based upon sales margin.

Steps to Advanced Sort
•Select the desired container, on the toolbar, click on Sort ��Advanced Sorting..
•Grouping & Sorting box appears.
•Depending upon requirement drag data items to Sort List of desired group.
•Click OK.

Sections
•Create sections in a report to show a query item as the heading of a section.
–Click the column that you want to show as a section heading.
–From the Structure menu, click Create Section.
–Or click on Section button on toolbar

Adding Header-Footer
•Adding a header or footer to make a report easier to read.
•You can use headers and footers to provide additional information or to organize data into logical sections
•To add Header / Footer
–Click a column, If you have a list and you grouped columns, you can also add grouped columns as headers and footers.
–To add a header, click the Create Header button from Structuremenu.
–To add a footer, click the Create Footer button from Structuremenu.

Adding Summary
•Adding a summary provides an overview of data in a list report.
•To add summaries to a report :
–Click the column to which you want to add a summary.
–From the Data menu, click the kind of summary you want.
–The summary appears in the report as a footer.

Creating a Calculation
•Creating a calculated column makes a report more meaningful by deriving additional information from the data source.
--•To create a calculation:
•In the Insertable Objects pane, click the Toolbox tab.
•Drag the QueryCalculation object to the location where you want to create a calculation.
•In the Name field, type the name of the calculation.
•In the Expression Definitionbox use available functions & query items to create a calculation.
•To create a calculation in the layout that contains run-time information, such as current date, current time, and user name, drag Layout Calculation fromtoolbox.
•Click OK.

Formatting Data
•Format data in a report to improve readability.
•If you do not set properties here, data is formatted according to the properties set in the model
--•From the Data menu, click Layout Data Format.
•In the Format Type box, click a format type.
•The properties that you can set for the selected format type appear in the Properties box.
•Set the properties that you want.
•Click OK.

Prompts
•Prompts act as questions for dynamic selection of data in a report.
•Report Studio provides several ways to create prompts. You can:
–use the Build Prompt Page tool
–build your own prompt and prompt page
–create a parameter to produce a prompt
–insert prompts directly into the report page
Using Build Prompt Page
•Report Studio creates the prompts for you in a default prompt page.
•Use the Build Prompt Page tool to quickly add prompts to a report.
•Follow The Sequence to build a prompt:
•Click the columns you want users to be prompted on.
•From the Tools menu, click Build Prompt Page.
•A prompt page is created that has:
–Page header
–Prompt control for each selected column
–Page footer containing Cancel, Back, Next, and Finish prompt buttons
•From the General tab in Properties select whether it is single value or multi value prompt, it should use Drop down box or list box etc.
Building own Prompts
•From the Page Explorer, click PromptPages.
•From the InsertableObjects pane, drag or double click Page.
•From the Insertable Objects pane, Toolbox tab, drag a prompt object.
•If you are creating a value or select & search prompt:
–Create a new parameter or use an existing.
–If you create a new parameter, define the expression.
–Create a new query to build the list of values for prompt.
•Display value could be different than value to used in report
–To create a cascading prompt, click the Cascade Source box, and select the parameter that represents the cascade source.
•Click Finish
Creating prompt in Report Page
You can add prompt controls directly in a report page instead ofcreating a prompt page.
•Steps to insert a prompt in report page:
•From the Insertable Objects pane, drag a prompt control to the desired location. The Prompt Wizard appears.
•Provide the information necessary to create the prompt.
•Make the prompt optional by clicking it and, in the Properties pane, setting the Required property to No. & Auto-Submitto Yes
•The prompt control is added to the report page. You can modify its properties by clicking it and making changes in the Properties pane.
Parameter calling Prompt
•Report Studio can automatically generate prompted reports based on parameters you create.
•To create a Prompt Parameter :
–From theData menu, click Filters.
–In the Detail Filters tab, click the add button.
–In the Available Components box, click the Source or Data Items tab to select the query item you want to use for the prompt:
–Select the query item & In the Expression Definition box, type an operator after the query item, or select an operator from the Functions tab.
–Type a name after the operator to define the prompt parameter. Aquestion mark must precede and follow the name. Click OK twice.
Cascading Prompts
•A prompt uses values from another prompt to filter values in its own value list.
•Steps :
•Click the prompt control that you want to make into a cascading prompt.
•In theProperties pane, double-click the Cascade Source property.
•Click the parameter that represents the cascade source, and click OK.
Cascading while creation
•If you are creating a prompt & already another prompt exists, you can link new prompt as cascade with existing.
•In prompt wizard, on Populate Controlpage, you can select existing prompt from cascading source drop down box for creating Cascade link.

Conditional Formatting
•In Report Studio, you can create variables to compare output data for conditional formatting.
•This facility helps developers to treat the output dynamically.
•The variables can be :
–Report Language Variable
–String Variable
–Boolean Variable.
Steps to create variable
•In Explorer Bar, pause/click mouse pointer on Condition Explorer
•FromInsertableObjectsdrag desired variable type to Variables pane.
•Specify conditions in Expression Editor.
•Validate the expression and click OK.
Conditional Formatting Uses..
•use conditional formatting to identify exceptional data in a report. You add conditional formatting to a report by defining variables.
•With conditional formatting, you can
–highlight data
–add multiple layouts
–create a multilingual report
1)Highlighting Data (Use of Style Variables)
•Highlighting data in report helps in better identification of exceptional results.
Steps to Highlighting Data
•Create a variable for condition specification.
•In the work area, click the desired column to highlight based onthe condition.
•Select the created / existing variable
•In the Properties pane, in Conditionalclick the Style variable property.
•Select the variable In the Variable dialog box,
•Expand the Condition Explorer, and select the value you want the condition to support.
•In the Properties pane, specify the formatting that you want to highlight the column with when the condition is satisfied.

Render Variables
•Are used to render (Display) objects based on condition
•E.g. Render Margin Column only if Margin >0.4

Drill Through (Parent Child Report)
•Creating a drill-through report links two reports containing related information.
•With this you can access related or more detailed information in one report by selecting a value in another report.
•Before setting drill through, make sure you have at least 2 reports :
–1 as parent report &
–Another as child report(s)
Setting Drill Through
•In the child report create a parameter using the column that will serve as the drill-through column. (E.g. : [Product line]=?prodline_p? as filter)
•Open the parent report.
•Click the column that will serve as the drill-through column.
•Click the Drill Throughsbutton from properties
•Click the add button.
•In the Target report box, click on (...) and select the drill-through or target report.
•In the Source query item box, click the query item whose values will be used to drill through to the target report. Click OK.

Master-Detail Report
A master-detail relationship delivers information in one report, that would otherwise require two or more reports.
•It relates the data from two different queries based upon certain matching data items.
Setting up Master-Detail relation
•In new list report, add data items.
•Unlock the container, drag another container inside the desired column & lock the container.
•Add required data items to child container. Make sure, at least 1 data item (non-measure) is common between both containers (queries).
•Select common column from child container, right click, select Master Detail Relationships..
•In Master-Detail Relationships box, click New Linkbutton, match the relation to Detail query with Master query, click OK.
•Use Page Sets to specify value based page breaks for better viewing & analysis.

Page Sets
•A page set is a set of pages associated with a specified group or level that indicates the set is to be repeated for that group or level.
•Page Sets are used to associate pages with a specific query structure to implement page breaks.
Steps to create Page Sets
•Open Page Explorer, drag Page Set from InsertableObjects.
•Select Page set,in Properties, select Query& link appropriate query to the page set.
•Drag the existing/new pages to either Detail Pagesor direct in page set.
•InProperties for selected Page Set, click in Grouping & Sorting under Data.
•Drag the data item you want to use for forcing page break upon changing its values.
•Link the pages using Master-Detail Relationshipsfor perfect functioning.

Shared Layout References
•Shared Layout References are the objects which use some another report as the Layout library.
•This helps in saving time for creating same formats in all reports.
•Comes in handy for maintaining the consistency of report Layouts.
•Used for standard reporting.
Steps to Create Shared Layout Library
•This topic is explained with the help of an example.
•Create a New blank report.
•Click on page body, click on headers button or click Structure ��Create Headersfrom menu.
•Select the header part of report.
•Insert 2 column table in header.
•Click to select table, specify a name to this object.
•In last column, drag Layout Calculation & in Expression Editor, drag f(x)AsOfDatefrom Report Functions available under Functionstab.
•In first column drag Layout Calculation & in Expression Editor, drag Report Name function.
•Save the report.

Using Shared component
•Create or open an existing report, clear its header section by removing other components.
•Drag & drop Layout Component Reference to the header.
•In dialog box, select Another Report option & select the report that you created as component library.
•Select the name you specified for table & click OK.
•Save the report & Run from CognosConnection as Report Name function works only when report is Run from CognosConnection

Creating advanced Queries
•For certain report requirements, regular package queries alone don’t serve the purpose.
•For such scenarios, creating new queries using Union, Intersect, Minusoperators or Joins, Free-hand SQL/MDX come in handy.

Union Query
•Union Query serves as its name suggests, which is derived from database concepts.
•To add multiple data items from different queries in single query, use Union.
•It fetches all values from say, [Query1].[data_item1] as well as [Query2].[data_item1], which can be used as single data item in Query3.
Rules to follow for Union
•Both the source queries must have same number of columns.
•The data-types must be matching for all the columns from source query 1 to source query 2.
•There should be no mismatch in terms of data-types or number of items in Source Queries.
Steps to create Union Query
•Go to Query Explorer.
•Create new query by dragging it from InsertableObjects.
•DragUnionto newly created query.
•Drag queries to empty slots next to Union.
•Populate these queries located in Union slots with data items from package source.
•Click on Unionoperator.
•InProperties, open Projection List
•Though you will not see any columns from your Query2, the data is lying hidden.
•Double Click on an uncommon column from 2 source queries, that is displayed , Click OK.

Drill up & Drill Down Repots
•You can create a report that allows the reader to drill down to lower level data sets or drill up to higher-level data sets, provided you are using a Dimensionally Modeled Relational (DMR) data source. This will allow you to rerun the report for a different data set without having to regenerate the report.
•An example of a dimensional hierarchy might be:
–Years -Year -Quarter -Month
•Drilling up and down allows you to view more general or more detailed information on your data within such a predefined dimensional hierarchy, without having to create different reports.

Slicer
•If you are working with a dimensional data source, you can define slicers.
•If you are filtering non-numeric data, you must select members from the data tree instead of typing member names.
•Use slicersto create dimensional filters that reduce the data included in measure rollups.
•A slicermember set is an expression that returns a set of members from the same dimension. You can create multiple slicermember sets if you want to filter across two or more dimensions.
•You cannot create multiple slicermember sets against the same dimension.

Defining a Slicer
•Select the query to which you want to add a slicer.
•Choose whether to create a slicerwith one or more members:
–To create a slicerwith a single member, in the InsertableObjects pane, on the source tab, drag the member that you want to the Slicerpane.
–To create a slicerwith more than one member, on the toolbox tab, drag SlicerMember Set to the Slicerpane. Then, drag the members that you want to the Expression Definition box.
•You must join the members in the expression by using the set function. For example: set([Fax],[Telephone])
•If you want to add members from another dimension, repeat step 3to create a separate slicermember set.


No comments:

Post a Comment