Summary BI Publisher RTF Template Suggestions (Financial Report) Content Hello, I'm trying to develop an appropriate BI Publisher RTF Template for a report that we need.. Basically the report will use a Tree structure and for each level of the Tree it will get some information from databases and sort them depending on tree level.. The input parameters of the report includes: Business Unit, Fiscal Year, Accounting Period From, Accounting Period To, Level of detail requested.. The tree structure will be like this: * MAIN* Tree Member 1 - Level 1* Level 1 - Member 1 Sublevel 1 - Member 1 (L2) * Level 1 - Member 1 Sublevel 1 - Member 2 (L2)* Level 1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 1 (L3) * Level 1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 2 (L3) * Level 1 - Sublevel 1 - Member 3 (L2) * Tree Member 2 - Level 1* Level 1 - Member 2 Sublevel 1 - Member (L2) You get the idea... Let's say for argument's sake there will be only 3 levels (it will most likely be more but 3 is enough to give the idea I think). The generic report template is as follows: Member NameAllocated BudgetExpenseDifference (Budget - Expense) MAIN TOTALB + B1E + E1D + D1L1 Member 1B = Q + NE = W + MD = P + LL1 - Member 1 Sublevel 1 - Member 1 (L2)QWP = Q+WL1 - Member 1 Sublevel 1 - Member 2 (L2)N = X+ZM = Y+VL = (X-Y)+(Z-V)L1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 1 (L3)XYX-YL1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 2 (L3)ZVZ-VL1 Member 2B1....E1...D1.....L1 - Member 2 Sublevel 1 - Member 1 (L2)Q1W1P1 Now think about this but with more level 2s and 3s and 4s (Thinking we will need 5 levels). Also there will be more L1s of course, probably say around 15 at least. The idea is if a user requests (to follow the example table above) a L3 detailed report, all the rows will be showing. However if the user say asks for a L2 detailed report, L3s won't be shown in detail for each L2, but in this case L2s and L1s should still contain the correct monetary amount for budget and expense, it's just that the user will specify which level of detail they want to see and report will be structured in such a way to allow them to do this, the data inside the report will not change, just what user sees will change. I thought about creating variables for each level and using them to calculate the other amounts (like a running total), and using a hidden column to specify levels, and to use an IF condition to hide the row if the level requested != level of current row, but I'm not too sure how to implement it just yet. The report source will be a query inside PeopleSoft and the report will be generated to the webpage and / or excel depending on user preferences. How would you go around creating a template for this? Any ideas and feedback is appreciated. Thank you!

So I managed to find a thread with something akin to what I want to have: I'll try to see if I can get a workable template for my data structure.. But just for your information, in my case, my data structure will be like this: Grand TotalBusiness UnitOperating UnitChapterEconomicBudgetExpenseTotal for all Business Units----BU01Budget + BU02Budget +...BU01Expense + BU02Expense + ....-Business Unit 01---BU01Budget = OU01Budget + OU02BudgetBU01Expense = OU01Expense+ OU02Expense--Operating Unit 01--OU01Budget = Chp01Budget + Chp02BudgetOU01Expense = Chp01Expense + Chp02Expense---Chapter 1-Chp01Budget = V+WChp01Expense = A + B----Economic 1VA----Economic 2WB---Chapter 2-Chp02Budget = X+YChp02Expense = C + D----Economic 1XC----Economic 2YD--Operating Unit 02--OU02Budget = Chp01Budget + Chp02BudgetOU02Expense = Chp01Expense + Chp02Expense---Chapter 1-Chp01Budget = FChp01Expense = G----Economic 1FG---Chapter 2-Chp02Budget = HChp02Expense = I----Economic 2HI-Business Unit 02---.........................--Operating Unit 01--..............---Chapter 1-...........................----Economic 3....................----Economic 1......................---Chapter 2-----Economic 2 I know it's a big table but well, the report will be a very long report Just need to make the template do all the work and it'll be good to go.

Oracle Communities

Oracle Analytics and AI

Child Item

BI Publisher RTF Template Suggestions (Financial Report)

Ozgur

Summary

BI Publisher RTF Template Suggestions (Financial Report)

Content

Hello,

I'm trying to develop an appropriate BI Publisher RTF Template for a report that we need.. Basically the report will use a Tree structure and for each level of the Tree it will get some information from databases and sort them depending on tree level..

The input parameters of the report includes:

Business Unit, Fiscal Year, Accounting Period From, Accounting Period To, Level of detail requested..

The tree structure will be like this:

MAIN
- Tree Member 1 - Level 1
  - Level 1 - Member 1 Sublevel 1 - Member 1 (L2)
  - Level 1 - Member 1 Sublevel 1 - Member 2 (L2)
    - Level 1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 1 (L3)
    - Level 1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 2 (L3)
  - Level 1 - Sublevel 1 - Member 3 (L2)
- Tree Member 2 - Level 1
  - Level 1 - Member 2 Sublevel 1 - Member (L2)

You get the idea... Let's say for argument's sake there will be only 3 levels (it will most likely be more but 3 is enough to give the idea I think).

The generic report template is as follows:

Member Name	Allocated Budget	Expense	Difference (Budget - Expense)
MAIN TOTAL	B + B1	E + E1	D + D1
L1 Member 1	B = Q + N	E = W + M	D = P + L
L1 - Member 1 Sublevel 1 - Member 1 (L2)	Q	W	P = Q+W
L1 - Member 1 Sublevel 1 - Member 2 (L2)	N = X+Z	M = Y+V	L = (X-Y)+(Z-V)
L1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 1 (L3)	X	Y	X-Y
L1 - Member 1 Sublevel 1 Member 2 Sublevel 2 - Member 2 (L3)	Z	V	Z-V
L1 Member 2	B1....	E1...	D1.....
L1 - Member 2 Sublevel 1 - Member 1 (L2)	Q1	W1	P1

Now think about this but with more level 2s and 3s and 4s (Thinking we will need 5 levels). Also there will be more L1s of course, probably say around 15 at least.

The idea is if a user requests (to follow the example table above) a L3 detailed report, all the rows will be showing. However if the user say asks for a L2 detailed report, L3s won't be shown in detail for each L2, but in this case L2s and L1s should still contain the correct monetary amount for budget and expense, it's just that the user will specify which level of detail they want to see and report will be structured in such a way to allow them to do this, the data inside the report will not change, just what user sees will change.

I thought about creating variables for each level and using them to calculate the other amounts (like a running total), and using a hidden column to specify levels, and to use an IF condition to hide the row if the level requested != level of current row, but I'm not too sure how to implement it just yet.

The report source will be a query inside PeopleSoft and the report will be generated to the webpage and / or excel depending on user preferences.

How would you go around creating a template for this? Any ideas and feedback is appreciated.

Thank you!

Find more posts tagged with

Accepted answers

All comments

YGUTTIKONDA

Agree with ChrisThi10 .. adding to that comment in your data model please create the grouping per the requirement.. so it would decrease the burden on layout.

--YG

Christian Thibaudeau-90201

Hi, maybe you are facing the usual dilema of doing it really complicated vs developing it so that it's maintanable.

- Calculate every level in the Template but it needs more variables as you say

- Maybe do different sub-template and depending of the parameter, only call the one user ask.

Personnaly in these cases, I strongly recommand user two types of outputs.

- Summary : which is fixed and present necessary summary level

- Detail : I give them all the level needed and 99% of the time, they get the output in Excel and play with it as they want (they get satisfaction of not getting restricted to a layout).

Hope this help...

Christian Thibaudeau-90201

Hi , i can't access you file but what i usually do to get total it's :

1a - Declare in a form field a variable Inside the group you want the calculation to occur.

--> xx_trans_count : variable in my XML and the sum will be in XX_TOT_XFER

<?xdoxslt:set_variable($_XDOCTX,’XX_TOT_XFER', sum(XX_TRANS_COUNT))?>

1b - in another form field, display the total at the end (this is the total of a column for example)

<?xdoxslt:get_variable($_XDOCTX, ‘XX_TOT_XFER’)?>

--> what do you mean by 'subtotals appear on top is a nightmare' ?...on the top of the page ?

2a - Since the calculation is in a form field, nothing is dsplay.

- Yellow highlighted below are 1a

- Green highlighted below are 1b

Ozgur

Dear ChrisThi10 and YGUTTIKONDA,

Thank you for your input!

Using sub-templates is actually a good idea, thank you.

There are two main issues I found working on this template so far:

Making subtotals appear on top is a nightmare, and I still haven't really managed to do it.
Making the calculations while making the calculation numbers invisible is not something I've been able to do yet.

For more details, you can refer to the template and a sample XML file attached..

The template is not the final version of course and will still need additional parameters, I just created this while working on the kinks.

Template includes a Chapter and Economic fields, basically the users will never want to see economics separately, so what I effectively need to do is to calculate all the economics per each chapter and then put the resulting number as totals for each chapter.

XML: https://www.dropbox.com/s/kilqgf9lgtj1122/BIP_TEST_REPORT1.xml?dl=0

RTF: https://www.dropbox.com/s/o8do74yt1yfilss/Fin_Report.rtf?dl=0

Ozgur

Hi again Chris,

Thank you for your input.

Since you can't seem to get my example file, let me put a screenshot of my problem.

What the user wants to see is NOT economics, but simply the Chapter totals, which consist of economics sums per chapter. The calculations have to be done inside the template. By the layout, this total has to be up top and cannot be below. As you can see I unsuccessfully tried to compute the totals, so it shows as 0.00

Just to give you an idea, here's a screenshot of my template:

And here's a screenshot of my field browser:

Again, thank you very much for taking the time!

Ozgur

So I managed to find a thread with something akin to what I want to have:

I'll try to see if I can get a workable template for my data structure..

But just for your information, in my case, my data structure will be like this:

Grand Total	Business Unit	Operating Unit	Chapter	Economic	Budget	Expense
Total for all Business Units	-	-	-	-	BU01Budget + BU02Budget +...	BU01Expense + BU02Expense + ....
-	Business Unit 01	-	-	-	BU01Budget = OU01Budget + OU02Budget	BU01Expense = OU01Expense+ OU02Expense
-	-	Operating Unit 01	-	-	OU01Budget = Chp01Budget + Chp02Budget	OU01Expense = Chp01Expense + Chp02Expense
-	-	-	Chapter 1	-	Chp01Budget = V+W	Chp01Expense = A + B
-	-	-	-	Economic 1	V	A
-	-	-	-	Economic 2	W	B
-	-	-	Chapter 2	-	Chp02Budget = X+Y	Chp02Expense = C + D
-	-	-	-	Economic 1	X	C
-	-	-	-	Economic 2	Y	D
-	-	Operating Unit 02	-	-	OU02Budget = Chp01Budget + Chp02Budget	OU02Expense = Chp01Expense + Chp02Expense
-	-	-	Chapter 1	-	Chp01Budget = F	Chp01Expense = G
-	-	-	-	Economic 1	F	G
-	-	-	Chapter 2	-	Chp02Budget = H	Chp02Expense = I
-	-	-	-	Economic 2	H	I
-	Business Unit 02	-	-	-	......	...................
-	-	Operating Unit 01	-	-	........	......
-	-	-	Chapter 1	-	.........	..................
-	-	-	-	Economic 3	..........	..........
-	-	-	-	Economic 1	...........	...........
-	-	-	Chapter 2	-
-	-	-	-	Economic 2

I know it's a big table but well, the report will be a very long report Just need to make the template do all the work and it'll be good to go.

Christian Thibaudeau-90201

Hi, seeing your table, it would be possible to sum() column in the XML..

Withing your data structure, you can define an element that will hold the sum result..

take a look at this example. : http://www.quest4apps.com/xml-report-part3/

This way, in your XML data strcuture, sum will be calculated and then, the .rtf will only have to show the results.