We have column that contains a text description and we want to filter on certain keywords, and a column which shows which keywords have been filtered (as not all keywords are in the same description).

I've tried several options with CASE, IF and IN_RANGE statements, but do not get the desired report.

Example
Keywords:
Test1
Fox
Cat
Dog
Test2

Text description:
The test1 module has been tested and shows good results on cats and dogs.

The example has 3 keywords, test1, cat and dog, which I try to filter with cognos queries. The other 2 keywords, fox and test2 are not in text description examples, but might appear in another text description.

Hope someone can help me out.

QuoteResults are:"a","b","123","2025-02-20","Hello, World!",....
Again, this indicates that .csv output format is doing something unexpected to the result set.

If that is what you see in HTML,

"""a""","""b""","""123""","""2025-02-20""","""Hello, World!"""
...is the expected outcome.  That's because if a value contains a quote, the CSV must be encoded as such a way as to not lose that character when the data is read.

Using Excel, for example, if I start with this data...

c1	c2	c3	c4	c5
a	b	123	2025-02-20	Hello, World!
"a"	"b"	"123"	"2025-02-20"	"Hello, World!"

...and save as CSV, I get...

c1,c2,c3,c4,c5
a,b,123,2025-02-20,"Hello, World!"
"""a""","""b""","""123""","""2025-02-20""","""Hello, World!"""
Clearly, modifying your data by adding characters to every value is not the right answer here.


Looking through https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=reference-report-service-batch-report-service-advanced-settings I see you can set the qualifier, but there is no way to force the qualifier to be used when it is not needed.


A CSV parser will read the following rows as identical:

a,b,123,2025-02-20,"Hello, World!"
"a","b","123","2025-02-20","Hello, World!"
It seems unlikely that the vendor can't consume generic CSV.  Is the vendor really not using a generic CSV parser?  Did they go to the effort to develop their own, non-standard CSV parser?

If this truly is a requirement, something like this PowerShell script would work.

$fileIn = Join-Path (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path "Book1.csv"
$fileOut = Join-Path (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path "Book2.csv"

Import-Csv -LiteralPath $fileIn | Export-Csv -LiteralPath $fileOut -UseQuotes Always
But if you're going that route, you should also have PowerShell (or your script runtime of choice) run the query to get the data to begin with.  This is not a good task for Cognos Analytics.

To answer some of your questions.

When I run to HTML the results look as I would expect, a single set of quotes wrapped around each data item regardless of data type. To confirm each data item looks like '"' || cast([Data Item] as varchar(256)) || '"'.

Results are:"a","b","123","2025-02-20","Hello, World!",....
Again, this indicates that .csv output format is doing something unexpected to the result set.


The need is to replace a manual data dump for a Vendor executed out of Toad.
The idea is to match the current data format of the existing data feed so that the vendor has no impact on their end.

We want to automatically trigger it to run on a dependency of two file loads completing on the database (this we have successfully done).
I don't see any reason why Cognos shouldn't be a perfectly good fit to produce this output - There may be other tools, but Cognos should work too!

Again, any help would be greatly appreciated.
Thanks,
Adam.

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?