SQL Server View Based Pages
This section explains the concept of SQL Server View Based Pages, including advantages and use cases, how to choose the right approach, and creating read-only pages.
A Read Only Page is a page based on a SQL Server Database View. This is in contrast to most pages in MinistryPlatform that are based on SQL Server Tables. Because they are based on SQL Server views, you can't edit the data that you see on these pages. For security roles, you can only grant Read rights to these pages.
Note: Want to create a Read Only Page? Sometimes that's a great idea! And sometimes, not so much. Keep reading to learn how to discern the difference. As always, you must first identify the problem you want to solve before you can determine the solution method. We strongly recommend working with our Professional Services team to determine the right approach for your unique goals.
Advantages & Use Cases
- Read Only Pages can have "summary" fields right on the form. So, you can calculate a value like "total giving last year" in real time and put it on the form.
- Read Only Pages can pull data into the summary fields from across the entire database. We can have a value like "salvation date" from a milestone right below a field titled "first attended" and followed by a field called "first joined a group." These three fields might create a clear picture of someone's progression, which you often want in a summary dataset.
- You can use summary fields on Read Only Pages in charts on that page. Charts cannot be based upon "correlated subqueries," which are often used in views on other pages to summarize data.
- You can use summary fields on Read Only Pages in mail merge emails if contact_ID_Field is populated.
- If created correctly, you can use existing tools and reports if Contact_ID_Field is populated.
- If created correctly, you can use security roles and global filters (Congregation Filters) if Global_Filter_ID_Field is populated.
- Read Only Pages can create "virtual records." For example, if you wanted a table with a row per month and facts about that month, you don't need a database table containing months, and you can summarize facts in any table with a date by month. The selections on such a Read Only Page may be unreliable.
- You can consume data from Read Only Pages as read-only data from the REST API.
- Read Only Pages that contain the proper foreign keys can have tabs (sub-pages) you can edit. While you can't edit the page itself, you could edit various things in tabs.
- Read Only Pages can have foreign keys from a variety of tables which means you can put tabs not normally found together under the same open record from a Read Only Page.
- Another major case is if you want to merge two datasets with use of a Common Table Expression (CTE). For example, analyzing someone's past history as a servant or leader. We need to summarize and often join a particular dataset with "N" rows per person, but the actual Read Only Page should only display one row per person. So, you can answer questions like "have they served, how long since, where, how often" and so on in advanced searches that are simple for people to use. You can reuse correlated subqueries and custom SQL in view clauses as they move one layer deeper in the stack.
- You can organize data on the form according to the needs of the audience of that Read Only Page. Making someone use a filtered page means they still currently see all fields in the same order as the page it is based upon.
- The singles minister wants to understand how singles are involved at the church so he can communicate with them strategically. He has ten questions about their involvement (serving, giving, in groups, has kids, and so on). We can deliver a view of just active single adults with just the information he wants and serve up just the charts he cares about.
- The membership department needs a lot of summary data about the church population by age groups. While we could deliver this as a report, we can use a Read Only Page to use the resulting dataset for charts. It also ensures we can base the dataset we use for any reports for the same underlying view.
- You need to restrict a third party writing an application who uses the API to a subset of donor data. The subset must not include donor-identifying information or donations outside their scope. A Read Only Page can limit the data access at the source rather than doing this through a stored procedure. This way, the church can see the data from within MinistryPlatform that they're extracting. That increases confidence and surfaces issues.
- A consultant encourages a church to write a complex nightly routine to snapshot data into a redundant table structure to merge facts about people into one page. A Read Only Page based on a SQL View can eliminate this heavy operation nightly and make data real time as opposed to 24-hour delay.
- The advanced search will not have folders you can expand to get to other fields on other tables. This is because views do not have foreign keys defined even if the foreign key's value is present. So all fields you want people to see must be on the form.
- The transfer icon will not be available (but the Transfer Selection Tool might be).
- You can make a Read Only Page where selections are reliable from one session to another, but it is also possible that selections might not be reliable. They will be reliable if the person who wrote the SQL Server View ensured there is a unique primary key per record and that records which qualify to be in the SQL View always have the same primary key.
Choosing the Right Approach
The Platform offers a lot of solutions for working with data:
- The Page
- The Page View (saved search in the Platform)
- The Filtered Page
- The Read Only Page (based on SQL Server views)
You can pair one or more of these with routines, snapshots, and/or charts.
Tip: Knowing which solution to use and when to use it is a technical consideration. We strongly recommend working with Professional Services to determine the right approach for your unique goals.
Creating Read Only Pages
So you've carefully considered all of your options, the pros and cons, and decided that a Read Only Page is the way to go. Here's what you need to know for this adventure!
The following are required in your view:
- Your Read Only Page must have a primary key.
- The view must begin with the letters "vw".
- The first field must be INT and, if possible, unique. This serves as the Primary Key Field. If this if not reliably the same from one session to another, you should not rely on any selections made or saved in the view.
- Domain_ID must be in your view or you can't set up security roles to read this page.
If the rows are about people:
- Make sure the view returns the Contact_ID field.
- Make sure the view returns the Congregation_ID field.
- Make sure that if the results are "one row per person," you use Contact_ID as the primary key (first field).
If you want users to be able to create a view on the Read Only Page, create the view keys.