Quote from: dougp on 23 Apr 2025 12:56:40 PMAre you saying you want the user to tell the report to use arbitrary data for one of the queries?

That is exactly what I'm saying! :)

I am... reasonably sure that I can build this (or rather: that this can be built. whether I can do it is a different q) by using macros in a SQL object. It's just rather painstaking and tedious to build; I have no decent way of finding out what exactly a macro turns my input into. I wish there were some kind of "macro sandbox" which lets me fiddle about with several input scenarios and that evaluates macros at runtime *and shows me the result*.

dougp, thank you so much.  your suggestion worked perfectly!

We're running IBM Cognos Analytics version 11.0.1. Users are experiencing issues when attempting to delete reports or folders from their "My Content" area. The process is as follows:

 1. User clicks on the three dots (...) next to a report or folder. 
 2. Selects "Delete" from the dropdown menu. 
 3. A confirmation window appears; user clicks "OK".
 4. The window closes, but the item remains in "My Content".

No error messages are displayed, and the item is not deleted. 
 
Troubleshooting Steps Taken:

 - Verified that users have "Write" permissions on their "My Content"
   folders.
 - Checked ownership of the items; users are listed as owners.
 - Reviewed folder hierarchy for overridden permissions; none found.

Question: Are there any known issues or additional settings in Cognos 11.0.1 that could prevent users from deleting items in their "My Content" area?

I'm unclear on the usage requirement.  Are you saying you want the user to tell the report to use arbitrary data for one of the queries?  If so...

This is something we do.  Long ago, I created packages using the "add external data" feature in Cognos 10.2.1 running in IE10, and checking the box to have Cognos ask for the spreadsheet.  But IE10 (and 11) is a security flaw.  That hole was patched, so I don't know how to create such a configuration today in Cognos 11.2.4 and Edge/Chrome.

I have been teaching my users to refresh the spreadsheet, then run the report.  Naturally, that breaks down if you can't trust your basic Excel user to not do typical Excel things, like adding or removing columns, mucking up data types, etc.  So if the report will be used broadly, across many business units and groups who do not own the data and reports, this is not a good solution.

This requirement for one report is keeping me from upgrading a package and from migrating the report to Power BI.  It's a nice feature that I would like to see return.  Please post back if you find a solution.

I use SQL Server, so I can't try LIKE ANY, but will this work?

fieldname LIKE ANY (#csv(substitute('$', '%', substitute('^', '%', split(',',  toupper(prompt('SEARCH_CRITERIA', 'token'))))))#)
Get SEARCH_CRITERIA value.
Convert the value to upper case.
Split comma-delimited string into individual values.
Replace the beginning (0 characters) of each string with %.
Replace the ending (0 characters) of each string with %.
Join the individual strings into a comma-delimited list.

You may need to wrap the csv() function in sq()

Hi all,


here's what I want to achieve: A query runs against my data source, fetching e.g. three rows of data (three is not fixed, results may vary):

Dim	Value
A	10
B	25
C	42

I want to crossjoin this to variable input at runtime, e.g. let's say this is the table I want to crossjoin against for one execution (again, number of rows is not fixed):

User	Weight
Jane	0.5
Jim	0.3
John	0.2

So that my final result can be this:

Dim	User	Value * Weight
A	Jane	5
A	Jim	3
A	John	2
B	Jane	12.5
B	Jim	7.5
B	John	5
C	Jane	21
C	Jim	12.6
C	John	8.4

Now, I know how to achieve this by a) updating an uploaded Excel file with my variable input and refreshing the data module, or b) embed an SQL object in my report definition. Both variants are decent if it's me as an analyst running the report, but are nonviable if the report is for enduser execution.

Is there any way to achieve the same result using prompts to input my weights table at runtime?

This is what i used for LIKE:

fieldname LIKE UPPER('%'||(#sq(prompt('SEARCH_CRITERIA', 'token'))#)||'%')

You say you got it to work with LIKE.  What expression did you use with LIKE?

I am using the below prompt in a report and I want to be able to have the % wildcard automatically concatenated around each word in the search criteria.  For example if a user wants to search for SMITH,JONES, I want the prompt to put in the % wildcard so it looks like this:  Search Criteria: %SMITH%,%JONES%

I was able to concatenate the % when just using LIKE but with LIKE ANY, I couldn't get it to work.

fieldname LIKE ANY (#csv( split(',',  toupper(prompt('SEARCH_CRITERIA', 'token'))))#)

The version of Cognos I am using is: IBM Cognos Analytics with Watson 11.2.4

Thank you

You can try using a calculated field or a conditional aggregation in your crosstab. Basically, you need to create a formula that calculates the year-to-date sales regardless of the month filter. For the first column, you could set the calculation to ignore the month filter, so it always shows the total for the entire year.