QUERY STUDIO

INTRODUCTION
•Query Studio is the ad hoc reporting tool for ReportNet.
•In Query Studio, you can
–View data
•Connect to a data source to view data in a tree hierarchy.
–Create reports
•Use the data source to create reports, which you can save and reuse.
–Change the appearance of reports
•Improve the layout of your report, like creating a chart, addinga title etc.
–Work with data in a report
•Use filters, summaries, and calculations to compare and analyze data.

Launching Query Studio
•Launch your web browser,
•Type the URL provided by your Administrator, this will launch ‘Cognos Connection’
•On homepage, in rightmost upper corner click Query Studio.
•Next page lists available packages, Select the one you require to work on.

URL for CognosConnection
•http://localhost/cognos8/

Creating a Report
•To create a report :
–Select multiple report items from package
–Click Insert
OR
–Double click on individual item
& your basic report is ready for further advanced formatting !

Running a report
•You can run a report to retrieve any recently updated data from the data source.
•Cognos provides following options for execution :
–Run with All Data
–Preview Reports with Limited Data
–Preview Reports with No Data

Report Output Formats
•Once you are satisfied with the report, you can save it, print it, retain it for other usage.
•You can run the report in
–PDF format
–Excel 2000 single sheet format
–Excel 2002 format
–CSV format
–XML format
•For running a report you can:
–click on Run button for All Data & Prompts availability
–or for more options click on Run Reportlink from Menu

Advanced Run Options
•Advanced options under Run Report Menu enable users to deal with Summaries & Drill functionality.
•Query Options:
–Automatically generate footer summaries for measures
–Automatically summarize detail values, suppressing duplicates

•Drill Options :
–Enable drill up and drill down in the report output (If the hierarchy is defined user can navigate from one level to other)
–Enable drill through from a package in the report output (Jumping from one report to other)

Report Types
•In Query Studio, you can create the following report types:
–List reports
–Crosstabreports
–Charts
•You can also combine a chart with either a list report or a crosstab report.

List Reports
•A list report shows data in rows and columns.
•By default, Query Studio builds list reports when you create a report from scratch.
•Query Studio automatically suppresses duplicates, summarizes detail values, and generates footer summaries for measures.

Grouped List Reports
•A simple list report that contains columns of unique values is easy to understand.
•After you add more columns, you may find duplicate values in your report.
•Grouping a report sorts the data and suppresses duplicate values in the grouped column.

Steps to create Grouped List
•From your existing list report, click on the desired column title to select for grouping.
•Click on the Group button .
•For multiple columns to be grouped select them all using ctrl+click.

Crosstab Reports
•A crosstab report shows a measure at the intersection of each row and column.
•This is useful for
–showing more information in a smaller area
–a better view for analysis.

Steps to create a crosstab
•Select desired column to place on X axis
•Click on Pivot button .
•Your crosstab is ready.
•You can have multiple values on X axis as well as Y axis of the crosstab. This is called Nested-Crosstab.

Charts
•Use charts to present information graphically.
•A report requires at least one measure and one non-measure to produce a chart.
•How the data is plotted depends on the chart type.
•The maximum number of non-measures is two.
•You can plot any number of measures in a report.
Steps to create charts
•Click on Charts button or from Menu, click Change Layout& select Chart…
•Select type of chart as per your requirement
•By checking options decide whether your want to display values in chart & want to see only chart or with table.•Click OK.
•To remove chart : –click chart button , select chart type None. Click OK.

ChangingtheAppearance
•You can change the appearance of your reports to make them easier to understand & interpret without changing the underlying data.
•This can be done by –
–changing the title
–collapsing or expanding a report (Multi Group Report)
–reordering report items
–changing the heading name
–swapping rows and columns
–controlling the number of rows per page

Collapse & Expand a Report
•You can view only the details you want by expanding and collapsing a report.
•You can expand and collapse only those reports that contain grouping or crosstab reports that contain nesting.

Steps to expand or collapse
In an open report :
•Click on Collapse Report button
•Repeat it till you go to desired Level.To Expand collapsed Report
•Click on Collapse Report button•Repeat it till you go to desired Level

Steps to Reorder Columns
•Click the heading of the column that you want to move.
•Click the cut button on the toolbar.
•Click the heading of the report item before which you want to insert the column.
•Click the paste button on the toolbar.
•To position a column as the last column, paste with nothing selected.

Swap Rows and Columns
•You can interchange rows and columns in a crosstab, a chart based on a crosstab, or both.
•To Swap :–Click the swap rows and columns button on the toolbar.–If you have both a chart and a crosstab report open, you can specify which to swap.

Specify the Rows Per Page
•You can specify the maximum number of rows of data to appear on the page.
•By default, Query Studio shows 20 rows of data. More the rows to retrieve, more the time is needed to retrieve data.
•You can improve performance by showing fewer rows.
Steps to control Output Rows
•From theChange Layout menu, click Set Web Page Size.
•In the Number of Rows box, specify the maximum number of rows you want to appear on a page.
•Click OK.
•To see row numbers in your report, click the Show row numbers check box.

Sorting
•Sorting columns help in re-arranging them in Ascending or Descending order.
•To sort, select a report item, click on Sort button.
–By default it will sort ascending,
–To make it descending, re-click Sort button while sorted report item is selected.
•A small arrow is displayed (upward/downward) in report item name, when it is sorted.

Filters
•You can use a filter to specify the subset of records that the report retrieves.
•You can filter textual, numeric, or date & time data. When filtering measures, you can apply the filter to :
–details
–groups in reports that contain summaries
–individual records in the database for measures only

Types of Filters
•Detail :
–When you filter on details, you filter the values that appear in the detail rows of your report.
•Grouped Summaries :
–When you filter on summaries, you filter the values in the footers. It Filters groups from your report.
•individual records in the database (for measures only)
–When you use a filter based on individual records in the database, it is these underlying values that are filtered.

So for the filter we learned
•Filtering at value in database
•Filtering at summarized row value in report
•Filtering at group by results

Custom Groups
•You can create custom groups to produce a new report item containing values that are meaningful to you.
•Use custom groups to
–convert a list of numeric results into broader categories
–E.g., break down sales results into Low, Medium, and High.
–reduce the number of values into a smaller, more meaningful set,wiz -change a list of employees into My Team and Others.
•You cannot collapse a report that contains custom groups.

Creating Custom Groups
•Click the heading of the report item for which you want to create a custom group.
•From the Edit Data menu, click Define Custom Groups.
•InCustomization type, choose how to define the values:
•To create a group of values based on individual items, click Individual values.
–In the New group name box, type a name, and click the down arrow link to add it to the Custom groups box. In the Available values box, click the values you want, and click the left arrow to add them to the Custom group values box.
•To create a group of values by defining a range, click Ranges.
–In the New range name box, type a name. In the From and To boxes, specify the criteria you want, and click the arrow to add to the Ranges (in sequential order) box.
•In the Item name box, type a name for your report item. Click OK.

Sections
•Creating sections shows the value of a selected report item as the heading of a section.
•If you create a chart for a report that is grouped in section headings, one chart appears for each section.
•To create a section, select a report item & click on Create Sections button .

Auto Summarization
•Query Studio automatically
–suppresses duplicates.
–summarizes detail values.
–generates footer summaries.
•You can turn this feature Off or On by:
•From the Run Report menu, click Advanced Query.
•In the Advanced Query Options dialog box, choose an option:
–To turn off the automatic generation of footer summaries for measures, clear the Automatically generate footer summaries for measures check box.
–To turn off the automatic suppression of duplicates and the summarization of detail values, clear the Automatically summarize detail values, suppressing duplicates check box.
•Click OK.

Changing Data formats
•You can use predefined formats to change the appearance of numbers, dates, and times without changing the underlying data.
•Supported Formats:
–Default
–Number
–Currency
–Percentage
–Scientific
–Date & Time.
•To change format:
–Click the heading of the report item you want to format.
–From theEdit Data menu, click Format.
–Or just select a report item & right-click it, from the menu select Format.
–Specify the format you want & set other options.

Calculations
•You can perform many types of calculations in Query Studio.
•Calculation results are not stored in the underlying data source.
•The results are always based on the most current data in the data source.
•They can be :
–Summaries (Aggregations)
–Calculations (Arithmetic)

Summaries
•Use the Summarize command to add or remove footer values, or to change how detail values are calculated.
–You can apply a summary to
–detail values (for detail rows )
–summary values (for footers)
•Advanced summaries can be performed using multiple report items to create a calculation & then summarizing it.

Creating a summary
•Click the heading of the report item you want to summarize.
•From the Edit Data menu, click Summarize.
•In the Summary for footers box, click the function you want.
•If you want to apply
–one summary calculation to the detail values and a different summary calculation to the footer values,
–or you want to change the default order of operations in a calculated column,
click the Advanced link.
•Click OK.

Calculations
•You can perform calculations in a report using data from one or more report items.
•You cannot edit a calculation. Instead, you must delete the calculated report item and recalculate it.
Creating Calculation
•Click the headings of the report items you want to include in the calculation.
•Click the calculate button on the toolbar .
•Select Operation Typefrom Arithmetic, percentage & analytics for measure items.
•OrOperation Type from First Characters, Last Characters, Concatenation & Remove trailing spaces for dimension items.
•Depending upon your Operations selection Operation list is updated. Select desired one.
•Specify a name for your calculation, as it will appear in reportas report item.
•Click OK.

Conditional Styles
•Conditional styles are helpful when certain data needs more attention.
•Using this feature one can highlight certain values from report that don’t fulfill or fulfill certain criteria.
•Users can set color, font etc. properties for these specific cells.
Steps to define conditional styles
•Select desired column for styling.
•Click Change Layoutfrom menu, click Define Conditional Styles
•If it is numeric column, define Highest or Lowest value, & select built-in styles or define custom style.
•If it is character value column, you can select values or define range.

Drill Through
•You can navigate from a Query Studio report to another target.
•Before navigating to another target, a drill-through definition must be created in the package.
•This helps in analyzing multiple reports based upon specific query items.

•To drill through the current report,
•Open the report you want in Query Studio.
•Right-click the column from which you want to navigate and click Go To.
•Click the target you want to navigate to.
•& you are navigated to the target report you selected.

Scope Of Report
•The scope specifies where the drill-through links are available in the source reports. For example, if you select Countries as the scope, in every source report that includes Countries, users can access links for Countries and all query items within Countries