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):
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?
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.
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*.
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
[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'),
', '
)
)
)
)
)
)#)