Search code examples
xmlrtfbi-publisher

count dictinctly by group in xml bi publisher

So I have to do a report that will display and count distinctly the CwaProductCode based on CwaChannel and CwaOrderType.

Heres my xml:

 <OrderEntry-Orders>
            <CwaChannel>Customer Portal</CwaChannel>
            <CwaOrderType>UT Sales</CwaOrderType>
            <ListOfOrderEntry-LineItems>
                    <OrderEntry-LineItems>
                            <CwaLineTotalAmount2>1000</CwaLineTotalAmount2>
                            <CwaProductCode>001</CwaProductCode>
                    </OrderEntry-LineItems>
                    <OrderEntry-LineItems>
                            <CwaLineTotalAmount2>1000</CwaLineTotalAmount2>
                            <CwaProductCode>005</CwaProductCode>
                    </OrderEntry-LineItems>
            </ListOfOrderEntry-LineItems>
    </OrderEntry-Orders>
    <OrderEntry-Orders>
            <CwaChannel>Customer Portal</CwaChannel>
            <CwaOrderType>UT Sales</CwaOrderType>
            <ListOfOrderEntry-LineItems>
                    <OrderEntry-LineItems>
                            <CwaLineTotalAmount2>1000</CwaLineTotalAmount2>
                            <CwaProductCode>005</CwaProductCode>
                    </OrderEntry-LineItems>
            </ListOfOrderEntry-LineItems>
    </OrderEntry-Orders>
    <OrderEntry-Orders>
            <CwaChannel>Customer Portal</CwaChannel>
            <CwaOrderType>UT Redemption</CwaOrderType>
            <ListOfOrderEntry-LineItems>
                    <OrderEntry-LineItems>
                            <CwaLineTotalAmount2>1000</CwaLineTotalAmount2>
                            <CwaProductCode>005</CwaProductCode>
                    </OrderEntry-LineItems>
            </ListOfOrderEntry-LineItems>
    </OrderEntry-Orders>

Heres my BIP code:

<?for-each-group: OrderEntry-LineItems; CwaProductCode?>
<?if:../../CwaOrderType='UT Sales' and ../../CwaChannel='Customer Portal'?>
<?for-each: current-group()?>
<?CwaProductCode?>
<?xdoxslt:set_variable($_XDOCTX, 'countFund', xdoxslt:get_variable($_XDOCTX, 'countFund')+1)?>
<?xdoxslt:set_variable($_XDOCTX, 'TotalCount', xdoxslt:get_variable($_XDOCTX, 'TotalCount')+1)?>
<?xdoxslt:get_variable($_XDOCTX, 'countFund')?>
<?xdoxslt:set_variable($_XDOCTX, 'countFund', 0)?>
<?end if?>
<?end for-each-group?>
<?xdoxslt:get_variable($_XDOCTX, 'TotalCount')?>

But the result came out as if there was no if condition. For example, the CwaProductCode will display the correct result, but it will count everything altogether as if there was no filtering at all.

Not sure where went wrong.

Solution

  • Once you do a group-by, accessing any parent above that grouping returns ambigous data. It might always point to the first 'OrderEntry-Orders' node. You can see this if you change the order of elements in the xml.

    If your requirement is to group only those 'OrderEntry-LineItems' nodes which has CwaOrderType='UT Sales' and .CwaChannel='Customer Portal', you can give that filter right in the grouping like this:

    <?for-each-group: OrderEntry-LineItems[../../CwaOrderType='UT Sales' and ../../CwaChannel='Customer Portal']; CwaProductCode?>

    Try this code:

    <?for-each-group: OrderEntry-LineItems[../../CwaOrderType='UT Sales' and ../../CwaChannel='Customer Portal']; CwaProductCode?>
<?for-each: current-group()?>
<?CwaProductCode?>
<?xdoxslt:set_variable($_XDOCTX, 'countFund', xdoxslt:get_variable($_XDOCTX, 'countFund')+1)?>
<?xdoxslt:set_variable($_XDOCTX, 'TotalCount', xdoxslt:get_variable($_XDOCTX, 'TotalCount')+1)?>
<?xdoxslt:get_variable($_XDOCTX, 'countFund')?>
<?xdoxslt:set_variable($_XDOCTX, 'countFund', 0)?>
<?end for-each-group?>
<?xdoxslt:get_variable($_XDOCTX, 'TotalCount')?>

    Gives this result:

    001
1
1
005
1
005
1
3

    <?for-each-group: OrderEntry-LineItems[../../CwaOrderType='UT Sales' and ../../CwaChannel='Customer Portal']; CwaProductCode?>
<?CwaProductCode?>
<?count(current-group()/.)?>
<?xdoxslt:set_variable($_XDOCTX, 'TotalCount', xdoxslt:get_variable($_XDOCTX, 'TotalCount')+ count(current-group()/.))?>
<?end for-each-group?>

<?xdoxslt:get_variable($_XDOCTX, 'TotalCount')?>

    gives

    001
1
005
2
3