PROBLEM: Output in Excel 2003 with Cognos 8.1 the window just disappears

PROBLEM: Output in Excel 2003 with Cognos 8.1 the window just disappears

SOLUTION: Alter/set the following conditions in Internet Explorer

1. Open Internet Explorer

2. Click 'Tools' -> 'Internet Options..'

3. Click the 'Security' tab

4. Click on 'Internet'

5. Click 'Custom Level...'

6. Ensure the 'Automatic prompting for file downloads' and 'File download' are enabled.

7. Click 'OK'

8. Repeat for 'Intranet' and 'Trusted Sites'

Dynamic Sorting Based on Selection in Cognos Report Studio

Dynamic Sorting Based on Selection in Cognos Report Studio

Requirement: User should be given a choice to select the columns on which the output should be sorted. Ex: A List has Month and Quantity.  If the user selects Quantity, output should be sorted based on Quantity in ascending order.

Solution: Create a ‘Sort’ data item in the same query subject and perform ‘Advanced Sorting’ based on the ‘Sort’ data item.

Detailed Steps:

1.     Create a List report with Month and Quantity columns

2.     Create a ‘sort’ prompt with static choices as 1-Month and 2-Quantity

3.     Create a Data Item ‘Sort’ in the query subject as
     case
when ?sort? = 1 then [Month]
when ?sort? = 2 then [Quantity]
end

4.      Select 'Month' column in Report Page, select ‘Advanced Sorting’ and  add ‘Sort’ under ‘Detail Sort List’

                 5.  Repeat the above step for “Quantity” also.

Limitations: The above solution sorts both columns in the same order. i.e either by Asc or by Desc. This works only when both columns are of same data type

Scope Relationship

Scope Relationship

Scope Relationship: which is exist between measure dimension and regular dimension to define the level at which the measures are available for reports- This is available in DMR model.

What are summary filters? Grouped filters?

Summary filter: If the filter applied after aggregation then aggregated records are filtered.

Group filter: A filter which is applied to the group is known as group filter

a	10
c	12
c	8
d	6	Details	> 10	2 records
d	7	Summery	> 10	3 records
b	6
b	15

Cognos improve report performance in model

·         Default aggregation properties

·         Uses properties

·         Joins

·         Limited local

·         Remove loop's

·         Proper determinants

·         Removing non supported function in model

·         Cardinality

·         Try to use cube

·         Make sure all the "Joins" and "Join strategy" are correct.

·         Check for the cardinality.

·         Create summary tables (one of the best approach in increasing the performance)

·         Use Power play cubes as data sources.

·         You can remove loop's at that time you can improve performance at model level

·         use indexes you can improve performance

·         Change the property of query processing to Limited local or database only.

Static Choices

Requirment:

I have 4 countries in my database as USA, UK, INDIA, CHINA. In value prompts prompt control list, I want WESTERN_COUNTRIES, ASIAN_COUNTRIES instead of those country names. When I click on WESTERN_COUNTRIES,

1. From Query Explorer, open up the query for which the filters should be applied on.
2) Drag a Filter object from the Toolbox tab to the Detail Filters pane.
3) In the Expression Definition, enter the following including the #s
                        #prompt ('<prompt parameter>', 'token', '<default filter>')#
                       example: #prompt ('Countries', 'token', '[Country] IN ('USA', 'UK', 'INDIA', 'CHANA')#
4) Add a Value Prompt to the report .
5) Select the Value Prompt and in its Properties in the bottom right corner, go to the Static Choices property.
5) For each possible filter that needs to be applied, add a new Static Choice.  Make the Use the expression of the filter that you should be applied and the Display is what is shown to users in the prompt's drop down list.:

Use: <filter expression>
Display: <what is displayed to users>

Example :
Use: [Country] IN ('INDIA','CHINA')
Display: Eastern Countries

Report performance tuning in report studio

If all reports are slow, the issue may be due to your environment or database. If all reports from a specific package are slow, the issue may due to the model design. If just one report is slow, the issue may be due to a specific report element.

1. How many queries does your report contain?
2. The number of queries on the report will proportionally affect the report execution time.
3. Does the report run slowly for everyone, or just for one user?
4. If the report runs slowly for just one user, the issue may be due to something in that user’s environment, such as virus scanning, page file size or location
5. Settings or their location on the network.
6. If many people are running the same report at the same time, you may need to scale your environment or consider using dispatcher routing rules to direct all requests for a specific package or group of users to a specific server or server group. For more information, see the IBM Cognos Business Intelligence Administration and Security Guide.
7. Do your queries require local processing?
8. The following report elements require local processing: crosstabs and charts, master relationships, unions or joins, multiple fact queries, bursting, and non-vendor specific functions. Local processing requires the IBM Cognos server to compute operations on the result set returned by the database, which can impact the SQL execution time.
9. Does your environment use a Custom Authentication Provider?
10. Using a Custom Authentication Provider could cause a memory leak if the code is not destroying objects correctly.
11. Have you reviewed the logs in the c10_location/logs directory and the audit logs?
12. They may help you identify the source of the problem. Monitoring your processes, such as the Java TM and Business Intelligence bus processes could also identify excessive memory use.
13. Is your environment tuned correctly?
14. For more information, see the Performance Tuning Settings for IBM Cognos 8 Business Intelligence and the IBM Cognos 8 Business Intelligence Performance Tuning Cheat Sheet documents available online at www.ibm.com.
15. Have you recently upgraded?
16. Ensure that any tuning settings that were applied to your previous installation are applied to the new environment. Ensure that your models have been verified, upgraded, and republished. Verify that the IBM Cognos Framework Manager governor that allows enhanced model portability at runtime is not enabled. Depending on your upgrade method, you may also need to open and save the reports again after upgrading.
17. Change the order in which items are queried from the database.
18. Create sections without creating master detail relationships.
19. Share queries between lists and crosstab.
20. Convert queries to SQL
21. Avoid using functions with limited support.
22. Be aware of the limitations of creating expressions with SAP BW data sources
23. Use Select & Search prompts instead of value prompts if your list of prompts is long
24. Provide your own prompt values in a prompt to avoid accessing the database.
25. Suppress null cells using filters.
26. Set page breaks without creating master detail relationships
27. Do not modify IBM Cognos Analysis Studio set definitions.
28. Do not put filters on non-identifiers.
29. Avoid combining large hierarchy levels and sets in a drill-through report in a way that creates large
30. Use database functions when possible.