Creating a Tab Report in Report Studio

Steps for creating a Tab report in Report Studio:

1. Create a new report using Go Sales (Query) Package.
2. Insert a table with 3 Columns and 2 Rows.
3. Merge the cells in the second row.
4. Create 3 text items (List, Crosstab and Chart) in the first row.
5. Insert a Value Prompt before the table and change the name to Prompt.

6. Create three static choice values for the value prompt.

7. Change the following properties of value prompt.

·         Required – No

·         Hide Adornments – Yes

·         Auto Submit – Yes

8. Place a HTML Item next to the value Prompt and copy the following code.

<script language="javascript">

function func(a)

{

var f = getFormWarpRequest();

var prompt = f._oLstChoicesPrompt;

if(a==1)

{

prompt.options.selectedIndex = 2;

}

else if(a==2)

{

prompt.options.selectedIndex = 3;

}

else if(a==3)

{

prompt.options.selectedIndex = 4;

}

promptAction('finish');

}

</script>

9. Place HTML Items before and after the text Items.

·         Before the Text Items

ü  List

<div id="hi" onclick="func(1)">

ü  Crosstab

<div id="hi" onclick="func(2)">

ü  Chart

<div id="hi" onclick="func(3)">

·         After the Text Items

</div>

10. Create a String variable with the following expression.

case (ParamDisplayValue('Parameter1'))

when 'List' then 'List'

when 'Crosstab' then 'Crosstab'

when 'Chart' then 'Chart'

end

11. Create three values for the string variable (List, Crosstab and Chart).
12. Goto Page 1 and insert a conditional block in the second row.
13. Select the conditional block and change the block variable to String 1.
14. Select the current block as List and insert a List in the conditional Block.
15. Insert dataitems in the list.
16. Select the current block as Crosstab and insert a Crosstab in the conditional Block.
17. Insert dataitems in the Crosstab.
18. Select the current block as Chart and insert a Chart in the conditional Block.
19. Insert dataitems in the Chart.
20. Change the background of the first row.
21. In the condition explorer, select List and change the background color of List text item’s table cell.
22. Similarly make the changes for other table cells.
23. Select the value prompt and change the following properties.

·         Default Selections – List

·         Visible – No




Report Page



Report Output





VBScript for downloading attachments from Outlook

VBScript for downloading attachments from Outlook and for saving in your local machine. Just save this file with .vbs extension and run it.

dim item1
dim objsubject
dim pa
dim savename
dim savefolder
savefolder = "D:/temp/"
Set olApp=CreateObject("Outlook.Application")
 Set olns=olApp.GetNameSpace("MAPI")
 Set objFolder=olns.GetDefaultFolder(6)
 msgbox objfolder
 For each item1 in objFolder.Items
if item1.Unread=true then
objsubject = item1.subject
if objsubject = "*****Give the mail subject here*****" then
pa =  item1.Attachments(1).filename
savename = savefolder & pa
msgbox savename
item1.Attachments(1).SaveAsFile savename
end if
end if
Next

Cognos - Google Map Report with Distance Calculation and Direction Tracking

In this example, we are going to create a Cognos report with Google maps which shows the distance between the two markers and also the route and direction between the two markers.


Steps:

1. Take the example which we previously did.
2. Declare the below variables:

var isCreateHeadPoint = true;
var headMarker, tailMarker;
3. Add the below line in the LoadMapGadget() function.

gdirection = new GDirections(map, document.getElementById("directions"));
4. Add the below lines to the GEvent.addListener of ShowAddress()

if (isCreateHeadPoint) {
          //add the head marker
          headMarker = marker;
          isCreateHeadPoint = false;
}
else{
         //add the tail marker
         tailMarker = marker;
         isCreateHeadPoint = true;
         // create a path from head to tail
         gdirection.load("from:" + headMarker.getPoint().lat()+ ", " +headMarker.getPoint().lng() +" to:" + tailMarker.getPoint().lat() + "," + tailMarker.getPoint().lng(), {preserveViewport:true});
}
5. Add a row in the table.
6. Add a html item in the last row.
• Source Type : Text
• Set the text to the following.

<div id="directions" style="width: 275px"></div>
</body>
</html>
7. Save and run the report.
8. To find the route and directions, click on any two markers.

Report Page

Report Output

Integrating Google Maps with Cognos - Sample

Steps:

1.  Login with your google account into the below url.
https://www.google.com/accounts/ServiceLogin?hl=en&continue=http://www.google.co.in/
2. Sign up for the Google Maps API using the following URL:
http://code.Google.com/apis/maps/signup.html
3. Enter the IP address of the Cognos server where the reports will be running.
4. Click on the Generate API Key button to generate a unique key. The unique key that is generated is only valid for the server that you entered earlier to generate the key, and it will be used in the script for working with the Google Maps API. Please remember that the key is only valid for a single Cognos server IP.
5. Create a new Blank report using Go Sales(Query) Package.
6. From the insertable objects toolbox, add a Repeater object to the body of the report.
7. Right-click on the new repeater object and select Go To Query.
8. Add to the query the Data Items you want to display. This data will be used to assemble a JavaScript array (in a subsequent step).
9. If you plan to have more than one row in the query (i.e. more than one marker on the map) you will need a column that contains a comma for all but the first row. This comma will be used as a separator for the elements in the JavaScript array. To Do this:
• Add a Data Item to the query named RowCount. Set the expression to:
rank([Address 1])
• Add another Data Item named ElementSeparator. Set the expression to:
If ( [RowCount] = 1 ) THEN
( ” )
ELSE
( ‘, ‘ )
10. Return to the page (Page1).
11. Click on the repeater and edit the Data->Properties. Click check-boxes by each data item that you will want to use. This will allow you to use them in a report expression.
12. Change the repeater’s Data->Rows Per Page to 99999(otherwise, Report Studio will try to create a new page for every 20 rows in your repeater)
13. Create a table with 1 row and 2 columns.
14. Place the repeater in the second column.
15. Add an HTML Item to the left of the table. This will contain the start of the JavaScript.
• Source Type : Text
• Set the HTML text to the following
<html>
<script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAx7vAbzXFBJGQEupKGA51dBQTTvU48baM3A06e5jSe_wpl0bp3RShVke5jiOCbv1e64rD40-_wur7QA" type="text/javascript">
</script>
16. Add a second HTML Item immediately to the left of repeater.
• Source Type : Text
• HTML text to following:
<div id="map" style="width: 700px; height: 400px"></div>
<script type="text/javascript">
var map = new GMap2(document.getElementById("map"));
var geocoder = new GClientGeocoder();
var locations=[
17. Add a third HTML Item inside the repeater. This will be the assembly of the array containing your data.
• Source Type : Report Expression
• Set the report expression to the following, modifying it to fit your data:
[Query1].[ElementSeparator] + '{Address:"' + [Query1].[Address 1] + '",City:"' + [Query1].[City] + '",Country:"' + [Query1].[Country] + '"}'
18. Immediately to the right of the repeater, add a fourth HTML Item
• Source Type : Text
• Set the HTML text to the following:
];
function showAddress(i, j) {
          if (!geocoder)
         {
               return;
         }
         var address = locations[i].Address.concat(",".concat(locations[i].City));
         var html="<B>";
         html=html.concat(locations[i].Address);
         html=html.concat(", ");
         html=html.concat(locations[i].City);
         html=html.concat("</br></br>");
         html=html.concat(locations[i].Country);
         html=html.concat("</B></br>");
         html=html.concat("<a href=/"http://172.25.119.224:8080/ibmcognos/cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=CAMID(%22%3a%3aAnonymous%22)%2ffolder%5b%40name%3d%27My%20Folders%27%5d%2ffolder%5b%40name%3d%27My%27%5d%2freport%5b%40name%3d%27Prompt_B%27%5d&ui.name=Prompt_B&run.outputFormat=&run.prompt=false&p_City=">
         html=html.concat(locations[i].City);
         html=html.concat("\" target=\"_blank \"> Click here to view Sales Report </a>");
         var customIcon = new GIcon(G_DEFAULT_ICON);
         customIcon.image = "http://maps.google.com/mapfiles/marker_yellow.png";
         geocoder.getLatLng(address, function(point) {
                   if (!point) {
                           return;
                   }
                   map.setCenter(point, 1);
                   var marker = new GMarker(point, {icon: customIcon});
                   GEvent.addListener(marker, "click", function() {
                            map.openInfoWindowHtml(point,html);
                   });
                   if(j == 1)
                   {
                           map.openInfoWindowHtml(point,html);
                   }
                   map.setZoom(3);
                   map.addOverlay(marker);
                   map.addControl(new GSmallMapControl());
                   map.addControl(new GMapTypeControl());
                   map.checkResize();
      });
    }
         function loadMapGadget()
    {
                for (var i = 0; i < locations.length; i++)
               {
                      showAddress(i, 0);
               }
    }
    </script>
    <body onload="loadMapGadget();">
    </body>
    </html>
19. Add a list to the first column of the table. Add a HTML item to the list.
• Source Type : Report Expression
• Set Report Expression to the following:
'<a href="#" onClick="showAddress('''+ number2string ([Query1].[RowCount]-1) + ''', 1)" >' + [Query1].[Address 1] + ", " + [Query1].[City] + ' </a>'
20. Goto Page properties. Under Data, Select the Query and in properties add all the DataItems.
21. Save and run the report.

Report Page

Report Output

Integrating Google Maps with Cognos

Even though IBM Cognos has its own maps as a powerful feature, some users like to see their data integrated or represented using some commonly used tools as Google Maps. With Google Maps, we can obtain a view for any geography, choose the desired location, and open reports related to that particular location.

Architecture : 

Advantages :

1. All the functionalities of Google Maps can be integrated with cognos like direction tracking, distance calculation etc.
2. Drill down to another cognos report from the map.

Cognos 8.2 Installation and configuration Guide(PDF)

Please click the below link for the installation and configuration guide(PDF) of Cognos 8.2 with SQL server 2000.

http://rapidshare.com/files/416620672/Cognos_Installtion_and_configuration_with_SQL_server_2005.pdf


Note : While Configuring Content Store, delete the existing one and create a new one using the steps below.

1. Right Click Content manager and create a new content store
2. Give data base name as SQL server database.
3. For Name, give the database name which you have created for Content Manager.
4. For Database Server, give the server in which you have installed your database(localhost, if you installed in your local machine).
5. Give the user id and Password you created
6. Test the connection.

If you are using oracle as your database, don't forget to copy these jar(ojdbc14.jar, classes12.jar) files to WEB-INF/lib/ folder.

For any Queries contact me at sathishmm2003@yahoo.co.in