| Output based on Groups [message #675262] |
Tue, 31 May 2011 00:18  |
 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  |
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 |
|
|
Powered by
FUDForum. Page generated in 0.03506 seconds
] 
Search
Help
Register
Login
Home
