Weekly Report - Crystal Reports

Options
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:
eb7051e60ac21942c2aa9ed3a5d06910-huge-ca


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?
Tagged:

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?
  • 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.
  • 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?
  • 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.
  • 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.
  • 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.
  • And how would you code a trustee giving to a special event?  ?
  • Elaine Tucker
    Elaine Tucker Community All-Star
    Ancient Membership 500 Likes 100 Comments Photogenic
    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,
  • 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.

  • 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!
  • 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
  • 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.

     





  • 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
  • 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.

     





  • 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
  • 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.

     





  • 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.



     
  • 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
  • Thanks David! Our server is managed by an external IT company. I'll pass this info on.
  • 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.

     





  • 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


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

Categories