excelxpathbi-publisher

Oracle BI Publisher Excel Template Connecting Parent And Child Nodes

I have one main data set for project list that contains "Project ID" columb and multiple child sets for different information for each project with "Project ID" and "Value" columns. Data sets connect via Project ID column. my bi publisher data model looks like this

My goal is to create table below.

the table I need to

When I use report editor of BI Publisher and apply following steps;

  1. Insert a table
  2. Add Project ID as first column from "main" data set.
  3. Add Value columns from child data sets.

it worked perfectly fine and matched project id of every value and project id of main data set.

But I need to use excel templated but I cant get same table using excel template. I tried couple of repeating section combination, non of them work.

How do I connect child nodes to parent for each row?

Because of confidential reasons, I cant share screenshots and don't have any copy of template.

Thanks in advance.

Solution

  • Thank you for your help, I couldn't share any screen shot or code before because I didn't have any in my PC. But I setup BI Publisher to my PC and try to recreate sample data.

    My data set

    What I am trying to was creating a table like this Table I want to create

    This is my what xdo_metadata sheet looks like.

    XDO_?XDOFIELD2?   <?NAME?>
XDO_?XDOFIELD2?   <?TASK_COUNT?>
XDO_?XDOFIELD3?   <?TASK_COUNT?>
XDO_?XDOFIELD4?   <?TASK_COUNT?>
XDO_GROUP_?XDOG1? <xsl:for-each select=".//PROJECT">

    But it didnt work as I expected. It created a table like below Output in first try

    I found out why it didnt work, Beacuse I used same aliases for the all value columns.

    Later I tried the change xdo_metadata with parent node before field names and it works perfectly fine;

    XDO_?XDOFIELD2?   <?NAME?>
XDO_?XDOFIELD2?   <?./SET1/TASK_COUNT?>
XDO_?XDOFIELD3?   <?./SET2/TASK_COUNT?>
XDO_?XDOFIELD4?   <?./SET3/TASK_COUNT?>
XDO_GROUP_?XDOG1? <xsl:for-each select=".//PROJECT">

    I guess using unique aliases in the each query like TASK_COUNT1,TASK_COUNT2,TASK_COUNT3 would work too.

    I hope it will help those who will have problems in the future.