I had a math professor who said stuff like "The problem is always tough until you find the solution", which makes sense, if you don't think about it.
I was tasked with solving what seemed like a tough problem. An existing BIP report used this XML structure to generate MS Excel output using an RTF template.
G_MAIN
G_CUSTOMER
CUSTOMER
TOTAL_SALES
G_PRODUCTS
G_PRODUCT
PCT_TOTAL
SALES_AMT
The output from a BIP report needed to be changed so that it went from this
to this
Getting the XML data to include all products, even ones that the customer did not purchase, was easy - good old data densification. In addition, an independent group of all products was required to display product names in the labels. Effectively, the XML structure changed (a little) to
G_MAIN
G_ALLPRODUCTS
G_CUSTOMER
CUSTOMER
TOTAL_SALES
G_PRODUCTS
G_PRODUCT
PCT_TOTAL
SALES_AMT
To get the XML rows to appear as columns wasn't too bad either. All that was needed was a <?for-each@cell:grpname?> directive and the BIP engine takes care of the rest.
The RTF template looks like this: where F = <?for-each@cell:grpname?> , end = <?end for-each-group?>
I was tasked with solving what seemed like a tough problem. An existing BIP report used this XML structure to generate MS Excel output using an RTF template.
G_MAIN
G_CUSTOMER
CUSTOMER
TOTAL_SALES
G_PRODUCTS
G_PRODUCT
PCT_TOTAL
SALES_AMT
The output from a BIP report needed to be changed so that it went from this
Customer | Total $ | Breakdown by Product | ||
Product | % of Total | Sales $ | ||
Cust1 | 100,000.00 | Prod1 | 25.00% | 25,000.00 |
Prod2 | 30.00% | 30,000.00 | ||
Prod4 | 45.00% | 45,000.00 | ||
Cust2 | 200,000.00 | Prod2 | 40.00% | 80,000.00 |
Prod3 | 25.00% | 50,000.00 | ||
Prod5 | 35.00% | 70,000.00 | ||
Cust3 | 1,000,000.00 | Prod1 | 30.00% | 300,000.00 |
Prod3 | 40.00% | 400,000.00 | ||
Prod4 | 15.00% | 150,000.00 | ||
Prod5 | 15.00% | 150,000.00 | ||
Cust4 | 2,000,000.00 | Prod1 | 20.00% | 400,000.00 |
Prod2 | 20.00% | 400,000.00 | ||
Prod3 | 20.00% | 400,000.00 | ||
Prod4 | 20.00% | 400,000.00 | ||
Prod5 | 20.00% | 400,000.00 |
to this
Customer | Total $ | Prod1 | Prod2 | Prod3 | Prod4 | Prod5 | |||||
% of Total | Sales $ | % of Total | Sales $ | % of Total | Sales $ | % of Total | Sales $ | % of Total | Sales $ | ||
Cust1 | 100,000.00 | 25.00% | 25000 | 30.00% | 30000 | 0.00% | 0 | 45.00% | 45000 | 0.00% | 0 |
Cust2 | 200,000.00 | 0.00% | 0 | 40.00% | 80000 | 25.00% | 50000 | 0.00% | 0 | 35.00% | 70000 |
Cust3 | 1,000,000.00 | 30.00% | 300000 | 0.00% | 0 | 40.00% | 400000 | 15.00% | 150000 | 15.00% | 150000 |
Cust4 | 2,000,000.00 | 20.00% | 400000 | 20.00% | 400000 | 20.00% | 400000 | 20.00% | 400000 | 20.00% | 400000 |
Getting the XML data to include all products, even ones that the customer did not purchase, was easy - good old data densification. In addition, an independent group of all products was required to display product names in the labels. Effectively, the XML structure changed (a little) to
G_MAIN
G_ALLPRODUCTS
G_CUSTOMER
CUSTOMER
TOTAL_SALES
G_PRODUCTS
G_PRODUCT
PCT_TOTAL
SALES_AMT
To get the XML rows to appear as columns wasn't too bad either. All that was needed was a <?for-each@cell:grpname?> directive and the BIP engine takes care of the rest.
FE = <?for-each:groupname?>, E = <?end for-each?>
No comments:
Post a Comment