Monday, November 3, 2008

COGNOS 8.3 REPORT STUDIO - Columns or rows missing from crosstab if they contain no data

Faced this issue when one of the client wanted to display all the dates in the date range even though there was no data present for that particular date ‘Column’. This client was very particular about the format of the report and what they wanted. There was no compromise on anything at all.

Following are the steps we need to follow and we can solve the issue.
1) Create a "Column Query", containing only the column information and a dummy data item with a value of 1.
2) Create a "Row Query", containing only the row information and a dummy data item with a value of 1.
3) Create a "Dimension Query" query that joins the queries from steps 1 and 2 on dummy. This requires that the Outer Join Allowed property of the query be set to Allowed. This creates a cross join that includes all possible combinations of rows and columns
4) Create a fourth query that contains the data for the crosstab. This is the same as a normal crosstab report.
5) Join the queries from steps 3 and 4, using cardinality of 1..1 and 0..n respectively. In the join create 2 links, for both the data items (row and column). When dragging data items into this new query, ensure that you are dragging in the row and column headings from the "Dimension Query".

This ensures that all possible rows and columns will be returned, even if there is no data associated with them. (You can also find this solution in the COGNOS knowledge bank.)

Monday, October 6, 2008

COGNOS Report Studio 8.3 Filtering a TIME column and CASE WHEN

I had a report to complete, in which I had to filter in all appointments over 30 minutes. The metadata that I was using was a measure and it had both semi-aggregate and aggregate property as ‘SUM’.
Initially to filter the time column I used,
[time] > 30 , I got weird errors. I tries other functions like ‘contains’, ‘like’ and many more options. Later found out that the solution was very simple. The time should be filtered in the form of time only. I mean,
[time] > (000 00:30:00.000)
This took about 2 hours for me to figure it out. The other problem I had to face was using this [time] in a case when statement. Here the situation was that; I had to display in the rows as ‘8AM – 9 AM’ and so on. The format to filter here again changes, which took me about ½ an hour.
CASE WHEN
([Time] > 07:59:00.000 AND [Time] < 09:00:01.000) THEN '8AM - 9AM'
ELSE ‘FULL DAY'
END
So clearly, we don’t make use of that extra ‘000’ at the beginning of the time format. Crazy formats.

Monday, September 22, 2008

Cognos 8.3 - Steps for Comma delimited file addition into Cognos Environment

The default format of the CSV is tab delimited.

The steps involved in adding it to the Cognos Connection is as follows:

1. Go to Cognos Connection.
2. In the upper-right corner, click Launch, Cognos Administration.
3. On the Status tab, click System.
4. From the System drop-down menu, click Set properties.
5. Click the Settings tab.
6. Next to Environment, Advanced Settings, click Edit.
7. Enter parameters and values from "CSV Properties and Values", as required.
ie.
RSVP.CSV.DELIMITER: ,
RSVP.CSV.ENCODING: windows-1252
8. Click OK.

Note:
It takes about 30 seconds after this change for the changes to take effect.
COOL!!! Right 

Tuesday, September 16, 2008

Framework Required Filter in Cognos 8.3 RSV-SRV-0042

One thing I have been observing is that, in 8.2 when we have required filters to access the metadata we would again filter the same filter in a logic manner with the ‘Usage’ set to ‘Optional’. So basically what is happening here is that, we are using the filter once in the prompt page and a filter in the query also. But in the Cognos 8.3 it throws an error when I try to do it that way.
The error looks something like this.
Prompt 'beginning_AR_date' is used multiple times with conflicting data types
In Details
RSV-SRV-0042 Trace back: RSReportService.cpp(747): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(217): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_Request RSASyncExecutionThread.cpp(690): QFException: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(204): QFException: CCL_CAUGHT:…….and goes on.

Tuesday, August 26, 2008

Cognos 8.3 – Date prompt Error Microsoft SQL database

Recently, we had an issue with date prompts. When we use regular date prompts, one each for start and end, the reports were running fine. But later when we had to test the report on ‘Microsoft SQL’, we faced some issues.
Something like: -69 , -9, -201 Errors and sometimes
‘prepare’ [Microsoft ODBC] and some crappy message.
Initially, I had come out with a simple solution, that is to keep the date prompt in ‘in _range‘ and the report used to work. But we could not make too many changes with the ‘in_range’ prompt. Then I found out that, because Microsoft uses a different date format we need to cast the filter.
Example - [date] between cast(?begdate?, timestamp) and cast(?enddate?,timestamp)

Even if we use cast into ‘Date’, the prompt does not seem to work.
The main problem one faces in this situation is identifying the issue, The errors are so strange and are not at all related to the issue sometimes.

Never took the screen shot of the issue and now too lazy to make the changes and put in the screen shot.

Tuesday, August 12, 2008

Disabling a database connection in Cognos 8


Get on to the ‘Content Administration’ in the ‘Launch’ drop down. Select the ‘Configuration’, this by default one will be in the database section. Click on the database connection, it will take you to the 2nd level of databases, where we can have n number of database connections.

Now click the ‘set properties’ button in the properties of the database you want to disable. On the ‘set properties’ click the ‘general’ and you will find a ‘check box’ for disabling the database. Just check the ‘Check Box’ and you have done it.
The image shown below should give you a better idea of how he database connection should look like when it is disabled.(One with ‘Hour Glass’ is disabled)

Wednesday, August 6, 2008

Cognos Report Studio 8 – Setting Up Mail Server

Setting up the mail server is so easy. Find out from your Network Administrator about the SMTP connection and port number. Usually, it is 25 or 2525, depending on the security issues of the company. Once you find that out, it is all simple.

1. Open the Cognos Configuration
2. Click on ‘Notification’.
3. SMTP mail server: mail.urcompanyname.com:2525
4. Account and password --- leave it default or your company security password
5. Default Sender --- you@yourcompany.com

You will have to restart the Cognos Configuration, once the mail server check runs during the restart; it should send you an email to the specified email address in the ‘Default Sender’.

So, once this is done. When we run the report, we do have a default option ‘Send by Email’ for all the report output in 8.3. Every time we run a report, we can attach the report output to the email or just send a link (which will be HTML by default, works more like a report view.)

Wednesday, July 30, 2008

Image of the custom Cognos Home Page




I have the detailed code of the system.xml file but I was thinking I will also add the screen shot of the changes I made to the home page icon. I have also done couple of other branding in my local machine, will put on the screen shots soon.

Thursday, July 10, 2008

Replacing the Cognos Connection text with a corporate logo

Some of the code here is not right as the HTML form will not allow it to display. But the missing code is basically some open or close braces. Otherwise, this should help do the work easily.

1. Place image in the folder /c8\webcontent\skins\my_style\branding. In my case the address is like
'C:\Program Files\cognos\c8\webapps\p2pd\skins\corporate\branding'
2. Edit the system.xml file using Notepad or an XML editor. (C:\Program Files\cognos\c8\templates\ps\portal )
3. Search for “ Custom OEM headers ”.
4. You will notice that this section is commented out. Un-comment out this section and add the following section for your new style:

Custom OEM headers
param name="OEM"
Specify custom Cognos Connection / Cognos Viewer left side header here in the form of XHTML snippets. Custom headers can be style-specific. Example:
customheader showcontext="false" contextdelimiter=""
style stylefoldername="my_style"
table style="background-color:#2d5d3d"
tr
td
img "../skins/my_style/branding/bank_logo.gif" / /td
/tr
/table

/style

style stylefoldername="corporate"
table style="background-color:#ffffff"
tr>
td > img "../skins/corporate/branding/my_logo.gif" / /td
td class="headerTitle" style="padding-right:2px;white-space:nowrap"> My company /td>
/tr>
/table
/style

5. Restart Cognos 8 service for these changes to take effect.


If we have the Framework permission, then this should be a childs play. Simple and clear.

Tuesday, June 3, 2008

Errors, Cognos and Parameter Mapping

I did come across so many errors this week in Cognos. Luckily I could solve most of them. I faced errors like -9, -28. I want to put in complete details and solutions for them, but all the documents related to the errors are at office and I hardly get time in the office to blog. May be this week I could take out 20 minutes and update on couple of errors that I faced.

Parameter mapping is one thing I have been working on for over a week. I did most of the work on the first day itself but I was getting errors. The errors were not clear and were different every time I ran the report. The simple mistake I made was that, in the html code I had given a default value and in the actual value prompt, I did not have a default value. It took me a week to find that out.

My only advice to people who are using html codes is that, first check the cognos report is completely fine and only then add the html code to it. I will try to put the "parameter mapping" code on this blog soon.

Friday, May 30, 2008

Cognos 8 Report Studio – Broken Links

Sometimes when u make changes / save other reports on an existing one, all the report views related to that report will break (i.e. broken links). The symbol for a broken link would be something like that is seen in the picture above.
The solution is simple, go to the “set properties” of that report view. Under “General Properties” you can find “Link the Report” at the bottom of the page just above the “Ok” button. Click on this link and choose the report that u want to connect to. The report view should run fine with all the predefined selections that we had done earlier.

Wednesday, May 21, 2008

Filter accepting only 1 value instead of multiple values Congos Report Studio

This is one issue where I spent like 1 full day. The issue I was facing was, the report had a ‘search and select prompt’. I had created similar reports just couple of days back and this new report basically had a different object, nothing more.
I added some new prompts and when I ran the report, everything was fine except that the filter was taking in only 1 value in a multi select prompt. When I display the filter on the report page as a text, it shows all the data items I selected but in the table it was giving only one value. I checked on the filter whether I used ‘=’ or ‘in’ because usually that is the mistake we do.
Anyways, the reason it was happening was because of the framework. The parameter filter in the framework was assigned to ‘=’ rather than ‘in’, so no matter what the filter was, it would not allow it to work. So when things act very weird, just talk to your framework manager on the conditions that he has applied on the framework.

Thursday, April 24, 2008

Hide / Show Column / Row depending on the conditional prompts

The steps for conditional columns and rows is simple.

1. Create a string boolean variable from the style variable.
2. Write in your condition.
Column logic is different from a row hiding logic.

Column:
1. select the column and its header, u will get a property "Render Variable" (To select the entire column, use the property button to select the "entire column")
2. Set this variable to the boolean stringn that you need to apply.
3. That's it. Job done.

Row:
1. Select the entier row and not the single cell.
2. go to Conditional explorer, select the boolean string.(Yes)
3. The explorer will turn green, select the style variable and click ok.
4. You will get back the entire property column, select the "box type" and set it to "None".
5. Double click on the explorer and the green color will disappear. The row will come back.

I believe this will be useful for me also in future. Hahahaha.

Saturday, April 12, 2008

Paging Prompt - Cognos Report

Sometimes when we do things right and fast, we feel everything is easy. But sometime we get stuck on small things.

The logic to create a prompt on displaying different report pages.

1. Create a prompt( with static choice).
2. Create boolean variables (Page1, Page2)
3. Create the static choice.
4. Go to the explorer and click on page1 and in the properties select "rendering" and assign it to the created boolean variable.

It is as simple as that.

Good luck.

Saturday, April 5, 2008

Naming Dynamic Columns - Cognos Report Studio 8.2

Ok....... Here I am .. back with the blogging after a long time.

This week I did do some work in Cognos, I will try to update with the problems I faced.

There was a issue where the columns of a crosstab were named as ' 0 ' for all the representatives and the second column was ' 1 ' for all the non representatives. This is because, the data item is been stored in a such a way. The solution is simple here.

Create a data item with the representative and put in a if condition with the respective names. For example:

if ([Dataitem] = 0 ) then ('Representative') else (' Non Representative').

Please keep in mind about the syntax.