Re: [basex-talk] Huge No of XML files.

Hi Christian,

1. What is the average size of each of your document? 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

On Tue, Dec 17, 2019 at 12:27 PM Christian Grün christian.gruen@gmail.com wrote:

Hi YSL,

...

Every day we are moving collections around 55k to 60k no of xml files

large account. Its taking more than 18 hours. At that time we want access the collection for generating report its on lock mode and scrip that collection.

Some questions back:

1. What is the average size of each of your document?
2. Will this sum up to a yearly amount of appr. 20 million files, or

do you have an upper limit? 3. Which of our API(s) are you using to add the documents? 4. How do your queries for reporting data look like?

...

Please help and do needful.

https://ell.stackexchange.com/a/17626 ;)

Best, Christian