Atlas SSRS Reports
SSRS and other types of output like Tableau can be access from the Reporting tab in Atlas. Click here for information on adding reports to the Atlas Reporting Tables
If an affiliate has a new logo for reports, they should entered a Mojo ticket and attach the logo. Reports for Atlas are written with a particular proportion for the logos. The size of most of the current logos is 200×87 pixels. As long as a logo has that same proportion, it looks fine in the report, e.g. 600×261 looks fine. 600×173 (or, sized down, 200×58) gets stretched vertically. The same thing would happen if the logo were proportionally narrow. If the logo doesn’t have these proportions, we can modify the logo by adding white space to compensate if the logo isn’t the size the reports are expecting.
Click here for additional information on MGB Revenue Reporting and Crediting
Click here for Atlas Shared Reports Formatting Standards
And for more entertainment, try Creating Report Subscriptions in Reporting Services
Servers and Environments Cheat Sheet
QA
- Atlas OLTP QA: PHSSQLD2232
- BBDW OLAP QA: PHSSQLD2233
- SSRS QA: https://phssqld2231.partners.org/Reports/browse/Blackbaud/AppFx/AtlasPreviews
- Linked Servers: PHSSQLD2232 -> PHSSQL588 (QA)
Staging
- Atlas OLTP Staging: PHSSQL2269
- BBDW OLAP Staging: PHSSQL2270
- SSRS Staging: https://phssql2276.partners.org/Reports/browse/Blackbaud/AppFx/AtlasPreviews
- Linked Servers: PHSSQL2270 -> PHSSQL588 (staging)
Production
- Atlas OLTP Prod: PHSQLAO4600A
- BBDW OLAP Prod: PHSSQL2294
- SSRS Prod: PHSSQL1333
- Linked Servers: PHSSQL2294 -> PHSSQL586 (prod)
Training
- Atlas OLTP: PHSQLAO4600C
MGB Reporting Layer
We will be adding more information about our MGB Reporting Layer in the table below. Why isn’t everything there you may ask? Tables and views are being created now and as they are tested and available in QA more detail will be added, but meanwhile information is available here. The sharepoint-online folders are how the Reporting Team is communicating with the programmers, this area in the wiki is for our internal documentation. Please feel free to update/add information as you see gaps or errors, or if you have a tip or clarification that will help others. If you need permissions to edit/update please contact Helen. Click here for the BBDW FAQ.
Our goal is to use PH_ % tables and views for reporting. These will include our business rules where appropriate, and will reduce the need to join to code tables. Additional info can also be obtaining by joining to the underlying BBDW tables/views. Partners reporting standards are available here. Note that the suffix _EXT is added for any tables that extend an existing BB table, use caution as these may not contain a row for every value you expect. Unlike RE, many tables have a DIM_ AND a FACT_ version, so if you don’t find what you need in one check the other. BB protocol tends to fill coded columns, for example a blank business unit would not be a blank or null in the DW, but instead the text “No Business Unit”.
The DW ETL removes leading blanks, tabs and carriage returns from note fields.
RE to CRM Data Object Translation
| Area | Object Name (Click on hyperlink for addl info) | Comments |
|---|---|---|
| Addresses | PH_V_CONSTITUENTADDRESSATTRIBUTE | |
| Addresses | PH_V_CONSTITUENTADDRESS | |
| Alternate Lookup IDs | PH_V_ALTERNATELOOKUPID | |
| Alternate Lookup IDs | DIM_ALTERNATELOOKUP_EXT | |
| Appeals and Marketing | DIM_APPEALNOTE_PHEXT | Notes related to appeals |
| Appeals and Marketing | FACT_APPEALNOTE_PHEXT | Notes related to appeals |
| Appeals and Marketing | PH_V_APPEAL | |
| Appeals and Marketing | PH_V_APPEALATTRIBUTE | |
| Appeals and Marketing | FACT_CONSTITUENTAPPEAL_EXT | |
| Appeals and Marketing | PH_V_CONSTITUENTAPPEAL | |
| Appeals and Marketing | FACT_CONSTITUENTAPPEALRESPONSE_PHEXT | |
| Appeals and Marketing | DIM_MARKETINGSEGMENTATION_PHEXT | |
| ATTRIBUTES | PH_V_CONSTITUENTATTRIBUTE | If you are missing an attribute, confirm it is checked in Atlas to be included in the DW |
| BUSINESS UNIT GOALS | DIM_BUSINESSUNITGOAL_EXT | Goals for each business unit |
| Campaigns | PH_V_CAMPAIGN | |
| Campaigns | DIM_CAMPAIGNNOTE_PHEXT | |
| Campaigns | FACT_CAMPAIGNNOTE_PHEXT | |
| Constituencies | DIM_CONSTITUENCY_PHEXT | Constituent code information |
| Constituencies | FACT_CONSTITUENCY_PHEXT | |
| Constituencies | FACT_CONSTITUENCY | |
| Constituencies | PH_V_CONSTITUENCY | constituent codes by constituent |
| Constituent | DIM_CONSTITUENTNOTE_PHEXT | |
| Constituents | PH_ConstituentRevenueSummary | combined features of both tables into one table in CRM |
| Constituents | PH_CONSTITUENT | Data for individuals and organizations - use dim_constituent for committees or groups |
| Constituents | DIM_DECEASEDCONSTITUENT_PHEXT | |
| Constituents | PH_V_CONSTITUENTMAILPREFERENCE | |
| Custom Apps | PH_V_APPEALS_ACTIVERECENT | view added to MGH only to facilitate exchange of data with custom application |
| Custom Apps | PH_V_DAFINSTITUTION_PAIRSLIST | view added to MGH only to facilitate exchange of data with custom application |
| Custom Apps | PH_V_DESIGNATIONS_ACTIVE | view added to MGH only to facilitate exchange of data with custom application |
| Custom Apps | PH_V_OPPORTUNITIES_ACTIVE | view added to MGH only to facilitate exchange of data with custom application |
| Custom Apps | PH_V_SOLICITORS_DEVELOPMENT | view added to MGH only to facilitate exchange of data with custom application |
| Designation | PH_V_DESIGNATIONATTRIBUTE | |
| Designations | FACT_DESIGNATIONGOAL_PHEXT | Note that designations can have multiple goals, check start/end dates |
| Designations | PH_V_DESIGNATION | Designation specific information |
| Education | PH_V_CONSTITUENTEDUCATION | |
| Education | PH_V_EDUCATIONATTRIBUTE | |
| Emails | PH_V_CONSTITUENTEMAIL | |
| Event Registrants | PH_V_EVENTREGISTRANT | |
| Event Registrants | PH_V_EVENTREGISTRANTATTRIBUTE | |
| Events | PH_V_EVENT | |
| Events | DIM_EVENTCOORDINATOR_EXT | |
| Events | DIM_EVENTCOORDINATORROLE_PHEXT | |
| Events | DIM_EVENTNOTE_PHEXT | Notes related to events |
| Events | FACT_EVENTNOTE_PHEXT | Notes related to events |
| Events | FACT_EVENTPRICE | Event registration information |
| Events | PH_V_BENEFIT | Event Benefits |
| Events | PH_V_EVENTAPPEAL | |
| Events | PH_V_EVENTATTRIBUTE | |
| Functions | PH_UFN_BUILDSTRINGBLOCK | builds a block of text separated by pipes |
| Functions | PH_UFN_GETAPPLICATIONLINK Coolest Function Evah! | Function that provides a hyperlink back to Atlas |
| Functions | BBDW.PH_UFN_DWSTATUS () | #NAME? |
| Funding Interests | PH_V_PROSPECTFUNDINGINTEREST | Prospect Funding Interests |
| Fundraiser | DIM_FUNDRAISER_PHEXT | Solicitor data, link to fact_revenuefundraiser |
| Groups | DIM_CONSTITUENTGROUP_PHEXT | |
| Groups | DIM_CONSTITUENTGROUPMEMBERROLE | |
| Household | PH_HOUSEHOLD | Contains all single individuals, the primary member of households, and all organizations |
| Household | PH_HOUSEHOLD_RECOGNITIONSPLIT | combines recognition at the household level, removing duplicates |
| Household | PH_HOUSEHOLDREVENUESUMMARY | PH_ConstituentRevenueSummary at the household level, despite the name, this table is calculated using RECOGNITION dates and amounts Note that the years follow different conventions. Current Yr: H_totalamountCurrentFY Other years: H_totalamountfy_1-10 |
| HOUSEHOLD OR GROUPS? | PH_V_HOUSEHOLD_GROUPS | |
| Interactions | FACT_INTERACTIONNOTE_PHEXT | |
| Interactions | DIM_INTERACTIONNOTE_PHEXT | |
| Interactions | PH_INTERACTIONSBYCONSTITUENT | One row for each interaction, plan step or stewardship plan step for each constituent involved including all participants |
| Interactions | PH_INTERACTIONSBYOWNER | One row for each interaction, plan step or stewardship plan step for each owner or additional solicitor (from attribute). |
| Interactions | PH_V_INTERACTION | |
| Interactions | PH_V_INTERACTIONATTRIBUTE | |
| Interests | PH_V_CONSTITUENTINTEREST | Constituent personal interests |
| Model Scores and Ratings | PH_V_MODELINGANDPROPENSITYATTRIBUTE | |
| Name Formats | DIM_NAMEFORMAT_PHEXT | |
| Name Formats | PH_V_NAMEFORMAT | |
| Naming Ops | PH_NAMINGOPPORTUNITYATTRIBUTE | |
| Naming Ops | PH_V_NAMINGOPPORTUNITYATTRIBUTE | Naming Opportunity Attributes |
| Naming Ops | DIM_NAMINGOPPORTUNITY_EXT | Naming Opportunities |
| Naming Ops | DIM_NAMINGOPPORTUNITYFACILITY_EXT | Facilities associated with Naming Opportunities |
| Naming Ops | DIM_NAMINGOPPORTUNITYINSTALLEDLOCATION_EXT | Installed Locations associated with Naming Opp Recognition |
| Naming Ops | DIM_NAMINGOPPORTUNITYRECOGNITION_EXT | Constituents associated with naming opportunities |
| Notes | DIM_CONSTITUENTNOTE_PHEXT | |
| Notes | FACT_CONSTITUENTNOTE_PHEXT | Constituent notes |
| Other | PH_V_PARAMETERDEFAULTS | Report parameter defaults including dates, affiliates, logo location, etc |
| Other | PH_ORGANIZATIONINFO | |
| Other | DIM_DATE | MGB started updating the ISHOLIDAY field with a custom SP beginning in 2023 when holidays were standardized across MGB affiliates |
| Patient Visits | PH_EPIC_CONSTITUENTSUMMARY | |
| Phones | PH_V_CONSTITUENTPHONE | |
| Planned Gifts | PH_V_PLANNEDGIFT | |
| Planned Gifts | PH_V_PLANNEDGIFTATTRIBUTE | |
| Planned Gifts | DIM_PLANNEDGIFT | |
| Planned Gifts | DIM_PLANNEDGIFTNOTE_EXTFACT_PLANNEDGIFTNOTE_EXT | Notes relating to planned gifts |
| Planned Gifts | FACT_PLANNEDGIFTNOTE_PHEXT | Notes relating to planned gifts |
| Pledges | PH_PledgeSummary | one row for each pledge split |
| Pledges | PH_INSTALLMENTPAYMENT | |
| Prospect | DIM_PROSPECT_PHEXT | |
| Prospect | DIM_PROSPECTMGRHISTORY_PHEXT | |
| Prospect | DIM_PROSPECT | |
| Prospect | FACT_PROSPECTTEAMROLE_EXT | Note that prospect manager info is not here, former prospect manager info is in DIM_PROSPECTMGRHSITORY_PHEXT |
| Prospect Plans | PH_OPPORTUNITIES | Opportunities |
| Prospect Plans | PH_V_OPPORTUNITYATTRIBUTE | |
| Prospect Plans | PH_OPPORTUNITYDESIGNATIONSPLITS | Opportunity Designation Splits |
| Prospect Plans | FACT_REVENUEOPPORTUNITY | joins revenue with opportunities |
| Prospect Plans | DIM_PROSPECTPLANNOTE_PHEXT | |
| Prospect Plans | FACT_PROSPECTPLANNOTE_PHEXT | |
| Prospect Plans | PH_PLANS | Plans |
| Prospect Plans | PH_V_OPPORTUNITYASSOCIATEDREVENUE | |
| Prospect Research Requests | PH_PROSPECTRESEARCHREQUESTDOCUMENTATION | |
| Prospect Research Requests | PH_V_PROSPECTRESEARCHREQUEST | Prospect research requests - includes research requests, prospect assignment requests, and stewardship reports |
| Prospect Research Requests | PH_PROSPECTRESEARCHREQUESTCONSTITUENTATTRIBUTE | |
| Purposes | PH_PURPOSE_RECIPIENTS | |
| Purposes | PH_PURPOSE_RELATEDSTAFF | |
| Purposes | PH_PURPOSE_STEWARDSHIP | |
| Purposes | DIM_PURPOSELEVEL_PHEXT | |
| Purposes | PH_V_PURPOSELEVEL | |
| Purposes | PH_V_PURPOSELEVELATTRIBUTE | Purpose attributes |
| Purposes | DIM_PURPOSELEVELNOTE_PHEXT | |
| Purposes | FACT_PURPOSELEVELNOTE_PHEXT | |
| Recognition Programs | FACT_CONSTITUENTRECOGNITION_EXT | One of 3 objects in the BBDW currently related to recognition programs: how this constituent is recognized within the program |
| Recognition Programs | DIM_RECOGNITIONPROGRAM_EXT | One of 3 objects in the BBDW currently related to recognition programs: describes the program and levels associated with it |
| Recognition Programs | PH_V_RECOGNITIONPROGRAM | PH working table for One of 3 objects in the BBDW currently related to recognition programs: reporting |
| Regions | DIM_REGION_PHEXT | |
| Regions | DIM_CONSTITUENTREGION_PHEXT | |
| Relationships | PH_V_CONSTITUENTRELATIONSHIP | |
| Revenue | FACT_FINANCIALTRANSACTIONLINEITEM | |
| Revenue | FACT_FINANCIALTRANSACTIONLINEITEM_EXT | |
| Revenue | DIM_GIFTINKIND_EXT | |
| Revenue | DIM_GIFTTYPE_EXT | The table is part of the standard BB rollout. It takes standard CRM revenue/application/transaction types and translates them to a RE Gift Type that we all know and love |
| Revenue | PH_V_RECURRINGGIFT | |
| Revenue | FACT_REVENUEFUNDRAISER | Gift solicitors and event fundraiser solicitors |
| Revenue | DIM_REVENUENOTE_PHEXT | |
| Revenue | FACT_REVENUENOTE_PHEXT | |
| Revenue | V_QUERY_BBDW_REVENUESOLICITOR | Solicitors linked to revenue |
| Revenue | PH_RevenueSplit | split level gift information, donor information is for the hard credit donor |
| Revenue | PH_MATCHINGGIFTPAYMENT | |
| Revenue | PH_REVENUECLASSIFICATIONS | Note that this is at the revenue level, not at the split level |
| Revenue | PH_REVENUEGLDISTRIBUTION | |
| Revenue | PH_REVENUESPLITGLDISTRIBUTION | |
| Revenue | PH_V_FINANCIALTRANSACTIONATTRIBUTE | |
| Revenue | PH_V_FINANCIALTRANSACTIONLINEITEMATTRIBUTE | |
| Smart Fields | FACT_CONSTITUENTSMARTFIELD | |
| Smart Fields | PH_V_SMARTFIELD | Smart fields have pre-calculated totals and other types of data. Also includes the Primary Rating, and the Primary Constituent Code. If you don't see what you are looking for make sure the smartfield is coded in Atlas to be included in the DW |
| Solicit Codes | PH_V_CONSTITUENTSOLICITCODE | |
| Solicit Codes | FACT_CONSTITUENTSOLICITCODE | |
| Stewardship Plans | PH_V_STEWARDSHIPPLANSTEPATTRIBUTE | |
| Stewardship Plans | PH_V_STEWARDSHIPPLAN | |
| Teams | PH_V_TEAM | |
| Tributes | FACT_REVENUETRIBUTE | link between the gift and tribute |
| Tributes | PH_V_TRIBUTE | Best source for data around Tributes, includes notes and link to tributee constituent |
| Tributes | PH_V_TRIBUTEACKNOWLEDGEE | Acknowledgees linked to the tribute; these are the ones set up as default tributees to receive |
| Tributes | FACT_REVENUETRIBUTELETTER_PHEXT | Letters associated with a tribute gift. This includes both the default letters set up on the tribute itself, and any manually added (ANOK) as the gift is entered as an addl notificant for this one gift |
| Wealth | PH_V_WEALTH | |
| Events | DIM_EVENTINVITATION_EXT | Event invitation names (Event/Invitations tab) |
| Events | FACT_EVENTINVITEE_EXT | Event invitees |
SQL Code Snippets
Tips on sql code in general, add your code to the pages below