grouped bar chart LiveReport

I've recently found the LiveReport bar chart useful for analyzing files added over time to a workspace, but is there a way to add a second group, i.e. grouped bar chart? That way I could graphically compare files added overtime to two different workspaces. I tried adding another column of data in the SQL, but it appears it just ignored the second group of data.

Find more posts tagged with

Comments

Colin_Schmidt

What is the code is the code in the LR?

Regards,

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, January 17, 2018 11:55 AM
To: eLink Recipient
Subject: [EXT] grouped bar chart LiveReport

grouped bar chart LiveReport

Posted bymrunyan@ctibiopharma.com (Runyan, Mark) On 01/17/2018 11:51 AM

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.

Mark_Runyan

Yes columnar text output of the SQL looks like this:

WEEKSTARTING	CTMF	WORKSPACE
13-NOV-17	0	7
20-NOV-17	0	5
27-NOV-17	0	29
04-DEC-17	0	13
11-DEC-17	0	8
18-DEC-17	0	168
25-DEC-17	0	2
01-JAN-18	122	36
08-JAN-18	253	68
15-JAN-18	0	18

The SQL output is starting day of the week, followed by number of files added in each workspace during the week interval. Just wanted to see this in a nice grouped bar chart. I'll post the SQL later (I have a meeting now).

Mark_Runyan

Here's the SQL query (Oracle, and sorry using old style outer join syntax) The LiveReport has a variable, %1, to allow the user to request a specify the number of months history, but the folders being compared are currently hard coded.

select to_char(auditdate) weekstarting, nvl(CN_CTMF,0) CTMF, nvl(CN_WS,0) WorkSpace
from
(select count(*) CN_CTMF, trunc(filemdate,'IW') weekstarting
from
(select
dataid,
v.filemdate
from
livelink.DVersData v,
(select
dataid
from
livelink.dtree
START WITH parentid = 28429302 -- CTMF
CONNECT BY PRIOR dataid = parentid ) d
where
d.dataid = v.docid
and VERTYPE is null -- removes otthumb image/jpg
)
where
filemdate > trunc(add_months(sysdate,-%1))
group by
trunc(filemdate,'IW')
order by
trunc(filemdate,'IW')
) dv,
(select count(*) CN_WS, trunc(filemdate,'IW') weekstarting
from
(select
dataid,
v.filemdate
from
livelink.DVersData v,
(select
dataid
from
livelink.dtree
START WITH parentid = 28777113 -- Project Team Workspace
CONNECT BY PRIOR dataid = parentid ) d
where
d.dataid = v.docid
and VERTYPE is null -- removes otthumb image/jpg
)
where
filemdate > trunc(add_months(sysdate,-%1))
group by
trunc(filemdate,'IW')
order by
trunc(filemdate,'IW')
) dv2,
(select -- using dauditnew just as template for each weekstarting
trunc(auditdate,'IW') AUDITDATE
from
dauditnew
where
auditdate > trunc(add_months(sysdate,-%1))
group by
trunc(auditdate,'IW')) date_templ
where
auditdate = dv.weekstarting(+)
and auditdate = dv2.weekstarting(+)
order by
auditdate