Overview

Project goal is to set up a view on the homepage which lists the queries to which a user has been granted direct permission (via User or Role).

To accomplish this, a custom portal is used which includes a custom SQL query on the query base (a query of queries) to display information about the queries depending on the logged in user viewing the homepage. This portal is then embedded into a Report, which is configured to display on the homepage.

For TUSMGP, the priority for these portals is that ultimately access to queries can be mediated by two elements: role and realm. The role is assigned to individual users depending on their job responsibilities, and the realm is assigned to each query depending on which program data the query is pulling. By assigning direct access to queries via role (instead of individual user) as more queries are needed and staff change, each query does not need to be individually modified for new/different users.

Portal

Depending on your business process, create at least one custom portal. If you wish to limit the results displayed on the homepage by user identity (user or role) and realm, you will need to create one portal for each realm. (It occurs to me as I’m writing this that possibly this could be done by adding realm data to the query and then using liquid markup or some other conditional display coding / alternate views to keep this all within one portal, but I don’t know how exactly to do that and need to finish this, so we’re going with multiple portals).

Using the suitcase import above will create one custom portal and one report within your instance. Below are details about the portal configurations.

Portal Details

The scope for the portal should be set to User.

The security for the portal should be set to User.

Ensure portal status is set to active when ready to use.

Query

Below is the custom SQL included in the query in the portal. This SQL connects the query table to the access and realm tables to filter the results by metadata about the query (folder, archived status), the permissions settings (access), and the realm assigned to the query.

You may wish to modify the WHERE clauses to customize what queries you do or do not want included using the metadata (e.g., it currently excludes archived queries and queries in the Query Library system folder).

If not using realms, you will need to remove the SQL for the left outer join to the realm table and the WHERE condition filtering to realm by GUID. If you are using realms, replace the GUID in the WHERE clause with the realm you want to display in this portal/report/homepage.

select qry.[id] as [guid], 
qry.[name] as [name], 
format(try_convert(date, qry.[updated]), 'MM/dd/yyyy') as [updated],
convert(nvarchar(max), qry.[description].query('//text()')) as [description]
from [query] qry 
left join [access] acc on (acc.[entity] = qry.[id])
left outer join [realm] rlm on (rlm.[id] = qry.[realm])
where 
(
	(isnull(qry.[folder], '') != 'Temp') 
	and 
	(acc.[entity_type] = 'query') 
	and 
	(qry.[archived] in ('0'))
	and
	(qry.[folder1] != 'SYS:Library') 
	and
	-- EDIT REALM GUID TO FILTER TO SPECIFIC REALM
	(rlm.[id] = '9bbfc091-449d-43fc-b501-62704b2c4a4c')
		and
	(
		(acc.[grantee_user] = @identity)
		or 
		(acc.[grantee_role] in (select [role] from [role.user] where [user] = @identity))
	)
)
order by qry.[name]

select 
    qry.[id] as [guid], 
    qry.[name] as [name], 
    format(try_convert(date, qry.[updated]), 'MM/dd/yyyy') as [updated],
    convert(nvarchar(max), qry.[description].query('//text()')) as [description]
from 
    [query] qry 
    left join [access] acc on (acc.[entity] = qry.[id])
    left outer join [realm] rlm on (rlm.[id] = qry.[realm])
where 
    (
        (isnull(qry.[folder], '') != 'Temp') 
        and 
        (acc.[entity_type] = 'query') 
        and 
        (qry.[archived] in ('0'))
        and
        (qry.[folder1] != 'SYS:Library') 
        and
        -- EDIT REALM GUID TO FILTER TO SPECIFIC REALM
        (rlm.[id] = '9bbfc091-449d-43fc-b501-62704b2c4a4c')
        and
        (
            (acc.[grantee_user] = @identity)
            or 
            (acc.[grantee_role] in (select [role] from [role.user] where [user] = @identity))
        )
    )
order by qry.[name];

View

The view uses liquid markup looping and a table element to display the list of queries returned by the portal query.

There is custom CSS code added to override the standard base.css code included in Slate’s UX, which would otherwise style the table to prevent text wrapping.

The table also has class elements added to it to allow for it to be both sortable and searchable.

The liquid markup identifes the node assigned to the query (r_list) and uses this variable to display the content. First, it checks to see if r_list is greater than 0. If it is, then the table will display. If it isn’t, a message that says “No queries have been shared with you.” displays.

Within the table, there is liquid markup looping to cycle through each element (updated, GUID, name, description) which was identified in the SELECT statement in the SQL for each variable r (or row/result).

Effectively, this will display each result (query) returned in the results in one row of the table, for as many results there are for each user.

The data that displays in the table can be modified by editing the HTML and the custom query SELECT statement if elements need to be added or removed depending on your business purpose.

Method

The method is simple and should be setup as follows:

View should be set to use whatever you named your view in the above step (if you changed the name).

Link the custom query to the view if it is not yet done.

Report

If you use the suitcase code provided, you will not need to build the report as it should be included. But if you need multiple homepage “views” or “tabs” you will need to create additional.

Once you have built the custom portal, ensure it is set to active and then select Embed Portal, change to allow embedding, and copy the code snippet generated.

Go to Queries > Reports and create new report.

Name the new report what you want to display on the tab of the homepage.

Assign the report to a realm if you need to restrict visibility by realm.

Save the report to a folder named System and then a subfolder named Homepage. (This is how Slate knows to display it on the homepage; if not saved here it will not work).

Edit the report and add a part. Edit the part to add a Data/Charts, then add a Static Content block. Within the static content block, open the source code and paste in the code provided to embed the portal. Save everything.

Return to the edit screen and open Edit Permissions. Add a grantee for each role which should view this report on the homepage.

Configurations for Shared Queries

After building the portal and homepage report as described here, to ensure that directly permissioned queries appear appropriately to each user, make sure to do the following to each query that is shared:

1. Ensure it is in a realm which matches the filtering done in the custom SQL in the portal. (i.e., if your custom SQL filters to “MBS” realm but your query isn’t in a realm or is in a different realm than MBS, it will not show in the list).

2. Edit the query and open the Edit Permissions tool. Add a grantee using the appropriate role.

3. Ensure the query is set as “not shared.”

Throughout the cycle it is also advisable to review queries assigned and archive any queries which are no longer in use or relevant to ensure the list per user does not become too long.