Percentages

Want to make it easy for your Users to see registrations and Group percentages at-a-glance? Just set Show Percentage to Yes on the Page View Rules record. This is visually illustrated with a fill and is always compared against 100. So, if the value is 25, the Record Fact fill is 25% full. For this to happen, any comparisons must occur in the View and require advanced SQL. The comparison of Target Size and Current Participants must happen in a View, so the View returns a value of 25.

CASE WHEN Groups.[Target_Size] = 0 THEN NULL ELSE(CAST((SELECT Count(*) FROM Group_Participants GP WHERE GP.Group_ID = Groups.Group_IDAND (GP.[End_Date] IS NULL OR GP.[End_Date] > @DomainTime)) AS decimal(6,2))/Groups.[Target_Size]) / 100)AS int) END AS [Current Participants]Copied

Age Calculations

Showing a Contact or Participant's age as a Record Fact is a no-brainer. If you want to calculate the age of deceased Contacts based on their Date of Death rather than the current date, use this in your Facts View.

(SELECT CASE WHEN C.Contact_Status_ID = 3 THEN (CONVERT(int,CONVERT(char(8),C.Date_of_Death,112))-CONVERT(char(8),Date_of_Birth,112))/10000  ELSE C.__Age END FROM Contacts C WHERE C.Contact_ID = Contacts.Contact_ID) AS [Age]

Combine Values

The Advanced or Power User can create a Record Fact which displays milestones a Participant has. When baptized, the Fact also turns green and displays an icon. This allows the Casual User to glance at a list and get all the information they need. Want to really empower that casual User? Set up a View Notification to send them a periodic email reminding them to check the list.

((SELECT CASE WHEN EXISTS(SELECT PM.[Participant_Milestone_ID] FROM Participant_Milestones PM WHERE PM.[Participant_ID] = Participants.[Participant_ID] AND PM.[Milestone_ID] = 2)THEN 'Baptized'ELSE 'Not Baptized'END)+  ' +' + CAST((SELECT COUNT(*) FROM Participant_Milestones PM WHERE PM.[Participant_ID] = Participants.[Participant_ID]) AS varchar(15)) ) AS [Milestones] Copied

Dates Without Times

Want to see the date something happened (for example, a birth date, registration date, or Event date) but not see the time? Add a little bit of code before the date field SQL so the Record Fact just displays the date.

Here's an example of removing the date from an Event Start Date Record Fact:

(SELECT CONVERT(date,Events.[Event_Start_Date])) AS [Event Start]