Introduction:

Cognos 10 BI Developer Edition (DE) is an easy-to-install 30-day-trial version of Cognos 10. It is meant for testing and evaluating the functionality and not for production environments.
Once the 30 days are over, the service locks down and the Portal is no longer accessible.

We ran into a case in where the client used the developer edition to test a large amount of reports. The 30 days ended and they did not export their reports to a zip file before this happened.
Their only choice was to re-do all the reports, but it was too much work and too little time. A lot of effort would be wasted.
So we decided to try and see if the reports could be extracted.

After researching on Cognos DE architecture and after a few failed experiments, I came out with this process to extract the contents of an expired DE Content Store.

Pre-requisites:

1. Cognos 10 BI Developer Edition
   You will need to install a new Cognos 10 Developer Edition. You can get the installer here.
   
2. Cognos BI 10 (Full Version)
   The main extraction requires a full version of Cognos. 
   

Additional Notes:

• DO NOT UNINSTALL COGNOS 10 DE UNTIL YOU HAVE DONE THE BACKUP STEPS ON THIS GUIDE.

In short, these are the required steps:

1. Backup the expired DE Content Store
2. Uninstall Cognos DE and reinstall a new copy of Cognos 10 DE
3. Restore the expired Content Store into the new installation
4. Set up the necessary privileges for 'Anonymous' and make a new copy of the Content Store
5. Install Cognos 10 BI server
6. Restore the modified Content Store and Export the reports

Below are the detailed steps.

NOTE: I will not include the installation step-by-step, only the details in the installation that are important for this process. If you need detailed steps for installing Cognos, you may look at the documented steps in the Information Center

Backup the expired DE Content Store

Cognos Developer Edition uses the built-in derby database for its Content Store. The backup for this kind of databases is very simple: just copy the full folder where the database files are kept.
To create the backup, go to <cognos_installation_folder>/contentstore and make a copy of the "cm" folder.

Uninstall Cognos DE and reinstall a new copy of Cognos 10 DE

1. Uninstall Cognos DE using the standard uninstaller. Go to Start Menu > Programs > Cognos Developer Edition > Uninstall IBM Cognos BI Developer Edition or use Windows' "Add/Remove Programs" in the Control Panel.
2. Delete the installation folder after successfully uninstalling the product.
3. Install Cognos 10 Developer Edition and start the services. Make sure all programs can be accessed.

NOTE: You can decide to use a different server to perform the new installation. The only requirement for this is that the machine name is exactly the same in the new server. If you use a different name, you will get a "too many dispatchers" error in the steps ahead.

Restore the expired Content Store into the new installation

1. Go to Windows Services (Control Panel > Administrative Tools > Services) and stop the Cognos Service and the Content Store service, in that order.
   
2. Delete or rename the "cm" folder in <cognos_installation_folder>/contentstore
   
3. Paste the backup "cm" folder here. Keep the name as "cm". The Content Store has already registered the database in that folder so it will recognize it only as it is.
4. Using the Windows Services, start the Cognos Content Store service and the Cognos Developer Edition service, in that order.

Set up the necessary privileges for 'Anonymous' and make a new copy of the Content Store

On a later step, we will install a basic Cognos 10 BI deployment. By default, it uses user 'Anonymous' to connect and manage all services. We will need to add privileges for this user in this Content Store to be able to perform the report extraction.

1. Open Cognos Administration and go to the Security Tab > Capabilities.
2. Add 'Anonymous' to the "Administrator" capability by clicking on the triangle and selecting "Set Properties"
   
3. Go to the Permissions tab and click "Add..". 
   
4. Click on "Cognos" and mark the "Show users in the list" checkbox.
   
5. Select 'Anonymous' from the list, click on the arrow to add it to the capabilities and click OK.
   
6. Select 'Anonymous' from the list and provide Execute and Traverse privileges. Click OK.
   
7. Click on "Administration".
   
   Repeat steps 2 - 5 for the "Administration tasks", "Configure and manage the system" and "Planning Administration" capabilities.
8. You need to add one more privilege to 'Anonymous'. Go to the Configuration Tab > Content Administration and select the Properties icon on the top-right
   
   Give ALL privileges to 'Anonymous'
   
9. Close the browser. Stop the Cognos Service and the Content Store Service using Windows Services and make a copy of the "cm" folder once again.

Install Cognos 10 BI server

1. Install  the full version of Cognos 10 BI, preferably in a single server installation. Again, use the same machine or a machine with the same server name.
   
   NOTE 1: This process has not been tested in a distributed installation. 
   
   NOTE 2: Make sure you install the Cognos Content Store Database. 
   
2. Configure the Cognos 10 BI using Cognos Configuration, as any regular installation, and start the services. Make sure all is working properly. Remember to check the Information Center if you need assistance with this step.
   

Restore the modified Content Store and Export the reports

1. Stop the Cognos service and the Content Database using the Windows Services or Cognos Configuration.
2. Delete or rename the "cm" folder in <cognos_installation_folder>/contentstore and paste the updated "cm" folder. 
   
   Note: If you use the first backup of "cm", you will not be able to access Cognos Administrator.
3. Start the Cognos Services using Cognos Configuration.
4. Open Cognos Administration. Go to the Configuration Tab > Content Administration and select "New Export"
   
5. Follow the wizard to create the Export file. You can select the folders you wish to include or you can select the complete Content Store.
   
   NOTE: If you select the full Content Store, don't worry if you get some error after exporting the file. There are some objects on which you won't have privileges, and therefore won't be included. These will be user groups or configuration folders, which you will not need when you import the reports. Just make sure that the reports are not in the list of objects that were not included.
6. After the Export completes, a zip file will be created in <cognos_installation_folder>/contentstore.
7. You can now take this file an import it into any Cognos BI deployment as documented here and here.

I hope this come in handy in the unfortunate event that you don't extract your work from the Developer Edition in time. As you can see, this is not a way of avoiding using the full version of Cognos, first of all because you need a full version to extract the reports (using the Export on the DE will result in error "CM-REQ-4102") and secondly because these are not steps that you would like to be doing every 30 days.

Lotus Domino is a tool widely used for application development. Many companies use Domino for workflow applications, tracking systems, collaboration applications, among other uses. 
A problem arises when we want to integrate the information stored in these systems with the information contained in our enterprise reporting systems. 
In this tutorial, we will show you how to use ODBC to access Domino databases from Cognos BI. With this, you will be able to create reports, drill down and analyze data directly from a Domino database. 
 

Prequisites:

1. Install and configure the Cognos Server
   
2. Install Lotus Notes Client on the Cognos Server
   
3. Install Notes SQL on the Cognos Server
   
   

Create the ODBC connection

1. After all the prerequisites above are met, create the ODBC connection.
   Go to ODBC on Control Panel > Administrative tools. 
   

   It is very important that you define how do you want to access the Notes database. The ODBC tool will treat each form and view as a relational table, where each field shown in the form/view is a table column. You must consider this when selecting which view(s) or form you want to analyze or if you need to create a new view to have all the necessary information.

2. Create a System ODBC specification.
   
   
   
3. Select "Lotus NotesSQL driver (*.nsf)" from the list of drivers.
   
   
   
4. Fill in the information depending on the location and name of your Notes application.
   
   Take note of the "Data source name". This will be used as reference when creating the Cognos connection.
   Select the user id that will be used to connect to the database. You can also set this id in the Cognos connection configuration.

   Make sure the ID has proper access in the notes database ACL.

   Click OK to save the ODBC connection.
    

Create the connection from Cognos

1. Once the ODBC connection is created on the server, launch the Cognos Administration application.
   
   
   
2. Go to Configuration > Data Source Connections and create a new Connection by clicking 
   
   
3. Fill in the name and description. Click Next and select ODBC as the type of data source.
   
   
   
4. Click Next. On the "ODBC data source" field, type the name of the ODBC connection created on the previous section.
   
   
   
   On Signon, fill in the selected Notes ID for accessing the database.
   
   Test the connection to make sure all works correctly.
   If the test was not successful, check your ODBC, make sure you can connect. If you can connect, then check that the selected user can access the application from Lotus Notes.
   If the test is successful, click Finish. We are now ready to upload the metadata to the Framework Manager.
    

Import the metadata to the Framework Manager

1. Open the Framework Manager and create a new Project.
   
2. Select the recently created connection as data source.
   
   
   
3. Select the view or form (Tables) that you wish to import.
   

   Do not select all views. If you do, you will get redundant information, since most of them will hold the same data, but displayed with different columns. You must select the view(s) or form that is the most relevant/best structured for your analysis process.
    

4. Once the project is created, proceed to create your analysis structures: Dimensions, measures and hierarchies. Since Notes is not a relational database, all the categories will most likely be contained in the same table(form) or view, but you will be able to create the hierarchies without any trouble.
   

Conclusion:

After completing all these steps, data will work as any relational database source, and will be accessible for any Cognos BI report. 

The purpose of this guide is to explain how to trace back an NLO file, generated by DAOS, to the database who holds the reference. Domino does not provide a direct way to trace this, so we will show how to work with the Domino DAOS commands and some OS scripting to get the reference information.  
 

The Problem

Domino Attachment and Object Service (DAOS) is the Domino attachment consolidation service. In short, attachments that go into DAOS will be sent to the file system as an NLO file and the database will save only the reference to this file. If the same file is kept in different databases, only one NLO will exist, but it will have many references. This reduces significantly the space taken by large attachments.

The problem for this case is that sometimes the administrator might notice particularly large files in the NLO directory and there is no direct way to see which database is holding this file. 

For example, a user sends a file that is larger than the permitted attachment size. The router will reject it, but the file will still be kept in sent mail and in the rejection notice from the router, hence creating the NLO in DAOS. If the administrator sees this NLO, meaning files larger than the allowed maximum size, he can't know which is the database that holds it, so it can be deleted.
 

The Tools

The only tool that provides information of what NLO are referenced by an NSF is the command:

tell daosmgr listnlo -o outputfilename.txt ALL nameofdatabase.nsf

This command will write on an output file the list of all the NLOs referenced from the database.
This is an example output of the command:

C:\Program Files\IBM\Lotus\Domino\data\DAOS\0001\D575F2E321893B8C98528D80DC3A3A3FA6D55A2F0004D8F8.nlo
C:\Program Files\IBM\Lotus\Domino\data\DAOS\0001\E97A108C5958D0804F19C8E580DF7EAC6FD6DCDB000606C3.nlo
C:\Program Files\IBM\Lotus\Domino\data\DAOS\0001\C0DC41B6C6297C2246BBA676B68373B7ED725D4000015D9D.nlo
C:\Program Files\IBM\Lotus\Domino\data\DAOS\0001\D575F2E321893B8C98528D80DC3A3A3FA6D55A2F0004D8F8.nlo
C:\Program Files\IBM\Lotus\Domino\data\DAOS\0001\E97A108C5958D0804F19C8E580DF7EAC6FD6DCDB000606C3.nlo
C:\Program Files\IBM\Lotus\Domino\data\DAOS\0001\C0DC41B6C6297C2246BBA676B68373B7ED725D4000015D9D.nlo

As you can see, the command requires that you include the name of the NSF file, which, for our case, is what we are actually trying to find out. 

So, we will use a combination of this command with DOS scripting to loop through the NSFs to check on which databases is the selected NLO referenced.
 

Sample script

For this example, I have prepared 3 scripts (in Windows and Linux versions) that will generate the list of NLOs, search it, show the NSFs that contain the NLO and then clean up all generated temporary files.

The 3 scripts are:

• ListDir: This scripts will list all .nsf files and will execute the command "tell daosmgr listnlo -o .txt ALL .nsf"
• FindNLO: This script will issue a simple FIND command on the generated text files. It will give the name of the database that contains the NLO.
• CleanUp: This script will delete all temporary text files.

Here are the contents for the 3 scripts:

The scripts must be placed in the "mail" directory and should be executed in the presented order (First ListDir, second FindNLO and last CleanUp)

As I mentioned before, this is not a direct option that Domino provides. Using the console commands and scripting, we can get the job done.
 

Conclusion:

With this tutorial we have learnt how to get the name of the NSF that holds a reference to a particular NLO.
It is also important to notice how we can execute console commands from the batch or shell OS text command lines. This is a powerful tool for every Domino administrator. It can help automate many administrative tasks, so they can be run just by clicking an icon.
 

References:

• DAOS Manager Tell commands

Resources:

• Windows scripts: WinFindNLO.zip
• Linux scripts: LinuxFindNLO.zip

IBM Cognos 8 BI Report Studio has a feature that allows us to render data columns depending on certain conditions, for example, depending on user selection, filters or user privileges.

With this tutorial, I will show how to use the data in the report, specifically Data Summaries, as input for this conditional rendering. List and crosstabs reports don't have direct access to the data while rendering, so there are a few tricks that we need to do to make this requirement possible.

In this report I will hide two columns (Expense Group and Expense Category) using a Count total and Sum total.

Report sample output

Prerequisites:

1. Cognos 8 BI Installed and configured
   Your Cognos environment should be already installed and configured.
   

Table of Content:

• Create the structure for using summaries in conditional rendering
• Create the paging specifications
• Add the conditional rendering

Create the structure for using summaries in conditional rendering

Lists and crosstabs are not able to access the totals when they render the report. For this reason, we need to use an additional query (identical to the original query) to calculate the data before the report renders. This way, we can use the new query data as input for the conditional rendering. In this section, we will configure a list report so that this can be achieved.

1. Go to the Query explorer and make a copy of the original query (Query1). We will refer Query1 as the display query. The new query (Query2) will be referred as the summaries query.
   
2. Open the summaries query (Query2) from the Query Explorer.
   
3. Create a Data Input for each summary you wish to calculate for the conditional rendering. For this exercise, we will create 2:
   
   • A count summary - CountExpGroup: count([Expense group] for [Employee key])
   • A total summary - TotalExpense: total ([Expense total] for [Employee key])
   
   
   
4. Go to the Report page and drop a List object into the report canvas.  
5. Set the summaries query (Query2) as the List's Query. Select the List from the Properties pane and set the query under Data/Query.
   
   
   
6. Drag and drop the display query list inside the new List.
   
   
   
7. Go to the summary list Properties/Data/Properties and select the recently created summary values that we will use for the conditional rendering. This step is needed to make this values available for calculations, even though they are not displayed.
   
   
   
8. (Optional) If you wish to hide the summary list borders and header, go to the List's properties/General / Column Titles and set it to Hide. Then, select the List Column Body on the properties and under Box/Padding set every value to 0.
   

Create the paging specifications

If we follow the steps above, the way paging works in the report will change. The report's main query will be the summaries query and not the display query. This will make it impossible for the report to change pages using values on the display query. We need to configure paging with a Master-detail relationship between the two queries.

1. Go to Report Pages using the Page explorer.
   
   
   
2. Create a new Page set. Set the Query as the summary query in Properties/Data/Query and add the required grouping. For this case we are using the Employee key as Grouping  
   
   
   
3. Make sure page properties/Data/Query is set to the summary query.
   
   
   
4. Create a Master-detail relationship for the display List (Query1)
   
   
   
5. The report should be paging properly now, using the selected grouping.
   
   
   

Add the conditional rendering

Now we have the report working normally and we have the data that we need available to create the report conditional rendering. We just need to create the formulas according to our needs. For this case, I will hide a column depending on the amount of values. This can be used to hide columns that are empty on some pages, but not on others, as required by our original issue.

1. Select, on the display list, the column you wish to render conditionally. 
2. Make sure you select the List Column. From the Properties/Conditional/Render Variable, create a new Boolean Variable
   
   
   
3. Give a name to the variable and create the formula for hiding the column. By default, the column will hide if the condition is true.
   For my example, I'll create conditions to hide 2 columns:
   
   • One to hide Expense Group when the column has no values, using the summaries from the summary query: [Query2].[CountExpGroup]>0
   • One to hide Expense Category when the total by group of Expense Total is greater than 40000: [Query2].[TotalExpense]>40000
4. The report is ready. The results would be as following
   
   Before:
   
   
   After:
   

Conclusion

In this tutorial we have learnt how to create conditional rendering using totals. This could be used for hiding columns when there is no data in the group, when we want to show only if some totals are above a limit or in any other case that requires a Summary value on it's condition.
This method will remove the lack of visibility of the Conditional Rendering over the Summary values.

References:

• How do you hide an empty List or Crosstab on a report?: https://www-304.ibm.com/support/docview.wss?uid=swg21344613
• How do you create a Master Detail Relationship in Cognos 8?: https://www-01.ibm.com/support/docview.wss?uid=swg21339010
• Cognos 8 Information Center