Data alignment for a row in CSV Format cognos 11 is not cmg in order

[krishdw85]

Hi All,
Please find attached. In cognos 11 csv format one of the report output is coming in different order. Numbers are coming right side and characters are coming left side but i am trying to make them in one particular order but i am unable to do it. I tried moving horizontal alignment but it was not working fine. Can you please help me with the solutions. TIA.

Regards,
Krish

[MFGF]

Hi All,
Please find attached. In cognos 11 csv format one of the report output is coming in different order. Numbers are coming right side and characters are coming left side but i am trying to make them in one particular order but i am unable to do it. I tried moving horizontal alignment but it was not working fine. Can you please help me with the solutions. TIA.

Regards,
Krish

Have you tried casting the value to a specific type, eg

cast([OPID], varchar(4))

Are you sure the result you're seeing isn't just Excel interfering? What do you see if you open the file with a text editor?

MF.

[krishdw85]

Thanks for the reply.

select
T1.OFC_NBR ||' - '|| RTRIM(T1.OFC_NM) "Office Nbr - Name",
T2. DEPT_CD "Dept Code",
T2. TEAM_CD "Team Code",
cast((T2. ASSOC_ID "OPID"), varchar(4)),
RTRIM(T2. LST_NM)  "Last Name",
RTRIM(T2. FST_NM)  "First Name",
T2. ACTV_FLG  "Active Type",
T1. OFC_NBR   "Office Nbr"
from
F6417DB . ASSOCIATE  T2,
F6417DB . OFFICE  T1

This is DB2 query.... when i apply the above code it is not validating and throwing error.

[MFGF]

Thanks for the reply.

select
T1.OFC_NBR ||' - '|| RTRIM(T1.OFC_NM) "Office Nbr - Name",
T2. DEPT_CD "Dept Code",
T2. TEAM_CD "Team Code",
cast((T2. ASSOC_ID "OPID"), varchar(4)),
RTRIM(T2. LST_NM)  "Last Name",
RTRIM(T2. FST_NM)  "First Name",
T2. ACTV_FLG  "Active Type",
T1. OFC_NBR   "Office Nbr"
from
F6417DB . ASSOCIATE  T2,
F6417DB . OFFICE  T1

This is DB2 query.... when i apply the above code it is not validating and throwing error.

I don't follow. Are you using Cognos Analytics to render your report? If so, why are you coding SQL? Just add a calculation to your report to cast the data, if that is causing your issue. However, I suspect it is just Excel messing things up here?

MF.

[Kiran P]

Hi,

I assume you are using direct SQL in your report.

cast((T2. ASSOC_ID "OPID"), varchar(4)), will throw error because you kept alias name inside the function. Change this to
cast(T2. ASSOC_ID , varchar(4)) "OPID" and try.

Also, check if excel is considering the format of the column to be 'General' in which case the only other way to force this to be considered text is to append a single quote in front of the value i.e. ''' +cast(T2. ASSOC_ID , varchar(4))  "OPID" which means the actual values exported to excel would be '232, 'abc etc which will be treated as text by excel. Note that in excel single quote will only be shown in the formula bar and not in the cell itself.


Thanks
Kiran

[krishdw85]

Yes i am using direct sql in my report and this is DB2 database and the query is not validating (RQP-DEF-0177 error code)

and in excel the report output is coming fine without any issue only in CSV Format i see the alignment is not coming as expected