Sorting based on user selected column - Dynamic sorting by prompt selection

Dynamic sorting by prompt selection

=====================================

> drag two cols like country and revenue

> drag value prompt to prompt page

> Static choice country and revenue used value and display value

> Drag one query expression mean create new cols in list

> case

when paramdisplayvalue='country' then 'contry'

when paramdisplayvalue='revenue' then 'revenue'

else end

> select that particular col country / revenue then grouping/sorting keep that cols

> select another col grouping and soring keep that cols

> remove sort that cols from list

IBM Cognos Business Intelligence 10.2.2 NEW features

Cognos 10.2. Features number - 1

My data sets - User can create his own without any assistance of IT. 

a) Much high volume of data set can import into like excel, text files
b) imported data can process on server as long as user wants
c) stand alone package can be created

1. Go to welcome screen of cognos connection
2. Click on IBM cognos content
3. click on user icon
4. click on my data sets (this will show all data sets which i have and can see package i generated for data sets, it can show no of records, files size)
5. New data sets : by clicking icon employee button
6. import screen i can import like xls, exls, text file(comma separated, tab separated, pip separated) value i can port.
7. after import i can metadata left side and data preview right side pan
8. I can remove unwanted columns from my meta data screen (left side pan)
9. I can change default summery to none, which change icon of data items to attributes
10. Then I can publish package by just click on publish button.
11. I can select location like public / my folder to publish this package, i can give name of the package

My data  sets - administration.

1.  click on cognos administration
2.  Go to status tab in administration, there is a Data sets option
3.  Click on Data sets right side pan will show all details about the data sets like (name, owner, refreshed, file size, rows, tenant)
4.  Left pan we can apply filter to as you want to see.
5.  we can delete any of data set by selecting like data source
6.  User access to my data sets capabilities :
7.  capabilities : click on security and then click on capabilities  then my data sets
8.  click on set properties
9.  go to permission tab add user/group, can add execute and traverse
10. After giving capabilites persimission to user then user can see data sets when login into cognos connection.
11. Data from files user uploade is loading to one of more repositories, you can configure connection to these repository in data source configuration area in cognos administration.
12. Create new data source, in wizard data source type drop down select my data sets repository, its like any other JDBC data source.
13. From initial release of cognos 10.2.2 DB2 only repository for my data sets repository, going forword this will update to SQL server and Oracle

Cognos 10.2. Features number - 2

In Cognos 10.2.2, you now have the ability to create customized User Interface (UI) profiles to control the features and functionality that are available in the User Interface for Workspace Advanced Users. The default user interface profiles still exist, but now, once enabled by your administrator, you have the ability to create new profiles and customize them in the Library tab in the Cognos Administration console. You also have the ability to choose whether to automatically show next generation functionality in the UI when the software is upgraded.


Cognos 10.2. Features number - 3

Static choice defined in value prompt

Requirement:

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

Solution:

• Develop values prompt name of the prompt : 'Countries'
• In Static choice define 2 values like : WESTERN_COUNTRIES, ASIAN_COUNTRIES
• Use value of the prompt : [Country] IN ('INDIA','CHINA')
• Display value of the prompt : Eastern Countries
• Query explorer open query and apply filter.
• In the Expression Definition:
• #prompt ('<prompt parameter>', 'token', '<default filter>')#
• #prompt ('Countries', 'token', '[Country] in ('USA', 'UK', 'INDIA', 'CHINA')')#

 Thank you.

 

How cube joined data sources ??? cube machanism

How cube makes join in transformer between two data sources or multiple data sources.

This is unique features provided by cognos cube, link magic provided by cognos.

When you create cube with multiple data sources cube transformer do not have provision to create join between data sources. Then how cube is establishing join and when we open cube in power play studio its showing data without any issue even there are no relation defined in data sources.


Answar :

• Transformer Cube mechanism itself making join between facts and dimensions.
• How its making join : Dimension table key ( primary key ) with Fact table key ( foreign key ).
• Cube making eque join automatically in between facts and dimensions.

Any body know out of these please advise.

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.

Role - playing dimension

Role- playing dimension

Dimension having multiple valid relationship itself and other dimension as well call role playing dimension.

Dimensions are often recycled for multiple purposes within the same database. For instance, a “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a “role-playing dimension”.

Basically, if the same dimension is used more than once with different names in the cube then it is called the role- playing dimension

Cub VS DMR

Sl No.	Cube	DMR
1	Cognos 7 technology on words	Cognos 8 technology on words
2	Performance high	Performance Low
3	offline data	online - life data
4	Attributes nested within dimensional values	not possible with cubes
5	Cube does require a separate model/IQD	DMR does not require a separate model/IQD
6	Calculated categories creation - YTD, MTD	No Calculated categories creation
7	supported multiple environments/formats	Not supported multiple environments/formats
8	Not supported HUGE volume data	Supports HUGE volume data sets
9	Security in custom view and access manager	Security in Cognos server or FM
10	Custom view	No custom view
11	MDX	SQL
12	Support up to 2 GB	No Size limit
13	Manually maintain data in categories for data correction	Data correction only possible in database level not model / package level

Single cognos report multiple data sources

Stpes

1.    Get ready with SQL query

2.    Open report studio with/without any package

3.    Go to query explorer

4.    Drag query1

5.    Drag SQL object from Toolbox to query explorer

6.    Click on SQL object

7.    Properties pan select data source (Which you should have to created data source before for given SQL)

8.    Double click on SQL object

9.    Copy query into SQL ojbect

10.  Validate SQL

11.  Click ok

12.  Now you can design report

13.  Same way you can develop multiple query with multiple data sources report