How to use the ‘Convert-XLS’ tool for creating a fixed length text file from an Excel / How to convert a COGNOS report to a fixed length text file

COGNOS does not support fixed length text file output. So we found this tool which converts an excel file into fixed length text file. Following are the steps involved in this 2 step process for getting a fixed length text file from COGNOS.
1. Download the software from http://www.softinterface.com/Convert-XLS/Convert-XLS.htm
2. Install it to the default location (which should be C:\Program Files\Softinterface, Inc\Convert XLS)
3. Create a simple text file (Uncomp-Original.txt) where we can define the length for each column. Something like this:
a. 1 2 [32] Space Left
b. 2 12 [32] Space Left
c. 3 8 [32] Space Left
d. 4 8 [32] Space Left
e. 5 20 [32] Space Left
f. 6 1 [32] Space Left
g. 7 8 [32] Space Left
h. 8 1 [32] Space Left
4. Now place this ‘Uncomp-Original.txt’@ the location ‘C:\Program Files\Softinterface, Inc\Convert XLS\Profiles’. Please make sure that it is ‘Profiles’ and not ‘Profile’. If the ‘Profiles’ folder does create by default please create one.
5. Now, run the ‘Covert-XLS’ tool.
6. Click on ‘Conversion Options’
7. Now that we have file in the place, we should be able to see ‘Uncomp-Original.txt’ in the drop down and just click on ‘Apply’.
8. Next we need to set the ‘Original File Format’ to [-4142] Excel Workbook (*.XLS) and choose the radio button to ‘Use Convert XLS’. Once this is done, from the ‘Folder’ under the ‘Original File Format’, choose the source file.
9. Under the ‘Convert to File Format’, choose ‘[21] Text (ASCII), Fixed Width (*.TXT)’ and click on the ‘Convert’ or ‘Play Button’.
10. You will find the output @ the specified location (Target File)

COGNOS 8.3 sqlPrepareWithOptions status=’-9’

We came across this issue very recently when we updated over ODBC driver from 3.51.12 to 3.51.27. The main reason why we upgraded the ODBC was because of the ‘Auto Reconnect’ property that is available. Especially when pulling large DB and network issues, it is advised that we have this new ODBC which can reconnect automatically when the connection is lost due to network problem or large data size.
Anyways, coming back to the error message here; the framework was also throwing this error and I was thinking that it was something to do with the joins. But later found out that, when a filter is added on to the query subject and this filter has ‘=’ to the prompt ([service date] = ?date_prompt?) then SQL bind parameter issue was coming up. So I changes it to ‘IN’ instead and things started working properly.

Even though I made the changes in the framework, the reports were still having issues. I had to changes all the filters in the errors to ‘IN’ from ‘=’ in place where the prompt was considering a ‘numeric’ filter.

I don’t know if this is best solution and the only solution to the problem but temporarily I am able to run the reports. I am pretty sure I did see this issue before for which I had a different solution then.

Second COGNOS Conference Successfully !!!!!!

Successfully made it to the second year of COGNOS conference at Hyatt Residency, Boston. The day was pretty interesting from driving all the way from Westborough to Cambridge which took me one hour and ten minutes with all the traffic and the rain pouring badly.

Started with the Key Note presentation where the concentration was majorly on the new COGNOS iPhone app and the COGNOS integration with SAP. They were also showing COGNOS Express and COGNOS Go Dashboard which were already shown in the previous conference.

The first session I sat was the Report/ Dashboards and Scorecards. There was only one report that really impressed me. All the other stuff that they were demoing where the regular ones.

The second session was the COGNOS Express Manager – Integration of excel sheet with IBM COGNOS. The new feature they showed was the updation of the data from the excel creates immediate effect on the reports that are generated from the excel sheet. The COGNOS Express had some good featuers like the Scorecards (which have interesting features like the charts with default values and calculator, just like a computer app.) and COGNOS Configuration and Log files and ODBC and Users list; are all added to the Xccelator GUI at the Home level. Ifelt it is a Fantastic approach, especially to the install team at my company who can have much faster and easier way to update clients. So the installation and configuration will just a click away.

Third session --- experience of COGNOS USER. Big client with 85 million records who use Teradata. Not the best of presentations but was OK. They seem to be using the cubes rather the live DB due to their humungous data. This session also spoke about how important is it to maintain global standards and documentation, centralization of things.Fourth and the final session – balanced scorecard methodology… I have no clue and I need to ready about it. Case study about Eneco (GATNER) . This was more about Metric Studio.The most funny part was that, in each of the presentation they had the same dashboard and everyone was explaining it in their own way. It actually shows us that how we can interpret one single report into so many ways.

After seeing all these people presenting on the stage… I strongly believe I can give a better presentation of COGNOS .

From query studio to a pdf link from the right click property called Glossary

Apache and IIS as default webserver and not Web Sphere.

The default DB which comes with COGNOS is Informix and Not DB2.

COGNOS 8.3 Query Studio report must update Error

The above image actually shows 2 different errors. The first error asks you to update to the newer package that was published (This usually occurs when the query studio is open and a newer publish has been published during that time period.) During which we usually, log out of query studio and log in again. But here, we could not even do that and when we click on ‘OK’ it took us to the second error ‘Failed to load the pluggable shared library’.
The error that COGNOS is throwing and the actual issue are completely different according to me. The issue here was that, ‘Guarantor Name’ metadata in the development layer had a different spelling than what it was before and so the Presentation layer was not able to retrieve the metadata from the development layer. Once I ran the query subject in the framework to test, it through an error which clearly identified that the development layer did not have the metadata which the query was looking for.
The solution here was that I had to update the development query with the right path for each of the metadata elements in that query subject.

COGNOS 8.3 – sqlpreparewithoptions status = -17 Error

I have seen this issue couple of times on the machine where there are too many users or at places where the DB is massive. My general solution to this issue is to restart COGNOS services every night (setting up a scheduled job) or weekly once. If this does not work then try the alternative solution.
The alternative steps that I do follow are:
1. Go to task manager, look for Derby.exe under the process and end the process.
2. Go to COGNOS/c8/bin and delete all the *.dmp files. (DMP – Are basically memory / error dump files)
3. Increase the memory size in the COGNOS Configuration.
4. Restart COGNOS.

COGNOS 8.3 Report Studio /Package issue Model /Package does not exist Error

The basic nature of this error is that the report that we are trying to open does not have a package assigned / selected and the solution is to the open the report in a report studio and select report package option from the file option. Save it.
I got this error at of the end users machine. Accordingly to my analysis, there are 2 reasons for this error to occur:
1. This report could be pointing to a different package rather than the package from which it was deployed. One more similar issue could be that people could have opened the report and might have cancelled the package loading to check some formatting and may be they saved the report that way before closing.
2. The second issue could be, sometimes (very rarely) we have seen that the package is not created properly by COGNOS itself but in this case when the report does not have a package selected; the error is a little bit different. (Multi processing - framework is been published and some one is trying to create a package at the same time)
Due to lack of time my language is not in its best. 

COGNOS Home in COGNOS Admin page

When this first happened, I was actually on a call with the IBM support going through an older error that we were facing. If you look at the second image you can see that; the more I drill down, the no of pages increases. For a while I was thinking I need to re install everything on the server. But when I tried the same ‘COGNOS Administrator’ from my local machine through a URL, things seemed to be working fine.

So I believe the problem seems to be with the browse and its settings rather than COGNOS. So I upgraded the web browser on the server and things got back to normal. One can also try to delete the cache memory in the browser.

COGNOS 8.3 [the_dispatcher] com.cognos.pogo.performance.PerformanceIndicationHandler Error

I came across this bizarre issue while running an existing report. This particular report used to run fine; but all of a sudden it started throwing this error. I was wondering why ? We never touched the report and the modified date was dated way back last year.
Coming to the point, I tried checking the metadata and other totals / sub totals in the report. We even restarted the cognos service thinking it is something to do with the dispatcher.
But the solution was ……. We had added a some new filters to the metadata and the report was running on older filters. So all we had to do was to rename the filters in the report to the newer ones and things got going smoothly.

The problem with COGNOS is that, it does not point out the exact error message.

COGNOS 8.3 displaying date format from MM/DD/YY to MMDDYYYY

This part actually looks simple and straight forward. All I did was, go to data format property of the column date and set the pattern to MMDDYYYY. But the interesting part was that, the output was stranger; something like 041042009.
I was thinking, why the day is in 3 digits instead of 2? The reason being it counts the days from the start of the year. So if you want to use the MMDDYYYY format keeping the days to 2 digits, then you need to write the pattern as MMddYYYY. Isn’t it silly?

COGNOS 8.3 Error - Setup cannot continue because an outstanding reboot is required. Please reboot your computer and try again

I haven’t come across this issue but my colleague did; it is such a strange error that even after rebooting the system several times we were getting this error. So I logged into the COGNOS knowledge bank and found the solution.

Basically this issue may occur when trying to reinstall Impromptu or PowerPlay (Series 7) or COGNOS 8.3 on Windows operating system. The setup prompts to reboot the system but rebooting the system several times does not help and we get the error message “Setup cannot continue because a reboot is required.”

So the solution here is:
1. Open Windows Explorer.
2. From the Tools menu, click Folder Options, View, Hidden files and folders radio button.
3. From the Start menu, click Search, All Files or Folders and enter GSUNINSTALL.FLG.
4. Rename the GSUNINSTALL.FLG to GSUNINSTALL.OLD in the local Temp directory.
5. Proceed with the installation.

COGNOS – Using a blob column in this query requires that the query subject ….. must have either a key or a unique index

BLOB is basically an abbreviation for Binary Large OBject. It is a collection of binary data in the database.
The snapshot above shows an error because of the BLOB and one of the main reasons for that error could be a particular field in the DB that is a text field. At least in my case, when I first encountered this issue, it was because of ‘text’ field in the db which is not supported by COGNOS. So the solution for this is to change the text field in the DB into a VarChar.
In one of the metadata queries, we again faced this issue even though we had updated the DB long back. This error started coming when we added new data into one metadata query @ the package level. When I tested the query individually at the import layer; it seems to be working but when I am combining couple of these BLOB VarChar from different metadata queries I was facing this issue again. So I named these same objects differently at the import layer level (on different queries) and joined them @ the package level it seemed to be working.
I am sure that this error had nothing to do with the blob issue but it was still throwing the BLOB error. I would say; always name the metadata with different and specific names so that the system does not get confused when we create relationships.

Content Manager did not return an object for the requested search path COGNOS 8.3

This issue usually comes up when it is a drill through or a drill down and there are couple of issues why we could get this error:

1. Firstly, check the path of the report ones. Mainly check the spaces or change in name of the report. Sometimes what happens is that; when some one uses a report they might save it with a different name and delete the actual one.

2. The other thing is to check the package. It might happen that we develop the report in one package and without deploying we copy / paste the report in a different package. Later when we delete the actual package the Link to the second level report is lost.

This happens to me a lot as we develop reports in one package and test it in a different one and I don't get enough time to create and deploy the package every time.

3. The other issue is a very very rare case but has happened to me once, when we deployed the package; COGNOS did not convert all the reports properly and the paths in the reports did not get updated.