Implementing SQL into RE7 NXT
I recently started at my school's alumni foundation and they've asked me to find a way to link SQL with RE7 in order to execute more precise queries.
I'm also unsure of how the database system of RE7 works and where the SQL server is located (how to access it), in order to connect to it with Microsoft SQL management.
I am relatively new to RE7 NXT and don't know exactly how to proceed, would anyone be able to help me out or give me a path to follow?
Thanks!
Comments
-
If you have NXT then your database is hosted by Blackbaud and you will not have direct SQL access to the data.4
-
You can request a backup of your database once a month. If your IT department will allow it, and drop it onto a sql server environment, you can use the MS management studio (or other methods) to write sql against the data. The issues are ... data is not current obviously, you have to put in a request each month. But, for a lot of informational purposes, it suffices.2
-
Hi Nawras,
Your other option is to use Sky API to pull or push data from NXT. If SQL Server is your preference, you can build your API scripts to get the data you want and then use SSIS or another method to ETL the data in your SQL Server DB. Then you can run any program on top of that. T-SQL to just get the data out, SSRS or a visualiztion tool like Qlik or Tableau.
Good luck,
David4 -
Hello David,
Do you think you could provide a link to a guide on how to proceed with the method you've given me?
I'm relatively new to RE7 and been asked to try to find a way to implement SQL. I should mention, however, that our main database is with RE7 and is hosted by Blackbaud. We do have access to the RE7 NXT platform, but we have yet to create anything on it. If there is a way to query through the database we have hosted by Blackbaud, it would be better, but I don't believe that is possible.
I also believe that SkyAPI is only available for RE7-NXT, but we are planning to build a database onto the NXT platform.
So, if the SkyAPI method can allow me to push and pull data from the database freely and query SQL commands on it, I'm all for it, however a guide on how to proceed would be very helpful!
0 -
You can not connect SQL server to the database in an RE NXT environment. You won't be able to access the database using SQL Server Management Studio.
If you just need the database to support local/archival reporting you can request to have the latest backup copy sent to you up to once a month (or more for an additional fee)
If you need to access live information in the database on-demand then your option is to use the SKY API
For example, if you just wanted to retrieve a list of your consituents you can use this call:
https://developer.sky.blackbaud.com/docs/services/56b76470069a0509c8f1c5b3/operations/SearchConstituent
Now even though it's a bit more work to make API calls instead of direct SQL queries, the SKY API will allow you not only read but write/update access to your db.
And there's also the hybrid approach that people who are used to working with SQL implement try - using the SKY API to download various data sets from RE NXT and then upload it to a custom database schema in their local environment to use for their local apps/reporting. Sort of a local cache of commonly reported on data.3 -
@Kevin Hardy
hi there,I know this is a very old post, but I'm new to setting up a SQL Server database with management studio (I've been using SSMS to query and write reports for 5 years though) and I'm wondering if this hybrid model is still the best method to create a reporting back up?
I'm interested in building this API tool for specific data updates as well, but wondering if there have been any changes to how we can use SSMS for RE7 and RENXT?0 -
@Theresa Markham I'm still a little torn on this, but yes - having something such as a scheduled task that will make API calls to grab all the data you need and store it in a local SQL Server database for various reporting queries will still work, depending on your needs.
The main drawback here is that not everything is available through the SKY API. For example, if you needed to do reporting on Membership details (levels, history, upgrades/downgrades, etc.), you'll be out of luck. Something like that you'd need to request and have full database backup delivered (free - once a month, $$ - if you need a higher frequency) if you're in the Blackbaud hosted environment.
Also there are some datasets that are just not available through the any means at all (not the SKY API or stored in the Database we can download) such as attachments stored through the WebView or NXT E-mail statistics.
It just depends on your needs. If they're simple, one-off live API calls might just work too!
0 -
@Kevin Hardy Thank you so much for the reply.
I think I might be stuck with SKY API calls for now, I think I'm getting the impression that my IT dept doesn't want to host a backup database. Maybe I just need to explain the situation more clearly.
The database is so full of expired data! I can't imagine doing updates without SQL. Guess I'm going to get very good at other methods.0 -
I'm most interested in your comment about “executing more precise queries”. I've found queries to be pretty precise when you know how to run them. When they “don't work” it is usually because people try to use the output as their report, the query isn't constructed properly, or the underlying data is a mess.
It just seems like (potentially) a decent amount of effort to set this up and it still may not really address the problem.
Now that I've answered, I see the original post was from September 2019. That doesn't change my response, which I'll leave here, but I recognize that I may not get an answer which is fine.
Karen
0 -
@Karen Diener hi there,
here's an example from today,I want to pull a list of the gifts I've entered today to ensure they are entered accurately.
I changed a number of fields, and I'd like to see them, if updates were mades.
I'm not formatting a report or anything complicated, I just want to see the gifts I've entered, and if there is an email present and it's primary, I'd like it to show. we have email type of e-mail, and e-mail-Work.my query is Gift Date added equals 1/6/2022
and (phone type blank
OR (phone is primary equals yes
and (phone type equals e-mail
or phone type equals e-mail-Work)
I need to add two more parens, but the query editor only allows a max of one per line. I'd be done with the entire project by now if I had sql server management studio connected to the database.
Another situation I encounter often, I am asked to pull lybunt and sybunt data quarterly so we can target our renewal efforts strategically, and it would be nice to use that information for marketing segments without an import selection. but It's so extra difficult in the query editor/information library/canned reporting that Blackbaud provides, especially if I want to run LYBUNTS for donors who gave to one area but not another (whether it's a unit, or a fund, or a campaign.) I just moved from a university that used Blackbaud CRM to an org that uses RE7, but I'm still hitting the same roadblocks.I'm a bit limited by my dependence on SQL, I admit it. If you have another way to get to this information elegantly, I'd be thrilled to hear it.
for today, I'm left with pulling several different queries to check the data I entered in separate data sets. I find this… inefficient.0 -
@Theresa Markham I know queries can be frustrating because it is really tempting - and more efficient - to use them as auditing tools. In the first example you described, you could always use a Gift Export to review the email information.
For the second, it is hard to know for sure without understanding your full process, but I've always used the segmentation feature of QuickLetters to help me with mailings for renewals that fall into those categories.
I agree that not having the ability to nest parentheses in query is very annoying! I've also often wished I could add comments to explain why some criteria is included, since the Description field doesn't provide a lot of space.
Personally, I don't think what you're looking to do requires SQL since other tools exist in RE that would help you. But I appreciate your response so I can learn from it!
Karen
0 -
I come from an SQL background so I'm a bit biased in the ways I prefer to pull my data (but i would recommend SQL as a last resort) but given what you've described, that you want to pull a list of the gifts that you enteredtoday, if you're in the Blackbaud hosting environment SQL might not be an option.
They only allow you 1 backup copy of the database once per month (unless you work out an extra agreement $$$) so although this might work for you for a db pull today, if you get this same question in a week you're going to have to wait a full month!
The other option would be what I mentioned before, using the SKY API to download the data sets you need real time (ok… maybe up to 30 minutes for some endpoints) , storing them in a local database and then performing your queries there.
If your IT department doesn't want to host a SQL Server installation, you can always install a free version of SQL Server Express on your Windows workstation which should give you everything needs. (your local IT Policy permitting, of course!)
0 -
@Kevin Hardy @Karen Diener I am currently leaning toward this second option, SKY API and a local database with SQL Server express.
The thing is, I'm cleaning up a database that has been under-utilized for five years and making it functional for a very small office. I'm coming for a large university where these systems were already in place, and it still took me quite some time to get at the data I needed! So, I do need to do some auditing here, and I'm working with a short timeframe and the bare necessities in terms of available tools. It's just me setting this up as quickly and efficiently as I can.
Eventually I DO want to get the team set up on using on built-in features, I want to enable them to get their data without me, because I'm only here temporarily! But I need to look at this data before I can clean it up.They've never used QuickLetters, but aside from that, I'd like to give them some historical trend information and do some prospect analysis before I go, and I just hate the limitations of the Blackbaud query tool for anything but a quick data grab or basic segmentation tool. We used query to build marketing efforts in Blackbaud CRM at my last place, which I believe would be similar to using Quickletters for segmentation and it was a nightmare of datachecking to ensure we had what we needed in each segment. We never felt confident that people weren't getting left out, or included who shouldn't be. We constantly had to revise queries, or rerun them because one component or another had failed to execute properly. In my position before that, I programmed all mailing lists as ad-hocs in SQL from Ellucian Advanced, we never used the database to track our mailings.
So without getting too deep into the weeds about why I'm doing what I'm doing, SQL is the fastest option for me to dig into what information and what fields are actually available for us to use, without everyone having to learn TOO many extra new things.0 -
@Kevin Hardy as for example one below, I ended up having to pull multiple queries to see the data and check it against the original lists I pulled. It wasn't very efficient.
I'll be looking for the Gift export Karen mentioned to see what it contains.
That is another problem, this is a new org for me, and I want to learn as much as I can as fast as I can, but Blackbaud doesn't offer some of the classes on demand, so I'm having to wait to do trainings that would teach me about the canned reporting. Some things you just learn with time, but I don't have as much of that as I would like.0 -
@Kevin Hardy @Karen Diener Just one more thing, I appreciate these replies more than I can say. Thank you for answering. You've given me some good ideas for options. I would love any other advice you might have.
0
Categories
- All Categories
- Shannon parent
- shannon 2
- shannon 1
- 21 Advocacy DC Users Group
- 14 BBCRM PAG Discussions
- 89 High Education Program Advisory Group (HE PAG)
- 28 Luminate CRM DC Users Group
- 8 DC Luminate CRM Users Group
- Luminate PAG
- 5.9K Blackbaud Altru®
- 58 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 409 bbcon®
- 2.1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- donorCentrics®
- 1.1K Blackbaud eTapestry®
- 2.8K Blackbaud Financial Edge NXT®
- 1.1K Blackbaud Grantmaking™
- 527 Education Management Solutions for Higher Education
- 1 JustGiving® from Blackbaud®
- 4.6K Education Management Solutions for K-12 Schools
- Blackbaud Luminate Online & Blackbaud TeamRaiser
- 16.4K Blackbaud Raiser's Edge NXT®
- 4.1K SKY Developer
- 547 ResearchPoint™
- 151 Blackbaud Tuition Management™
- 61 everydayhero
- 3 Campaign Ideas
- 58 General Discussion
- 115 Blackbaud ID
- 87 K-12 Blackbaud ID
- 6 Admin Console
- 949 Organizational Best Practices
- 353 The Tap (Just for Fun)
- 235 Blackbaud Community Feedback Forum
- 55 Admissions Event Management EAP
- 18 MobilePay Terminal + BBID Canada EAP
- 36 EAP for New Email Campaigns Experience in Blackbaud Luminate Online®
- 109 EAP for 360 Student Profile in Blackbaud Student Information System
- 41 EAP for Assessment Builder in Blackbaud Learning Management System™
- 9 Technical Preview for SKY API for Blackbaud CRM™ and Blackbaud Altru®
- 55 Community Advisory Group
- 46 Blackbaud Community Ideas
- 26 Blackbaud Community Challenges
- 7 Security Testing Forum
- 3 Blackbaud Staff Discussions
- 1 Blackbaud Partners Discussions
- 1 Blackbaud Giving Search™
- 35 EAP Student Assignment Details and Assignment Center
- 39 EAP Core - Roles and Tasks
- 59 Blackbaud Community All-Stars Discussions
- 20 Blackbaud Raiser's Edge NXT® Online Giving EAP
- Diocesan Blackbaud Raiser’s Edge NXT® User’s Group
- 2 Blackbaud Consultant’s Community
- 43 End of Term Grade Entry EAP
- 92 EAP for Query in Blackbaud Raiser's Edge NXT®
- 38 Standard Reports for Blackbaud Raiser's Edge NXT® EAP
- 12 Payments Assistant for Blackbaud Financial Edge NXT® EAP
- 6 Ask an All Star (Austen Brown)
- 8 Ask an All-Star Alex Wong (Blackbaud Raiser's Edge NXT®)
- 1 Ask an All-Star Alex Wong (Blackbaud Financial Edge NXT®)
- 6 Ask an All-Star (Christine Robertson)
- 21 Ask an Expert (Anthony Gallo)
- Blackbaud Francophone Group
- 22 Ask an Expert (David Springer)
- 4 Raiser's Edge NXT PowerUp Challenge #1 (Query)
- 6 Ask an All-Star Sunshine Reinken Watson and Carlene Johnson
- 4 Raiser's Edge NXT PowerUp Challenge: Events
- 14 Ask an All-Star (Elizabeth Johnson)
- 7 Ask an Expert (Stephen Churchill)
- 2025 ARCHIVED FORUM POSTS
- 322 ARCHIVED | Financial Edge® Tips and Tricks
- 164 ARCHIVED | Raiser's Edge® Blog
- 300 ARCHIVED | Raiser's Edge® Blog
- 441 ARCHIVED | Blackbaud Altru® Tips and Tricks
- 66 ARCHIVED | Blackbaud NetCommunity™ Blog
- 211 ARCHIVED | Blackbaud Target Analytics® Tips and Tricks
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- Luminate CRM DC Users Group
- 225 ARCHIVED | Blackbaud eTapestry® Tips and Tricks
- 1 Blackbaud eTapestry® Know How Blog
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)
- 1 Blackbaud K-12 Education Solutions™ Blog
- 280 ARCHIVED | Mixed Community Announcements
- 3 ARCHIVED | Blackbaud Corporations™ & Blackbaud Foundations™ Hosting Status
- 1 npEngage
- 24 ARCHIVED | K-12 Announcements
- 15 ARCHIVED | FIMS Host*Net Hosting Status
- 23 ARCHIVED | Blackbaud Outcomes & Online Applications (IGAM) Hosting Status
- 22 ARCHIVED | Blackbaud DonorCentral Hosting Status
- 14 ARCHIVED | Blackbaud Grantmaking™ UK Hosting Status
- 117 ARCHIVED | Blackbaud CRM™ and Blackbaud Internet Solutions™ Announcements
- 50 Blackbaud NetCommunity™ Blog
- 169 ARCHIVED | Blackbaud Grantmaking™ Tips and Tricks
- Advocacy DC Users Group
- 718 Community News
- Blackbaud Altru® Hosting Status
- 104 ARCHIVED | Member Spotlight
- 145 ARCHIVED | Hosting Blog
- 149 JustGiving® from Blackbaud® Blog
- 97 ARCHIVED | bbcon® Blogs
- 19 ARCHIVED | Blackbaud Luminate CRM™ Announcements
- 161 Luminate Advocacy News
- 187 Organizational Best Practices Blog
- 67 everydayhero Blog
- 52 Blackbaud SKY® Reporting Announcements
- 17 ARCHIVED | Blackbaud SKY® Reporting for K-12 Announcements
- 3 Luminate Online Product Advisory Group (LO PAG)
- 81 ARCHIVED | JustGiving® from Blackbaud® Tips and Tricks
- 1 ARCHIVED | K-12 Conference Blog
- Blackbaud Church Management™ Announcements
- ARCHIVED | Blackbaud Award Management™ and Blackbaud Stewardship Management™ Announcements
- 1 Blackbaud Peer-to-Peer Fundraising™, Powered by JustGiving® Blogs
- 39 Tips, Tricks, and Timesavers!
- 56 Blackbaud Church Management™ Resources
- 154 Blackbaud Church Management™ Announcements
- 1 ARCHIVED | Blackbaud Church Management™ Tips and Tricks
- 11 ARCHIVED | Blackbaud Higher Education Solutions™ Announcements
- 7 ARCHIVED | Blackbaud Guided Fundraising™ Blog
- 2 Blackbaud Fundraiser Performance Management™ Blog
- 9 Foundations Events and Content
- 14 ARCHIVED | Blog Posts
- 2 ARCHIVED | Blackbaud FIMS™ Announcement and Tips
- 59 Blackbaud Partner Announcements
- 10 ARCHIVED | Blackbaud Impact Edge™ EAP Blogs
- 1 Community Help Blogs
- Diocesan Blackbaud Raiser’s Edge NXT® Users' Group
- Blackbaud Consultant’s Community
- Blackbaud Francophone Group
- 1 BLOG ARCHIVE CATEGORY
- Blackbaud Community™ Discussions
- 8.3K Blackbaud Luminate Online® & Blackbaud TeamRaiser® Discussions
- 5.7K Jobs Board