Many prompt queries make the report slow, how do I speed it up

    Having a lot of different prompts or having a prompt that displays a lot of entries can slow down the execution of a report. This document describes methods of improving this.

Resolving the problem

   The first, and easiest thing to do is to ensure that your prompts are retrieving their data from the smallest dataset possible. Thus, if the same data exists in two different (joined) tables, use the smaller table.

   Second, you can try to modify the prompt query to include a filter that will reduce the amount of data that the prompt has to go through.

   Third, try to make your prompts based on key data instead of text data by setting the use and display options of the prompt; so if you're prompting on
Customer, you want the prompt Use to be based on Customer #, not Customer Name.

    Fourth, make sure that the data you're prompting for is indexed. This is tied to the previous suggestion, because key data is automatically indexed in most database systems.

    Fifth, and the most difficult but potentially most effective solution is to join many of your prompt queries together. There are a couple of strategies that might be effective.

If you expand the query to include other fields you can set multiple prompts to use different parts of the same query. This could be effective if some of your prompts are exclusive of other prompts. Alternatively, you can set up cascading prompts. This allows you to use the selection of one prompt as a filter for another prompt. This is highly effective in cases where you are selecting increasingly restrictive fields 

(so if you were selecting a product, you'd show a product Line prompt, and then restrict the query for the product name based on the product line that was selected). This allows the queries to show less data, which allow them to run faster.

Customizing the welcome page in Cognos Connection

For hiding links the "Manage Cognos Content" link edit the following file.

In the : <install directory>/templates/ps/portal/welcome/welcome.xml file comment out or remove the following line.

<tool name="ManageCognosContent" type="cognos" />

An HTML comment is like so,

<!-- <tool name="ManageCognosContent" type="cognos" /> -->

Restart Cognos 8 service to see the change

Tools can be permanently hidden be removing the relevant tool tag from the welcome.xml file. If a tool is not present in the welcome.xml file, it will not show up as a link in the welcome page (even with the appropriate capabilities).

How to pass an empty string to a prompt macro

Use a space for the default value in prompt macro.

The prompt, which is created in FM using prompt macro, becomes required prompt. This means that we need to supply a value in order to satisfy this prompt.

If a space is added as a default value in prompt macro, the prompt is satisfied and the report runs.

Macro will look like this: # prompt('Desk' , 'string' , ' ')#

Please note that there is space between " ".

Also, in the SDK a space needs to be used as a value to satisfy the required prompt.

For example: item.setUse(" ");

The space does not get passed to the stored procedure and the report runs successfully.

How to use _round up and down in Report Studio

1) round:

for _round, _round(0.0283,3)=0.028

_round(0.0283,2)=0.03
_round(0.0283,1)=0

2) round up:

use ceiling(numeric_exp),returns the smallest integer greater than or equal to numeric_exp.

ceiling(0.0283*1000)/1000=0.029
ceiling(0.0283*100)/100=0.03
ceiling(0.0283*10)/10=0

3) round down:

use floor(numeric_exp), returns the largest integer less than or equal to numeric_exp.

floor(0.0283*1000)/1000=0.028
floor(0.0283*100)/100=0.02
floor(0.0283*10)/10=0

How to: Display the bottom 10 in a Cognos 8 Report Studio report

Use a rank() calculation on a calculated sort key column and then filter on the rank calculation. See attached GO Sales sample report spec.

Steps:

   Create a new List Report:

   1) Add Product Name, Product Line and Revenue
   2) Add a Detail Calculation = Revenue * -1 (to get the inverse ranking)
   3) Add a Detail Calculation = rank() on the calculation above
   4) Add a filter = rank <= "bottom" (a parameter to enter the number for bottom)

How to do page breaks in Cognos 8 Report Studio

Steps:

1.  Created a new List Report with grouped query items.

2.  Go to Report Pages.

3.  Add a Page Set.

4.  Set the Page Set Query property to the list query.

5.  Open the Page Set Grouping & Sorting property.

6.  Add the grouped Query item to the Grouping folder in the left pane and click OK.

7.  Back in Report Pages, drag the original page (default Page1) to the Page Set Details folder.

8.  Run the report.

How to burst in Cognos 8 without a Burst Table

       This document will describe how to set up a report for bursting via email that does not require a burst table. This is the Cognos 8 equivalent of the bursting demo that was included in the Report Authoring Part 2 book for CRN. 

        Requires Argosoft mail server with an account for Alessandra Torta (atorta@grtd123.com), Notification configured in Cognos Configuration, Outlook Express with an account set up for Alessandra Torta (atorta@grtd123.com).

Resolving the problem

        Basically, you create a calculation that holds the names of the people to which you want to burst reports. This requires that their names exist in the data source in such a way that you can create an email address from the first and last name fields.

The calculation would then be used as the burst key.

Link:    How to burst in Cognos 8 without a Burst Table

How to display the Header on the first page only

 1. Open the relevant report in Report Studio.

 2. Drag a calculation into the Header Block, from the Toolbox - Insertable Objects panel.

 3. Select the Type of Calculation as Layout calculation.

 4. In the Expression Definition, type the expression as follows:

 IF ( PageNumber()=1 ) THEN ( 'This text only displays on the first page' ) ELSE ( '' )

How to set different Data Format for each measure in a Crosstab in Cognos 8

Set the Data Format on the Crosstab Fact Cell Style object.

Steps:

   

   1. Select the Measure Header (or Crosstab Node Member).

    2. Right-click, and select Fact Cell Style for "Measure Name".

or

    1. Select the Measure Header.

    2. Use the Ancestor Navigator to select the Crosstab Fact Cell Style.

How can I view the members of a cube level to report on it in Report Studio

     Enable View Members in the options of Report Studio.

     Steps:

1.     In the Tools menu, click Options.

2.     On the General tab, select the Show Members option.

You should now be able to select member items for use in your report.

NVL() function in Crosstabs not working

The Oracle NVL() functions doesn't seem to work in Crosstab- still see blank cells.

This function is applied at the detail rather than summary level (used in most Crosstabs) and so, depending upon the measures/calculations/aggregation being used, may not be applied.

Resolving the problem

1. Select the Crosstab Measure, Properties > Data Format > Number > Missing Value Characters > set this to 0.

or

2. Create a new calculated measure that uses the 'is missing' operator based on the original measure, for example:

if ([Quantity] is missing) then (0) else ([Quanatity])

Then Cut (don't Delete) the original measure so that only the calculated Measure appears in the Crosstab.

How to find out the duration of a report execution

Either you manually time it or schedule a report and then view the run history of the report.

There are two ways to see the run history:-

1.     Click on the More link under actions for your report and then select "View Run History".

2.     Go to Tools -> Schedule Management -> Schedule. From here, you can click on the More link as well and click on "View Run History".

Current date as default value in text box prompt

Text box prompt for date, would the current date to be the default value.

Add filter to the form:

[Date] = #prompt('a','string','current_date')#

How to access the My Folders of users using the administrative account

    The System Administrator role has the ability to view, delete and modify the contents of My Folders for users with profiles and able to copy the reports to another folder. Separate steps are provided for ReportNet below. If the consistency check has been ran against the content store and/or within the portal, then all "My Folders" will be deleted for all users that were removed from the authentication source and all data in those folders will be lost. All "My Folders" for users still active in the authentication source will remain intact.

Steps:

Cognos 8.2 and earlier:

1.     In Cognos Connection, go to the "Tools" menu, then click on "Directory". "Users, Groups, and Roles" should be the selected tab.

2.     Select the security namespace that contains your users (not the built-in Cognos namespace). Active users, users that have logged into the portal in the past, will have their username displayed as a hyperlink.

3.     Click on the name, then click on the "My Folders" link for that user to view its contents.

4.     The System Administrator can then cut, copy, or delete any reports that they find within that user's My Folders. Reports will need to be copied and pasted elsewhere first in order to run.

Cognos 8.3 and 8.4: 

1.     In Cognos Connection, go to "IBM Cognos Administration", then click on the "Security" tab. "Users, Groups, and Roles" should be the selected menu on the left.

2.     Select the security namespace that contains your users (not the built-in Cognos namespace). Active users, users that have logged into the portal in the past, will have their username displayed as a hyperlink.

3.     Click on the name, then click again on the My Folders link for that user to view its contents.

4.     The System Administrator can then cut, copy, or delete any reports that they find within that user's My Folders. Reports will need to be copied and pasted elsewhere first in order to run.

How to apply simple conditional formatting in Report Studio?

Highlight data in a report to better identify exceptional results. For example, to identify sales representatives who have exceeded their quota, create a condition that checks if each representative's sales for the year is greater than their quota for the year.

 Steps:

1.  Open the report that you want.

2.  From the File menu, click Variables.

3.  Create a variable by clicking the add button bellow the Variables box.

4.  In the Name box, type a name for the variable.

5.  In the Type box, select the type of variable you want to create:
            To create a variable that has only two possible values, yes and no, click Boolean.

            To create a variable whose values are string-based, click String.

6.  Click OK.

7.  Click the Edit button below the Expression box.

8.  In the Expression Definition box, define the condition.

9.  Click OK twice.

10. In the work area, click the column that you want to highlight based on the condition you just created.

11. Click the Condition Explorer button .

          The Condition Explorer dialog box appears.

12. Click the variable you just created.

13. In the Properties pane, click the Conditional Style property.

14. In the Variable dialog box, click the Variable box.

15. Click the variable you want to assign to the object.

16. In the Values box, select the values you want the condition to support.

         Tip: A default value exists for each variable you define, and it is always selected.

17. Open the Condition Explorer dialog box again, and click one of the possible values for the variable other than the         default value.

        For example, if you created a boolean variable, click the Yes value.

18. In the Properties pane, specify the formatting that you want to highlight the column with when the condition is           satisfied.

        For example, click the Border property to create a thicker border around the column.

19. Repeat steps 17 to 18 for other possible values defined for the variable.

20. Repeat steps 10 to 19 for other objects that you want to highlight.

Tip: 

    In the Condition Explorer dialog box, click (No variable) to view how the report looks when no variable is applied.

When you run the report, the report objects to which you applied the variable are highlighted when the condition is satisfied. For example, if you created a Boolean variable, the objects are highlighted when the condition is met.

HowTo: Change the default signon used for a datasource

How to avoid data source security login prompt when running a report. When the users attempt to run a report, they are prompted to enter the login ID and password for the database.

Resolving the problem

Either there was no signon defined for the datasource being used for the report, or the credentials were invalid. We need to go the Signon datasource object, and enter in valid database credentials.

Steps: To change the signon user and password:

     

     1.   In Cognos Connection, click the Tools button, and select Directory.

     2.   Once the directory screen loads, click the Datasources tab.

     3.   Find and click on the name of the datasource.

     4.  The next screen will also show the name of the datasource. Click on it.

     5.   Click on the Set Properties icon.

     6.   Once the properties screen loads, click the Signon tab.

     7.   Near the top of the page there is an Edit the signon link. Click on it.

     8.   On this screen, enter in an appropriate user name and password.

Note that there are two levels involved that usually have the same name (the name of the datasource), so ensure that your breadcrumbs look similar to:

Directory > Cognos > [datasourcename] > [datasourcename]

If you don't see a signon tab in the properties screen, but you do see a Connections tab, then you haven't drilled down deep enough.

How to define more than one schedule for a report

There are two different methods to run a report at different scheduled times.

          

                    create a separate job for the report. For example, to have 'report01' report to run at 2 different schedules, create 2 jobs containing the same report and set the schedule for the jobs accordingly. So each job will be associated to the same report but will have a different schedule.

         create a Report View for the report, and set a schedule for the report view object. Report Views are similar to shortcuts in that they refer to the original report. Thus, if the design of the original report changes, the Report View doesn't need to be modified. And, because the Report View is a distinct object, it can have its own set of schedules and run options. Note that if the original report is moved, the Report View will need also to be updated to reflect the new location.

The request time out specified in the options dialog has been exceeded

Running a report in Cognos 8 Report Studio, the request time out settings could be changed to allow more or less time.

Symptom

The request time out specified in the options dialog has been exceeded.

Resolving the problem

In Cognos 8 Report Studio, from the toolbar:

Tools > Options > General Tab > Request time out (seconds):


Change the existing value to allow more time to run the report

Drill down for multiple items using one query

Drill down on a chart and have a crosstab synchonized to the drilldown so that it shows detailed information without having to drill down on the crosstab. 

Resolving the problem

You can have multiple items (lists, charts, crosstabs) linked to a query. When drilling down on one of the items, the other ones will also display the detailed information if they are all using the same query, and if each of the layout objects contains the query item which the user is drilling down on.

You can also have one item show more detailed information on one axis compared to the other. For example, a report with a crosstab and a chart. The crosstab can display Years, Products and Revenue while the chart displays Quarters, Products and Revenue.

If you are trying to synchronize drilldown with a 2nd layout object which does not display the query item being drilled on, you need to add this query item to one of the edges in the layout object. For example, if you are trying to drill down on a crosstab and you want to have a pie chart which does not contain the drilldown query item synchronize with the crosstab, you need to add that query item into one of the Chart Node Member objects. See the attached report spec "chart drilldown sync.txt" for a demonstration of this technique based on the Great Outdoors samples.

Steps:

1. Create a crosstab report.

2. Add the items to the crosstab.

3. Add a chart to the report.

4. In the chart Query properties, select Query 1 (the one you created for the crosstab).

5. If you will not be displaying the query item the user will drill on in the chart, click the Unlock button on the toolbar.

6. Drag the query item into one of the existing Chart Node Member objects, so it sits to the left of the item you are displaying.