How To: Add Custom Columns to Dashboard Lists using Metadata

The Ed-Fi Dashboards allow developers to add custom columns to Dashboard lists using metadata. This article provides a technical overview of the Configurable List feature of the Ed-Fi Dashboards followed by a detailed walkthrough example.

Technical Overview

Ed-Fi Dashboard allows editing the definitions of the Metadata List IDs (a set of application-specific integer constants) via the database (the Metric.MetadataList table, which is accessed by the DatabaseListMetadataProvider class).

Each MetadataListId identifies a specific set of entities (e.g., Students, Teachers, or Staff) and metric columns (e.g., Grade Level, Absences, or Failing Grades) used to make up a particular List (e.g., General Overview List, Subject Specific List, Student Demographic List) displayed in the Ed-Fi Dashboard UI.

A snippet of the database is shown below:

The List Metadata Provider uses the MetadataListId to retrieve a description of the columns to be displayed by the List, and that description is used by the calling service to build and return the actual List.

This change allows new list types to be configured simply by adding metadata records to the metric.MetadataList, metric.MetadataListColumnGroup, and metric.MetadataListColumn tables. A few light code changes are required to complete the configuration.

As an example of how this appears on the dashboard UI, the following screen capture shows StudentDrilldownHighSchool dashboard view powered by metadata:

Database Scripts

Scripts used the add the tables to the Ed-Fi Dashboards can be found 201607221344 - Dashboard Schema.sql.

Walkthrough Example:
Adding an "Experience" Column to the Teacher List

This walkthrough covers the steps necessary to add a fixed column (i.e., a column that has a specific location in a list) as well as a custom column (i.e., a column in a list that users can reorder) to the EdFiGrid for the Teacher List. The EdFiGrid is common to any entity whether it is a student, a staff member, or a teacher. In this walkthrough, we'll add an "Experience" column. For comparison purposes, we'll implement the same Experience column twice: first as a fixed column and then as a custom column.

In the Dashboard UI, the Teacher List can be found in the Ed-Fi Dashboard in any School section under School Information > Teacher List. The screen capture below shows the Experience column added as a fixed and custom column. The column labeled "Experience" in the gray header bar is the fixed column. The column labeled "Experience" in the dark brown, tabbed header bar is the custom column.

Adding Experience as a Fixed Column

To add the Experience metric value in a fixed column, the high-level steps are:

Detail for each step follows.

Fixed Column Step 1. View the MetadataListColumnGroupIds (Optional)

This optional step is useful to run at least once for background and context. The query in Step 3, below, actually performs the necessary lookup inline, which is why it's optional. Use a SQL SELECT from the MetadataListColumnGroup to get the Column Group IDs for the Teacher List.

SELECT g.* 
FROM 	[EdFi_Dashboard].[metric].[MetadataListColumnGroup] g, 
    	[metric].MetadataList l
WHERE l.Name = 'Teacher'
	AND l.MetadataListId = g.MetadataListId
    AND g.MetadataGroupTypeId = 1

The SQL statement returns the following data:

After running the query, note the following:

MetadataListId = 30 represents the Teacher List.
MetadataGroupTypeId = 1 means it is part of a fixed column.
These are the MetadataListColumnGroupIds that will have a MetadataListColumn called Experience.
Note that there needs be a record in this table for each subject area.

Fixed Column Step 2. Query the MetadataListColumn Table (Optional)

Select from the MetadataListColumn table by joining it to the query above. Similar to the previous step, this is optional and provided here for background and context.

SELECT c.*  
FROM 	[metric].[MetadataListColumnGroup] g,
		[metric].[MetadataListColumn] c,
		[metric].MetadataList l 
WHERE l.Name = 'Teacher'
	AND l.MetadataListId = g.MetadataListId AND g.MetadataGroupTypeId = 1
	AND g.MetadataListColumnGroupId = c.MetadataListColumnGroupId

The SQL statement returns the following data:

The MetadataListColumnGroupIds are 595, 598, 601, 604, 607, and 610. Note that the query results return Staff, E-Mail, and Gender MetadataListColumn rows for each of the MetadataListColumnGroupIds. We need to add records for "Experience" in the MetadataListColumn table for the same MetadataListColumnGroupIds (595, 598, and so on) and each of the MetadataListColumn rows (Staff, E-Mail, and Gender).

Fixed Column Step 3. Insert the Metadata for the New Column

Insert into MetadataListColumn data according to the following table structure and values:

MetadataListColumnId. The script will get the max value from the MetadataListColumnId and add 1 to it.
MetadataListColumnGroupId. This is the Primary Key from the MetadataListColumnGroupId table. In this example case, the query will set the values to be 595, 598, 601, 604, 607, 610.
ColumnName. This is the ColumnName that you see in the gray header bar. We'll use Experience for this example.
ColumnPrefix. This will be NULL for Teacher List Grid.
IsVisibleByDefault. In this example, we set the value to 0, i.e., not visible by default. The user needs to check the checkbox in the view options to see the value in the Teacher List.
IsFixedColumn. Set to 1. This column is important – this is the column that defines a fixed column instead of a custom column (i.e., 1 = fixed, 0 = custom).
MetadataMetricCellListTypeId. Set to 1. This denotes the MetricCellListType, which in this case is None. This is from a set of fixed values (i.e., None = 1, MetricValue = 2, Metric = 3, TrendMetric = 4, AssessmentMetric = 5, StateValueMetric = 6).
MetricVariantId. Set to 0. The value 0 means sort by teachers’ name. In the case of teachers and staff, the MetricVariantId value is used for sorting purposes.
ColumnOrder. Set to 4 for this example. This is the 1-based order for the column within the fixed set of columns. In the as-shipped configuration, Staff = 1, Email = 2, Gender = 3, with Experience as the next column, 4. We get the max column order within the domain/entity/fixed group and add 1 to it.
SortAscending, SortDescending and Tooltip will be set to NULL values similar to the Highly Qualified Teacher column.
UniqueIdentifier. This value should be unique within the Teacher List metadata. For example, if a Teacher List has 5 columns (no matter whether it is fixed or custom columns) each one those columns should have a unique identifier. This unique identifier will be the same for all of the subject areas. Note in the screen shot that the unique identifier is the same for the column "Experience" in all subject areas – but the unique identifier value will be different from staff, email, gender or education.

Fixed Column Script

DECLARE @title AS NVARCHAR(100),
			@isVisibleByDefault AS BIT,  
			@isFixedColumn AS BIT, 
			@metricCellListTypeId as INT, 
			@name as NVARCHAR(100), 
			@columnName as NVARCHAR(100), 
			@columnOrder as INT, 
			@metricVariantId as INT   
 
SELECT @isVisibleByDefault = 0     
			,@isFixedColumn = 1      
			,@name = 'Teacher'     
			,@columnName = 'Experience'     
			,@columnOrder = 1     
			,@metricVariantId = 0   
 
SELECT @metricCellListTypeId = ct.MetadataMetricCellListTypeId  
FROM metric.MetadataMetricCellListType ct  
WHERE Name = 'None'   
 
INSERT INTO [metric].[MetadataListColumn]            
			([MetadataListColumnId]            
			,[MetadataListColumnGroupId]            
			,[ColumnName]            
			,[ColumnPrefix]            
			,[IsVisibleByDefault]            
			,[IsFixedColumn]            
			,[MetadataMetricCellListTypeId]            
			,[MetricVariantId]            
			,[ColumnOrder]            
			,[SortAscending]            
			,[SortDescending]            
			,[Tooltip]            
			,[UniqueIdentifier]) 
 
SELECT  (SELECT MAX(MetadataListColumnId) + t.rownumber FROM metric.MetadataListColumn ) as MetadataListColumnId        
			,t.metadatalistcolumngroupid as MetadataListColumnGroupId          
			,@columnName as ColumnName         
			,NULL as ColumnPrefix         
			,@isVisibleByDefault as IsVisibleByDefault         
			,@isFixedColumn as IsFixedColumn         
			,@metricCellListTypeId as MetadataMetricCellListTypeId         
			,@metricVariantId as MetricVariantId         
			,(SELECT max(c.ColumnOrder ) + 1              
				FROM metric.MetadataListColumnGroup g, metric.metadatalistcolumn c              
				WHERE g.metadatalistid = t.MetadataListId             
				AND g.MetadataListColumnGroupId = c.MetadataListColumnGroupId) as ColumnOrder         
			,NULL as SortAscending         
			,NULL as SortDescending         
			,NULL as Tooltip         
			,(SELECT max(uniqueidentifier) + 1              
				FROM metric.MetadataListColumnGroup g, metric.metadatalistcolumn c              
				WHERE g.metadatalistid = t.MetadataListId             
				AND g.MetadataListColumnGroupId = c.MetadataListColumnGroupId) as UniqueIdentifier 
				FROM (  
					SELECT ROW_NUMBER() OVER (ORDER BY g.MetadataListColumnGroupId ASC) as rownumber      
						,g.MetadataListColumnGroupId       
						,l.MetadataListId  
					FROM   metric.metadatalist l   
					INNER JOIN [metric].[MetadataListColumnGroup] g on g.metadatalistid = l.MetadataListId  
					WHERE l.name = @name  
					AND g.MetadataGroupTypeId = 1 
				) AS t

After the MetadataListColumn insert, the values will look like this:

At this point, the database is prepared to show the Experience as a fixed column in the Teacher List. We need to do one small code change as a final step.

Fixed Column Step 4: Make a Code Change

An if statement needs to be added for Experience in the GridTableHelper.cs file, typically found at: [project root]\Ed-Fi-Dashboard\Dashboard\Application\EdFi.Dashboards.Presentation.Core\Utilities\ExtensionMethods\GridTableHelper.cs.

The highlighted code is required to add the fixed column "Experience" to the Teacher List at runtime.

 				foreach (var metadataColumn in metaDataColumns)
                {
                    if (String.Equals(metadataColumn.ColumnName, "E-Mail", StringComparison.OrdinalIgnoreCase))
                    {
                        row.Add(new EmailCellItem<string> { V = t.EmailAddress, M = t.EmailAddress });
                    }
                    if (String.Equals(metadataColumn.ColumnName, "Date of Birth", StringComparison.OrdinalIgnoreCase))
                    {
                        row.Add(new CellItem<long?>
                        {
                            V = (t.DateOfBirth != null) ? t.DateOfBirth.Value.Ticks : 0,
                            DV = (t.DateOfBirth != null) ? t.DateOfBirth.Value.ToShortDateString() : ""
                        });
                    }
                    if (String.Equals(metadataColumn.ColumnName, "Gender", StringComparison.OrdinalIgnoreCase))
                    {
                        row.Add(new CellItem<string> { V = t.Gender, DV = t.Gender });
                    }
					if (String.Equals(metadataColumn.ColumnName, "Experience", StringComparison.OrdinalIgnoreCase))
                    {
                        row.Add(new YearsOfExperienceCellItem<int?>
                        {
                            V = t.YearsOfPriorProfessionalExperience,
                            Y = t.YearsOfPriorProfessionalExperience
                        });
                    }
                }

Compile and run the application. "Experience" will appear on the Teacher List, similar to the screen shot below.

Adding Experience as a Custom Column

To add the Experience metric value in a custom column, the high-level steps are:

Detail for each step follows.

Custom Column Step 1. Query the MetadataListColumnGroup Table, Note MetadataListID

In the custom column scenario, we need a record for EXPERIENCE in the MetadataListColumnGroup table similar to the EDUCATION entry. Use a SQL SELECT to view the existing metadata for the Teacher list.

SELECT g.*      
FROM [EdFi_Dashboard].[metric].[MetadataListColumnGroup] g, metric.MetadataList l     
WHERE l.Name = 'Teacher'     
AND l.MetadataListId = g.MetadataListId

The SQL statement returns the following data:

After running the query, note that MetadataListId = 30 represents the Teacher List.

Custom Column Step 2. Insert Column Groups Metadata for Experience

After noting the MetadataListId of 30 from the query above, we'll do an insert into metric.MetadataListColumnGroup.

Table Structure:

MetadataListColumnGroupId. The script will get the max number from the MetadataListColumnGroup table and add 1 to it.
MetadataListId. The query will set this to 30, the ID for the Teacher List.
MetadataSubjectAreaId. Get the MetadataSubjectAreaId from metric.MetadataSubjectArea.
Title. We'll set this to EXPERIENCE. By convention, Column Group titles are ALL CAPS. This title will appear in the top of the header tab.
MetadataGroupTypeId. This value tells whether the metadata entry is for a domain/entity or a metric group (i.e., Domain/Entity = 1, Metric = 2).
IsVisibleByDefault. In this example, we'll make this visible by default and set the value to 1.
IsFixedColumnGroup. This example is for a custom column, so we'll set the value to 0.
GroupOrder. The 1-based order for this group. The GroupOrder value should be unique for the MetadataListId within this table.

Script:

DECLARE @title AS NVARCHAR(100), 
		@isVisibleByDefault AS BIT,  
		@isFixedColumnGroup AS BIT, 
		@groupTypeName as NVARCHAR(100),  
		@name as NVARCHAR(100)                
 
SELECT  @title = 'EXPERIENCE'     
		,@isVisibleByDefault = 1     
		,@isFixedColumnGroup = 0         
		,@groupTypeName = 'MetricData'     
		,@name = 'Teacher'     

INSERT INTO [metric].[MetadataListColumnGroup]
           ([MetadataListColumnGroupId]
           ,[MetadataListId]
           ,[MetadataSubjectAreaId]
           ,[Title]
           ,[MetadataGroupTypeId]
           ,[IsVisibleByDefault]
           ,[IsFixedColumnGroup]
           ,[GroupOrder])
SELECT  (SELECT max(g.metadatalistcolumngroupid) FROM metric.MetadataListColumnGroup g) + t2.RowID as MetadataListColumnGroupId        
		,t2.MetadataListId         
		,t2.MetadataSubjectAreaId         
		,@title as Title         
		,t2.MetadataGroupTypeId          
		,@isVisibleByDefault as IsVisibleByDefault         
		,@isFixedColumnGroup as IsFixedColumnGroup         
		,(SELECT max(grouporder) + 1 FROM metric.MetadataListColumnGroup g WHERE g.MetadataListId = 30) GroupOrder 
FROM  
	(     
		SELECT t1.MetadataSubjectAreaId             
				,t1.MetadataGroupTypeId             
				,t1.MetadataListId             
				,ROW_NUMBER() OVER (ORDER BY t1.metadatasubjectareaid) AS RowID     
		FROM     
			(         
				SELECT DISTINCT g.metadatasubjectareaid               
						,t.MetadataGroupTypeId               
						,l.MetadataListId         
				FROM metric.MetadataListColumnGroup g         
				INNER JOIN metric.MetadataGroupType t ON 1 = 1         
				INNER JOIN metric.metadatalist l ON 1 = 1         
				WHERE t.name = @groupTypeName         
				AND l.Name = @name     
			) t1 
	) t2

As with the fixed example, we'll insert one record for each subject area.

After the insert, the new records in the MetadataListColumnGroup table will look similar to the following:

Custom Column Step 3. Insert Column Metadata into the MetadataListColumn Table

We'll insert metadata into the MetadataListColumn table according to the following structures and values:

MetadataListColumnId. The script will get the max value from the table and add 1 to it.
MetadataListColumnGroupId. The Primary Key from the MetadataListColumnGroupId table inserted previously.
ColumnName. In this example, we'll use Experience. This will be the column heading shown under the group heading.
ColumnPrefix. This will be NULL for the the Teacher List.
IsVisibleByDefault. In this example, we'll set it to 0, hiding the column by default.
IsFixedColumn. Set to 0. This column is important – this is the column that defines a fixed column instead of a custom column (i.e., 1 = fixed, 0 = custom).
MetadataMetricCellListTypeId. Set to 1. This denotes the MetricCellListType which in this case is None. This is from a set of fixed values (i.e., None = 1, MetricValue = 2, Metric = 3, TrendMetric = 4, AssessmentMetric = 5, StateValueMetric = 6).
MetricVariantId. Set to 0. The value 0 means sort by teachers’ name. In the case of teachers and staff, the MetricVariantId value is used for sorting purposes.
ColumnOrder. Set to 1 for this example. This is the order for the column within the EXPERIENCE group. Since this is the only column under the group EXPERIENCE it should have a value of 1.
SortAscending, SortDescending and Tooltip will have NULL values similar to the Highly Qualified Teacher column.
UniqueIdentifier. This value should be unique for each MetaDataListColumn. For this example, we use 9. Note that If there are duplicates in the unique identifier column for different groups (e.g., for Highly Qualified Teacher and Experience) then the visibility of the columns will be mirrored when selected and unselected for display in the UI. Make sure the unique identifier for Experience column for all the subject areas is the same.

Custom Column Script

DECLARE @title AS NVARCHAR(100), 
		@isVisibleByDefault AS BIT,  
		@isFixedColumn AS BIT, 
		@metricCellListTypeId as INT, 
		@name as NVARCHAR(100), 
		@columnName as NVARCHAR(100), 
		@columnOrder as INT, 
		@metricVariantId as INT   
 
SELECT  @title = 'EXPERIENCE'     
		,@isVisibleByDefault = 0     
		,@isFixedColumn = 0      
		,@name = 'Teacher'     
		,@columnName = 'Experience'     
		,@columnOrder = 1     
		,@metricVariantId = 0   
 
SELECT @metricCellListTypeId = ct.MetadataMetricCellListTypeId  
FROM metric.MetadataMetricCellListType ct  
WHERE Name = 'None'   
 
INSERT INTO [metric].[MetadataListColumn]            
			([MetadataListColumnId]            
			,[MetadataListColumnGroupId]            
			,[ColumnName]            
			,[ColumnPrefix]            
			,[IsVisibleByDefault]            
			,[IsFixedColumn]            
			,[MetadataMetricCellListTypeId]            
			,[MetricVariantId]            
			,[ColumnOrder]            
			,[SortAscending]            
			,[SortDescending]            
			,[Tooltip]            
			,[UniqueIdentifier]) 
 
SELECT  (SELECT MAX(MetadataListColumnId) + t.rownumber FROM metric.MetadataListColumn ) as MetadataListColumnId         ,t.metadatalistcolumngroupid as MetadataListColumnGroupId          
		,@columnName as ColumnName         
		,NULL as ColumnPrefix         
		,@isVisibleByDefault as IsVisibleByDefault         
		,@isFixedColumn as IsFixedColumn         
		,@metricCellListTypeId as MetadataMetricCellListTypeId         
		,@metricVariantId as MetricVariantId         
		,@columnOrder as ColumnOrder -- This is going to be 1 because this is the first time that we are inserting into the metadatalistcolumn table for Experience         
		,NULL as SortAscending         
		,NULL as SortDescending         
		,NULL as Tooltip         
		,(SELECT max(uniqueidentifier) + 1 as UniqueIdentifier 
			FROM metric.MetadataListColumnGroup g, metric.metadatalistcolumn c  
			WHERE g.metadatalistid = t.MetadataListId 
			AND g.MetadataListColumnGroupId = c.MetadataListColumnGroupId) 
FROM 
	(  
		SELECT ROW_NUMBER() OVER (ORDER BY g.MetadataListColumnGroupId ASC) as rownumber      
		,g.MetadataListColumnGroupId       
		,l.MetadataListId  
		FROM   metric.metadatalist l
		INNER JOIN [metric].[MetadataListColumnGroup] g on g.metadatalistid = l.MetadataListId  
		WHERE l.name = @name  AND g.MetadataGroupTypeId = 2  AND UPPER(g.Title) = @title ) AS t

After the MetadataListColumn insert:

At this point, the database is prepared to show the Experience column as a custom column in the Teacher List. We need to do one small code change as a final step.

Custom Column Step 4: Make a Code Change

The code already exists in the GenerateRows(this List<MetadataColumnGroup> metadataColumnGroups, List<TeachersModel.Teacher> teachers, int schoolId) function in the GridTableHelper.cs class located in the [project root]\Ed-Fi-Dashboard\Dashboard\Application\EdFi.Dashboards.Presentation.Core\Utilities\ExtensionMethods directory. Placing the GetExperienceColumnGroup method call line of code before the GetEducationColumnGroup method call line of code will enable the Experience column values to appear on the Teacher List.

 
                GetExperienceColumnGroup(metadataColumnGroups, row, t);

                GetEducationColumnGroup(metadataColumnGroups, row, t);

                rows.Add(row);
            }
            return rows;
        }

        	private static void GetExperienceColumnGroup(
            List<MetadataColumnGroup> metadataColumnGroups, List<object> row, TeachersModel.Teacher teacher)
        {
            List<MetadataColumn> metaDataColumns = 
                metadataColumnGroups.Any(x => x.Title.Equals("EXPERIENCE", StringComparison.InvariantCultureIgnoreCase))
                ? metadataColumnGroups.First(x => x.Title.Equals("EXPERIENCE", StringComparison.InvariantCultureIgnoreCase)).Columns
                : new List<MetadataColumn>();

            if (metaDataColumns.Count > 0)
                //Spacer
                row.Add(new SpacerCellItem<double> {DV = "", V = 0});

            foreach (var metadataColumn in metaDataColumns)
            {
                if (String.Equals(metadataColumn.ColumnName, "Experience", StringComparison.OrdinalIgnoreCase))
                {
                    row.Add(new YearsOfExperienceCellItem<int?>
                    {
                        V = teacher.YearsOfPriorProfessionalExperience,
                        Y = teacher.YearsOfPriorProfessionalExperience
                    });
                }
            }
        }

Compile and run the application. The page should show "Experience" as a custom column after the Education group.

Technical Overview

Database Scripts

Walkthrough Example: Adding an "Experience" Column to the Teacher List

Adding Experience as a Fixed Column

Fixed Column Step 1. View the MetadataListColumnGroupIds (Optional)

Fixed Column Step 2. Query the MetadataListColumn Table (Optional)

Fixed Column Step 3. Insert the Metadata for the New Column

Fixed Column Step 4: Make a Code Change

Adding Experience as a Custom Column

Custom Column Step 1. Query the MetadataListColumnGroup Table, Note MetadataListID

Custom Column Step 2. Insert Column Groups Metadata for Experience

Custom Column Step 3. Insert Column Metadata into the MetadataListColumn Table

Custom Column Step 4: Make a Code Change

Walkthrough Example:
Adding an "Experience" Column to the Teacher List