I don't think a macro can be created that can be reasonably flexible to handle anticipated user requirements.  As a fun exercise, I worked on exactly that.  Here's what I came up with.  Keep in mind... This falls squarely into the "Why in the world would you do that?" category.

• Create a report.
• Select a source.
• Create a query with a custom SQL object.
• Set the data source.
• Modify the code from below (Custom SQL - hard coded) to include your column names and the correct number of values in the data, then put it in the custom SQL object.
• Create a prompt page and add a textbox prompt named *customdata* with multi-select and multi-line both turned on.
• Create something on a report page that uses *customdata*. Maybe include a filter like
  Code Select Expand
  [querysubject] in (#promptmany('customdata', 'string')#)
• Preview the report page.  This should ask you for values for *customdata*.  Add two values.  Value 1 is your tab-delimited list of column names.  Value 2 is your tab-delimited list of values (for one row of data).
• Copy the code from below (Custom SQL - with parameter) and overwrite the custom SQL object's definition.
• Now you can run the report.  Don't forget that copying from Excel includes a \r\n that you don't want, so backspace once after pasting.

limitations:

• All rows of data being pasted into the textbox prompt, including the header row, must be shorter than 128 characters.
• Don't include values with leading spaces.
• Don't include values that contain commas.  Specifically comma+space (, ) because that's what Cognos uses to delimit lines in the parameter value when used in a SQL object.  This was a little confusing because a semicolon (;) is used to delimit lines when processing the parameter in a data item.
• This script is specific to SQL Server.

It may be possible to mature this a bit more, but I'm thinking some of the limitations of the macro language may be insurmountable.

Note:  This is intended to copy data from Excel and paste into a textbox prompt.  Values are tab-delimited.  In the code below there is a tab in two places.  It may not come across well on this forum.

**Custom SQL - hard coded**

    select *
    from (
      values
        ('', '')
      , ('', '')
    ) q ([customcolumn1], [customcolumn2])

**Custom SQL - with parameter**

    select *
    from (
      values
        #substitute(
        '$',
        ''')',
        substitute(
          '^',
          '(''',
          join(
            '''), (''',
            split(
              ':::',
              join(
                ''',''',
                split(
                  ' ',
                  join(
                    ':::',
                    split(
                      ', ',
                      substr(
                        substr(
                          promptmany('customdata', 'token'),
                          index(
                            promptmany('customdata', 'token'),
                            ', '
                          )
                        ),
                        2
                      )
                    )
                  )
                )
              )
            )
          )
        )
    )#
    ) q (#join(
          ', ',
          substitute(
            '$',
            ']',
            substitute(
              '^',
              '[',
              split(
                ' ',
                substr(
                  promptmany('customdata', 'token'),
                  0,
                  index(
                    promptmany('customdata', 'token'),
                    ', '
                  )
                )
              )
            )
          )
    )#)


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