Requirement
Enhance a simple seeded report by adding additional attributes is very common.
Solution1
We can enhance the seeded data set by modifying SQL statement using new attributes.
Example
DataSet SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
FROM
ap_invoices_all
Need to display Business Unit Name.
Modified Data Set SQL
SELECT
invoice_num
, invoice_id
, invoice_date
,fbu.bu_name
FROM
ap_invoices_all apa
,fun_all_business_units_v fbu
WHERE
apa.org_id = fbu.bu_id
OR
SELECT
invoice_num
, invoice_id
, invoice_date
,(SELECT bu_name from fun_all_business_units_v where bu_id = apa.org_id) bu_name
FROM
ap_invoices_all apa
Solution2
We can add a new data set for requested attributes and common attribute from existing data set. Then link the new data set using common attribute.
DataSet1 SQL -
SELECT
invoice_num
, invoice_id
, invoice_date
,org_id
FROM
ap_invoices_all
Need to display Business Unit Name.
DataSet2 SQL
SELECT
bu_id
,bu_name
FROM
fun_all_business_units_v
Linke DataSet 1 with DataSet2 using attribute Org_id (DS1) and Bu_id (DS2).
No comments:
Post a Comment