updated 3/1/2006 Crystal Reports – How to set up and run reports Crystal reports are like the canned reports in Raiser’s Edge, but can be manipulated to show data in a very specific way. Crystal reports are very versatile, but all data has to be an exportable field from Raiser’s Edge. Before beginning, it is helpful to sketch out on paper what you want the report to look like, and where that information is located in Raiser’s Edge. There are three steps in creating a Crystal Report: 1. creating a query, 2. exporting the data, and 3. formatting the report. Step One – Creating the Query 1. The query is where you designate which people should be included. It is the WHO of the report – “Who should be included in this report?” 2. For example, if you are interested in creating a report analyzing board member giving, use the Prospect ID or the relationship tab to choose those people. 3. The output and sort options aren’t necessary, as the query is being used to draw certain records into the export, and will not be used on its own. You can, however, use an existing query in export. 4. Save and close. Step Two – Creating the Export 1. The Export is the WHAT – “What information do you want to appear in the report?” 2. From the Raiser’s Edge menu, choose the Export toolbar on the side. Open a new export. a. On the General tab, use the Include button to search and use the query created in Step One, above. Also check the “Create control report” box – this will be useful when creating the report in Step Three, below. b. The Output tab is similar to the Output of a query – what information do you want to show up in the report? This is where you select and create filters to show selected information. 3. Using the board giving example above, say you want to know which board members give to which appeals in the previous year. a. b. c. d. e. updated 3/1/2006 use the Available Fields to Export to find the information you want. If you are interested in the total number of gifts during a certain time period or for an appeal, use the Summary information (at the bottom of the Fields to Export screen). If you only want information about one gift, use the Gifts field. When using the Summary Information field, be specific about the different criteria, such as date, gift appeals and funds, and gift type. Say you want to know board members’ event giving in three consecutive years. You would use the Summary Information field to get all gifts for a certain event in a certain time frame. You want to know if a board member gave to Roll & Stroll, Golf, or the Benefit in 2003, 2004, or 2005. Select Addressee/Salutation from the Fields to Export list, and click Format to choose the correct appearance (ie Board member addressee) From the Fields to Export list, choose Summary information, Gifts, Total Gift amount. f. g. h. i. On the Output screen, highlight the Gift and select Criteria. On the General tab, select the date range (ie 2005). Use the Filters tab to specify campaign, funds, or appeals (ie 2005 Roll & Stroll) Use the Gift Types tab to be very specific about what gifts are included. Make sure to not include both pledges and pay-cash, as that will double count money for the total. Also make sure if you use pledge, that the check box at the bottom is unchecked for “use pledge balance instead of pledge amount.” This will give you the total amount pledged, not the balance remaining. j. Repeat for additional criteria. In this case, repeat for 2005 Golf, 2005 Annual Benefit, 2004 Roll & Stroll, 2004 Golf, 2004 Annual Benefit, 2003 Roll & Stroll, 2003 Golf, and 2003 Annual Benefit. k. Click Export Now. Save in the Q: RE Crystal Reports folder as a Blackbaud Report Writer Database (MDB) file. Print the Control Report and make notes of the order of data or anything that may be helpful when writing the report. Gift Summaries will not print the criteria used, so make notes at to the order of appeals and years used. Step Three – Opening the Database in Crystal 1. Before beginning a new report, set up the default source directory. This only has to be done once and will remain the default source every time a new report is opened. a. On the Windows toolbar, go to Start, Programs, Crystal Reports (or click on the desktop link). b. Go to File, Options. c. Click on the Data Source Defaults Tab. d. Under Crystal Dictionaries, click Browse. Search for the folder RE Crystal Reports. e. Click OK to close out of the Options Screen. This will set your default file source folder. Continue with creating a new report. 2. 3. 4. 5. 6. updated 3/1/2006 On the Windows toolbar, go to Start, Programs, Crystal Reports (or click on the desktop link). Create a New Crystal Report Document as a Blank Report. Open the folder marked Find Database File and click Add. Your default Data Source folder will open (see 1. above). Select the Export file saved in Step Two, above (ends in .mdb) and click Open. The name of the mdb file will appear, with all the export fields below. Highlight all export fields and click Add. 7. This will make a green check mark appear beside each export field. Click Close. 8. The Visual Linking Export screen will open. This is to ensure that each export field is properly linked. Confirm that all the colors are linked to similar colors, and click Okay. updated 3/1/2006 Step Four – Creating a new Crystal Report Design A few comments on the appearance of the Report page: 1. The Design stage is where the report is actually created. The information along the left side splits up the report into headers, footers, and where the details go. These lines can be stretched or tightened depending on the amount of data present. a. the Report Header appears at the beginning of the report, on the first page only. b. the Page Header appears at the beginning of every page (helpful for column headers). c. the Details are where the Export fields go (the report information) d. the Report Footer appears at the end of the report, on the last page only. (Good for summary information/totals.) e. the Page Footer appears at the bottom of every page (good for page numbers, dates etc) 2. Once data is present in the report, it is helpful to see it in a print preview mode instead of just in the design phase. a. To preview the report, go to File, Print Preview on the main toolbar. b. A preview tab will appear next to the Design tab c. Now you can toggle between the Design and Preview from here on out. (This process needs to be repeated whenever you create a new report.) 3. If at any time you add data and decide that it’s not what you want, you can go back to the original Export and edit your output fields. a. Once edits are made, resave the Export in the same place that it was before. b. Go back to the Crystal and go to Database, Verify Database. c. Once Database is verified, click on the lightning bolt on the toolbar. This will refresh your data and include any changes you made in the recently saved Export. Adding data into the report: 4. After completing number 8. in Step 3, above, your blank report template will appear, along with the Field Explorer toolbox. This toolbox allows you to select Export fields and drag them into the report. You can close the Field Explorer toolbox at any time. To reopen it, go to Insert, Field Object. 5. Click on any of the plus boxes next to an Export field to access the data. a. Three pieces of data will appear. Select only the one that looks the most readable, which is the code from the Export. updated 3/1/2006 b. Click on this and drag it to where you want it to appear on the report. Most of the export data should go in the Details section. c. Repeat with all Database Fields, positioning them in the Details section of the report. Worry about their location and appearance later. 6. When you add data to the Details section, a header automatically appears in the Page header row. These can be deleted and renamed with a more helpful title. a. Delete the header that automatically appears by highlighting the text and clicking the delete button, or right clicking and selecting Delete. b. Add a new header field. Go to Insert, Text Object on the top tool bar. A field will appear which you can drag into place above the data you are working with. Select the new field and right click; choose Edit Text Object and type the new header in. c. You can also change the length of the field by moving your mouse over one of the box edges and dragging it in to be the proper size. This is helpful when you have multiple fields of data across one row. You can also resize multiple fields at once; just select all of them by holding down the shift key, and then resize. d. Repeat with all database fields, renaming as necessary. Adjust the size of the Details section by dragging the bottom bar up or down to expand or shrink. 7. Periodically check the appearance of the Report by toggling to the Preview screen (see 2., above). updated 3/1/2006 Changing the appearance of the report: 8. The report can be embellished in a number of ways. a. Highlight certain text and format it using the font toolbar. This can change the type and size of font and also make text appear bold, italicized, or underlined. b. You can also add lines or boxes to separate fields of data. i. First, add the appearance of the supplemental toolbar by clicking on the picture of the wrench in the top right corner of the screen. The supplemental toolbar will appear at the bottom left corner of the screen. or a box. . ii. Use these two fields to add a line c. You can also change the way the numbers appear on the preview screen. i. On the Design screen, highlight the Gift Amounts you wish to change. You can highlight all amounts at the same time to format everything the same way by holding the shift key down as you click on each data field. ii. Right click and choose Format Objects. iii. Click on the Number tab, and Customize. This will change the appearance of the number, based on how many decimal points to use and if there should be a $ sign iv. The Customize screen shows how the numbers will appear. You can choose the number of decimal points and rounding places used. v. You can also check the box marked “Suppress if Zero.” This will make a blank space show up in the report instead of a $0, to make the fields where there is actual data more prominent. d. You can also change the page set up of the report by selecting File, Page Setup from the main toolbar. Here you can select Portrait or Landscape format and the margins. This is important as the margins default to 0”. Adding Summaries and Total Fields at the end of the report 9. It is often helpful to have totals at the end of the report, to calculate dollars, ie. This data is entered in the same way. a. Expand the report footer to be an adequate size. b. Enter text fields for the summaries (ie, “Total Dollars”) using step 5b., above. c. Go to Insert, Grand Total from the toolbar. A Grand Total screen will appear. updated 3/1/2006 d. First, select from the second drop down which report field you want to total (in this case, the first column, which is summary gift totals 1). e. Second, select what you want the summary to calculate. Some of the most common are: i. sum – this adds all the gift amounts in the column and totals the dollars ii. average – this adds all the gifts amounts and finds the average dollar amount iii. maximum, minimum – this finds the maximum and minimum dollar amounts iv. count – this counts all data regardless of amount (including $0); it is helpful in telling you how many people/gifts appear on the report. v. distinct count – this counts all the fields with distinct dollar amounts. For example, it will count the first field with $0, the first field with $100, etc. Not very accurate, but gives an estimate when working with a large amount of data. f. Once you have selected your summary field, move the data to the proper place in the report. g. Repeat with each column to get the desired results. Preview the report to confirm the totals are appearing in line with each column. Step Five – Updating data within the Report Crystal reports can be manipulated and edited to produce results in a variety of formats. The most important thing is to export the appropriate fields in Export. Once all the appropriate fields have been chosen in Export, they can be rearranged in the report. However, the data cannot be changed once it is in Crystal, so the most important changes need to be done in Export. 1. Make any changes or additions in Export 2. Save using the same .mdb file extension. a. Save the export b. click the Export Now button and write over the old file. 3. In Crystal Reports, go to Database and select Verify Database. 4. Once the database is verified, click on the lightning bolt to refresh the data. This will update the data so you can continue to add data without having to create a new report. (This info also under Step Four, 3. above) 5. 6. 7. 8. updated 3/1/2006 If you add data instead of just changing existing data, there is one more step. Repeat steps 1 and 2, above. In Crystal, go to Database, Add Database to Report. a. under Database Files, find the new MDB file you just saved. b. find the new data, highlight, and click Add. c. click Close. The Visual Linking Expert will appear. Click OK. Refresh the data. Your new fields should appear in the Field Object table. With questions, access Blackbaud’s solution: BB16538 Step Six – using the same report parameters with a different Export This is helpful when you have a report that you wish to use with a group of constituents from different giving levels. To begin, follow steps One and Two, above. 1. Open the report that you would like to use. a. Go to Database, remove from report b. Select each field and click “Remove.” It will prompt you that “There are fields in the report from this file. Continue?” Click OK. Repeat with all fields. c. Go to Database, Add Database to Report. i. Under Database Files, select Find Database File. Search for the newly saved Export created in Step Two, above. ii. Select all the fields and click Add. Confirm the table and click okay. d. This should bring you to the report, with all the text fields but no data fields remaining. e. Use the Field Explorer to enter new data into the proper columns. f. Preview and print! 2. This process can be repeated multiple times, to use the same report columns with different data fields. All that is changed in the Export Database.