This technique uses JavaScript against underlying report objects and methods in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.
Description:
This sample shows how to add Google Maps to Cognos reports.
Solution for Cognos 8.4:
This sample displays a report containing branch addresses and a column containing a hyper-link "Show Map". When the hyper-link is clicked, the map will be displayed in the left panel. The hyper-link is implemented as a HTML item with the "Source type" as Report Expression, containing:
'<a href="#" onClick="displayMap( '''+ [Query1].[Address 2] +', '+[Query1].[City] +', '+[Query1].[Country]+''')"> Show Map</a>
The map contains also a red marker, a balloon displaying the selected address and controls to manipulate the map. The script uses the Google Maps API available here:
1. Create a new data item Address 2 with the following expression (this is a SQL Server function, the second argument is 4 quotes:
replace([Sales (query)].[Branch].[Address 1],'''','\''')
2. Associate the page to Address 2, because, it is not displayed in the layout.
3. Modify the HTML ITEM to use Address 2.
***************************************************
SDK sample
Description:
The SDK Java sample will connect to a Microsoft SQL Server database, extract the records from a specified table, create new contact objects and import the values in the name and email columns into the new objects in the Cognos Content Store.
The script can be changed to include additional columns if required.
Steps -
1. Unzip the attached java file.
2. Change the following values appropriately
String theServer = "localhost:1435"; //db server and port
String theDatabase = "gosales"; //database name
String theTable = "Contacts"; //table name with the contacts
String theUser = "sa"; //DB userID
String thePassword = "sa"; //DB password
String endPoint = "http://localhost:9300/p2pd/servlet/dispatch"; //URL to ReportNet
String namespaceID = "DLDAP"; //CRN namespaceID
String userID = "admin"; //CRN userID - has to have Sys Admin permissions
String password = "password"; //CRN user password
String selectClause = "SELECT FROM " + theTable; //Colums to be selected from the DB
3. Compile and execute the script according to the Cognos Software Development Kit Getting Started Guide
/*
* - How to copy contacts names and emails from MSSQL DB into Content Store.
*
* Description: The sample will connect to a Microsoft SQL Server 2005 database, extract name and email
* records from table theTable and import the values in name and email columns into Cognos8 Content Store.
* Note: This sample uses the Microsoft JDBC driver 2005. To download this driver consult Microsoft site.
* The code can be edited to include additional columns if required.
*/
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import com.cognos.developer.schemas.bibus._3.*;
public class ImportContactsFromDB
{
//Store contacts names and emails
protected Map<String, String> nameEmailMap = new HashMap<String, String>();
private ContentManagerService_ServiceLocator cmServiceLocator = null;
private ContentManagerService_Port cmService = null;
public ImportContactsFromDB()
{
try
{
// Load the Microsoft SQLServer JDBC Driver.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch (Exception e)
{
e.printStackTrace();
}
}
//construct the connection string to the database.
public String getConnectionString(String theServer, String theDatabase)
{
String connectionString = "jdbc:sqlserver://" + theServer +";databaseName=" + theDatabase;
return connectionString;
}
//Test connection
public void checkDbConnection(String theConnectionString)
throws SQLException
{
try
{
Connection connection = DriverManager.getConnection(theConnectionString);
}
catch (SQLException e)
{
if (e.getErrorCode() == 0)
{
throw e;
}
}
}
//get connection with valid id, password.
public Connection getConnection(String theConnectionString,
String theUsername, String thePassword) throws SQLException
{
return DriverManager.getConnection(theConnectionString, theUsername, thePassword);
}
//query the database with the select statement.
public void query(Connection theConnection, String theSqlQuery)
throws SQLException
{
Statement stmt = theConnection.createStatement();
ResultSet result = stmt.executeQuery(theSqlQuery);
while (result.next())
{
String name = null;
String email = null;
name = result.getString("first_name").trim() + " " + result.getString("last_name").trim();
if (result.getString("EMAIL") != null)
{
email = result.getString("EMAIL").trim();
}
//add names and their corresponding emails in the map
if (name != null && email != null)
{
nameEmailMap.put(name, email);
}
System.out.println(name + "\t" + email);
}
}
//add contacts in Cognos namespace.
public void addContactsToCRN()
{
BaseClass bc[] = new BaseClass[nameEmailMap.size()];
Iterator nameEmailItr = nameEmailMap.entrySet().iterator();
int i = 0;
while (nameEmailItr.hasNext())
{
Map.Entry nameEmail = (Map.Entry) nameEmailItr.next();
Contact contacts = new Contact();
TokenProp tp = new TokenProp();
tp.setValue(nameEmail.getKey().toString());
StringProp spe = new StringProp();
spe.setValue(nameEmail.getValue().toString());
BaseClassArrayProp bcap = new BaseClassArrayProp();
BaseClass bcc[] = new BaseClass[1];
Nil nil = new Nil();
StringProp sp = new StringProp();
sp.setValue("CAMID(\":\")");
nil.setSearchPath(sp);
bcc[0] = nil;
bcap.setValue(bcc);
contacts.setDefaultName(tp);
contacts.setParent(bcap);
contacts.setEmail(spe);
bc[i] = contacts;
i++;
}
this.addObjectsToCRN(bc);
}
//add contact objects in content store.
public void addObjectsToCRN(BaseClass[] objects)
{
AddOptions ao = new AddOptions();
ao.setUpdateAction(UpdateActionEnum.replace); //replace if already exists
SearchPathSingleObject singleObject = new SearchPathSingleObject();
singleObject.setValue("/directory/namespace[@name='Cognos']");
try {
// add the new contacts to the content store
BaseClass bc[] = cmService.add(singleObject,objects,ao);
System.out.println("The contacts from the DB were added to the CS.");
} catch (RemoteException e) {
e.printStackTrace();
}
}
public void connectToCRN(String sendPoint, String namespaceID,
String userID, String pass)
{
//Connect to ReportNet
String endPoint = sendPoint;
try
{
cmServiceLocator = new ContentManagerService_ServiceLocator();
cmService = cmServiceLocator.getcontentManagerService(new java.net.URL(endPoint));
if (namespaceID != null && userID != null && pass != null)
this.quickLogon(namespaceID, userID, pass);
}
catch (Exception e)
{
System.out.println(e);
}
}
public String quickLogon(String namespace, String uid, String pwd)
{
StringBuffer credentialXML = new StringBuffer();
credentialXML.append("<credential>");
credentialXML.append("<namespace>").append(namespace).append("</namespace>");
credentialXML.append("<username>").append(uid).append("</username>");
credentialXML.append("<password>").append(pwd).append("</password>");
credentialXML.append("</credential>");
String encodedCredentials = credentialXML.toString();
XmlEncodedXML xmlCredentials = new XmlEncodedXML();
xmlCredentials.setValue(encodedCredentials);
//Invoke the ContentManager service logon() method passing the credential string
//You will pass an empty string in the second argument. Optionally,
//you could pass the Role as an argument but for the purpose of this
//workshop don’t be concerned with Roles.
try
{
cmService.logon(xmlCredentials,null );
}
catch (Exception e)
{
System.out.println(e);
}
return ("Logon successful as " + uid);
}
public static void main(String args[])
{
String theServer = "localhost:1433"; //db server and port
String theDatabase = "gort"; //database name
String theTable = "Contact"; //table name with the contacts
String theUser = "sa"; //DB userID
String thePassword = "sa"; //DB password
String endPoint = "http://localhost:9300/p2pd/servlet/dispatch"; //URL to ReportNet
String namespaceID = "LDAP"; //CRN namespaceID
String userID = "admin"; //CRN userID - has to have Sys Admin permissions
String password = "password"; //CRN user password
String selectStatement = "SELECT first_name, last_name, email FROM " + theTable; //Colums to be selected from the DB
ImportContactsFromDB driver = new ImportContactsFromDB();
String connString = driver.getConnectionString(theServer, theDatabase);
try {
Connection conn = driver.getConnection(connString, theUser, thePassword);
driver.query(conn, selectStatement);
driver.connectToCRN(endPoint, namespaceID, userID, password);
driver.addContactsToCRN();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
************************************************