Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Output based on Groups
Output based on Groups [message #675262] Tue, 31 May 2011 00:18 Go to next message
Gopi  is currently offline Gopi
Messages: 10
Registered: May 2011
Junior Member
Hi ,

I having issue to display report format:
current format that report is displaying :
Report grouped by: Maxgrp (EX:Admingrp)
Output displays in three lines for Admingrp
MaxGrup Read Insert Update
--------------------------------
Admingrp X


Admingrp (X at Insert) X


Admingrp (X at Update) X


I need to display output like below:
All priviliges should be one line for one Group.

MaxGrup Read Insert Update
---------------------------------
Admingrp X X X

I am using Union in the SQL statment :
var sqlText = new String();

// Add query to sqlText variable.
sqlText = " SELECT MAXGRP ,APP ,PRIV "
+ " from "
+ " (SELECT APPLICATIONAUTH.GROUPNAME AS MAXGRP,APP ,'READ' as PRIV from APPLICATIONAUTH, SITEAUTH where APPLICATIONAUTH.GROUPNAME=SITEAUTH.GROUPNAME and APPLICATIONAUTH.OPTIONNAME='READ' and APP ='LABOR' UNION "

+ " SELECT APPLICATIONAUTH.GROUPNAME AS MAXGRP,APP , 'UPDATE' as PRIV from APPLICATIONAUTH, SITEAUTH where APPLICATIONAUTH.GROUPNAME=SITEAUTH.GROUPNAME and APPLICATIONAUTH.OPTIONNAME='UPDATE' and APP ='LABOR' UNION "

+ " SELECT APPLICATIONAUTH.GROUPNAME AS MAXGRP,APP , 'INSERT' AS PRIV from APPLICATIONAUTH ,SITEAUTH where APPLICATIONAUTH.GROUPNAME=SITEAUTH.GROUPNAME and APPLICATIONAUTH.OPTIONNAME='INSERT' and APP ='LABOR' UNION "

+ " SELECT APPLICATIONAUTH.GROUPNAME AS MAXGRP,APP,'COMPLETE' as PRIV from APPLICATIONAUTH,SITEAUTH where APPLICATIONAUTH.GROUPNAME=SITEAUTH.GROUPNAME and (APPLICATIONAUTH.OPTIONNAME='COMPLETE'or APPLICATIONAUTH.OPTIONNAME='COMP') and APP ='LABOR' )"

+ " where "
+ " APP IN ('LABOR')"

Please help.
Thanks in advance

[Updated on: Tue, 31 May 2011 00:23]

Report message to a moderator

Re: Output based on Groups [message #675403 is a reply to message #675262] Tue, 31 May 2011 12:25 Go to previous message
Tomas Greif is currently offline Tomas Greif
Messages: 53
Registered: September 2010
Member
Hi,

I think there are two possible ways:
1) Use your union query, create data cube and then cross tab, where group name will be in rows, access rights (read/write/insert) in columns and aggregation function will be count
2) Re-write your query (following is an example, not final solution):

SELECT
A.GROUPNAME, A.ROLE, B.ROLE, C.ROLE
FROM
(SELECT GROUPNAME, 'READ' AS ROLE FROM ROLES_TABLE WHERE SOMETHING) A
LEFT JOIN (SELECT GROUPNAME, 'WRITE' AS ROLE FROM ROLES_TABLE WHERE SOMETHING) B ON (A.GROUPNAME = B.GROUPNAME)
LEFT JOIN (SELECT GROUPNAME, 'INSERT' AS ROLE FROM ROLES_TABLE WHERE SOMETHING) C ON (A.GROUPNAME = C.GROUPNAME)


Please note that this assumes that when user has 'write' or 'insert' role then user always has 'read' role (if this is not true you can still try full outer join)

Hope this helps.

Regards,

Tomas

[Updated on: Tue, 31 May 2011 12:57]

Report message to a moderator

Previous Topic:no bars on barchart for y values of 1 on logarithmic y axis
Next Topic:expand collapse rows and export to PDF
Goto Forum:
  


Current Time: Mon Sep 22 08:13:05 GMT 2014

Powered by FUDForum. Page generated in 0.02851 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software