All Forums Extensibility
paisano 7 posts Joined 10/04
10 Feb 2015
Passing result set from stored procedure to Cognos

I have a requirement in in which I would like to call a stored procedure from Cognos, passing an ID
to the SP. The SP would retrieve data from a table and then loop through the data, formatting it.
Here is the basic idea:
Cognos calls SP with ID "ABC100".
SP retrieves following data:
100  Julus Caesar
100  crossed
100  the Rubicon
200  Santa Claus
200  lives in the Bahamas
and formats it as follows:
100 Julius Caesar crossed the Rubicon
200 Santa Claus lives in the Bahamas
These last two lines would be returned to Cognos to be displayed in a report. The actual task as hand is
far more complex, with multiple nested loops and various lines to be concatenated, but essentially a set
of 1:n lines would be returned to Cognos.
Has anybody done this and can steer me in the right direction, or alternatively can say it cannot be done
(thereby saving me the time and effort of trying it)?

dnoeth 4628 posts Joined 11/04
10 Feb 2015

Of course you could do it with a SP, but there might be much simpler ways to do it.
How do you get the rows to be concatenated?
Can you elaborate on the "far more complex, with multiple nested loops" part?


paisano 7 posts Joined 10/04
10 Feb 2015

The data I am dealing with is historical transaction data. From a set of vertical lines I have to
produce a horizontal line where the data from the vertical lines are concatenated. Ultimately I want to produce an answer set and build up a report output that is ready to be displayed (in Cognos). 
What I have are transactions, and each transaction can have multiple categories and sub-categories. For each new transaction, I pull out the time and user info from the first line, then step through the categories. Each sub-category will yield one line, but within the category I can have four different types of output line. It is a classical task for a procedural language, with at least three nested loops and building up output lines by concatenation based upon different criteria. Here is a simplified example, omitting the transactions, categories and sub-categories:

Example of data in SP              

locn    time                   type   txn cd         new            old        user id
Car 1   11.01.2015 15:23:40    add    last name      smith                     P100F
Car 1   11.01.2015 15:23:40    add    first name     john                      P100F
Car 1   11.01.2015 15:23:40    chg    doc type       passport       id card    P100F
Car 1   11.01.2015 15:23:40    del    service                       tablet     P100F
Car 1   11.01.2015 15:23:40    add    gender         Male                      P100F
Car 1   11.01.2015 15:23:40    add    DOB            11.07.1977                P100F
Car 2   11.01.2015 15:23:40    chg    Title          Mr              Mrs       P100F
Car 1   11.01.2015 21:11:02    del    Gender: Male                             AB200

Example of output              
Time: 11.01.2015  15:23:40    User: P100F              
Car 1     Added: Last Name = Smith   First Name = John  DOB: 11.07.177 Gender =  Male              
          Changed: Document ID Card --> Passport              
          Deleted: Service Tablet              

Car 2     Changed: Title Mrs --> Mr   

Time: 11.01.2015  21:11:02          AB200              
Car 1     Deleted: Gender Male              

I considered using arrays to build up the output, but I still have to step through each row and analyze if a break has taken place and where to add the information. The logic in the SP is not so much the problem as how to get the results back to Cognos so it doesn't even know something happened in the background.
Any ideas?

paisano 7 posts Joined 10/04
10 Feb 2015

Rats - sorry about that mess in the previous post. When I posted it, it was a nicely organized table, which was turned into a ghastly one-column post.

dnoeth 4628 posts Joined 11/04
10 Feb 2015

What's your TD release? Are XML services installed?
There's a nice XMLAGG function for concatenating multiple rows into one:

create volatile table vt (
   locn varchar(10), 
   time_ timestamp(0),
   type_ char(3),
   txn_cd varchar(20),
   new_ varchar(20),
   old_ varchar(20),
   user_id char(5)
 ) on commit preserve rows;
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','last name'      ,'smith'         , ''           ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','first name'     ,'john'          , ''           ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','chg','doc type'       ,'passport'      , 'id card'    ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','del','service'        ,''              , 'tablet'     ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','gender'         ,'Male'          , ''           ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','DOB'            ,'11.07.1977'    , ''           ,'P100F');
ins vt('Car 2',timestamp'2015-11-01 15:23:40','chg','Title'          ,'Mr'            , 'Mrs'        ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 21:11:02','del','Gender: Male'   ,''              , ''           ,'AB200');

SELECT time_, locn, 
      when type_ = 'del' then 'Deleted: '
      when type_ = 'add' then 'Added: '
      when type_ = 'chg' then 'Changed: '
   || (XMLAGG(case
                when type_ = 'del' then txn_cd || ' ' || old_
                when type_ = 'add' then txn_cd || '=' || new_
                when type_ = 'chg' then txn_cd || ' ' || old_ || ' -> ' || new_ 
             ) (VARCHAR(1000))
GROUP BY 1,2,type_
order by 1,2,type_



paisano 7 posts Joined 10/04
10 Feb 2015

We're on 14.10. XML services are not installed. We had them on 13.10, but they were not reinstalled after the upgrade. I'll request they be installed and try out the XMLAGG function. The example looks promising, and even if I cannot use it here, it never hurts to have tried it.
If I do have to go with a SP, I still do not know how or if it is possible to return a result set to Cognos. Any idea if this is possible or should I look for another solution Iif XMLAGG does not wok out)?

paisano 7 posts Joined 10/04
11 Feb 2015

just a short update - and a big thank you to you Dieter for the suggestion - that the XMLAGG does an excellent job of concatenating the different rercord types. The output I now have substantially reduces what I previously had (e.g. ten lines reduce to two). As opposed to arrays, XMLAGG is a piece of cake to use.
Now that I have an output I can work with, the next thing to try will be using this as input to a SP which writes to a volatile table. Embed the SP and a subsequent select on the VT in a macro and let Cognos call the macro. That hopefully wil return a set of records Cognos can display. Even if that fails, I am far ahead of where I was yesterday.
Thank you once more for the suggestion.

dnoeth 4628 posts Joined 11/04
11 Feb 2015

You need to return a single answer set to Cognos?
Why don't you do it directly from the SP using DYNAMIC RESULT SETS, afaik Cognos supports one result set returned by a SP (I don't know if it can handle multiple Selects in a macro):


You must sign in to leave a comment.