13 Replies Latest reply: Jun 24, 2016 9:36 AM by Lauren Mills RSS

    Drill-down Column Header (shows as expression)

    Lauren Mills

      Hello,

       

      I need to know how to change the Header of a Column that is a Drill-down (see picture below). Its shows my logic but I want to be able to assign it a name of what its showing. Can someone walk me through this edit please?

       

        • Re: Drill-down Column Header (shows as expression)
          Gabor Tarnoczai

          Hi Lauren,

           

          You can't change the labels of drill-down dimension levels,

          try define these levels at data load editor and add a fancy alias

           

          G.

            • Re: Drill-down Column Header (shows as expression)
              Lauren Mills

              Do you have advice on how to do that? This logic includes multiple columns of data and I can't rename them. How would I identify where this logic is placed in the Data Load Editor and how would I assign it an alias (expression wise or the verbiage used to write it)?

               

              Thanks for your reply!!

               

              Lauren

                • Re: Drill-down Column Header (shows as expression)
                  Gabor Tarnoczai

                  Could you describe/explain what kind of data do you have?

                  Especially the hierarchy levels.

                   

                  G.

                    • Re: Drill-down Column Header (shows as expression)
                      Lauren Mills

                      I just want to label the picture above as "Student ID" and not the expression itself.

                        • Re: Drill-down Column Header (shows as expression)
                          Gabor Tarnoczai

                          Ok, Based on your attached image the script will look like that:

                           

                          YourTable:

                          Load

                               ...

                              ,if("LEVEL 1 COMPLETION" = 'N' or LEVEL 1 COMPLETION" = 'L' LEVEL 1 COMPLETION" = 'S', SID) as LabelYouWant

                          From [InputTable];

                           

                          You can use match() instead of multilevel if:

                           

                          YourTable:

                          Load

                               ...

                              ,if(match("LEVEL 1 COMPLETION",'N','L','S'),SID) as LabelYouWant

                          From [InputTable];


                          Hope this helps.


                          G.

                            • Re: Drill-down Column Header (shows as expression)
                              Lauren Mills

                              So what you're saying is I would add this to my auto-generated script section?

                                • Re: Drill-down Column Header (shows as expression)
                                  Gabor Tarnoczai

                                  Yes, exactly.

                                   

                                  ,if(match("LEVEL 1 COMPLETION",'N','L','S'),SID) as LabelYouWant


                                  Or


                                     ,if("LEVEL 1 COMPLETION" = 'N' or LEVEL 1 COMPLETION" = 'L' LEVEL 1 COMPLETION" = 'S', SID) as LabelYouWant

                                    • Re: Drill-down Column Header (shows as expression)
                                      Lauren Mills

                                      Okay, sorry to be a pain.... this is one of my script: where would I put it?

                                       

                                      [Student Detail ]:
                                      LOAD
                                      [BC] AS [BC],
                                      [Dist] AS [Dist],
                                      [Dealer
                                      Code] AS [Dealer Code],
                                      [Dealer Name] AS [Dealer Name],
                                      [Phone] AS [Phone],
                                      [Address Line 1] AS [Address Line 1],
                                      [City] AS [City],
                                      [St] AS [St],
                                      [Zip Code] AS [Zip Code],
                                      [Planning
                                      Potential
                                      Size] AS [Planning Potential Size],
                                      [Sales
                                      Group
                                      Size] AS [SalesGroup Size],
                                      [Franch] AS [Franch],
                                      [BusinessLink] AS [Business Link],
                                      [SID] AS [SID],
                                      [First Name] AS [First Name],
                                      [Last Name] AS [Last Name],
                                      [Position
                                      Code] AS [Position Code],
                                      [Position
                                      Code
                                      Start Date] AS [PositionCode Start Date],
                                      [F21] AS [F21],
                                      [Email] AS [Email],
                                      [Tenure] AS [Tenure],
                                      [2015 Cert Status] AS [2015 Cert Status],
                                      [Level 1 - Product Knowledge - Fundamental Towing & Job Rating with iShowroom (SSJR03WB)] AS [Level 1 - Product Knowledge - Fundamental Towing & Job Rating with iShowroom (SSJR03WB)],
                                      [Level 1 - Product Knowledge - Advanced Job Rating with iShowroom (SSJR04WB)] AS [Level 1 - Product Knowledge - Advanced Job Rating with iShowroom (SSJR04WB)],
                                      [Level 1 - Product Knowledge - Working with Upfitters (SSWWUPWB)] AS [Level 1 - Product Knowledge - Working with Upfitters (SSWWUPWB)],
                                      [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)] AS [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)],
                                      [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)] AS [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)],
                                      [LEVEL 1 COMPLETION] AS [LEVEL 1 COMPLETION],
                                      [Level 2 - Ram Exam (RAMEXAM1)] AS [Level 2 - Ram Exam (RAMEXAM1)],
                                      [LEVEL 2 COMPLETION] AS [LEVEL 2 COMPLETION],
                                      [Early Adopter Status] AS [Early Adopter Status],
                                      [YTD
                                      Sales
                                      Total] AS [YTDSales Total],
                                      [2016
                                      Cert
                                      Status] AS [2016Cert Status],
                                      [Retail Perf
                                      Specialist] AS [Retail Perf
                                      Specialist],
                                      [Product
                                      Specialist] AS [Product
                                      Specialist],
                                      [Regional Training Manager] AS [Regional Training Manager];
                                      LOAD
                                      [BC],
                                      [Dist],
                                      [Dealer
                                      Code],
                                      [Dealer Name],
                                      [Phone],
                                      [Address Line 1],
                                      [City],
                                      [St],
                                      [Zip Code],
                                      [Planning
                                      Potential
                                      Size],
                                      [Sales
                                      Group
                                      Size],
                                      [Franch],
                                      [BusinessLink],
                                      [SID],
                                      [First Name],
                                      [Last Name],
                                      [Position
                                      Code],
                                      [Position
                                      Code
                                      Start Date],
                                      [F21],
                                      [Email],
                                      [Tenure],
                                      [2015 Cert Status],
                                      [Level 1 - Product Knowledge - Fundamental Towing & Job Rating with iShowroom (SSJR03WB)],
                                      [Level 1 - Product Knowledge - Advanced Job Rating with iShowroom (SSJR04WB)],
                                      [Level 1 - Product Knowledge - Working with Upfitters (SSWWUPWB)],
                                      [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)],
                                      [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)],
                                      [LEVEL 1 COMPLETION],
                                      [Level 2 - Ram Exam (RAMEXAM1)],
                                      [LEVEL 2 COMPLETION],
                                      [Early Adopter Status],
                                      [YTD
                                      Sales
                                      Total],
                                      [2016
                                      Cert
                                      Status],
                                      [Retail Perf
                                      Specialist],
                                      [Product
                                      Specialist],
                                      [Regional Training Manager]
                                      FROM [lib://Ram Expert/Ram Expert Data SET.xls]
                                      (biff, embedded labels, table is [Student Detail$]);


                                      [Summary Detail]:
                                      LOAD
                                      [BC] AS [BC],
                                      [Dealer Count] AS [Dealer Count],
                                      [Student Count] AS [Student Count],
                                      [Dealer Completion Count] AS [Level 1 - Dealer Completion Count],
                                      [% of Dealer Completed] AS [Level 1 - % of Dealer Completed],
                                      [Student Completion Count] AS [Level 1 - Student Completion Count],
                                      [% of Student Completed] AS [Level 1 - % of Student Completed],
                                      [Dealer Completion Count1] AS [Level 2 - Dealer Completion Count],
                                      [% of Dealer Completed1] AS [Level 2 - % of Dealer Completed],
                                      [Student Completion Count1] AS [Level 2 - Student Completion Count],
                                      [% of Student Completed1] AS [Level 2 -  % of Student Completed],
                                      [Dealer Completion Count2] AS [Early Adopter Dealer Completion Count2],
                                      [% of Dealer Completed2] AS [Early Adopter - % of Dealer Completed],
                                      [Student Completion Count2] AS [Early Adopter -  Student Completion Count2],
                                      [% of Student Completed2] AS [Early Adopter -  % of Student Completed];
                                      LOAD
                                      [BC],
                                      [Dealer Count],
                                      [Student Count],
                                      [Dealer Completion Count],
                                      [% of Dealer Completed],
                                      [Student Completion Count],
                                      [% of Student Completed],
                                      [Dealer Completion Count1],
                                      [% of Dealer Completed1],
                                      [Student Completion Count1],
                                      [% of Student Completed1],
                                      [Dealer Completion Count2],
                                      [% of Dealer Completed2],
                                      [Student Completion Count2],
                                      [% of Student Completed2]
                                      FROM [lib://Ram Expert/Ram Expert Data SET.xls]
                                      (biff, embedded labels, header is 3 lines, table is [Summary Detail$]);


                                      [autoCalendar]:
                                        DECLARE FIELD DEFINITION Tagged ('$date')
                                      FIELDS
                                        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
                                        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),
                                        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),
                                        Month($1) AS [Month] Tagged ('$month'),
                                        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),
                                        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),
                                        Date(Floor($1)) AS [Date] Tagged ('$date');

                                      DERIVE FIELDS FROM FIELDS [PositionCode Start Date] USING [autoCalendar] ;

                                        • Re: Drill-down Column Header (shows as expression)
                                          Jonathan Dienst

                                          [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)] AS [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)],

                                          [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)] AS [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)],

                                          [LEVEL 1 COMPLETION] AS [LEVEL 1 COMPLETION],

                                          if(match([LEVEL 1 COMPLETION], 'N', 'L', 'S'), SID) as LabelYouWant,

                                          [Level 2 - Ram Exam (RAMEXAM1)] AS [Level 2 - Ram Exam (RAMEXAM1)],

                                          [LEVEL 2 COMPLETION] AS [LEVEL 2 COMPLETION],

                                          [Early Adopter Status] AS [Early Adopter Status],

                                          • Re: Drill-down Column Header (shows as expression)
                                            Gabor Tarnoczai

                                            Hi,

                                             

                                            The Level 1 completion occurs in Student Detail table. Try this:

                                             

                                             

                                            [Student Detail ]:

                                            LOAD

                                            [BC] AS [BC],

                                            [Dist] AS [Dist],

                                            [Dealer

                                            Code] AS [Dealer Code],

                                            [Dealer Name] AS [Dealer Name],

                                            [Phone] AS [Phone],

                                            [Address Line 1] AS [Address Line 1],

                                            [City] AS [City],

                                            [St] AS [St],

                                            [Zip Code] AS [Zip Code],

                                            [Planning

                                            Potential

                                            Size] AS [Planning Potential Size],

                                            [Sales

                                            Group

                                            Size] AS [SalesGroup Size],

                                            [Franch] AS [Franch],

                                            [BusinessLink] AS [Business Link],

                                            [SID] AS [SID],

                                            [First Name] AS [First Name],

                                            [Last Name] AS [Last Name],

                                            [Position

                                            Code] AS [Position Code],

                                            [Position

                                            Code

                                            Start Date] AS [PositionCode Start Date],

                                            [F21] AS [F21],

                                            [Email] AS [Email],

                                            [Tenure] AS [Tenure],

                                            [2015 Cert Status] AS [2015 Cert Status],

                                            [Level 1 - Product Knowledge - Fundamental Towing & Job Rating with iShowroom (SSJR03WB)] AS [Level 1 - Product Knowledge - Fundamental Towing & Job Rating with iShowroom (SSJR03WB)],

                                            [Level 1 - Product Knowledge - Advanced Job Rating with iShowroom (SSJR04WB)] AS [Level 1 - Product Knowledge - Advanced Job Rating with iShowroom (SSJR04WB)],

                                            [Level 1 - Product Knowledge - Working with Upfitters (SSWWUPWB)] AS [Level 1 - Product Knowledge - Working with Upfitters (SSWWUPWB)],

                                            [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)] AS [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)],

                                            [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)] AS [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)],

                                            [LEVEL 1 COMPLETION] AS [LEVEL 1 COMPLETION],

                                             

                                             

                                            if(match("LEVEL 1 COMPLETION",'N','L','S'),SID) as LabelYouWant,

                                             

                                             

                                            [Level 2 - Ram Exam (RAMEXAM1)] AS [Level 2 - Ram Exam (RAMEXAM1)],

                                            [LEVEL 2 COMPLETION] AS [LEVEL 2 COMPLETION],

                                            [Early Adopter Status] AS [Early Adopter Status],

                                            [YTD

                                            Sales

                                            Total] AS [YTDSales Total],

                                            [2016

                                            Cert

                                            Status] AS [2016Cert Status],

                                            [Retail Perf

                                            Specialist] AS [Retail Perf

                                            Specialist],

                                            [Product

                                            Specialist] AS [Product

                                            Specialist],

                                            [Regional Training Manager] AS [Regional Training Manager];

                                             

                                             

                                            LOAD

                                            [BC],

                                            [Dist],

                                            [Dealer

                                            Code],

                                            [Dealer Name],

                                            [Phone],

                                            [Address Line 1],

                                            [City],

                                            [St],

                                            [Zip Code],

                                            [Planning

                                            Potential

                                            Size],

                                            [Sales

                                            Group

                                            Size],

                                            [Franch],

                                            [BusinessLink],

                                            [SID],

                                            [First Name],

                                            [Last Name],

                                            [Position

                                            Code],

                                            [Position

                                            Code

                                            Start Date],

                                            [F21],

                                            [Email],

                                            [Tenure],

                                            [2015 Cert Status],

                                            [Level 1 - Product Knowledge - Fundamental Towing & Job Rating with iShowroom (SSJR03WB)],

                                            [Level 1 - Product Knowledge - Advanced Job Rating with iShowroom (SSJR04WB)],

                                            [Level 1 - Product Knowledge - Working with Upfitters (SSWWUPWB)],

                                            [Level 1 - Product Knowledge - Truck Foundations Part 1 (SSTF01WB)],

                                            [Level 1 - Product Knowledge - Truck Foundations Part 2 (SSTF02WB)],

                                            [LEVEL 1 COMPLETION],

                                            [Level 2 - Ram Exam (RAMEXAM1)],

                                            [LEVEL 2 COMPLETION],

                                            [Early Adopter Status],

                                            [YTD

                                            Sales

                                            Total],

                                            [2016

                                            Cert

                                            Status],

                                            [Retail Perf

                                            Specialist],

                                            [Product

                                            Specialist],

                                            [Regional Training Manager]

                                            FROM [lib://Ram Expert/Ram Expert Data SET.xls]

                                            (biff, embedded labels, table is [Student Detail$]);

                                             

                                             

                                            [Summary Detail]:

                                            LOAD

                                            [BC] AS [BC],

                                            [Dealer Count] AS [Dealer Count],

                                            [Student Count] AS [Student Count],

                                            [Dealer Completion Count] AS [Level 1 - Dealer Completion Count],

                                            [% of Dealer Completed] AS [Level 1 - % of Dealer Completed],

                                            [Student Completion Count] AS [Level 1 - Student Completion Count],

                                            [% of Student Completed] AS [Level 1 - % of Student Completed],

                                            [Dealer Completion Count1] AS [Level 2 - Dealer Completion Count],

                                            [% of Dealer Completed1] AS [Level 2 - % of Dealer Completed],

                                            [Student Completion Count1] AS [Level 2 - Student Completion Count],

                                            [% of Student Completed1] AS [Level 2 -  % of Student Completed],

                                            [Dealer Completion Count2] AS [Early Adopter Dealer Completion Count2],

                                            [% of Dealer Completed2] AS [Early Adopter - % of Dealer Completed],

                                            [Student Completion Count2] AS [Early Adopter -  Student Completion Count2],

                                            [% of Student Completed2] AS [Early Adopter -  % of Student Completed];

                                            LOAD

                                            [BC],

                                            [Dealer Count],

                                            [Student Count],

                                            [Dealer Completion Count],

                                            [% of Dealer Completed],

                                            [Student Completion Count],

                                            [% of Student Completed],

                                            [Dealer Completion Count1],

                                            [% of Dealer Completed1],

                                            [Student Completion Count1],

                                            [% of Student Completed1],

                                            [Dealer Completion Count2],

                                            [% of Dealer Completed2],

                                            [Student Completion Count2],

                                            [% of Student Completed2]

                                            FROM [lib://Ram Expert/Ram Expert Data SET.xls]

                                            (biff, embedded labels, header is 3 lines, table is [Summary Detail$]);

                                             

                                             

                                            [autoCalendar]:

                                              DECLARE FIELD DEFINITION Tagged ('$date')

                                            FIELDS

                                              Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

                                              Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

                                              Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

                                              Month($1) AS [Month] Tagged ('$month'),

                                              Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

                                              Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

                                              Date(Floor($1)) AS [Date] Tagged ('$date');

                                            DERIVE FIELDS FROM FIELDS [PositionCode Start Date] USING [autoCalendar] ;

                                             

                                            //G.