Build Advanced Cognos Reports
On this page:
Report Design Layout
Insert Calculation
The Insert calculation field can be used to update existing fields to represent the information you need in a report. For example you can use insert calculation to create a formula for a column of information or you can use it to concatenate a two fields into one, such as creating a Name field from First_Name; Last_Name fields.
- Select the column(s) header(s) for the calculation.
- Click the Insert Calculation button.
- Fill out the form displayed for the calculation and click OK.
Example: Concatenation
- Select the column headings for First_Name Last_Name, use the SHIFT key to select multiple fields.
- Select the Insert Calculation field drop down list and choose Custom.
- Set the Separator to a space.
- Click OK.
Prompts
Add prompts to a report to add interactivity for users. Prompts act as questions that help users to customize the information in a report to suit their own needs. For example, you create a prompt so that users can select a date range for a fiscal period or filter by a department.
Prompts are composed of three interrelated components: parameters, prompt controls, and parameter values. Parameters are based on parameterized filters and form the questions to ask users. Prompt controls provide the user interface in which the questions are asked. Parameter values provide the answers to the questions.
Add a Prompt in a report
- Switch from the data items area to the Toolbox by clicking the Toolbox button.
- Click and drag Block from the Toolbox to the area of the report you want to put the prompt.
This step is not required but helps format the report for a better layout. - Click and drag the prompt type from Toolbox
- Choose Create New Parameter and enter a name in the text box then click Next.
- Choose Package Item ellipse and select the field that the prompt will be used against.
- Click Finish.
- Run the report
- Go to Tools: Build a report page.
- Choose Create New Parameter and enter a name in the textbox then click Next.
- Choose Package Item ellipse and select the field that the prompt will be used against.
- Click Finish.
- Run the report
Build a Prompt page
- Go to Tools: Build a report page.
- Choose Create New Parameter and enter a name in the textbox then click Next.
- Choose Package Item ellipse and select the field that the prompt will be used against.
- Click Finish.
- Run the report
Delete a Prompt
To delete a prompt from a report you need to delete the prompt object and then delete any filters created.
- Select the Prompt object and choose the Delete button.
- Choose the Query Explorer button and select the query you built.
- Go to the Detail Filters section, select the filter and click the Delete button.
Add reference to prompt in report
You can add a reference to the prompt that is set for the report as part of the heading or listed in the report, so it is clear it is a filtered report using a Query calculation from the Toolbox.
- Click and drag Add Query Calculation in the place it should be displayed.
To create a space for the Query calculation you may want to click and drag a block to the report layout. - Enter a name for the new object and click OK.
- Choose Parameters button and double click the name of the prompt that should be displayed.
- Run the report.
Drill through to another report
You may want to drill through from one report to another such as a Summary report to a Detail report. To set up a drill through you would create an object to drill through and follow these steps. For this example we will use the Query Calculation object created above, but you can drill through from any object on the report.
- Right click the report object and choose Drill Through.
- Click the Add button.
- Choose the ellipse for the Report and choose the report to be drilled to.
- Set the following:
- Target Report: name of report to be drilled to
- Action: Run the report,
- Format: default;
- Check Open in new window
- Parameters: click Edit: set to the field that filters the original report
- String: required,
- Value: Name of object that filters
- Method: Pass Parameter value
- Property to pass: default
Import Data from an Excel spreadsheet
- Go to design view of the report you want to add the data to.
- Click the button Manage External Data.
- Click the Browse button and choose the file with the new data then click Next.
- Click the ellipse for Existing query/subject report then Choose Query/Subject and expand the package.
- Choose the table with the field that has matching data.
- Under External data choose the field that matches and under Existing Query/ Subject choose the matching field.
- Click the New link button then click Next.
- Leave the defaults and choose Next again
- Set the mapping options and choose Finish and then Publish.
- From the new table add any new fields to the report and run the report.