Making the Size of Select & Search Prompt Dynamic

Fact less Fact table does not contain any facts. Generally Fact less fact tables are used to record the events such as students attendance, attendance of participants for an Event like a Meeting. Actually this is not a Fact table but due its position we should call it as Fact table without facts.. Look at the following image..

See the attendance Table in the Image, There is no measure or fact but Attendance Table sits like a Fact table and also have the connections to dimension tables and hence we can say that it is Fact Table and since it does not contain any measures so that we can call it as fact less fact table.
Now Let us understand the use of the Attendance Fact less Fact table….
If a student attended a class then we enter a Record in Attendance table else no row will  be stored. Only aggregation here is possible only count.

Making the Size of Select & Search Prompt Dynamic

By default, the width of Select & Search prompt is fixed and therefore wide text will cut off…..

Note: The following Technique is only Applicable for Cognos 8.4  Fix Pack 2 and Higher..
STEPS:
1) Locate for a File properties.js   in the location …c8\web content\prompting (on the machine where you installed cognos 8 gateway)
2) Take a back of  it ( This backup is useful to restore the file properties.js if anything goes wrong)
3) Open the file with any Text editor and search for the line..
“SYSTEMPROPERTY_CSEARCH_AUTO_RESIZE_RESULT_LIST=FALSE”
4) Replace FALSE with TRUE


5) Save the file
6) Launch the cognos and check the prompt you can see as follows.

COGNOS: CSV or Excel output limitations & NTLM security:

When saving a report in  Excel , we can’t save all the report output if the report output contains more than 65535 rows due to the  limitation of Excel .
Excel 2007 limits : Worksheet size 1,048,576 rows by 16,384 columns
By default, .CSV files are opened in Excel, so even though .CSV files don’t have the 65k row limitation, the file will be truncated if it is opened in Excel.

Resolving the problem

View the file as a CSV and change the format from Excel to a text file. Steps: After generating the report in HTML format, select the toolbar button to View in CSV Format Once the File Download dialogue appears?click Save and the Save As dialogue will appear (Note: If you do not see a Save As dialogue, verify in Internet Explorer you have Automatic prompting for file download set to ENABLE under Tools -> Internet Options -> Security -> Custom level) From the Save as Type list?change the format from Microsoft Excel Worksheet to All Files Change the File Name from reportfile.xls to reportfile.txt and click Save This will create a Tab-Delimited text file that can be used as a data source.  Cognos: NTLM security:

COGNOS: Difference between Two values in a COLUMN

Suppose I have report output as follows…

Now, I want to find difference between 2006’s Revenue and 2007’s Revenue in its footer…
To Resolve this follow the steps
1)      Create a list report as follows

2)      Create a Filter to only show 2006 and 2007 as follows


3)      Create “list overall” footer as follows..

4)      Select the summary row (The overall footer ) and split it
5)      Unlock the report
6)      Drag query calculation to the Cell as follows..

7)      Give any name and enter the following expression in Expression definition box..

8)      Click OK and Run the report, you will get output as follows..

Cognos filters & prompts?

Filters:
Filters are used to remove unwanted data from the report.
Filters are two types 1) detailed filter 2) summary filter
Detail filter: The working of detail filter is based on APPLICATION
For Ex:
The report has the following columns
[year], [product name],[Quantity]
The filter I applied as follows….



If I select “Before auto aggregation”, what will happen?
The data will be filtered based on qty<500 at database level. Whatever the records are there which less than 500 are will be summed up for year, product line and result will be displayed.
If I select “After auto aggregation”, what will happen,
First the records are aggregated for year, productname then the filter qty<500 will be applied on those aggregated records, If there are any records statisfying qty<500 then we will get the output
Summary filter:
IF I want to use this summary filter, I should have groups and groups totals in the report.



While applying summary filter, we should set scope to specify particular subtotals to apply the filter weather filter to be applied on product type totals, or product line total or year totals…

PROMPTS:
All the filter are hardcoded. If u run the report any number of times the same filter will be applied and the same output we will get.
Suppose If you want to select filter values at run time, then we use prompts..
There are several prompts that cognos supports

1. Text box prompt
2. Value prompt
3. Select & search prompt
4. Data prompt
5. Time prompt
6.  Date & time prompt
7. Tree prompt
8. Interval prompt
9. Generated prompt

There are several ways to get the prompts…
1) When we use parameter (for ex:? x? ) in the filter then we will get prompt automatically..

When we run the report, Cognos will create a prompt automatically as follows…

The automatic generated prompt may not consistent…, suppose if you want prompt at center of the page, it is not possible here, suppose if I want select & search prompt, I cant change here..
2) Select the columns in the report page for which u want to create prompts, then go to tools menu, select Build prompt page option as follows…

Then the prompt page will be created automatically with prompts for all the three columns..

This method is the quickest way to create prompts…
3) Fully customized method. In this method we can create prompt page and prompts manually..
Point on Page explorer and click on “Prompt page” folder as follows..


Drag “page” from tool box to prompt page folder…


Double click on Prompt page1 to maximize prompt page..
Text box prompt
It does not display values in it, we have to imagine values
Value prompt
This will display values from database.
Drawbacks:
1)   1) Performance is slower (Every time u run the report, it has to connect to the database and bring the values..)
2)      It can’t display more than 5000 values
3)     3)    We have to scroll down/up for searching the values..
Select & search prompt….
All the above drawback will be resolved in S&S prompt…

Determinants Explained with Example

Granularity is a simple concept describing the level data relates to.
Imagine sales areas:
Level1: Country
Level2: Sales Area
Level3: State
Level4: City
Level5: Store
Level6: Department
Level7: Employee
we can summarize sales volume (quantity) at each of the levels above.
The level at which we report is the grain (or granularity) of the table.
Tables or query subjects in Framework Manager often have multiple levels (grains) which you may have to join to another table with a different level grain.
An example of this is budgets.
Using our example above, we want to join our sales area table to our budgets table.
Unfortunately the grain of the sales area table is employee but the budgets have been completed at Store level.


In Framework Manager, if we join directly from store in sales areas to store in the budget data the budget data value will be repeated for how many employees are part of that store.
New Query Subject (join on Store with no determinants set):
Sales Area. City
Sales Area. Store
Budget. Month
Budget.Sales_Target
Result:
As there are four employees at the Bayside store this forces the model query subject to return four rows repeating the sales budget.
To correct this Framework Manager has to be instructed what level of granularity the budget applies to. This is done using determinants.
In our example we need to set the determinants on the Sales Area query subject.
Right click the query subject and select the determinants tab.
One determinant should be created for each level of granularity as shown below.
Create the determinant by clicking Add.
Rename the determinant and then drag the identifying column (Key) into the key window (bottom left). If the level has any attributes that exist at the same level of granularity these should be added to the Attribute window.
If the new determinant you have created is the lowest level grain (Employee in this case) the Uniquely Identified check box should be ticked.
The Group By tickbox should be ticked if the level is ever to be used for aggregating values.
Ensure that once you have created all of your determinants for the query subject that they are in the correct order (use the up and down buttons to sort them correctly).

After setting the determinants click OK to save the changes.
When the model query subject is now run, only one row of data is returned:

Cognos PDF output in Single page:

When we have more columns in cross tab or list and run in PDF format, we get some columns in one page and some columns on another page and so on.. We can simply avoid this by setting one property. By doing this, we can have all the columns of cross tab or list report in single page in PDF output format.