Displaying columns dynamically based on SQL query results

[gyani86]

There is a process where a Document / invoice submitted goes through different queues in a workflow. So I have to display all the queues through which a document has gone through dynamically. There are many queues and all documents do not necessarily go through all the queues. Also, there are chances that a doc is returned to a previous queue for some review and that also has to be captured.

For e.g. say the docs move through the queues as below -

Doc 1  Q1  Q2  Q3  Q4

Doc 2   Q1  Q2  Q4  Q5

Doc 3   Q1  Q3  Q5  Q6

Doc 4   Q1  Q2  Q1  Q2  Q3  Q4

So the report output should be as below -

Q1     Q2     Q1     Q2     Q3     Q4     Q5     Q6    Doc 1      x       x                          x       x                Doc 2      x       x                                  x       x        Doc 3      x                                  x                x      x    Doc 4      x       x       x       x        x

So here, all the column headers are dynamic and I need to show the exit date from each queue which I can manage. Is there a way to dynamically populate the columns based on the SQL query result? The SQL query result would be as below -

DocID Q_Name Q_Seq Doc1 Q1  1 Doc1 Q2  2 Doc1 Q3  3 Doc1 Q4  4 Doc2 Q1  1 Doc2 Q2  2 Doc2 Q4  3 Doc2 Q5  4

Hope I have made my question clear. Please let me know if there is a way out. Thanks.

[Francis aka khayman]

create a calculated column with value = 1

DocID     Q_Name    Q_Seq   Val
Doc1         Q1             1         1
Doc1         Q2             2         1
Doc1         Q3             3         1
Doc1         Q4             4         1


This can be converted to a crosstab
              Q_name
          Q1         Q2         Q3      Q4
Doc 1   1           1           1         1

[gyani86]

Hi khayman, thanks for the suggestion but I need to display dates when the doc exited each queue and hence I cannot use the cross tab report.

[Francis aka khayman]

? sorry. can you show how your report should look like including the exit dates you need to display?

[dougp]

Doc 4 went through queue 1, then queue 2, then back to queue 1.

Also you more than simply the sequence, you want to show dates.

Maybe a Gannt chart would be more helpful for the reader.  A list of date strings in a crosstab or list can be difficult to relate to each other.


Doc3  Queue1  xxxx
      Queue3      xxxx
      Queue5          xxxxxxxx
      Queue6                  xx
Doc4  Queue1         xxxxxxxx   xxxx
      Queue2                 xxx    xx
      Queue3                          x
      Queue4                           xxx


In any case, you have two challenges: 

• Craft the query to return the data in a format that is useful to feed the presentation.  This may be difficult within Cognos.  You may need to write raw SQL.
• Design the presentation.  There is a Gannt chart in the set of Legacy visualizations.  It's not the most straightforward to use.  You can also use JavaScript to embed a d3 vis of your own design.

When you get the presentation you want, please post a picture of the output and any relevant details to the solution back to this forum thread.  I'd love to see it.

[Francis aka khayman]

i still don't see any dates

[dougp]

I tried to display a graphical Gannt chart as text.  The timeline would be across the top.

My bad:  I didn't notice this is the Cognos 8 forum.  I doubt there is a Gannt vis for that version.