Author Topic: Displaying columns dynamically based on SQL query results  (Read 256 times)

Offline gyani86

  • Associate
  • **
  • Join Date: Sep 2018
  • Posts: 2
  • Forum Citizenship: +0/-0
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.

Offline khayman

  • Statesman
  • ******
  • Join Date: Jun 2009
  • Posts: 416
  • Forum Citizenship: +25/-3
Re: Displaying columns dynamically based on SQL query results
« Reply #1 on: 14 Sep 2018 08:22:18 am »
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


Offline gyani86

  • Associate
  • **
  • Join Date: Sep 2018
  • Posts: 2
  • Forum Citizenship: +0/-0
Re: Displaying columns dynamically based on SQL query results
« Reply #2 on: 14 Sep 2018 10:09:54 am »
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.

Offline khayman

  • Statesman
  • ******
  • Join Date: Jun 2009
  • Posts: 416
  • Forum Citizenship: +25/-3
Re: Displaying columns dynamically based on SQL query results
« Reply #3 on: 17 Sep 2018 09:56:46 am »
? sorry. can you show how your report should look like including the exit dates you need to display?

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 305
  • Forum Citizenship: +16/-1
Re: Displaying columns dynamically based on SQL query results
« Reply #4 on: 17 Sep 2018 01:36:35 pm »
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.

Offline khayman

  • Statesman
  • ******
  • Join Date: Jun 2009
  • Posts: 416
  • Forum Citizenship: +25/-3
Re: Displaying columns dynamically based on SQL query results
« Reply #5 on: 18 Sep 2018 08:58:56 am »
i still don't see any dates

Offline dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 305
  • Forum Citizenship: +16/-1
Re: Displaying columns dynamically based on SQL query results
« Reply #6 on: 18 Sep 2018 01:32:37 pm »
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.

 


       
Twittear