A:  5k to 7k
2. Will this sum up to a yearly amount of appr. 20 million files, or
do you have an upper limit?

A: Every day we are generating collections (Unbilled Mobile Transactionn data) Per account appr 50k - 55k files.
3. Which of our API(s) are you using to add the documents?

A: import org.basex.server.ClientSession;
4. How do your queries for reporting data look like?

A:

Usage Summary Per Line:10 minutes

declare function local:getLabelName($serNo as xs:string) {  let $doc := collection('LabelCollection_1573624392934')   let $label := $doc//row[@ser_no = $serNo] let $lbCount:=count($doc//row[@ser_no = $serNo])  return     if(data($label) != '' and $lbCount<=1) then data($label)     else ('') }; declare function local:getRecords() {  let $Rows := (collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][DOB >='2019-10-01' and DOB <='2019-10-31'])   let $billNo := data($Rows/CONN/@NO)  let $topRecords := $billNo                              for $details in  $Rows[CONN[data(@NO)=$topRecords]]/Country/ROW     group by $billNo := data($details/../../CONN/@NO)                                     return <R> {               element Acno {distinct-values(data($details/../../AccNo/@NO))},               element BNO {$billNo},               element SLBl {distinct-values(local:getLabelName($billNo))},               element Stat {}, for $serviceDetails in $details   group by $groupService := data($serviceDetails/@ServType)   return (element {concat($groupService,'C') } {xs:decimal(sum(data($serviceDetails/@Calls))) } ), element TCls {xs:decimal(sum(data($details/@Calls)))} }</R> }; (             let $records := local:getRecords()[position() le 10000]             let $totalCalls := sum($records/TCls)  let $pstncls := xs:decimal(sum($records/PSTNC)) let $vpncls := xs:decimal(sum($records/VPNC)) let $tnbscls := xs:decimal(sum($records/TNBSC)) let $fmccls := xs:decimal(sum($records/FMCC)) return               ($records,                if(count($records) > 0) then ( <R>{ <Acno>{ 'Total' }</Acno>, <BNO></BNO>, <SLBl></SLBl>, <Stat></Stat>, <PSTNC>{ xs:decimal($pstncls) }</PSTNC>,  <VPNC>{ xs:decimal($vpncls) }</VPNC>, <TNBSC>{ xs:decimal($tnbscls) }</TNBSC>,  <FMCC>{ xs:decimal($fmccls) }</FMCC>, <TCls>{ xs:decimal($totalCalls) }</TCls> }</R>)                else ())           )

 
Trend Report by Call type:4 minutes  

declare function local:getDurationPer($ctDur as xs:double, $ctTotalDur as xs:double) {    if($ctTotalDur=0)then()else(   let $ctPer := concat(xs:decimal(round-half-to-even((($ctDur div $ctTotalDur) * 100), 3)), "%")   return $ctPer) };
declare function local:getHHMMSSFromNumber($num as xs:double) {  let $ss := xs:integer($num mod 60)  let $mm1 := xs:integer($num div 60)  let $mm := xs:integer($mm1 mod 60)  let $hh := xs:integer($mm1 div 60)  let $hhFinal := if(string-length(xs:string($hh)) >= 2) then $hh else (concat('0', $hh))  let $mmFinal := if(string-length(xs:string($mm)) >= 2) then $mm else (concat('0', $mm))  let $ssFinal := if(string-length(xs:string($ss)) >= 2) then $ss else (concat('0', $ss))  return concat($hhFinal,':',$mmFinal,':',$ssFinal) };
declare function local:getMMSSFromNumber($num as xs:double) {  let $ss := xs:integer($num mod 60)  let $mm := xs:integer($num div 60)  let $mmFinal := if(string-length(xs:string($mm)) >= 2) then $mm else (concat('0', $mm))  let $ssFinal := if(string-length(xs:string($ss)) >= 2) then $ss else (concat('0', $ss))  return concat($mmFinal,':',$ssFinal) };

let $subbrk := (collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][DOB >= '2019-10-01' and DOB <= '2019-10-31']) let $detailUsageTxn := $subbrk/DETAIL/TRANSACTION[@Usage = 'usage'] let $DistCallTypes := distinct-values($detailUsageTxn/SUB_SECTION/@Type) let $allmonths := distinct-values($subbrk/Month) let $rowcnt := count($detailUsageTxn/SUB_SECTION) let $months := $allmonths[position() le 10000] let $opttype := 'NumVal' let $durop := 'hh:mm:ss' return   (   if($DistCallTypes!='')then(for $month in $months   return     <R>        <GRPBY>{ $month }</GRPBY>{                   if($opttype = 'NumVal') then                     for $ct in $DistCallTypes                      let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Calls)                     return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {xs:decimal($ctCnt)}                   else                     if($opttype = 'NumPerViewPoint') then                       for $ct in $DistCallTypes                       let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Calls)                       let $ctTotalCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Calls)                       let $ctPer := concat(xs:decimal(round-half-to-even((($ctCnt div $ctTotalCnt) * 100), 3)), '%')                       return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {$ctPer}                     else                       if($opttype = 'NumPerCallType') then                         for $ct in $DistCallTypes                         let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Calls)                         let $ctTotalCnt := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Calls)                         let $ctPer := concat(xs:decimal(round-half-to-even(($ctCnt div $ctTotalCnt) * 100, 3)), '%')                         return element { replace(concat('_',data($ct)), '(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {$ctPer}                       else                         if($opttype = 'DurVal') then                           for $ct in $DistCallTypes                           let $ctDur := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Duration)                           return                             element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {if($durop = 'hh:mm:ss') then local:getHHMMSSFromNumber($ctDur)                                                                       else (local:getMMSSFromNumber($ctDur))}                         else                           if($opttype = 'DurPerViewPoint') then                             for $ct in $DistCallTypes                             let $ctDur := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Duration)                             let $ctTotalDur := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Duration)                             return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {local:getDurationPer($ctDur, $ctTotalDur)}                           else                             if($opttype = 'DurPerCallType') then                               for $ct in $DistCallTypes                               let $ctDur := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Duration)                               let $ctTotalDur := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Duration)                               return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {local:getDurationPer($ctDur, $ctTotalDur)}                             else                               if($opttype = 'CostVal') then                                 for $ct in $DistCallTypes                                 let $ctSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Amt)                                 return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {xs:decimal(round-half-to-even($ctSum, 3))}                               else                                 if($opttype = 'CostPerViewPoint') then                                   for $ct in $DistCallTypes                                   let $ctSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Amt)                                   let $ctTotalSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Amt)                                   let $ctPer := concat(xs:decimal(round-half-to-even((($ctSum div $ctTotalSum) * 100), 3)), '%')                                   return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {$ctPer}                                 else                                   if($opttype = 'CostPerCallType') then                                     for $ct in $DistCallTypes                                     let $ctSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Amt)                                     let $ctTotalSum := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Amt)                                     let $ctPer := concat(xs:decimal(round-half-to-even((($ctSum div $ctTotalSum) * 100), 3)), '%')                                     return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {$ctPer}                                   else ()                 }   <TOTAL>{   if($opttype = 'NumVal') then xs:decimal(sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Calls))   else    if($opttype = 'NumPerViewPoint') then      let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Calls)      let $ctTotalCnt := sum($detailUsageTxn/SUB_SECTION/@Calls)      let $ctPer := concat(xs:decimal(round-half-to-even((($ctCnt div $ctTotalCnt) * 100), 3)), '%')      return $ctPer    else      if($opttype = 'NumPerCallType') then data('100%')      else        if($opttype = 'DurVal') then          let $DurSum := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Duration)          return            if($durop = 'hh:mm:ss') then local:getHHMMSSFromNumber($DurSum)            else (local:getMMSSFromNumber($DurSum))        else          if($opttype = 'DurPerViewPoint') then            let $ctDur := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Duration)            let $ctTotalDur := sum($detailUsageTxn/SUB_SECTION/@Duration)            return local:getDurationPer($ctDur, $ctTotalDur)          else            if($opttype = 'DurPerCallType') then data('100%')            else              if($opttype = 'CostVal') then xs:decimal(round-half-to-even(sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Amt), 3))              else                if($opttype = 'CostPerViewPoint') then                  let $ctSum := sum($detailUsageTxn/SUB_SECTION[data(../../../Month) = $month]/@Amt)                  let $ctTotalSum := sum($detailUsageTxn/SUB_SECTION/@Amt)                  let $ctPer := concat(xs:decimal(round-half-to-even((($ctSum div $ctTotalSum) * 100), 3)), '%')                  return $ctPer                else                  if($opttype = 'CostPerCallType') then data('100%')                  else ()   }</TOTAL></R>)else()    , (   if($rowcnt > 0) then   <R><GRPBY>Total</GRPBY>{                 if($opttype = 'NumVal') then                   for $ct in $DistCallTypes                   let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Calls)                   return element { replace(concat('_',data($ct)), '(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {xs:decimal($ctCnt)}                 else                   if($opttype = 'NumPerViewPoint') then                     for $ct in $DistCallTypes                     return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {data('100%')}                   else                     if($opttype = 'NumPerCallType') then                       for $ct in $DistCallTypes                       let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Calls)                       let $ctTotalCnt := sum($detailUsageTxn/SUB_SECTION/@Calls)                       let $ctPer := concat(xs:decimal(round-half-to-even((($ctCnt div $ctTotalCnt) * 100), 3)), '%')                       return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {$ctPer}                     else                       if($opttype = 'DurVal') then                         for $ct in $DistCallTypes                         let $DurSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Duration)                         return                           element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {if($durop = 'hh:mm:ss') then local:getHHMMSSFromNumber($DurSum)                                                                     else (local:getMMSSFromNumber($DurSum))}                       else                         if($opttype = 'DurPerViewPoint') then data('100%')                         else                           if($opttype = 'DurPerCallType') then                             for $ct in $DistCallTypes                             let $ctDur := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Duration)                             let $ctTotalDur := sum($detailUsageTxn/SUB_SECTION/@Duration)                             return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {local:getDurationPer($ctDur, $ctTotalDur)}                           else                             if($opttype = 'CostVal') then                               for $ct in $DistCallTypes                               let $ctSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Amt)                               return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {xs:decimal(round-half-to-even($ctSum, 3))}                             else                               if($opttype = 'CostPerViewPoint') then data('100%')                               else                                 if($opttype = 'CostPerCallType') then                                   for $ct in $DistCallTypes                                   let $ctSum := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct)]/@Amt)                                   let $ctTotalSum := sum($detailUsageTxn/SUB_SECTION/@Amt)                                   let $ctPer := concat(xs:decimal(round-half-to-even((($ctSum div $ctTotalSum) * 100), 3)), '%')                                   return element { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |&apos;)', '_') } {$ctPer}                                 else ()               }<TOTAL>{                         if($opttype = 'NumVal') then xs:decimal(sum($detailUsageTxn/SUB_SECTION/@Calls))                         else                           if($opttype = 'NumPerViewPoint') then data('100%')                           else                             if($opttype = 'NumPerCallType') then data('100%')                             else                               if($opttype = 'DurVal') then                                 let $durSum := sum($detailUsageTxn/SUB_SECTION/@Duration)                                 return                                   if($durop = 'hh:mm:ss') then local:getHHMMSSFromNumber($durSum)                                   else (local:getMMSSFromNumber($durSum))                               else                                 if($opttype = 'DurPerViewPoint') then data('100%')                                 else                                   if($opttype = 'DurPerCallType') then data('100%')                                   else                                     if($opttype = 'CostVal') then xs:decimal(round-half-to-even(sum($detailUsageTxn/SUB_SECTION/@Amt), 3))                                     else                                       if($opttype = 'CostPerViewPoint') then data('100%')                                       else                                         if($opttype = 'CostPerCallType') then data('100%')                                         else ()                       }</TOTAL></R>    else ()      ))


Multilevel activity Summary report:-4-minitutes

declare function local:getMinsSecs($mins, $calls) {   let $mm := floor($mins div $calls)   let $pre := substring(substring-after(string(round-half-to-even(($mins div $calls) - $mm, 5)), '.'), 1, 3)   let $precision := if($pre != '') then number($pre)     else 0   let $ss := string(round($precision * 0.06))   return     concat(if($mm < 10) then concat('0', $mm)            else $mm, ':', if($ss < 10) then concat('0', $ss)           else $ss) };
declare function local:getHHMMSSFromNumber($num as xs:integer) {  let $ss := xs:integer($num mod 60)  let $mm1 := xs:integer($num div 60)  let $mm := xs:integer($mm1 mod 60)  let $hh := xs:integer($mm1 div 60)  let $hhFinal := if(string-length(xs:string($hh)) >= 2) then $hh else (concat('0', $hh))  let $mmFinal := if(string-length(xs:string($mm)) >= 2) then $mm else (concat('0', $mm))  let $ssFinal := if(string-length(xs:string($ss)) >= 2) then $ss else (concat('0', $ss))  return concat($hhFinal,':',$mmFinal,':',$ssFinal) };
declare function local:getMinutesFromDutation($duration as xs:string) {   let $duration := tokenize($duration, ":")   let $hh := $duration[1]   let $mm := $duration[2]   let $ss := $duration[3]   return xs:decimal(round-half-to-even((number(data($hh)) * 60 + number(data($mm)) + (number(data($ss)) div 60)), 3)) };
declare function local:getViewPontChargesData() {   for $viewPontLopp in <root><viewpoint><name>Default</name><accountnos>6945045</accountnos></viewpoint></root>//viewpoint             let $Rows := if(data($viewPontLopp/name)= "Default")then((collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][DOB >='2019-10-01' and DOB <='2019-10-31']))else((collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][DOB >='2019-10-01' and DOB <='2019-10-31']))         let $accNos := $viewPontLopp/accountnos          let $viewPointName := data($viewPontLopp/name)          let $viewPointdata :=$Rows[AccNo[@NO=tokenize($accNos, ",")]]/DETAIL/TRANSACTION/SUB_SECTION let $nCalls := xs:decimal(sum(data($viewPointdata/@Calls)))          let $totalAmt := round-half-to-even(sum(data($viewPointdata/@Amt)), 3)          let $BNCount := count(data(distinct-values($viewPointdata/../../../CONN/@NO)))    let $durSum :=  sum(data($viewPointdata/@Duration))     let $totDur := local:getHHMMSSFromNumber(xs:integer($durSum))          let $tMs := local:getMinutesFromDutation($totDur)          let $totalDurSec := $durSum         return            <ROW>            <VPNM>{ $viewPointName }</VPNM>            <CLS>{ $nCalls }</CLS>     <BNC>{$BNCount}</BNC>        <DUR>{ $totDur }</DUR>            <DUS>{$totalDurSec}</DUS>            <MIN>{ $tMs }</MIN>            <AM>{if($tMs > 0 and $nCalls > 0) then   (let $avgMin := local:getMinsSecs($tMs, $nCalls)    return $avgMin) else ('00:00')}</AM><AMT>{ $totalAmt }</AMT><PC>{   if($totalAmt > 0 and $nCalls > 0) then     (let $avgPeRCall := round-half-to-even($totalAmt div $nCalls, 3)      return $avgPeRCall)   else (0.00) }</PC><PM>{        if($totalAmt > 0 and $tMs > 0) then          (let $avgPerMin := round-half-to-even($totalAmt div $tMs, 3)           return $avgPerMin)        else (0.00)      }</PM><PN>{            if($totalAmt > 0 and $BNCount > 0) then              (let $avgPerNum := round-half-to-even($totalAmt div $BNCount, 3)               return $avgPerNum)            else (0.000)          }</PN></ROW>        };
let $viewPointCData := local:getViewPontChargesData()[position() le 10000] let $TotCalls := xs:decimal(sum(data($viewPointCData/CLS))) let $TotAmt := sum(data($viewPointCData/AMT)) let $totDur := data($viewPointCData/DUR) let $totalDur := local:getHHMMSSFromNumber(xs:integer(sum($viewPointCData/DUS))) let $totalMins := local:getMinutesFromDutation($totalDur) let $BNcount := sum(data($viewPointCData/BNC)) let $TAPAmt := sum(data($viewPointCData/PN)) let $TotNoofAccs := count(data($viewPointCData/VPNM)) return   (               if(count($viewPointCData) > 0) then (for $eachRow in $viewPointCData  let $eachAmount := $eachRow/AMT  return    <R>{         $eachRow/node(),         <PER>{    if($eachAmount > 0 and $TotAmt > 0) then      (let $perAll := round-half-to-even(($eachAmount div $TotAmt) * 100, 3)       return $perAll)    else (0.00)  }</PER>       }</R>,  <R><VPNM>Total/Average</VPNM><CLS>{ $TotCalls }</CLS><DUR>{ $totalDur }</DUR><MIN>{  format-number($totalMins,'0.000') }</MIN><AM>{          if($totalMins > 0 and $TotCalls > 0) then            (let $avgMinutes := local:getMinsSecs($totalMins, $TotCalls)             return $avgMinutes)          else ('00:00')        }</AM><BNC>{$BNcount}</BNC><AMT>{  format-number($TotAmt,'0.000') }</AMT><PC>{          if($TotAmt > 0 and $TotCalls > 0) then            (let $avgCallAmt := round-half-to-even($TotAmt div $TotCalls, 3)             return  format-number($avgCallAmt,'0.000'))          else (0.000)        }</PC><PM>{               if($TotAmt > 0 and $totalMins > 0) then (let $avgPerMin := round-half-to-even($TotAmt div $totalMins, 3)  return  format-number($avgPerMin,'0.000'))               else (0.000)             }</PM><PN>{  if($TAPAmt > 0 and $TotNoofAccs > 0) then    (let $avgNumAmt := round-half-to-even($TAPAmt div $TotNoofAccs, 3)     return  format-number($avgNumAmt,'0.000'))  else (0.000)}</PN><PER>100</PER></R>)               else ()             )
 

Multilevel activity Summary report (Site level drill down report):-5minutes
 
declare function local:getMinsSecs($mins, $calls) {   let $mm := floor($mins div $calls)   let $pre := substring(substring-after(string(round-half-to-even(($mins div $calls) - $mm, 5)), '.'), 1, 3)   let $precision := if($pre != '') then number($pre)       else 0   let $ss := string(round($precision * 0.06))   return     concat(if($mm < 10) then concat('0', $mm)            else $mm, ':', if($ss < 10) then concat('0', $ss)             else $ss) };
declare function local:getHHMMSSFromNumber($num as xs:integer) {  let $ss := xs:integer($num mod 60)  let $mm1 := xs:integer($num div 60)  let $mm := xs:integer($mm1 mod 60)  let $hh := xs:integer($mm1 div 60)  let $hhFinal := if(string-length(xs:string($hh)) >= 2) then $hh else (concat('0', $hh))  let $mmFinal := if(string-length(xs:string($mm)) >= 2) then $mm else (concat('0', $mm))  let $ssFinal := if(string-length(xs:string($ss)) >= 2) then $ss else (concat('0', $ss))  return concat($hhFinal,':',$mmFinal,':',$ssFinal) };
declare function local:getMinutesFromDutation($duration as xs:string) {   let $duration := tokenize($duration, ":")   let $hh := $duration[1]   let $mm := $duration[2]   let $ss := $duration[3]   return round-half-to-even((number(data($hh)) * 60 + number(data($mm)) + (number(data($ss)) div 60)), 3) };
declare function local:getViewPontChargesData() {      let $Rows :=(collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][DOB >='2019-10-01' and DOB <='2019-10-31'])           let $sites := "No Site"          let $eachSite := tokenize($sites, ",")          for $site at $index in $eachSite          let $siteName := if($site ='No Site')then('')else($site)     let $siteData := $Rows[Site[@NAME = $siteName]]/DETAIL/TRANSACTION/SUB_SECTION     let $nCalls := xs:decimal(sum(data($siteData/@Calls)))          let $totalAmt := round-half-to-even(sum(data($siteData/@Amt)), 3)          let $BNCount := count(data(distinct-values($siteData/../../../CONN/@NO)))      let $durSum := xs:decimal(sum(data($siteData/@Duration)))    let $totDur := local:getHHMMSSFromNumber(xs:integer($durSum))          let $tMs := xs:decimal(local:getMinutesFromDutation($totDur))          let $totalDurSec := $durSum     return            if($nCalls > 0) then(<ROW><SNM>{ $site }</SNM><CLS>{ $nCalls }</CLS><DUR>{ $totDur }</DUR><DUS>{$totalDurSec}</DUS><MIN>{ $tMs }</MIN><AM>{        if($tMs > 0 and $nCalls > 0) then          (let $avgMin := local:getMinsSecs($tMs, $nCalls)           return $avgMin)        else ('0:00')      }</AM>      <AMT>{ $totalAmt }</AMT><PC>{   if($totalAmt > 0 and $nCalls > 0) then     (let $avgPeRCall := round-half-to-even($totalAmt div $nCalls, 3)      return $avgPeRCall)   else (0.00) }</PC><PM>{          if($totalAmt > 0 and $tMs > 0) then            (let $avgPerMin := round-half-to-even($totalAmt div $tMs, 3)             return $avgPerMin)          else (0.00)        }</PM><PN>{  if($totalAmt > 0 and $BNCount > 0) then    (let $avgPerNum := round-half-to-even($totalAmt div $BNCount, 3)     return $avgPerNum)  else (0.000)}</PN></ROW>)            else () };
let $viewPointCData := local:getViewPontChargesData() let $TotCalls := xs:decimal(sum(data($viewPointCData/CLS))) let $TotAmt := sum(data($viewPointCData/AMT)) let $totDur := data($viewPointCData/DUR) let $totalDur := local:getHHMMSSFromNumber(xs:integer(sum($viewPointCData/DUS))) let $totalMins := local:getMinutesFromDutation($totalDur) let $TAPAmt := sum(data($viewPointCData/PN)) let $TotNoofAccs := count(data($viewPointCData/SNM)) return   ( if(count($viewPointCData) > 0) then   (for $eachRow in $viewPointCData    let $eachAmount := $eachRow/AMT    return      <R>{           $eachRow/node(),           <PER>{        if($eachAmount > 0 and $TotAmt > 0) then          (let $perAll := round-half-to-even(($eachAmount div $TotAmt) * 100, 3)           return $perAll)        else (0.00)      }</PER>         }</R>,    <R><SNM>Total/Average</SNM><CLS>{ $TotCalls }</CLS><DUR>{ $totalDur }</DUR><MIN>{  format-number($totalMins,'0.000') }</MIN><AM>{     if($totalMins > 0 and $TotCalls > 0) then       (let $avgMinutes := local:getMinsSecs($totalMins, $TotCalls)        return $avgMinutes)     else ('00:00')   }</AM><AMT>{  format-number($TotAmt,'0.000') }</AMT><PC>{            if($TotAmt > 0 and $TotCalls > 0) then(let $avgCallAmt := round-half-to-even($TotAmt div $TotCalls, 3) return  format-number($avgCallAmt,'0.000'))            else (0.000)          }</PC><PM>{     if($TotAmt > 0 and $totalMins > 0) then       (let $avgPerMin := round-half-to-even($TotAmt div $totalMins, 3)        return  format-number($avgPerMin,'0.000'))     else (0.000)   }</PM><PN>{           if($TAPAmt > 0 and $TotNoofAccs > 0) then             (let $avgNumAmt := round-half-to-even($TAPAmt div $TotNoofAccs, 3)              return  format-number($avgNumAmt,'0.000'))           else (0.000)         }</PN><PER>100</PER></R>) else ()             )


Multilevel activity Summary report (service number  level drill down report):error-no-collection

declare function local:getLabelName($serNo as xs:string) {   let $doc := collection('LabelCollection_1573625140685') for $label in $doc//row[@ser_no = $serNo] order by $label/@upDate descending return $label   };declare function local:getHHMMSSFromNumber($num as xs:integer) {  let $ss := xs:integer($num mod 60)  let $mm1 := xs:integer($num div 60)  let $mm := xs:integer($mm1 mod 60)  let $hh := xs:integer($mm1 div 60)  let $hhFinal := if(string-length(xs:string($hh)) >= 2) then $hh else (concat('0', $hh))  let $mmFinal := if(string-length(xs:string($mm)) >= 2) then $mm else (concat('0', $mm))  let $ssFinal := if(string-length(xs:string($ss)) >= 2) then $ss else (concat('0', $ss))  return concat($hhFinal,':',$mmFinal,':',$ssFinal) }; declare function local:getMinutesFromDutation($duration as xs:string) {   let $duration := tokenize($duration, ":")   let $hh := $duration[1]   let $mm := $duration[2]   let $ss := $duration[3]   return number(data($hh)) * 60 + number(data($mm)) + floor(number(data($ss)) div 60) }; declare function local:getViewPontChargesData() {           let $Rows := (collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][Site[@NAME='']][DOB >='2019-10-01' and DOB <='2019-10-31'])        let $serviceNumbers := string-join(distinct-values(data($Rows/CONN/@NO)),',')          let $eachServiceNumber := tokenize($serviceNumbers, ",")         for $service at $index in $eachServiceNumber          let $serviceNumber := $service      let $serviceData := $Rows[CONN[@NO = $serviceNumber]]/DETAIL/TRANSACTION/SUB_SECTION          let $nCalls := xs:decimal(sum(data($serviceData/@Calls)))          let $totalAmt := round-half-to-even(sum(data($serviceData/@Amt)), 3)          let $BNCount := count(data(distinct-values($serviceData/../../../CONN/@NO)))    let $durSum := sum(data($serviceData/@Duration))      let $totDur := local:getHHMMSSFromNumber(xs:integer($durSum))          let $tMs := local:getMinutesFromDutation($totDur)          let $totalDurSec := $durSum         return  <ROW><SNB>{ $serviceNumber }</SNB><SNL>{ distinct-values(local:getLabelName($serviceNumber)[position()=1]) }</SNL><CLS>{ $nCalls }</CLS><DUR>{ $totDur }</DUR><DUS>{$totalDurSec}</DUS><MIN>{ $tMs }</MIN><AM>{                              if($tMs > 0 and $nCalls > 0) then                      (let $avgMin := round-half-to-even($tMs div $nCalls, 2)                       return $avgMin)                              else (0.00)                            }</AM><AMT>{ $totalAmt }</AMT><PC>{                               if($totalAmt > 0 and $nCalls > 0) then                                 (let $avgPeRCall := round-half-to-even($totalAmt div $nCalls, 2)                                  return $avgPeRCall)                               else (0.00)                             }</PC><PM>{                                if($totalAmt > 0 and $tMs > 0) then                                  (let $avgPerMin := round-half-to-even($totalAmt div $tMs, 3)                                   return $avgPerMin)                                else (0.00)                              }</PM><PN>{                                if($totalAmt > 0 and $BNCount > 0) then                                  (let $avgPerNum := round-half-to-even($totalAmt div $BNCount, 3)                                   return $avgPerNum)                                else (0.000)                              }</PN></ROW>       }; let $viewPointCData := local:getViewPontChargesData() let $TotCalls := xs:decimal(sum(data($viewPointCData/CLS))) let $TotAmt := sum(data($viewPointCData/AMT)) let $totalDur := local:getHHMMSSFromNumber(xs:integer(sum($viewPointCData/DUS))) let $totalMins := local:getMinutesFromDutation($totalDur) let $TAPAmt := sum(data($viewPointCData/PN)) let $TotNoofAccs := count(data($viewPointCData/SNB)) return   (     if(count($viewPointCData) > 0) then       (for $eachRow in $viewPointCData        let $eachAmount := $eachRow/AMT        return          <R>{     $eachRow/node(),     <PER>{      if($eachAmount > 0 and $TotAmt > 0) then        (let $perAll := round-half-to-even(($eachAmount div $TotAmt) * 100, 2)         return $perAll)      else (0.00)    }</PER>   }</R>,        <R><SNB>Total/Average</SNB><CLS>{ $TotCalls }</CLS><DUR>{ $totalDur }</DUR><MIN>{  format-number($totalMins,'0.000') }</MIN><AM>{                   if($totalMins > 0 and $TotCalls > 0) then                     (let $avgMinutes := round-half-to-even($totalMins div $TotCalls, 2)                      return  format-number($avgMinutes,'0.000'))                   else (0.000)                 }</AM><AMT>{  format-number($TotAmt,'0.000') }</AMT><PC>{                  if($TotAmt > 0 and $TotCalls > 0) then                    (let $avgCallAmt := round-half-to-even($TotAmt div $TotCalls, 2)                     return  format-number($avgCallAmt,'0.000'))                  else (0.000)                          }</PC><PM>{                             if($TotAmt > 0 and $totalMins > 0) then                     (let $avgPerMin := round-half-to-even($TotAmt div $totalMins, 3)                      return  format-number($avgPerMin,'0.000'))                             else (0.000)                           }</PM><PN>{                             if($TAPAmt > 0 and $TotNoofAccs > 0) then                     (let $avgNumAmt := round-half-to-even($TAPAmt div $TotNoofAccs, 3)                      return  format-number($avgNumAmt,'0.000'))                             else (0.000)                           }</PN><PER>100</PER></R>)     else ()   )

Regards,

YSL