The requirement was to connect NetSuite searches with Power BI in order to create the reports. We can load tables from NetSuite to Power BI based on the permissions given in the custom role. The searches cannot be load this way. As solution to this we can use SQL queries to recreate the same search results in Power BI.
Here we are recreating the saved search results using SQL query in Power BI. A customer search is using here.
Criteria
Results
Corresponding SQL Query
select m.name as Banners,s.name as status,s.entitytype as Stage,q.name as MarketingNewsletter,c.isinactive,c.isperson,c.companyname,c.id as InternalID,h.Name as Channels,z.name as CCstate,c.custentity_hog_rep_state as Repstate,c.custentity_hog_old_cust_id as LegacySystemsId,c.cseg_hog_cc_state as CostCentreState,v.name as MarketSegment,c.custentity_hog_cust_iden_name as CustIdentifierName,c.custentity_hog_cust_identifier as CustIdentifier,c.custentity_hog_store_id as StoreId,c.custentity_hog_store_id as GroupName,c.custentity_hog_store_id as MarketingFlag,c.datecreated,c.entityid,c.fax,c.email,c.phone,p.name as pricelevel,t.name as terms,e.entityid as salesrep,g.name as Category,r.entityid as Parent,b.addressee as ShippingAddressee,b.addr1 as ShippingAddress1,b.addr2 as ShippingAddress2,b.addr3 as ShippingAddress3,b.city as City,b.state as State,b.zip as Zip
from
customer as c left join customercategory as g on c.category = g.id left join employee as e on c.salesrep = e.id left join pricelevel as p on c.pricelevel = p.id left join term as t on c.terms = t.id left join customrecord_cseg_hogchannel as h on c.cseg_hogchannel = h.id left join customer as r on c.parent = r.id left join customlist_jj_marketing_flag_new as m on c.custentity_jj_crm_form = m.id left join customlist_hog_mktng_newsletter as q on c.custentity_hog_mktng_newsletter = q.id left join entitystatus as s on c.entitystatus = s.key left join customrecord_csegcsegcseg_hogmkt as v on c.csegcsegcseg_hogmkt = v.id left join customrecord_cseg_hog_cc_state as z on c.cseg_hog_cc_state = z.id left join customeraddressbookentityaddress
as b on c.defaultshippingaddress = b.nkeyThe table view in Power BI after querying.
