Wednesday, December 2, 2009

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)

Tuesday, December 1, 2009

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.