Weekly Report - Crystal Reports

Ellen Kubit

Hello All,


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?

Mark Guncheon 2

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?

Ellen Kubit

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.

Mark Guncheon 2

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?

Joshua Bekerman bCLO bCRE

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.

Ellen Kubit

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.

Mark Guncheon 2

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.

Mark Guncheon 2

And how would you code a trustee giving to a special event?  ?

Elaine Tucker

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,

James Andrews

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.

Ellen Kubit

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!

Elaine Tucker

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!

David Ritchie

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,

David

Mubo Sani 2

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
Administrator

Pronouns:
She/Her/Hers

Office of
Institutional Advancement |
University of
Baltimore 

1130 North Charles
Street|
Baltimore, MD
21201

Direct:
410.837.6253 |Email: msani@ubalt.edu

 

 

CONFIDENTIALITY/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.

 

David Ritchie

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,

David

Mubo Sani 2

Thanks, David.
Please, let me know how we can get access to the RE SQL Server
DB.

 

Regards,

 

Mubo Sani, bCRE
| Database
Administrator

Pronouns:
She/Her/Hers

Office of
Institutional Advancement |
University of
Baltimore 

1130 North Charles
Street|
Baltimore, MD
21201

Direct:
410.837.6253 |Email: msani@ubalt.edu

 

 

CONFIDENTIALITY/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.

 

David Ritchie

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,

David

Mubo Sani 2

Thanks.

 

Mubo Sani, bCRE
| Database
Administrator

Pronouns:
She/Her/Hers

Office of
Institutional Advancement |
University of
Baltimore 

1130 North Charles
Street|
Baltimore, MD
21201

Direct:
410.837.6253 |Email: msani@ubalt.edu

 

 

CONFIDENTIALITY/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.

 

Cathleen Mai

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.



 

David Ritchie

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,

David

Cathleen Mai

Thanks David! Our server is managed by an external IT company. I'll pass this info on.

Mubo Sani 2

Thanks,
David.

 

Mubo Sani, bCRE
| Database
Administrator

Pronouns:
She/Her/Hers

Office of
Institutional Advancement |
University of
Baltimore 

1130 North Charles
Street|
Baltimore, MD
21201

Direct:
410.837.6253 |Email: msani@ubalt.edu

 

 

CONFIDENTIALITY/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.

 

Graham Getty

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


 

Cathleen Mai

Do I need to be hosted by Blackbaud/ have NXT to get the SKY API account? We are self hosted.

John Heizer

SKY API is only available with NXT.