Fundraiser/Canvasser Endpoint for NXT in Power BI

@Phil Calderwood
It is possible, but it takes a few steps, and if you're new to BI it may involve doing things you haven't done before.

There's a fundraisers column in the Gift endpoint. But you'll see that it only contains “null” or “List” values. That's because a gift can have multiple fundraisers. So you will first need to expand the Fundraisers column so that you can see the fundraiser's constituent IDs.

It's important to note here that expanding this column means that if a gift had two fundraisers, it will now appear twice. If it had five fundraisers, five times. Etc. Be sure you're taking this into account in your reports.

If you want the Fundraiser's names (which you probably do), you're going to have to bring the Constituent endpoint into your data model if it isn't already there so that you can get your fundraiser's names using their constituent ids.

This is admittedly a lot of hoops to jump through just to see fundraiser names. Depending on what you're reporting on and when/how you would like to refresh your data source, it may be easier to bring the data in a different way. You could build a query in database view that has all the data and fields you need, export it to an Excel file or a .csv and bring it in like that instead.

But if you want to use the connecter, it is possible.

@Ben Regier

Thanks for your post. I am trying to find “assigned fundraiser" in the connector so I can create KPI reports filtered on fundraiser assignment. We use different fundraiser types depending on the prospect. What table might I find that in the PowerBi connector?

Alternately, I was thinking I could create a constituent list in RENXT based on fundraiser but I'm unsure if that will solve my issue.

Thanks!

@Ben Regier
I think the OP and @Hallie Guiseppe was talking about is the Constituent Assigned Fundraiser, not gift fundraiser, which is more easy to deal with as it comes in an array from Gift List

@Hallie Guiseppe
what exactly are you trying to do, maybe can give some additional light to it. (though like Ben is saying, Power BI connector is likely not your answer, rather you will need to “gather” the info “elsehow”: RE:Queue or Power Automate to gather data then report on it in BI)

@Hallie Guiseppe
There is the Opportunity List, which has info on who is the fundraiser (ID only, so you do need Constituent List to know who the fundraiser is). If your org has it that the fundraiser profolio is managed through opportunity record (prospect records in database view terminology), then you can use PA to pull all Opportunity using Opportunity List, store it (it needs to be stored in a way that you can then pull into Power BI, so turning the JSON from API call into excel or csv). Do the same with Constituent List.

However, if you are not using Opportunity or not fully using opportunity, then it is slightly more complex, you will need to use the endpoint that Ben pointed out, but that endpoint is per constituent. So you will need to first “filter” on your fundraisers (best if your fundraisers have some tag you can filter on in a Constituent List endpoint). Then loop through each fundraiser, and call the Fundraiser assignment list (Single constituent) endpoint to get an array of all constituents that are assigned to the fundraiser (Please see below, I think this endpoint MAY not be how I think it works). Then you can start handling what you want to show in the portfolio (i.e. has the constituent given this year yet and how much, which will need you to run gift list for each constituent for current year).

A few things to consider:

• gift list on constituent id is not going to give you gift that has been soft credit to this constituent. 2 ways to handle in my opinion:
  • You have a data warehouse of all gifts, all soft credit info, and all gift fundraiser info
  • in the PA flow each run, get ALL gifts in current year, if not a LOT (less than 5000 so you don't need multiple run of Gift List). Then you can use data operation in flow to filter out only gifts that is direct credit to constituent and then another filter on all gifts soft credit to the constituent
• with the gift considered, then you can report on the portfolio also on gift that is the fundraiser's constituent but the gift did not have the fundraiser being him/her

Anyways, maybe too much info here, apology as I tend to go overboard sometime.

There is 1 problem I want to indicate here though, maybe Ben can shed some light.

The Fundraiser assignment list (Single constituent) endpoint provides info back like above, This JSON is for fundraiser 415636 (constituent sys id), I'm getting back 3 fundraiser_id which is not the ones that the fundraiser's “constituent assigned to him” rather, it is the fundraiser's “assigned fundraiser”.

If the endpoint actually provides the “opposite” info, then Hallie, you will have an even tougher times.

@Ben Regier
Thanks for confirming, then yes RE:Queue (if you want this report automated) would be the way to go, and setup queries that then turn to csv for you to process.

FYI: you can setup a flow that waits for sFTP for “new/modified” file and get the csv for processing WITHOUT RE:Queue. RE:Queue just automates the “exporting” of the query output, even without it (not sure if your org has this feature in your contract), you can still setup everything, except for the auto export part of RE:Queue. So whenever you want this workflow to “start”, just export the file from db view manually, and everything you setup in flow will kicks off. (I mention this b/c I do hear that some people “link” RE:Queue with sFTP, as if they are one and you have to have RE:Queue to do anything with sFTP csv in flow)

@Chris Li
Yes, you would have to expand the fundraisers column. After that you should have a fundraisers.constituent_id column which is the ID of the fundraiser for that opportunity.