Hi Eli,
maybe this helps to clarify one of the approaches.
I've created two queries in a new report
"local function" contains one column from a table and a calculation which combines a prompt macro with the data. It's just an example and can also be used with a stored procedure.
On the "data" query" allow cross joins:
Then you can create a new data item there referencing the calculation from the other query:
Run the report and you will be prompted for "input". This will then be calculated by the function. In my test 1.5 x Quantity x Unit Cost
------------------------------
Robert Dostal
Team Leader BI
GEMÜ
Ingelfingen
------------------------------
Original Message:
Sent: Tue March 01, 2022 04:19 PM
From: Elihu El
Subject: Creating a Function in a Cognos Report
...The report has a prompt page. Whatever value that is selected by the user, can it be passed to the function?
Original Message:
Sent: 3/1/2022 4:14:00 PM
From: Mark Fry
Subject: RE: Creating a Function in a Cognos Report
Hi,
As the very knowledgeable people above have indicated, there isn't really an easy way to define a function like this within an individual report. I guess you could add a query containing a query calculation, and then allow cross-products in the report so that you can reference it from any other query, but it would be messy and probably very inefficient.
Other options (as suggested above) would be:
1. Code the function within a database, and import it into a Framework Manager model as a UDF. You could define a standalone calculation within the model that references it, and then include that standalone calculation as part of your package, making it available for use in reports.
2. Create a stored procedure in a database to return the result you are looking for. Import it in Framework Manager - it would then appear as a query subject. You could join this to the rest of your data and publish it in a package. You would then have the ability to use the results in reports.
A third option would be simply to define a standalone calculation in your Framework Manager model that does what you need, created using the Cognos expression editor. Publish this in your package so it's then available for use in reports.
There may be other options, but those are the main ones I can think of right now.
Cheers!
MF.
------------------------------
Mark Fry
Technical Consultant
Original Message:
Sent: Tue March 01, 2022 03:42 PM
From: Elihu El
Subject: Creating a Function in a Cognos Report
...I want to pass one data item value from the data model and two parameter values from the report to produce the result in a calculated query data item. Thanks; However, I am uncertain of what you reference apply to this problem.
Warm Regards,
Eli
Original Message:
Sent: 3/1/2022 11:40:00 AM
From: Patrick Neveu
Subject: RE: Creating a Function in a Cognos Report
Hi Eli,
I believe that you can only create a stored procedure query subject (see also using prompts with a stored procedure in the documentation) in Framework Manager.
Correct me if I'm wrong but I think you cannot add a stored procedure with Data Module or with Report.
Best regards,
------------------------------
Patrick Neveu
Positive Thinking Company
Original Message:
Sent: Tue March 01, 2022 11:01 AM
From: Elihu El
Subject: Creating a Function in a Cognos Report
Good Morning,
I hope that you and yours are all doing well. I would like to create a function that will be used by multiple queries within the report. The function should input 3 parameters and generate 1 result. The user defined function would be programmed with built-in functions and constructs. How would I create the user defined function within the report?
Warm Regards,
Eli
#CognosAnalyticswithWatson