nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Create Org Unit with Higherarchy Info in Analytics Builder

Katie.B.1305

Hello,

I am hoping to get some help on how to create something in Analytics Builder (Amazon Quicksights) where I can have all course offering org units as rows with their semester, department/program, and school parts of the hierarchy displayed as columns.

In a simple form, this is what essentially I would want for the fields across the top:

CourseOfferingOrgUnitID, SemesterOrgUnitID, Department/ProgramOrgUnitID, SchoolOrgUnitID

What I have right now is more like:

OrgUnitID, ParentOrgUnitID, Parent Type
OrgUnitID, ParentOrgUnitID, Parent Type
OrgUnitID, ParentOrgUnitID, Parent Type

Does anyone know how in Analytics Builder to write a custom formula to pivot this information into rows or otherwise how to achieve what I am looking to do?

Thanks,

Katie Bush

Encuentra más publicaciones etiquetadas con

Comentarios

Carolyn.V.387

Hi Katie, I use temp tables to accomplish this (which may not be the prettiest/most elegant, but it works for our needs). I'm happy to share it if a framework is helpful to get you started! Feel free to shoot me a message with your email and I can send you the details.

Katie.B.1305

@Carolyn.V.387 that would be helpful. I’ll message you.

Jennifer.M.128

Screenshot 2025-04-25 at 8.53.18 AM.png

(Attached is a screenshot so that you can view it formatted; below is text to copy/paste. This actually takes the names instead of the orgunitids, though the same logic could be used.)

WITH Semester As (SELECT OU.name as Course, OUS.name as SemesterFROM brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunitancestors_9_16_4 OUA on OU.orgunitid = OUA.orgunitidINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUS on OUS.orgunitid = OUA.ancestororgunitidWHERE OU.type = 'Course Offering' AND OUS.type = 'Semester'),
Department As ( SELECT OU.name as Course, OUD.name as Department FROM brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OU INNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunitancestors_9_16_4 OUA on OU.orgunitid = OUA.orgunitid INNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUD on OUD.orgunitid = OUA.ancestororgunitid WHERE OU.type = 'Course Offering' AND OUD.type = 'Department')
SELECT OU.name as Course, OUM.name as Modality, CONCAT(SUBSTRING(OU.code,1,6),SUBSTRING(OU.code,11,16)) as Term_CRN, S.Semester, D.DepartmentFROM brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunitancestors_9_16_4 OUA on OU.orgunitid = OUA.orgunitidINNER JOIN brightspace_data_sets_5d0ac761_c893_4b1a_b2fe_d6ec1e1e454a.organizationalunits_9_16_4 OUM on OUM.orgunitid = OUA.ancestororgunitidINNER JOIN Semester S on OU.name = S.CourseINNER JOIN Department D on OU.name = D.CourseWHERE OU.type = 'Course Offering' AND OUM.type = 'Modality'

Katie.B.1305

@Jennifer.M.128 thank you so much for this. I'll give it a whirl along with what Carolyn sends.

Enlaces rápidos

Categorías

View all categories