1. 

   jasmondluk, Member

   SQL below:

   select 'dbr.tab', 'Monthly enrollment and drop (By enrollment date and drop date)';
   select 'dbr.tab', 'Monthly new student (By 1st class month) by subject';
   select 'dbr.tab', 'Monthly new student (By enrollment date) by subject';

   select 'DBR.text' as 'DBR.text', 'Eye Level Interactive Education Centre (Discovery Park)' as 'centername';

   select 'dbr.text', 'From: 2023-01-01 to 2023-07-31';

   select 'DBR.title' as 'DBR.title', 'Monthly enrollment and drop (By enrollment date and drop date)' as 'Monthly enrollment and drop (By enrollment date and drop date)';

   select 'DBR.crosstab' as 'DBR.crosstab', 'Record Month' as 'Record Month';

   select 'DBR.hsum' as 'DBR.hsum', '[value]' as '[value]';

   select 'DBR.sum' as 'DBR.sum', '[value]' as '[value]';

   select 'dbr.summary.text', 'Type', 'Net 淨增長';

   select 'dbr.search', 1;

   select 'dbr.hidecolumn', 'Center';

   select 'dbr.subtitle', 'Monthly Enrollment and Drop Statistic';

   select 'dbr.report', 'sp_DBR_monthly_enrollment_statistic_details', '[value]', 'popup', 'Center=Center', 'inMonth=Record Month', 'inType=Type', 'includeFreeSchedule=(includeFreeSchedule)';

   select '8' as 'Center', 'New 新生報讀' as 'Type', '2023-01' as 'Record Month', 23 as '[value]'
   union all
   select '8', 'New 新生報讀', '2023-02', 39
   union all
   select '8', 'New 新生報讀', '2023-03', 15
   union all
   select '8', 'New 新生報讀', '2023-04', 51
   union all
   select '8', 'New 新生報讀', '2023-05', 66
   union all
   select '8', 'New 新生報讀', '2023-06', 35
   union all
   select '8', 'New 新生報讀', '2023-07', 7
   union all
   select '8', 'Resumed 舊生重讀', '2023-01', 4
   union all
   select '8', 'Resumed 舊生重讀', '2023-02', 8
   union all
   select '8', 'Resumed 舊生重讀', '2023-04', 1
   union all
   select '8', 'Resumed 舊生重讀', '2023-05', 1
   union all
   select '8', 'Resumed 舊生重讀', '2023-06', 1
   union all
   select '8', 'Dropped 停學', '2023-01', -20
   union all
   select '8', 'Dropped 停學', '2023-02', -33
   union all
   select '8', 'Dropped 停學', '2023-03', -19
   union all
   select '8', 'Dropped 停學', '2023-04', -21
   union all
   select '8', 'Dropped 停學', '2023-05', -24
   union all
   select '8', 'Dropped 停學', '2023-06', -23
   union all
   select '8', 'Dropped 停學', '2023-07', -12;

   select 'dbr.chart', 'MSline', '', 1024, 400;

   select '2023-01' as 'Record Month', 'Net 淨增長' as 'Type', 7 as '[value]'
   union all
   select '2023-01', 'New 新生報讀', 23
   union all
   select '2023-01', 'Dropped 停學', -20
   union all
   select '2023-01', 'Resumed 舊生重讀', 4
   union all
   select '2023-02', 'New 新生報讀', 39
   union all
   select '2023-02', 'Net 淨增長', 14
   union all
   select '2023-02', 'Dropped 停學', -33
   union all
   select '2023-02', 'Resumed 舊生重讀', 8
   union all
   select '2023-03', 'New 新生報讀', 15
   union all
   select '2023-03', 'Net 淨增長', -4
   union all
   select '2023-03', 'Dropped 停學', -19
   union all
   select '2023-04', 'Resumed 舊生重讀', 1
   union all
   select '2023-04', 'Dropped 停學', -21
   union all
   select '2023-04', 'New 新生報讀', 51
   union all
   select '2023-04', 'Net 淨增長', 31
   union all
   select '2023-05', 'Resumed 舊生重讀', 1
   union all
   select '2023-05', 'Dropped 停學', -24
   union all
   select '2023-05', 'New 新生報讀', 66
   union all
   select '2023-05', 'Net 淨增長', 43
   union all
   select '2023-06', 'Resumed 舊生重讀', 1
   union all
   select '2023-06', 'Dropped 停學', -23
   union all
   select '2023-06', 'New 新生報讀', 35
   union all
   select '2023-06', 'Net 淨增長', 13
   union all
   select '2023-07', 'New 新生報讀', 7
   union all
   select '2023-07', 'Dropped 停學', -12
   union all
   select '2023-07', 'Net 淨增長', -5;

   select 'dbr.tab.next';

   select 'DBR.title' as 'DBR.title', 'Monthly new student (By 1st class month) by subject' as 'Monthly new student (By 1st class month) by subject';

   select 'DBR.crosstab' as 'DBR.crosstab', 'First Class Month' as 'First Class Month';

   select 'DBR.hsum' as 'DBR.hsum', '[value]' as '[value]';

   select 'DBR.sum' as 'DBR.sum', '[value]' as '[value]';

   select 'dbr.sort', 'Center id', 'Subject';

   select 'dbr.summary.text', 'Center id', 'Total';

   select 'dbr.search', 1;

   select 8 as 'Center id', 'R Eng' as 'Subject', 'English 英文' as 'Name', '2023-01' as 'First Class Month', 3 as '[value]'
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-01', 5
   union all
   select 8, 'R SPK', 'Sparks 拼音班', '2023-01', 1
   union all
   select 8, '7 Chin', '(Sun) Chinese (日) 中文', '2023-02', 2
   union all
   select 8, '7 Eng', '(Sun) English (日) 英文', '2023-02', 1
   union all
   select 8, '7 Math', '(Sun) Mathematics (日) 數學', '2023-02', 2
   union all
   select 8, 'EP-Math', 'Exam Preparation - Math 考試預備班 - 數學', '2023-02', 1
   union all
   select 8, 'R Chi', 'Chinese 中文', '2023-02', 3
   union all
   select 8, 'R Eng', 'English 英文', '2023-02', 7
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-02', 11
   union all
   select 8, 'R PMat', 'PlayMath 幼數', '2023-02', 1
   union all
   select 8, 'R SPK', 'Sparks 拼音班', '2023-02', 2
   union all
   select 8, 'SUNEXPM', '(Sun) Exam Preparation - Math (日) 考試預備班 -數學', '2023-02', 1
   union all
   select 8, 'TCE', 'Trial Package-English 試堂組合- 英文試堂', '2023-02', 2
   union all
   select 8, 'TCM', 'Trial Package-Maths 試堂組合- 數學試堂', '2023-02', 1
   union all
   select 8, '7 Chin', '(Sun) Chinese (日) 中文', '2023-03', 1
   union all
   select 8, 'EP-ENG', 'Exam Preparation - Eng 考試預備班 -英文', '2023-03', 1
   union all
   select 8, 'EP-Math', 'Exam Preparation - Math 考試預備班 - 數學', '2023-03', 1
   union all
   select 8, 'R Chi', 'Chinese 中文', '2023-03', 1
   union all
   select 8, 'R Eng', 'English 英文', '2023-03', 2
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-03', 3
   union all
   select 8, 'SUNEPENG', '(Sun) Exam Preparation - Eng (日) 考試預備班 -英文', '2023-03', 1
   union all
   select 8, 'SUNEXPM', '(Sun) Exam Preparation - Math (日) 考試預備班 -數學', '2023-03', 1
   union all
   select 8, '7 Chin', '(Sun) Chinese (日) 中文', '2023-04', 1
   union all
   select 8, '7 Math', '(Sun) Mathematics (日) 數學', '2023-04', 1
   union all
   select 8, 'R Eng', 'English 英文', '2023-04', 3
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-04', 7
   union all
   select 8, 'R SPK', 'Sparks 拼音班', '2023-04', 2
   union all
   select 8, 'TCE', 'Trial Package-English 試堂組合- 英文試堂', '2023-04', 1
   union all
   select 8, 'TCM', 'Trial Package-Maths 試堂組合- 數學試堂', '2023-04', 1
   union all
   select 8, 'R AW', 'Active Writers 英文寫作', '2023-05', 1
   union all
   select 8, 'R Eng', 'English 英文', '2023-05', 3
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-05', 10
   union all
   select 8, 'R SPK', 'Sparks 拼音班', '2023-05', 2
   union all
   select 8, 'EP-ENG', 'Exam Preparation - Eng 考試預備班 -英文', '2023-06', 1
   union all
   select 8, 'P14SBCM', '2023 Summer Bridging Chin Math P1-P4 2023 暑期升學預備 中數 P1-P4', '2023-06', 6
   union all
   select 8, 'P14SBE', '2023 Summer Bridging English P1-P4 2023 暑期升學預備 英文 P1-P4', '2023-06', 6
   union all
   select 8, 'P14SBEM', '2023 Summer Bridging Eng Math P1-P4 2023 暑期升學預備 英數 P1-P4', '2023-06', 1
   union all
   select 8, 'P56SBCM', '2023 Summer Bridging Chi Math P5-P6 2023 暑期升學預備 中數 P5-P6', '2023-06', 7
   union all
   select 8, 'P56SBE', '2023 Summer Bridging English P5-P6 2023 暑期升學預備 英文 P5-P6', '2023-06', 5
   union all
   select 8, 'P56SBEM', '2023 Summer Bridging Eng Math P5-P6 2023 暑期升學預備 英數 P5-P6', '2023-06', 2
   union all
   select 8, 'R Chi', 'Chinese 中文', '2023-06', 2
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-06', 6
   union all
   select 8, 'R SPK', 'Sparks 拼音班', '2023-06', 2
   union all
   select 8, '23SSTEAM', '2023 Summer STEAM 2023 暑期STEAM', '2023-07', 11
   union all
   select 8, '7 Eng', '(Sun) English (日) 英文', '2023-07', 1
   union all
   select 8, 'P14SBCM', '2023 Summer Bridging Chin Math P1-P4 2023 暑期升學預備 中數 P1-P4', '2023-07', 7
   union all
   select 8, 'P14SBE', '2023 Summer Bridging English P1-P4 2023 暑期升學預備 英文 P1-P4', '2023-07', 11
   union all
   select 8, 'P14SBEM', '2023 Summer Bridging Eng Math P1-P4 2023 暑期升學預備 英數 P1-P4', '2023-07', 5
   union all
   select 8, 'P56SBCM', '2023 Summer Bridging Chi Math P5-P6 2023 暑期升學預備 中數 P5-P6', '2023-07', 3
   union all
   select 8, 'P56SBE', '2023 Summer Bridging English P5-P6 2023 暑期升學預備 英文 P5-P6', '2023-07', 4
   union all
   select 8, 'P56SBEM', '2023 Summer Bridging Eng Math P5-P6 2023 暑期升學預備 英數 P5-P6', '2023-07', 3
   union all
   select 8, 'R 447', 'Oral 447 英文會話班', '2023-07', 3
   union all
   select 8, 'R AW', 'Active Writers 英文寫作', '2023-07', 3
   union all
   select 8, 'R Chi', 'Chinese 中文', '2023-07', 2
   union all
   select 8, 'R Eng', 'English 英文', '2023-07', 3
   union all
   select 8, 'R Math', 'Mathematics 數學', '2023-07', 6;

   select 'dbr.tab.next';

   select 'DBR.title' as 'DBR.title', 'Monthly new student (By enrollment date) by subject' as 'Monthly new student (By enrollment date) by subject';

   select 'DBR.crosstab' as 'DBR.crosstab', 'Enrollment Month' as 'Enrollment Month';

   select 'DBR.hsum' as 'DBR.hsum', '[value]' as '[value]';

   select 'DBR.sum' as 'DBR.sum', '[value]' as '[value]';

   select 'dbr.sort', 'Center id';

   select 'dbr.summary.text', 'Center id', 'Total';

   select 'dbr.search', 1;

   select 8 as 'Center id', 'R Chi' as 'Subject', 'Free' as 'Schedule Name', 'Chinese 中文' as 'Name', '2023-01' as 'Enrollment Month', 1 as '[value]'
   union all
   select 8, 'R Chi', 'Weekly', 'Chinese 中文', '2023-01', 1
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-01', 3
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-01', 3
   union all
   select 8, 'R Math', 'Weekly', 'Mathematics 數學', '2023-01', 5
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-01', 6
   union all
   select 8, 'R PMat', 'Weekly', 'PlayMath 幼數', '2023-01', 1
   union all
   select 8, 'R SPK', 'Free', 'Sparks 拼音班', '2023-01', 1
   union all
   select 8, 'SUNEPENG', 'Weekly', '(Sun) Exam Preparation - Eng (日) 考試預備班 -英文', '2023-01', 1
   union all
   select 8, 'SUNEXPM', 'Weekly', '(Sun) Exam Preparation - Math (日) 考試預備班 -數學', '2023-01', 1
   union all
   select 8, '7 Chin', 'Weekly', '(Sun) Chinese (日) 中文', '2023-02', 2
   union all
   select 8, '7 Eng', 'Weekly', '(Sun) English (日) 英文', '2023-02', 1
   union all
   select 8, '7 Math', 'Weekly', '(Sun) Mathematics (日) 數學', '2023-02', 2
   union all
   select 8, 'EP-Math', 'Free', 'Exam Preparation - Math 考試預備班 - 數學', '2023-02', 1
   union all
   select 8, 'R Chi', 'Free', 'Chinese 中文', '2023-02', 1
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-02', 2
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-02', 7
   union all
   select 8, 'R Math', 'Weekly', 'Mathematics 數學', '2023-02', 7
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-02', 8
   union all
   select 8, 'R SPK', 'Free', 'Sparks 拼音班', '2023-02', 2
   union all
   select 8, 'R SPK', 'Weekly', 'Sparks 拼音班', '2023-02', 2
   union all
   select 8, 'SUNEXPM', 'Weekly', '(Sun) Exam Preparation - Math (日) 考試預備班 -數學', '2023-02', 1
   union all
   select 8, 'TCE', 'Free', 'Trial Package-English 試堂組合- 英文試堂', '2023-02', 2
   union all
   select 8, 'TCM', 'Free', 'Trial Package-Maths 試堂組合- 數學試堂', '2023-02', 1
   union all
   select 8, '7 Chin', 'Free', '(Sun) Chinese (日) 中文', '2023-03', 1
   union all
   select 8, '7 Chin', 'Weekly', '(Sun) Chinese (日) 中文', '2023-03', 1
   union all
   select 8, '7 Math', 'Weekly', '(Sun) Mathematics (日) 數學', '2023-03', 1
   union all
   select 8, 'EP-ENG', 'Free', 'Exam Preparation - Eng 考試預備班 -英文', '2023-03', 1
   union all
   select 8, 'EP-Math', 'Weekly', 'Exam Preparation - Math 考試預備班 - 數學', '2023-03', 1
   union all
   select 8, 'R Chi', 'Free', 'Chinese 中文', '2023-03', 1
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-03', 1
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-03', 2
   union all
   select 8, 'R Math', 'Weekly', 'Mathematics 數學', '2023-03', 2
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-03', 3
   union all
   select 8, 'R SPK', 'Weekly', 'Sparks 拼音班', '2023-03', 1
   union all
   select 8, '23SSTEAM', 'Weekly', '2023 Summer STEAM 2023 暑期STEAM', '2023-04', 2
   union all
   select 8, '7 Chin', 'Weekly', '(Sun) Chinese (日) 中文', '2023-04', 1
   union all
   select 8, 'P14SBCM', 'Free', '2023 Summer Bridging Chin Math P1-P4 2023 暑期升學預備 中數 P1-P4', '2023-04', 1
   union all
   select 8, 'P14SBCM', 'Weekly', '2023 Summer Bridging Chin Math P1-P4 2023 暑期升學預備 中數 P1-P4', '2023-04', 6
   union all
   select 8, 'P14SBE', 'Free', '2023 Summer Bridging English P1-P4 2023 暑期升學預備 英文 P1-P4', '2023-04', 1
   union all
   select 8, 'P14SBE', 'Weekly', '2023 Summer Bridging English P1-P4 2023 暑期升學預備 英文 P1-P4', '2023-04', 5
   union all
   select 8, 'P14SBEM', 'Free', '2023 Summer Bridging Eng Math P1-P4 2023 暑期升學預備 英數 P1-P4', '2023-04', 1
   union all
   select 8, 'P56SBCM', 'Weekly', '2023 Summer Bridging Chi Math P5-P6 2023 暑期升學預備 中數 P5-P6', '2023-04', 1
   union all
   select 8, 'P56SBCM', 'Free', '2023 Summer Bridging Chi Math P5-P6 2023 暑期升學預備 中數 P5-P6', '2023-04', 2
   union all
   select 8, 'P56SBE', 'Free', '2023 Summer Bridging English P5-P6 2023 暑期升學預備 英文 P5-P6', '2023-04', 1
   union all
   select 8, 'P56SBEM', 'Weekly', '2023 Summer Bridging Eng Math P5-P6 2023 暑期升學預備 英數 P5-P6', '2023-04', 1
   union all
   select 8, 'R 447', 'Weekly', 'Oral 447 英文會話班', '2023-04', 1
   union all
   select 8, 'R AW', 'Weekly', 'Active Writers 英文寫作', '2023-04', 2
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-04', 3
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-04', 2
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-04', 8
   union all
   select 8, 'R Math', 'Weekly', 'Mathematics 數學', '2023-04', 7
   union all
   select 8, 'R SPK', 'Weekly', 'Sparks 拼音班', '2023-04', 2
   union all
   select 8, 'R SPK', 'Free', 'Sparks 拼音班', '2023-04', 2
   union all
   select 8, 'TCE', 'Free', 'Trial Package-English 試堂組合- 英文試堂', '2023-04', 1
   union all
   select 8, 'TCM', 'Free', 'Trial Package-Maths 試堂組合- 數學試堂', '2023-04', 1
   union all
   select 8, '23SSTEAM', 'Free', '2023 Summer STEAM 2023 暑期STEAM', '2023-05', 1
   union all
   select 8, '23SSTEAM', 'Weekly', '2023 Summer STEAM 2023 暑期STEAM', '2023-05', 7
   union all
   select 8, 'P14SBCM', 'Weekly', '2023 Summer Bridging Chin Math P1-P4 2023 暑期升學預備 中數 P1-P4', '2023-05', 6
   union all
   select 8, 'P14SBE', 'Weekly', '2023 Summer Bridging English P1-P4 2023 暑期升學預備 英文 P1-P4', '2023-05', 10
   union all
   select 8, 'P14SBEM', 'Weekly', '2023 Summer Bridging Eng Math P1-P4 2023 暑期升學預備 英數 P1-P4', '2023-05', 5
   union all
   select 8, 'P56SBCM', 'Weekly', '2023 Summer Bridging Chi Math P5-P6 2023 暑期升學預備 中數 P5-P6', '2023-05', 5
   union all
   select 8, 'P56SBE', 'Free', '2023 Summer Bridging English P5-P6 2023 暑期升學預備 英文 P5-P6', '2023-05', 1
   union all
   select 8, 'P56SBE', 'Weekly', '2023 Summer Bridging English P5-P6 2023 暑期升學預備 英文 P5-P6', '2023-05', 4
   union all
   select 8, 'P56SBEM', 'Weekly', '2023 Summer Bridging Eng Math P5-P6 2023 暑期升學預備 英數 P5-P6', '2023-05', 1
   union all
   select 8, 'R 447', 'Weekly', 'Oral 447 英文會話班', '2023-05', 1
   union all
   select 8, 'R AW', 'Weekly', 'Active Writers 英文寫作', '2023-05', 2
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-05', 1
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-05', 2
   union all
   select 8, 'R Math', 'Weekly', 'Mathematics 數學', '2023-05', 9
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-05', 9
   union all
   select 8, 'R SPK', 'Free', 'Sparks 拼音班', '2023-05', 1
   union all
   select 8, 'R SPK', 'Weekly', 'Sparks 拼音班', '2023-05', 1
   union all
   select 8, '23SSTEAM', 'Weekly', '2023 Summer STEAM 2023 暑期STEAM', '2023-06', 3
   union all
   select 8, '7 Eng', 'Weekly', '(Sun) English (日) 英文', '2023-06', 1
   union all
   select 8, 'EP-ENG', 'Free', 'Exam Preparation - Eng 考試預備班 -英文', '2023-06', 1
   union all
   select 8, 'P14SBCM', 'Weekly', '2023 Summer Bridging Chin Math P1-P4 2023 暑期升學預備 中數 P1-P4', '2023-06', 3
   union all
   select 8, 'P14SBE', 'Weekly', '2023 Summer Bridging English P1-P4 2023 暑期升學預備 英文 P1-P4', '2023-06', 1
   union all
   select 8, 'P56SBCM', 'Weekly', '2023 Summer Bridging Chi Math P5-P6 2023 暑期升學預備 中數 P5-P6', '2023-06', 1
   union all
   select 8, 'P56SBE', 'Weekly', '2023 Summer Bridging English P5-P6 2023 暑期升學預備 英文 P5-P6', '2023-06', 3
   union all
   select 8, 'P56SBEM', 'Weekly', '2023 Summer Bridging Eng Math P5-P6 2023 暑期升學預備 英數 P5-P6', '2023-06', 3
   union all
   select 8, 'R Chi', 'Weekly', 'Chinese 中文', '2023-06', 2
   union all
   select 8, 'R Chi', 'Free', 'Chinese 中文', '2023-06', 1
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-06', 2
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-06', 1
   union all
   select 8, 'R Math', 'Weekly', 'Mathematics 數學', '2023-06', 4
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-06', 7
   union all
   select 8, 'R SPK', 'Free', 'Sparks 拼音班', '2023-06', 2
   union all
   select 8, 'R 447', 'Weekly', 'Oral 447 英文會話班', '2023-07', 1
   union all
   select 8, 'R Chi', 'Free', 'Chinese 中文', '2023-07', 2
   union all
   select 8, 'R Eng', 'Weekly', 'English 英文', '2023-07', 1
   union all
   select 8, 'R Eng', 'Free', 'English 英文', '2023-07', 1
   union all
   select 8, 'R Math', 'Free', 'Mathematics 數學', '2023-07', 2;
2. 

   myDBR Team, Key Master

   Hi,

   the dbr.sort command uses asc and desc for the sorting order.

   select 'dbr.sort', 'Center id', 'asc', 'Subject', 'asc';

   See the documentation.

   --
   myDBR Team
3. 

   jasmondluk, Member

   thank you

Reply

You must log in to post.