QuoteThis is not what I would expect given they are all using the same concatenate calculations around each data item.

That does seem odd.

Quotewhen run to HTML output looks perfect

Define "perfect".  Do you mean you see the quotes?  Because, unless the quotes are part of the data, you should not.


The routine that produces the CSV will do whatever is needed to produce valid CSV.  Since you are adding quotes to your values, the value is being surrounded by quotes and the quotes in the data are being escaped by being doubled.

QuoteI have a requirement to run a report as a data dump to a .csv file where every column is wrapped in double quotes.

Is there a good technical reason that you would need...

"a","b","123","2025-02-20","Hello, World!","She said, ""I meant 20 widgets"""

...rather than...

a,b,123,2025-02-20,"Hello, World!","She said, ""I meant 20 widgets"""

...?


People don't read CSV, computers do.  Any requirement to output CSV from Cognos indicates to me that you are using a reporting tool to perform data integration.  There are data integration tools (SSIS, Informatica, etc.) that are designed for that task.  Short of that, I'd use PowerShell or C# before I'd use Cognos Analytics.

To confirm this is CA 12.0.2

Per your format, I want:

"a","b","123","2025-02-20","Hello, World!", ....

A set of double quotes around every column regardless of column data type.

But I am getting:

"a","""b"""","""123""","""2025-02-20""","""Hello, World!""", ....

A set of double quotes around the first data item and all subsequent data items have 3 sets of double quotes around them.

This is not what I would expect given they are all using the same concatenate calculations around each data item.
The first column is always the one that is correct regardless of which column is put first and the subsequent ones are always incorrect.

Could this be in anyway related to RSVP.CSV. Configuration Environment variables?
I have:

RSVP.CSV.DELIMITER = ,
RSVP.CSV.TERMINATOR = CRLF

Is there any thing else I need to set?

Again, when run to HTML output looks perfect, so to me this indicates something odd in producing the .csv

Any thoughts would be greatly appreciated.
Thanks,
Adam.

What is the expected outcome?  Are you just trying to get the CSV file to look like...


"a","b","123","2025-02-20","Hello, World!", "She said, ""I meant 20 widgets"""

...but you are getting...

"a",b,123,2025-02-20,"Hello, World!", "She said, ""I meant 20 widgets"""

...or...

a,b,123,2025-02-20,"Hello, World!", "She said, ""I meant 20 widgets"""

...?

I have a requirement to run a report as a data dump to a .csv file where every column is wrapped in double quotes.

Each column is defined as '"' || cast([Data Item] as varchar(256)) || '"'.

This works perfectly when the report is run to HTML, but when run to .csv, only the first column is wrapped in single double quotes. All other subsequent columns are wrapped in triple double quotes!

Any thoughts on how to resolve or explain the post first column behavior would be greatly appreciated.
Thanks in advance,
Adam.

Just my thoughts...

Unless I'm missing something, in looking at your output, it would appear that your rows are not unique.
Both Items appear to have different Y/N flags for "Zero in B" and "Zero in C&D" - Cognos will auto-aggregate to the lowest level of detail (grouping) shown on the output.
Assuming this to be correct, you could remove those columns from your output and the report should aggregate to a single row for each item.
However, if you need to show those columns, it will never consolidate unless you can conditionally display only one row.

Thanks,
Adam.

I am trying to do a Union between three queries but keep receiving the An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.  I am calculating week number by the function week(date) in each of the three queries.  One of the queries seems to be a different type of date than the other two, however, in my mind the function week() should return the same data type.  Nevertheless, I still tried to cast the data items to first integer, then string (using varchar(10)) and numeric.  I still receive the same error.  What is interesting is that numeric doesn't work for one of the data items.

Anyone come across this?  Any advice?

Is there a way to insert a hyperlink in an alert using Event Studios? 

I needed to shorten very long instructions to 150 characters.  Chat GPT did it in 2 seconds what would have taken me hours!

Cognos version: 11.2.4 FP5
Windows Server 2019

This is an intermittent issue, reports sometimes (more often than not) do not complete and return the following error, "XQE-JDB-0011 Error occurred when retrieving the database charset."  Other times the same report using the same package and same parameters run complete successfully. I have searched far and wide and haven't found a remedy, can someone point me in the right direction?

This topic has been moved to Reporting.

https://www.cognoise.com/index.php?topic=38322.0