Creating A Report based on a View Table

[Rheine Adithia]

Hello, 

Today im having a difficulties on making a report with idempiere to display a certain set of data from a postgresql view.
I have made a view that function is to display the full list of role access permission. so in this view, every window, process, workflow, and other component that need access is displayed.
If i use some select SQL in postgres or DBeaver, the data doesn't have any error, and it display the result set as i wanted it to be,

but after i deployed it as a Table, Report View, Report & Process and Menu in idempiere, it kept giving me this error :
** org.postgresql.util.PSQLException: ERROR: syntax error at or near "type" Position: 152

i have checked the query for the view over and over again and didn't found any error in the writing or the logic.
so can anyone help me with this?
do i made the mistake on making the report view in idempiere??
This is the SQL for the View table :

 

-- View: adempiere.rv_role_access
-- DROP VIEW adempiere.rv_role_access;
CREATE OR REPLACE VIEW adempiere.rv_role_access AS
 SELECT main.row_id AS ult_viewrole_id,
    main.ad_role_id,
    main.uu AS rv_roles_access_uu,
    main.ad_client_id,
    main.ad_org_id,
    main.isactive,
    main.created,
    main.createdby,
    main.updated,
    main.updatedby,
    main.ismasterrole,
    main."Name",
    main."Role",
    main."Read Write" AS isreadwrite,
    main."Reference List",
    main.rowtype
   FROM (( SELECT pa.ad_client_id,
            pa.ad_org_id,
            pa.ad_process_id AS row_id,
            pa.isactive,
            pa.created,
            pa.createdby,
            pa.updated,
            pa.updatedby,
            pa.ad_role_id,
            ar.ismasterrole,
            (pa.ad_process_id || '_'::text) || p.name::text AS "Name",
            ar.name AS "Role",
            pa.isreadwrite AS "Read Write",
            NULL::text AS "Reference List",
            'Process'::text AS rowtype,
            pa.ad_process_access_uu AS uu
           FROM adempiere.ad_process_access pa
             LEFT JOIN adempiere.ad_process p ON pa.ad_process_id = p.ad_process_id
             LEFT JOIN adempiere.ad_role ar ON pa.ad_role_id = ar.ad_role_id
          ORDER BY pa.ad_process_id)
        UNION ALL
        ( SELECT iwa.ad_client_id,
            iwa.ad_org_id,
            iwa.ad_infowindow_id AS row_id,
            iwa.isactive,
            iwa.created,
            iwa.createdby,
            iwa.updated,
            iwa.updatedby,
            iwa.ad_role_id,
            ar.ismasterrole,
            iw.name,
            ar.name AS "Role",
            'Y'::bpchar AS isreadwrite,
            NULL::text AS "Reference List",
            'Info Window'::text AS rowtype,
            iwa.ad_infowindow_access_uu AS uu
           FROM adempiere.ad_infowindow_access iwa
             LEFT JOIN adempiere.ad_infowindow iw ON iwa.ad_infowindow_id = iw.ad_infowindow_id
             LEFT JOIN adempiere.ad_role ar ON ar.ad_role_id = iwa.ad_role_id
          ORDER BY iw.ad_infowindow_id)
        UNION ALL
        ( SELECT wa.ad_client_id,
            wa.ad_org_id,
            wa.ad_window_id AS row_id,
            wa.isactive,
            wa.created,
            wa.createdby,
            wa.updated,
            wa.updatedby,
            wa.ad_role_id,
            ar.ismasterrole,
            (wa.ad_window_id || '_'::text) || w.name::text AS "Name",
            ar.name AS "Role",
            wa.isreadwrite,
            NULL::text AS "Reference List",
            'Window'::text AS rowtype,
            wa.ad_window_access_uu AS uu
           FROM adempiere.ad_window_access wa
             LEFT JOIN adempiere.ad_window w ON w.ad_window_id = wa.ad_window_id
             LEFT JOIN adempiere.ad_role ar ON ar.ad_role_id = wa.ad_role_id
          ORDER BY wa.ad_window_id)
        UNION ALL
        ( SELECT fa.ad_client_id,
            fa.ad_org_id,
            fa.ad_form_id AS row_id,
            fa.isactive,
            fa.created,
            fa.createdby,
            fa.updated,
            fa.updatedby,
            fa.ad_role_id,
            ar.ismasterrole,
            f.name,
            ar.name AS "Role",
            fa.isreadwrite,
            NULL::text AS "Reference List",
            'Special Form'::text AS rowtype,
            fa.ad_form_access_uu AS uu
           FROM adempiere.ad_form_access fa
             LEFT JOIN adempiere.ad_form f ON f.ad_form_id = fa.ad_form_id
             LEFT JOIN adempiere.ad_role ar ON ar.ad_role_id = fa.ad_role_id
          ORDER BY fa.ad_form_id)
        UNION ALL
        ( SELECT wa.ad_client_id,
            wa.ad_org_id,
            wa.ad_workflow_id AS row_id,
            wa.isactive,
            wa.created,
            wa.createdby,
            wa.updated,
            wa.updatedby,
            wa.ad_role_id,
            ar.ismasterrole,
            w.name,
            ar.name AS "Role",
            wa.isreadwrite,
            NULL::text AS "Reference List",
            'Workflow'::text AS rowtype,
            wa.ad_workflow_access_uu AS uu
           FROM adempiere.ad_workflow_access wa
             LEFT JOIN adempiere.ad_workflow w ON w.ad_workflow_id = wa.ad_workflow_id
             LEFT JOIN adempiere.ad_role ar ON ar.ad_role_id = wa.ad_role_id
          ORDER BY wa.ad_workflow_id)
        UNION ALL
        ( SELECT ta.ad_client_id,
            ta.ad_org_id,
            ta.ad_task_id AS row_id,
            ta.isactive,
            ta.created,
            ta.createdby,
            ta.updated,
            ta.updatedby,
            ta.ad_role_id,
            ar.ismasterrole,
            t.name,
            ar.name AS "Role",
            ta.isreadwrite,
            NULL::text AS "Reference List",
            'OS Task'::text AS rowtype,
            ta.ad_task_access_uu AS uu
           FROM adempiere.ad_task_access ta
             LEFT JOIN adempiere.ad_task t ON t.ad_task_id = ta.ad_task_id
             LEFT JOIN adempiere.ad_role ar ON ar.ad_role_id = ta.ad_role_id
          ORDER BY ta.ad_task_id)
        UNION ALL
        ( SELECT daa.ad_client_id,
            daa.ad_org_id,
            daa.c_doctype_id AS row_id,
            daa.isactive,
            daa.created,
            daa.createdby,
            daa.updated,
            daa.updatedby,
            daa.ad_role_id,
            ar.ismasterrole,
            dt.name,
            ar.name AS "Role",
            'Y'::bpchar AS isreadwrite,
            rl.name AS "Reference List",
            'Document Access'::text AS rowtype,
            daa.ad_document_action_access_uu AS uu
           FROM adempiere.ad_document_action_access daa
             LEFT JOIN adempiere.c_doctype dt ON dt.c_doctype_id = daa.c_doctype_id
             LEFT JOIN adempiere.ad_role ar ON ar.ad_role_id = daa.ad_role_id
             LEFT JOIN adempiere.ad_ref_list rl ON rl.ad_ref_list_id = daa.ad_ref_list_id
          ORDER BY daa.c_doctype_id)) main
  ORDER BY main.rowtype;
ALTER TABLE adempiere.rv_role_access
    OWNER TO adempiere;

for the setting of process, table, and report view, i will attach it as a image. 

for the column of the table, i use the idempiere process : "Create Columns from DB"
should i use the "Copy Components from View" instead??

Thank you for your attention, and please respond :)

Best Regards, 

Rheine Adithia Kurniawan

[Rheine Adithia]

Okay i have found the answer to my own Thread,

So the problem is i use Case Sensitive alias for the column header name,
for anyone who got the same problem as me,

don't use capital letter and spacing in a column header
just use lowercase and underscore ( _ )

now my problem is how to use the parameter that is used in the Report & Process..

Like in jasper you can use $RECORD_ID = AD_Window_ID
How do i do this in the idempiere report...
i want to use AD_Window_ID that was passed from the process to my SQL Query
it should look like this : 
$AD_Window_ID = main.Row_ID

Best Regards, 
Rheine Adithia

[Anozi Mada]

You just need to set the parameter db column name with the same name as your view column and iDempiere will filter it automatically.
In this case you might want to separate the columns instead of using generalized "row_id"

Regards,
Anozi Mada