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 is a search of such type with its SQL query.
This is a transaction search with search criteria
Search results
Corresponding SuiteQL
select r.id,p.name as PriceLevel,r.tranid,i.averagecost as AverageCost,a.displaynamewithhierarchy as COGSADJUSTMENT,t.ratepercent,r.Type, r.trandate,r.entity,c.entityid, c.companyname, c.custentity_hog_old_cust_id, c.custentity_hog_store_id, t.item , t.itemtype ,i.displayname,ROUND(i.averagecost,1) as EstUnitCost,ABS(ROUND(i.averagecost,1)*t.quantity) as EstExtendedCost,ABS(t.quantity) as Quantity,t.rate,t.rateamount as AmountINCLTAX,(t.quantity*t.rate) as AmountEXCLTAX,t.custcol_hog_w_sale, t.custcol_in8_go_vita_rate, t.custcol_in8_go_vita_claimback, t.costestimaterate, t.costestimate, t.custcol_hog_cogs_adjustment, f.tranid as createdfrom, r.otherrefnum,e.entityid as salesrep, h.Name as Channels,y.state as ShippingState
from
transactionLine as t left join transaction as r on t.transaction = r.id left join item as i on t.item = i.id left join customer as c on r.entity = c.id left join customrecord_cseg_hogchannel as h
on h.id = t.cseg_hogchannel left join employee as e on r.employee = e.id left join account as a on t.custcol_hog_cogs_adjustment = a.id left join pricelevel as p on t.price = p.id left join transaction as f on t.createdfrom = f.id left join transactionshippingaddressbookentityaddress as y on r.shippingaddress = y.nkey
where
(t.cseg_hogchannel
not in ('11', '13', '16', '21', '15', '14', '19', '20', '18', '17', '12') or t.cseg_hogchannel='')
and
t.assemblycomponent = 'F'
and
t.mainline = 'F'
and
t.iscogs = 'F'
and
t.taxline = 'F'
and
t.quantity != '0'
and
t.item
not in ('3939', '3028', '3937', '3030', '3963', '3982', '3969', '3966', '4035', '3983', '5020')
and
t.itemtype != 'NonInvtPart'
and
t.kitcomponent = 'F'
and
r.Type in ('CustInvc', 'CustCred')and (MONTHS_BETWEEN(SYSDATE,r.trandate) <= 1)The table view in Power BI after the querying
