Menu Close

Atlas Shared Reports Formatting Standards

Atlas Reporting Table

  • Should indicate if any custom tables are used, and if this report can be used by multiple affiliates
  • Add a report snapshot to the appropriate folder in ADO Atlas.Reports
  • Add full report notes so users will understand what the report is selecting and displaying, eg enhanced or regular opportunity status, etc.
  • If the report shows financials, a description in words and a link to the wiki page for “Atlas Financial Crediting” should be added
  • If there is something different about this report so that totals or counts here would not match to other reports this should be noted and explained
  • Embed search keywords either in the report notes text, or add “Keywords: ” at the bottom of the report notes.  This should include anything that would help a user find this report with a search, like “pipeline”, “portfolio” or “Annual Giving” and “AG”
  • Add a section titled “Purpose: ” and outline possible uses for this report, questions it answers versus other similar reports we have
  • MGB Affiliate notes should be used for comments on updates and changes helpful to MGB staff

 

SSRS Report

FORMATTING 

  • Font:  Arial 
  • Pitch:  10 for most data 
  • Color: Atlas colors is predominantly blue and gray, and these are incorporated when appropriate into reports.  Header background color can be “LightSteelBlue”, footers or group headers “LightGray”.  Goal is to keep the most important data front and center, so column headers or axis descriptions can use font color “DimGray” so they don’t detract from the actual data.
  • Paper:  When possible try to keep reports on letter size 
  • Margins:  as needed for report, but try to allow 1/2” to 1”  margin on left side for binding 
  • Fiscal year date range is 10/1 to 9/30  
  • Hyperlinks should be formatted in “Blue” (plain blue color) without hyperlinks, this is consistent with Atlas.  The hyperlinked data should be whatever is most intuitive for the user of the report, so it may vary report to report depending on space considerations.  Hyperlinks should also be referenced in the report notes so users don’t forget them
  • Hyperlinks in a new window:  if you want to open a URL from within a report in a new window, this is a way to do it:  Add an Action to the Placeholder on which you want the hyperlink, choose Go to URLUnder select URL, use this as your pattern:  =”javascript:void (window.open (‘” & Fields!URL.Value & “‘,’_blank’))”
  • Cross-Affiliate reports – we have some reports that cycle through all databases like cross-affiliate monthly reporting.  These reports require the cross-affiliate data warehouse issue notification added to the header block, this notifies the user if one of the affiliate data warehouses has not completed successfully so the report is not complete.  See existing reports for a sample.

 

PARAMETERS

  • Include “english” date selection options as appropriate
  • pull defaults for parameters from bbdw.ph_v_parameterdefaults
  • prompt for custom report header
  • REPORT DOCUMENTATION:  all reports should have a prompt for report notes using report meta data subreport.  Parameter label = “Report Documentation”, default option “Display on last page”, option 2 “Do not display”.  The subreport REPORT METADATA  should be in a rectangle, with a page break set at the start of the rectangle.  Set visibility on the rectangle to the parameter, and set the page number to:  =”Page “+ Str(Globals!PageNumber) + iif(Parameters!ReportDocumentation.Value=”display”,” of ” + Str(Globals!TotalPages-1),” of ” + Str(Globals!TotalPages)) and set page number visibility to show.
  • ensure the page numbers are set correctly to account for inclusion of the report notes or not
  • add parameter for report to fail if DW has not full completed, achieved by a dataset pulling
  • Parameters and subscriptions:  If a report is going to have subscriptions, special care needs to be used when creating the selection parameters.  The primary link between tables is the ConstituentDimID, however this is updated nightly and can change.  If a report will have subscriptions, then parameters that refer to people, like prospect managers, need to use the ConstituentSystemID as the value for the parameter selection.  This is the system GUID from Atlas and does not change, or you can use the constituent LookupID. 
  • ANONYMITY :  goal is to have consistent handling of anonymous names and donations across reports.  A donor can be marked anonymous in Atlas/Personal Info, or a specific donation can be marked as anonymous.  We will add another parameter prompt to reports which will have two options: 
    • Indicate Anonymity 
      • If report displays any financial data from proposals or gifts and the Anonymous flag from RE Bio1 is checked, append the text “(Anonymous)” after the constituent’s name, after the gift type if appropriate. 
      • If report contains gift information and a specific gift is anonymous but the donor is NOT anonymous, append the text “(Anonymous)” after the gift type  
      • This will be the default 
    • Suppress Name if Anonymous 
      • If report displays proposal data and the Anonymous flag from RE Bio1 is checked, replace the display name and the sort name with the text “Anonymous (999999)” where 99999 is the constituent ID.  Additional biographic data like address and spouse would also be suppressed. 
      • If report displays detailed financial data, any gift checked as anonymous should be credited to “Anonymous (999999)” where 99999 is the constituent ID.  In other words if Mr. X with ID 123 gives 3 gifts anonymously and 2 gifts regularly, he will only show up in the report with 2 donations credited to Mr. X (123) and 3 gifts credited to Anonymous (123). 
      • If report displays aggregated financial data (e.g. total giving in FY10) and one of the gifts is checked as anonymous, the aggregated data will be linked to “Anonymous (999999)” where 99999 is the constituent ID. 
  •  TYPE OF GIFT: When prompted user for type of gift options are the default “Commitments (Gifts and Pledges)”, “Cash (Gifts and Pledge Payments)”
  • RECOGNITION DONOR:  If appropriate, a report can prompt for the type of donor to display.  default “Hard Credit Donors”, “Primary Recognition Donor”, “All Recognition Donors”.  If using this parameter please note the following:
    • You may wish to optionally include all the other recognition donors, see FIR1052
    • You MUST include a link to the wiki page on crediting and explain in the report notes why totals may be different depending on the selection
    • Column headers must be dynamic to indicate what is being displayed (eg Change “Donor Name” to “Primary Recognition Donor”, “Total” to “Primary Recognition Total”)
    • Financial totals should be suppressed if “All Recognition Donors” is chosen to avoid double-counting

REPORT HEADER 

Contains logo, affiliate name and report title.  All reports should additionally prompt user for a custom report header.  The custom header will allow for situation where the parameters of the report need to be explained in a user-friendly way, or if the report needs a special title as it is included as part of a packet for volunteers. 

Affiliate name and report name should be bold and Arial 14, custom report header bold and Arial 12.  Other data left justified to right of logo.  A solid line appears underneath, to be more consistent to Atlas this line should be gray. 

Logos are accessed dynamically using the path in the bbdw.ph_v_parameterdefaults table

 

PAGE HEADER 

Page headers are suppressed from page one and appear on page two onward.  Contains report name, and custom report title, but in smaller font.  Report name now 12 pitch and bolded, custom title now 10 pitch and bolded.  And both are left justified.  As with the report header, the page header has a gray line underneath. 

If appropriate, the page header can include column headings under the line.  There may be  reports where the header may detract from the data displayed and may need to be modified – example a research profile report where the most appropriate header would be the prospect’s name. 

 

PAGE FOOTER 

Page footers on each page of the report with a gray line above (line should be less thick than line underneath report/page header).  Data is not bolded, and is 10 pitch.  Affiliate name concatenated with hypen, space, and text “Confidential” is left justified, page number (see question in report footer about page counting) is right justified.  When possible use ‘page N of M’, but if not appropriate ‘page N’.  The report number or name should also appear left justified if the report has an internal audience. 

Page footers are removed if the report is optimized to be exported to excel. 

 

REPORT BODY 

DATA FIELDS 

  • Text:  left justified  
  • Addresses:  if listing full address suppress any blank rows 
  • Dates:  appear in m/d/yy format, right justified 
  • Numbers:   
  • All numbers are right justified 
  • Money:  use floating “$” sign, and round up numbers to the nearest dollar on all reports except for GP Operational reports  (e.g $1,235 vs $1,234.56 for GP Operations reports).  If numbers use $1K for thousands, $5M for millions.  Use minus sign for negative numbers, ex  “-$1,234”. 
  • Counts:  use , for thousands, and minus sign for negative numbers.  In cases like a cross tab where counts are listed adjacent to money, it may make sense to italicize the count. 
  • Percents:  unless required, round percents up to remove decimal places. 
  • Subtotals should have one line above to show it is the sum of the numbers above 
  • Totals should have a double line above 

 

COLUMN HEADINGS 

  • Any report showing money needs to clearly indicate if the amounts are for “Commitments” or “Cash Received” 
  • Year based totals 
  • Fiscal years should be abbreviated as FYXX, example FY08 
  • To date totals can be abbreviated as “FYTD 08  11/15” or “FY08 as of 11/15/08” 
  • Calendar year should be abbreviated as CYXX, example CY08 
  • Column headings should have a light gray background with a gray line between columns.  Headers should not be bolded or underlined unless required by the report. 
  • If data is stacked, then column heading should also be stacked, or separated by “/” as appropriate to the layout 

 

GROUPING 

  • Group headers should repeat if group moves to next page, ideally containing the word “(Continued)”. 
  • Use color as appropriate to help identify group breaks or differentiate between groups 
  • Subsequent groups should be slightly indented from the group immediately above if this is possible given the report layout. 

 

DETAIL   

  • Where necessary, separate detail rows with a gray line 
  • Year by year comparison reports should list years left to right starting with the latest year first.   
  • Year by year comparison reports (2yr comparison) should include columns for variance percent and variance amount. 

 

CHARTS/GRAPHS 

  • Column headings and total rows and columns should be bolded or colored to be easily identifiable 
  • Reports containing charts should use a pie chart if data is for one year and bar chart if showing multi-year comparisons.  

 

 

REPORT FOOTER 

 REPORT NOTES 

In an effort to make reports as simple for end users to understand, report note text from Atlas can optionally print as a report footer. 

  • Add a parameter to the report “Display Report Notes?” with  “Yes” or “No” response, defaulting to “Yes”
  • Use the subreport “REPORTMETADATA” to pull in the Atlas reporting table report notes.  This pulls from the DW, so changes made today will appear tomorrow.  The notes should optionally appear depending on the parameter response
  • Report notes should generate a new page prior, so the notes are always the last page and the only data on the last page
  • Page numbers may need to be adjusted if report notes are included or excluded, report notes should not be included in page counts if your page numbers are “page x of y” be sure “y” accounts for the report notes 

 SSRS DATA SOURCES

Be sure to use the shared data source, not a custom one!

MOD HISTORY/TECH NOTES 

A text box with hidden set to True can be used to track changes to the report, and provide some info if the report uses an unusual feature or filters are buried, etc.  This is maintained by the report developers. 

When creating the SQL for a report, please add sufficient comments so someone unfamiliar with the report can follow what is going on.  Examples include:  –temp table for relationships to speed stuff in main select, etc. 

If changes are made to a dataset, the specifics should be entered in a commented out Mod History section at the top of the SQL statement.  If appropriate, individual SQL lines can also have comments.  The goal is to make it as easy as possible for another Partners’ staffer to understand your changes and to make additional ones as needed. 

SQL CONVENTIONS 

We realize that different programmers have different styles of writing SQL, however the following standards are proposed (most lifted from a SQL Saturday talk on bad T-SQL habits).  Please be kind to the next programmer who has to decipher your code šŸ™‚

 GENERAL FORMATTING/PERFORMANCE 

Cryptic code does not improve productivity! 

  • Readability is very important, please use indents and carriage returns 
  • Don’t be shy about adding comments, just don’t go overboard… 
  • Please use table and column aliases, but aliases like Q1, Q2, etc. are not helpful 
  • Please label temp tables with an appropriate description of what they contain 
  • Be consistent with case.  Case doesn’t matter now in SQL, but it does matter in SSRS. 
  • Group By tends to have faster performance than Distinct 
  • Date responsibly:  When using Date functions, please spell out what you mean so there is no question of what you are trying to do.  For example, “y” is not the same as “year” see example below: 
    • select getdate() –2017-03-06 14:10:32.490 
    • ,dateadd(y,-1,getdate()) –2017-03-05 14:10:32.490 
    • ,dateadd(year,-1,getdate()) –2016-03-06 14:10:32.490 
  • When in doubt please spell it out – don’t do @StartDate + 1, instead do DATEADD(day,1,@StartDate) 
  • Avoid using between, it is not as efficient as using >= and <=, example: select * from dr_gift g where g.fgift_giftdate >= @StartDate and g.fgift_giftdate < DATEADD(day,1,@EndDate) 

 

SUBSCRIPTiONS

When possible set subscription times to after noon so if there any DW build issues they would be resolved by the afternoon run.

Leave a Reply