Manual Resequencing Of Records

[former_member516039]

In my attempt to create an income statement I have reached the point where I have added a group name to each record of data. I now have my report showing one line per group. However, it's doing a default sort of alphabetical ascending on the group name. I need to be able to customize the sequence of groups as well as add various text fields and subtotals throughout the records. Is this possible?

[amitrathi239 · Accepted solution]

How many groups do you have in the report?If these are some 10 or 20 then you can do by sort in webi report.Right click Sort->Advance

For subtotal row use Break and use the subtotal.

[former_member516039 · Accepted solution]

I was able to use Advanced Sort to get the rows in the order I want but I don't think putting Breaks will help. I want to insert rows that are the sums of existing rows and not add default subtotals.

[amitrathi239 · Accepted solution]

are you expecting like this.

if not then share some screenshot for what are you expecting

[former_member516039 · Accepted solution]

The top section shows what my report is outputting. The bottom sections shows how I want to add calculations based on values in specific rows. For example, I want Revenue to be inserted after COGS and be the result of Sales minus COGS.

[amitrathi239 · Accepted solution]

In this case you need to create the multiple tables for bottom tables.Later use relative position to adjust the distance between the tables.

[former_member516039 · Accepted solution]

http://www.myaccountingcourse.com/financial-statements/images/income-statement-example.jpg

I don't think you are understanding what I am trying to do. I simply want to insert calculations between rows in the existing table. Is BusinessObjects not capable of doing that sort of design? I know that Insight does this as I've seen Income Statements designed with Insight. There are not separate tables in this design.

Here's an example of what an income statement looks like:

[former_member516039 · Accepted solution]

Is there a reason these forums don't allow proper pasting? Or is it only my browser that won't allow it?

[former_member198519 · Accepted solution]

is this what was required?

[Group]= If([Query 3].[Col1] ="Sales" Or [Query 3].[Col1] ="COGS") Then "Revenue" ElseIf([Query 3].[Col1] ="Labor" Or[Query 3].[Col1] ="Admin") Then "Expenses"

and for Revenue - Expenses, use the below folrmula

=(Sum([Actual Current Period]) Where ([Group]="Expenses")) - (Sum([Actual Current Period]) Where ([Group]="Revenue"))

Hope it make sense.

- Kuldeep

[amitrathi239 · Accepted solution]

I understood your question.In webi you can achieve but you need to go with multiple tables and relative position to align tables. Straightforward you can't.Your attached screenshot is one example of Crystal report.

one option is to create the Hierarchy in your back-end system like your requirement and use in the report.

[former_member516039 · Accepted solution]

Kuldeep, I think I understand most of what you saying. The difference between your example and what I am doing is that what you are calling Col1 already are groups in my report. They consist of dozens of individual records that I have put into the categories I need for the Income Statement.

[former_member516039 · Accepted solution]

But what about the rows that don't come from any table? That are simple calculations based on the rows onscreen? Should I be using Crystal Reports to do this instead of BusinessObjects? This is they type of report design that we were envisioning when deciding to use BusinessObjects.

[former_member198519 · Accepted solution]

Your requirement is a bit tricking to achieve in Webi report. Having said that, according to me it can be archived via Crystal Reports. But you need to provide exactly what you need.

- Kuldeep

[former_member516039 · Accepted solution]

If I made each of these groups (Sales, COGS, etc.) the highest level of a hierarchy, with the individual accounts contained in those groups the only other members (e.g. Sales might be accounts 100 through 400, COGS might be 600 through 900, etc.) and then ran individual reports so that each result was a single row/group, how could I get the equations that I wanted, such as a field that was Sales minus COGS?

[amitrathi239 · Accepted solution]

If your report is based in the BEx query then you can create the structure in BEx query and can use in the webi report

[former_member516039 · Accepted solution]

I don't know what is meant by BEx query. I created the report by building a query in Web Intelligence using data from a BusinessObjects universe.

[former_member516039 · Accepted solution]

Basically it sounds like that if my Income Statement includes 20 groups that I've defined I will have to run 20 different queries, filtering each query so that it only includes that group. Then I can manually add the various groups to a new report and add blank fields where I wish that can hold formulas doing math on the values in the various groups. Does that make sense?

[mhmohammed · Accepted solution]

Hi Jeff,

Yes, it makes sense and I understand. But, it's going to kill the performance of the report when you have 20 queries in the report (I hope you don't do that), and you'll obviously have to merge dimensions on the common objects (only which are needed, not on all common objects). Merge dimensions impact the performance, so queries is going to kill the performance.

Makes sense?

Can't you create variables in the report itself for different groups without creating multiple queries? That'd be one of the preferred solutions.

@Gurus: Please chime in.

Thanks,

Mahboob Mohammed

[former_member516039 · Accepted solution]

I definitely don't want to impact the performance, especially since the source table has over a million records. In addition to that I had to hide many columns just to get to the basic format of what I want the end user to see.

I would love to use variables but I'm not quite sure how to do that to get the results I need, as the numeric values shown, other than the Variance, are already variables. The first column, the groups, are assigned by the hidden account number column. This image I made using eFashion shows what I'm trying to do, though in a much simpler way. The two rows are individually placed elements but formatted to look like correct with another field added that shows the difference between the two. There would other groups below these, etc., also with subtotals. It's imperative that I be able to place these free-form fields as needed.

[mhmohammed · Accepted solution]

Jeff,

Currently, I'm working for a Finance Team and have created Income Statements and P&L reports, they are usually one of the most complex reports and tricky to create too.

Some sample images what I created look like below:

• For Total Company (including all Divisions and Regions) created using a Cross Tab

• By Division: (Division show on Top in the Cross Tab)

• By Region: (Region show on Top in the Cross Tab)

The report can be created using Multiple tables or just one table, each method has pros and cons.

Multiple tables (or Cross tabs):

Create different tables for a set of Revenue Streams grouped together and one for each Sub Total. They help us to reduce the number of variables, but the filters on each tables differ on each of those tables which is not bad. But, if you look at the By Division tab snap I have here, we may see some of those tables shrink and show less Divisions if you don't have data for all possible combinations (Revenues &/or Expense for Streams 1,2,3 don't have data for Division 1 or Region 2, then the table will be shrunk and wont shoe Division 1 or Region 2 in the report).

Multiple variables:

We can do this by creating many variables, may end up creating as many variables as the number of cells in a table, each cell may be a different formula similar to excel.

What I did is:

I created multiple queries:

1. Query 1 for Current Year's Data (using 2 combined queries)
   • Combined Query 1: for Revenue data
   • Combined Query 2: for Expense data
2. Query 2 for Current Year's Data (using 2 combined queries)
   • Combined Query 1: for Revenue data
   • Combined Query 2: for Expense data

And used Cross tabs in the report, but had table shrinking problem, in that case, I created one variable each for all the cells in a block. Ex: Suppose there is no data for Expense for Stream 10 for Regions 2 and 3, the table would shrink and show nothing for Regions 2 and 3, so, what I did is created below variables and put them in a cross tab with as many columns and formatted to look just like the other tables.

Expense for Stream 10_MTD_Region 1

Expense for Stream 10_YTD_Region 1

Expense for Stream 10_Prior Yr MTD_Region 1

Expense for Stream 10_Prior Yr YTD_Region 1

Expense for Stream 10_MTD_Region 2

Expense for Stream 10_YTD_Region 2

Expense for Stream 10_Prior Yr MTD_Region 2

Expense for Stream 10_Prior Yr YTD_Region 2

Expense for Stream 10_MTD_Region 3

Expense for Stream 10_YTD_Region 3

Expense for Stream 10_Prior Yr MTD_Region 3

Expense for Stream 10_Prior Yr YTD_Region 3

Makes sense?

How many queries do you currently have without even thinking about those 20 queries (which you'll never add)? What are the different queries used for?

Thanks,

Mahboob Mohammed

[former_member516039 · Accepted solution]

Thanks for the detailed reply. I didn't get a chance to look at it in depth yet but will definitely do so. I was afraid that a financial statement was not going to be an easy report to cut my teeth on.

[Former Member · Accepted solution]

Hi Jeff,

Try to create another group on top of the existing group.

Group2=If (Group1 inlist ("Sales";"COGS") ; "Revenue" ;if(Group1 inlist("Admin";"Labor");"G2"))....etc

Add this new group to your table and then apply break on this new group. Then delete the row between the each blocks formed by breaks. So you get single table.

In the footer row you can put the formula that is needed.

In my example Group1 is country and group2 is continent. The bold text is the break footer

Continent	country
G1	India
G1	1
	country
G2	Philipp
	Singapore
G2	2

[mhmohammed · Accepted solution]

Hi Jeff,

Please follow Divya's suggestion, that is one of the best ways to do it, which I forgot to mention last time. The only thing I don't about that way is, whether you'll have table shrinking problem/issue, if you're using multiple Crosstabs/Tables and if there is no data for a certain combination of values from different objects.

Thanks,

Mahboob Mohammed

[former_member516039 · Accepted solution]

This looks like something I might want to try but I'm not sure where to create the new groups based on existing groups. For example, if I want to create a new group called Revenue and have that group contain groups Sales and Cost of Goods Sold where do I define Revenue? If I right click on the existing group column Sales I can only work with the existing groups. It doesn't seem to let me create a new group. If I insert a new column to the left of my existing group column I can't seem to create new groups in that column, either.

[Former Member · Accepted solution]

Hi Jeff,

What we meant by creating new group is create a variable as mentioned in previous step

Group2=If (Group1 inlist ("Sales";"COGS") ; "Revenue" ;if(Group1 inlist("Admin";"Labor");"G2"))....etc

Add this variable to your table and then apply break and add the calculations at the footer level.

Then hide this variable by right clicking this new column

[former_member516039 · Accepted solution]

To test this procedure I created a new variable called rv_Category and gave it just two possible results: "Revenues" or "Expenses". When I insert this column it looks fine but when I insert the Break I get #MULTIVALUE display at the bottom of the two breaks. Is this normal?

[former_member516039 · Accepted solution]

I've encountered a couple of other issues with this, as well, using the sample data I have. I can neither manually sort by the new variables or add sums to the bottom of them, as shown in the images.

[mhmohammed · Accepted solution]

Hi Jeff,

That object rv_Category seems to be a Measure, can you change it to Dimension and see if it fixes any of your issue(s).

Thanks,

Mahboob Mohammed

[former_member516039 · Accepted solution]

Mahboob, you were correct that it had defaulted as a Measure. However, even changing it to a Dimension and refreshing the report didn't fix the main issues. I still can't custom sort or add a sum. However, the #MULTIVALUE error went away. Thanks for that helping!

[Former Member · Accepted solution]

Hi Jeff,

Can you show the screenshot where you were trying to add sort. I have created similar case with sample universe and am getting the option of default as well as custom sort option.

Hope you are trying to right click on new column rv_Category -->sort-->advanced --> Custom Order ...

[former_member516039 · Accepted solution]

Divya, today when I tried the option to sort by Values was there. It wasn't after I first changed it, or maybe I'm just crazy. However, I still do not have the option to add a Sum to the Break footers.

[mhmohammed · Accepted solution]

Hi Jeff,

Why do you say that you don't have option to add Sum in the Break Footer? Can you please try to add formula in the cell you selected as =Sum([Actual Current Period]) and see what happens?

Thanks,

Mahboob Mohammed

[former_member516039 · Accepted solution]

You are spot on again, Mahboob! Manually entering the formula worked. I was trying to use the Function button or the right click Insert menu and both options are grayed out. I just assumed that the cell was not eligible for a Sum because of that. Never overlook the obvious, I guess!

[former_member516039 · Accepted solution]

We're getting closer. The manual sequencing and adding of some new cells is allowing me to get near the final formatting for a prototype. What is the easiest way to get rid of the grid lines between cells? I'd like to have just numbers on a plain background.

[mhmohammed · Accepted solution]

Hi Jeff,

Do you want to get rid of all the grid lines? No matter which row and column it is, e.g. header row, data rows, break footer Summary row, Grand Summary row?

Go back to the Structure mode, hold CTRL and select all the cells one by one (while holding CTRL), then right click and select Format Cell -> Border -> click that No Border icon (just below the icon that has black borders -> click Apply and OK.

Voila.

Thanks,

Mahboob Mohammed

[former_member198519 · Accepted solution]

Make use of border editior for this:

Just make the Style as None and Change the color to White.

[former_member516039 · Accepted solution]

That worked. I can see, though, how that could be tedious if you have a LOT of rows and columns in your structure.

[mhmohammed]

Hi Jeff,

Check this out.

https://scn.sap.com/thread/3272367

Thanks,
Mahboob Mohammed

[former_member516039]

This works for getting the rows into the desired sequence, but then how do I add a new row that is, for example, the values of Row 1 minus Row 2? If I try to Insert Row Below it seems to add a new row under each existing row.