Power BI - Reporting on Opportunities and Opportunity Custom Fields

Sonia Duckett

Hi,

Can anyone share with me the best way extract data from RE so that I am able to report effectively on opportunities and their custom fields, preferably in an automated way? The Power BI Connector doesn't have opportunity custom fields available (that I can see). You can't output the opportunity ID from a query in database view, and exports will output information for opportunities into separate columns (which is not the format I want the data in), which means that I can't use Queue to automatically export the information. Currently I am thinking that manually creating an import file to export opportunity and opportunity custom fields is the main option available to me that will give me the information I need, although it won't be automated.

If anyone has experience of reporting on opportunities and their custom fields in Power BI, and is able to share how they do this, any advice would be greatly appreciated.

Alex Wong

@Sonia Duckett
unfortunately SKY API opportunity endpoint to list all custom fields for all opportunity doesn't exist.

One automated option is to use Query API and download proposal attribute (custom fields). You can either use saved query or ad-hoc query to get it.

Dan Snyder

@Sonia Duckett I agree with what @Alex Wong said and whether you use Query API or not, we export opportunity data from the query output (in database view) and use that for our reporting. As part of our transformations we make a custom unique id from the proposal/opportunity name and constituent id. More of a workaround, but an option that has worked for us.

I should also mention this works for us because we have naming conventions around proposal/opportunity name to make sure no two have the same name. It requires a quick data audit to make sure those names are right but has not been too bad to manage for us.

Alex Wong

@Sonia Duckett
Thanks Dan, I just remember I responded to another post about Proposal (Opportunity) issue in Query: there is no field for export that is the Proposal Import ID.

Proposal Import ID (if not manully set by you if/when you import proposal in database view) is 00001-589-0000000001 where 0000000001 is 10-dights zero padded opportunity system record id. So if you have an opportunity record where sytem record id is 378, the Proposal Import ID would be 00001-589-0000000378. This allows for you to match automatically the opportunity record returned by opportunity list api, and Query API export of proposal attribute. However, b/c in Query (which is same as Query API available fields) doesn't give you output field for Proposal Import ID, you cannot automatically match them together.

there are 2 ways to work around this:

1. get RE:Queue, which is a “automated” scheduled way to export from Admin > Import > Create import file. In the Admin > Import >Create import file method, you can get Proposal Import ID. Once you got a schedule export of the attribute data to FTP location in BB, you can automate getting the CSV file from FTP using SFTP connector in PA for automation
   1. However, be aware that RE:Queue is not completely stable and fail occasionally
   2. RE:Queue is a paid add-on to your BB contract
2. Put the Opportunity System ID into a field that is exportable from Query API
   1. The only “real” option is Opportunity (Proposal) Name, as all other fields are either a lookup table, date, or amount
   2. if you want to go with this route, then it is about prepending or appending the opportunity system id to the Name field, using a special character to allow for the system record id to be picked up
      for example: Opportunity Name = 378|Soliciting for annual gift
   3. You will need 2 different automations
      1. Automation 1 that automatically update opportunity name that doesn't have the system record id: schedule this to run before the 2nd automation
      2. Automation 2 that use query api to export proposal attributes/custom fields, make sure to export the proposal name that you can use to programmatically “split by | character" and take the first() element: first(split('proposalname', ‘|’))