Weekly Report - Crystal Reports
At the beginning of every week, we send out a Weekly Report that shows our progress towards our Annual Fund goal.
The most important piece of this report is the Summary, which includes several metrics for our main Giving Types. It looks something like this:

We have had to make this report manually for the bulk of this year because of some much needed infrastructural changes in RE. Most, if not all, of those changes have been cleaned up, and now I am hoping to utilize are more streamlined, database-generated approach to making these every week.
I think something like this would have to be made with Crystal Reports but am admittedly stuck on how to develop this. I have never built a complete Crystal Report from scratch, in my previous jobs I either improved templates that already existed or used ones that already existed.
Can anyone help me get started?
Comments
-
HI, Ellen,
I would recommend taking the Crystal Reports blackbaud course. It's great for beginners or as a refresher.
Also, Josh has a great website: http://bekerman.com/blog/raisers-edge-query/
Filled with great information.
I start our crystal reports with an export that features all the columns needed in your report. From there, you create the CR, both in content and how it is designed.
Based on the screenshot you included, it looks like your information comes from spreadsheets pasted into the report. Is this correct?3 -
Correct! My original boss wanted us a tab for every gift and an individual tab per Giving Type, or the categories you see highlighted.
I don't think we need both. We could do one approach or the other, but the Summary page are just a bunch of sumifs/countifs of the "raw" data.0 -
I don't know enough about CR to advise you. But I don't know that you need CR to produce this report in an easier manner.
I would start from what you want the final project to look like. If it is that screenshot, I would think you pull the basic information using an export, run a macro that produces the counts and sums for you and then places those figures into the correct columns/formulas to produce your final spreadsheet.
What takes you the most time to produce this report?0 -
Thanks Mark!
Just as Mark mentioned, start with a gift export and figure out which fields you need. Hopefully your first two sections are based on constituent codes or gift constituent codes (trustee, honorary, members, corporate, etc.). If your Membership Giving and Governing Members are based on total giving then you'll need to export that summary giving info (or you could calculate it in the report, depending on the criteria, which should make the export run a bit quicker. You'll also need whichever field you are using to determine Annual Fund vs. Project Support, along with special events,venture fund, etc. (campaign? fund?). Since the report includes FY18 info make sure to include gifts in that date range in your export.
To be continued, let us know when you've got the export done.2 -
This is all super helpful, thanks guys.
How we code gifts is a little strange here, lots of historical discussions and reasons, but basically it comes down to Campaign (Odd Years are 2 Campaigns and Even Years are 1 Campaign) and a Gift Attribute, which specifics the specific Giving Type level. The Gift Attribute isn't ideal but has worked well this year and eased many tensions between our Development and Finance departments.
What I struggle with is that the information needed, to me, straddles both Const. and Gift queries.
One would think a Const. Query with an Export that includes Const. ID, Name, Gift Summary for Current FY, and Gift Summary for Previous FY would suffice, but the gifts are ultimately categorized according to the Gift Attribute, which designates Giving Type. In any given FY, many of our donors' giving fit multiple Giving Type categories (i.e. Trustees, Events, and Venture), and I don't feel confident in ensuring everything ends up in the right category.0 -
Without seeing all, I wonder if it might be time to look at how you code your gifts. In chatting with dbas from all over, I find some have databases that are designed to produce excellent financial reports for the accounting dept., at the expense of the development team. RE and NXT are fundraising programs, not accounting programs.
However, it sounds like you may need a const. export and a gift export to get all you need. I'd be glad to take a look at what you do to create your report to see if there might be an easier way.0 -
And how would you code a trustee giving to a special event? ?2
-
Could you do this with a Pivot Report within RE? You create a query with your output fields, then go to Reports>Pivot Reports> "Selected records = your query" Click Generate and then set up your columns and rows,1
-
Mark Guncheon:
And how would you code a trustee giving to a special event? ?Aw man, I was out of the office for all the fun reporting talk. I think Mark's question is the key here -- you have to figure out how your gifts are categorized and coded as it relates to your report, before you can figure out how to code the report to show them accurately.
1 -
Thanks all. How we code gifts is non-negotiable. In an ideal world, we would do a lot of things differently. In an ideal world, we wouldn't still be using RE....
But all these tips gave me some ideas to work with, thank you!0 -
Ellen,
I just had to chime in a bit here .... trying not to be "too much on the soapbox". It hurt my heart a little when I read "how we code gifts is non-negotiable". If the RE side of the house needs coding within the database to REPORT or MANAGE your data then you should have some leeway to code as needed. I'm thinking that maybe your strict coding standards are a result of having to integrate with a different department; if that is the case then I'd suggest communicating with them to find out the purpose of some of the restrictions on coding - and maybe then being able to find a compromise where all parties "get what they need" regarding information, coding and data management. If there really is no work around, and you can't use fields such as gift constituent code, then I'd recommend using a gift attribute to get where you need to be.
Stepping off the soapbox now.
Good Luck!6 -
Hi Ellen,
I didn't see you mention anything about Appeals. It might be an option for you to have full control over how a gift is coded for your reporting if the other areas are not negotiable. The other area we use allot are Fund Attributes to allow us to group our Funds into specific groupings as well. In terms of Crystal Reports, we reviewed but decided to move over to SSRS (SQL Server Reporting Services) or Power BI is an option as well. You would pull the data directly from the DB and it provides you with more flexibility with pulling the data and also presenting the data. Not sure how familiar you are with writing SQL, but it would be a skill that would be transferable to many areas within IT.
Good Luck,
David2 -
Hello
David,Thanks for the
information about creating the Weekly Report referenced in your
email. Please, let kindly share if there is another way to
manipulate a report from Raiser’s Edge using Power BI without first
generating the report from Raiser’s Edge, saving it in folder and
then importing it into PowerBI.Regards,
Mubo Sani, bCRE
| Database
AdministratorPronouns:
She/Her/HersOffice of
Institutional Advancement |
University of
Baltimore1130 North Charles
Street|
Baltimore, MD
21201Direct:
410.837.6253 |Email: msani@ubalt.eduCONFIDENTIALITY/PRIVACY
NOTICE - If you are not the intended recipient or the employee
responsible for delivering the information to the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or action taken in reliance on the contents of these
documents is strictly prohibited. If you have received this
document in error, please notify the sender immediately to arrange
for return or destruction of these documents.0 -
Hi Mubo,
You would need to have access to the RE SQL Server DB. Once you have access, you should be able to set up a data connection to allow you to pull the data into Power BI. You will need to use T-SQL to query the data to get your data set ready for consumption. We don't use Power BI, we use SSRS and QlikSense, but it is the same concept for any tool.
Cheers,
David1 -
Thanks, David.
Please, let me know how we can get access to the RE SQL Server
DB.Regards,
Mubo Sani, bCRE
| Database
AdministratorPronouns:
She/Her/HersOffice of
Institutional Advancement |
University of
Baltimore1130 North Charles
Street|
Baltimore, MD
21201Direct:
410.837.6253 |Email: msani@ubalt.eduCONFIDENTIALITY/PRIVACY
NOTICE - If you are not the intended recipient or the employee
responsible for delivering the information to the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or action taken in reliance on the contents of these
documents is strictly prohibited. If you have received this
document in error, please notify the sender immediately to arrange
for return or destruction of these documents.0 -
Hi Mubo,
I assume you are self hosted? If you are, do you manage the server that RE is installed on? If you do not manage the server, then you will need to speak with whom ever manages the server from your IT department. They will need to provision access for you and set up accounts. I would recommend that they also install SQL Server Management Studio on your machine so that you can build the T-SQL queries there to verify your data output before you attempt to pull the data into power BI.
Cheers,
David2 -
Thanks.
Mubo Sani, bCRE
| Database
AdministratorPronouns:
She/Her/HersOffice of
Institutional Advancement |
University of
Baltimore1130 North Charles
Street|
Baltimore, MD
21201Direct:
410.837.6253 |Email: msani@ubalt.eduCONFIDENTIALITY/PRIVACY
NOTICE - If you are not the intended recipient or the employee
responsible for delivering the information to the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or action taken in reliance on the contents of these
documents is strictly prohibited. If you have received this
document in error, please notify the sender immediately to arrange
for return or destruction of these documents.0 -
I use Power BI for reporting and find it much less cumbersome than Crystal reports, even so had to spend some time learning the Dax expressions etc.
I have not figured out how to connect it to RE. I asked our IT to get me read only access to RE before, but they struggled with it and after many attempts with RE support kind of postponed it. I also feel that it is not super time consuming to export to excel and then just hit refresh on my report.
0 -
Hi Mubo and Cathleen,
You will just need Read access to the SQL Server DB. With Microsoft Management Studio installed and the right permissions set up by your DBA, you should be able to access all the views and tables within the DB.
select * from dbo.records
That will get you all the records. You will need to apply lots of rules to make sure you only bring in the sets you want to work with.
Once you can connect locally, then it is just a matter of getting your Power BI server connected.
Cheers,
David1 -
Thanks David! Our server is managed by an external IT company. I'll pass this info on.0
-
Thanks,
David.Mubo Sani, bCRE
| Database
AdministratorPronouns:
She/Her/HersOffice of
Institutional Advancement |
University of
Baltimore1130 North Charles
Street|
Baltimore, MD
21201Direct:
410.837.6253 |Email: msani@ubalt.eduCONFIDENTIALITY/PRIVACY
NOTICE - If you are not the intended recipient or the employee
responsible for delivering the information to the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or action taken in reliance on the contents of these
documents is strictly prohibited. If you have received this
document in error, please notify the sender immediately to arrange
for return or destruction of these documents.0 -
You do not need access to the raw SQL tables on your server to use Power BI reporting. You can access it all through a custom connection and the SKY API.
https://resolvedllc.com/sky-api-enabled-power-bi/
https://resolvedllc.com/blackbaud-sky-custom-connector-power-bi/
https://github.com/GrantQuick/BlackbaudSkyApi
0 -
Do I need to be hosted by Blackbaud/ have NXT to get the SKY API account? We are self hosted.0
-
SKY API is only available with NXT.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™
- 1 YourCause® from Blackbaud®
- 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
- 1.1K ARCHIVED FORUMS | Inactive and/or Completed EAPs
- 3 Blackbaud Staff Discussions
- 7.7K ARCHIVED FORUM CATEGORY [ID 304]
- 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