0

How do I filter cell action list results to only show latest active term per student?

How do I filter cell action list results to only show latest active term per student?

Purpose of report: Provide list of students who have not graduated from a particular plan and are not currently enrolled. In other words, students who were at one time active in this plan but have since fallen off the map and never graduated. Will need address, email, ID, admit term and units completed for each students (which is currently provided by the cell action list).

I’ve created a report in the Student Term perspective that shows all students in the specific plan but only includes those who have not graduated. I purposely did not use “Term” so that the report would not be limited to a particular term and instead would include all students within that plan (regardless of term they were active).

 

Slicers:

Plan = LIBS-BA

Measures = Distinct Students

Row:

Student =

 

Column:

Months to Bachelor Degree Six Year Band = No Bachelor yet

Program Status for Plan = Active in Program (AC)

 

It’s technically provided me with the list I’m looking for but I still need the address, email, admit term and units completed for each student listed.

 

This is where I use cell action list to retrieve this information:

 

BUT, since I didn’t use “Term”, it gives me one row for every term the student was active. In many cases, I end up with over 10 rows per students because they were active all of those terms. I only want to see the latest active term for each student so that there is only 1 row per distinct student.

 

My questions are:

  1. How can I adjust the report so that only the students’ latest active term appears on the cell action list?

  2. How can I adjust the report to exclude students who are currently enrolled (Fall 2017)?

 

All ideas are welcome and appreciated!

Thank you,

Ariana

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Do you need to have the requirement so that you drill down?  It might be easier to just show the extra properties on the front of your report.  Some OLAP tools such as Excel will allow you to right-click in the pivot table and show additional, related member properties.  BI Office does not allow this, but you can accomplish the same thing with calculated measures.  Here is an example of the MDX to create a calculated measure to bring in the Address:

    [Student].[Student].CURRENTMEMBER.PROPERTIES("Address"),
    
    NON_EMPTY_BEHAVIOR = [Measures].[Distinct Students]
    Reply Like
  • Thanks, Tim! I've tried altering the current MDX below to create the calculated measure but I've had no luck. Could you provide guidance as to where to add the calculated measure?

     

    WITH
    /*- Qv6.30.3460.1915 -*/

    SELECT 
    NON EMPTY
    {
      { [Months to Bachelor Degree Band].[Months to Bachelor Degree Six Year Band].[Bachelor Earned Indicator].&[No Bachelor Yet] }
      *  { [Program Status for Plan].[Program Status for Plan].[Program Status for Plan].&[Active in Program (AC)] } 
    }
    PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE

     ON COLUMNS,
    NON EMPTY
    {
       HIERARCHIZE( DISTINCT ( { [Student].[Student].[All Students] , [Student].[Student].[Student].members } ) ) 
    }
    PROPERTIES PARENT_UNIQUE_NAME,  MEMBER_KEY, MEMBER_TYPE

     ON ROWS

    FROM [Student Term] 
    WHERE ( [Plan].[Plan].[Career].&[Undergraduate].&[No Default Academic Program (-----)].&[Liberal Studies Ukiah (BA) (LIBS-BA)] , [Measures].[Distinct Students] )
    CELL PROPERTIES FORMATTED_VALUE, VALUE, FONT_NAME

    Thank you!

    Ariana

    Reply Like
  • Oh, sorry about that.  Yeah, you're looking at the MDX editor for the final report.  I've come to consider that view more informational than anything.  Any changes that you make there will then be overwritten when you slice and dice in your report.

    To create a custom calc in Pyramid, click on the Analytics button on the ribbon --> Custom Items --> New Blank Customer Member

     

    You will then be presented with a dialog to create your measure.  My bad, you put the NON EMPTY BEHAVIOR in the drop down provided.  You can then select this calculated measure in your columns, along with your actual measure.

    Reply Like
reply to topic
Like Follow
  • Status Answered
  • 11 days agoLast active
  • 3Replies
  • 173Views
  • 2 Following