can we get the amount field in the first column in bex ?

[former_member182467]

W have a requirement to get the report as top 10 customer's with respect to expenses in regards to the expense type by cost center. how to do that ?

I mean if there are different cost center's we need the top 10 customer's with respect to the expense type for each cost center. for example,

expense type 1

amount 1       cust 1      cost center no

amount 2       cust 2      cost center no.

amount 3       cust 3      cost center no.

amount 4       cust 4      cost center no.

amount 5       cust 5     cost center no.

..........

..........

amount 10    cust 10      cost center no

expense type 2

amount 1      cust 1     cost center no.

.....

amount 10    cust 10    cost center no.

same set above the top 10 customer in this expense type 2 .....and same like this till all the expense type N are complete.. Can anyone please suggest how to achieve this using bex ? I mean what features to use. Do we need to use any cell definition or structure to get amount in the first column ? This is a custom infocube by the way... We are getting controlling data from ECC. we are getting cost center no, expense type, amount, customer name, cost center hierarchy in the IC. How to achieve this requirement. Please advise what KF to keep in column and what KF to keep in rows and do we need to create any structure on hierarchy to get expense type as the division category ? how to split the top 10 range for every expense type ? I know we need to create condition Top 10 and select KF amount and select the cost center in the characteristic ? But my question is how do we get amount in the first column and how to create the second set of expense type for the next top 10 list and there on for all the expense type category ? Please let me know if you need any more details to proceed further ?

Thank You

DR

[former_member182470 · Accepted solution]

Is it mandatory to show Amount in first column? Otherwise you can try below approach in Query designer:--

Rows pane : Expense Type, Customer, Cost Center

Columns Pane : Amount

Create a condition on your KF with TOP N = 10 Assign Customer in char assignment tab.

Hope this helps..

[former_member182467 · Accepted solution]

Thanks for the update Suman. but that is the report layout, cant we achieve this by creating a structure in the row's and adding a KF amount to that ...but my doubt is how do we split the top 10 by expense type, it should start the next expense type and show the next top 10, for the next expense type ? can we select the 2nd or 3rd level in the cost center hierarchy for each expense type.

[former_member182470 · Accepted solution]

You can try with my approach and you observe how data is getting split. Just follow the same sequence of fields in the query as I mentioned in my earlier reply.

Ping me after that to discuss further. Cell structure is only required when you want to show intersected data with own calculations and selections. I don't think it is required in your case. So please try my approach..

[former_member182467 · Accepted solution]

Ok, i will try that out. But with your approach we cant get the amount in the first column right. and will the row's get split per each expense type ? Dont we need to add expense type along with the customer for the report's ? Can we add 2 condition's customer and expense type to get the split ?

Thanks

DR

[former_member182470 · Accepted solution]

Don't get confused. My approach will split your expense type. Amount will not be in first column. It will be as last column. Try it and let me know..

[former_member182467 · Accepted solution]

great, i will try and post you the results ? Thanks for the prompt response.

[former_member182467 · Accepted solution]

Hi Suman,

I have 2 doubts here. First of all I have all the transactions happening on the transactino date. so now my table is like this.

expense type, customer, cost center, date of the transaction, amount.

Now I am getting more than 10 rows, for each expense type. what ever option i take. For example i have Expense type, customer, cc, date in condition ( individual char selection). I want top 10 customer's for each expense type. Have selected customer only in condition and getting more than 10 rows for each of the expense type's.

Any advise, what all to select. I tried to select customer, cc and date as well. Still its not working. Also I want all the amount's to come in ascending order. Any change of setting for the KF properties ? Because for example if there are multiple line items, it shows 2 or 3 customers for 2nd position under different line's, and the amount for all the 3 lines is not in ascending. We need the amount' should be decreasing from top.

Thanks

DR

[former_member182470 · Accepted solution]

Whatever char you select in char assignment in condition, that char must be in rows/drilldown to enable condition on.

I think you are getting 10 records because of Date field in the report. You are showing much granular data in the report. If you really don' want date to come in report, you may not use it in query.

To make your KF values to appear ascending, you have to create a condition Amount > 0. But Top 10 condition makes sense with descending order. What do you say?

[former_member182467 · Accepted solution]

Yes all the rows are in the rows and for few categories the values are getting doubled. I mean the same line items comes double. I have just selected customer and CC and removed date from the rows and unchecked in the condition. still its not resolved. I put Top N and 10 in the active condition details.

How do I resolve this ?

Thanks

DR

[former_member182467 · Accepted solution]

Suman,

How to select amount's > 0 as I am getting few negative values as well ?

[former_member182470 · Accepted solution]

I mean the same line items comes double.

Please share your report screen shot to understand your data.

[former_member182470 · Accepted solution]

I think you are mixing two requirements here. TOP N can give you the top 10 customers with amounts in descending. How do you think -ve values can fit in top 10? It can happen when you have set of data which has both -ve and +ve values but up to top 10 records.

[former_member182467 · Accepted solution]

can i send it to your pers email ? It has client data.

[former_member182467 · Accepted solution]

yes i have selected top 10, but its showing +ve and -ve . i think its because of one condition is deactivating other. as currently i have checked all the three, customer, cc and date. Because we need the top 10 customer with combine char option, for each expense type, we should see the combination of top 10 with all these 3 combinations right ?

Thanks

DR

[former_member182470 · Accepted solution]

Don't give real data. Just put some data in excel sheet and upload its screen shot.

[former_member182470 · Accepted solution]

Ok. now the problem is with ascending?, Am I right? Your report data is correct. Are you admitting it?

[former_member182467 · Accepted solution]

Exp 1	$ 940.00
	$ 500.00
	$ 453.29
	$ 427.83
	$ 398.75
	$ 159.34
	$ 65.00
	$ 63.37
	$ 21.85
Exp 2	-$ 1,189.80
	-$ 1,014.44
	$ 1,189.80
	$ 1,189.80
	-$ 1,189.80
	$ 1,014.44
	$ 11,390.90
	$ 1,032.40
	$ 1,797.80
	$ 6,636.80
	$ 6,765.00
	$ 188.39
	$ 6,059.30
	$ 5,671.70
	$ 2,924.40
	$ 145.40
	-$ 6.50
	$ 1,857.70
	$ 454.77
	$ 200.98
	$ 3,736.00
	$ 3,029.26
	$ 1,741.38
	$ 395.54
	$ 558.44
	$ 212.32
	$ 604.66
	$ 236.91
	$ 1,176.50
	$ 743.61
Exp 3	$ 21.95
	$ 21.95
	$ 39.95
	$ 39.95
	$ 34.95
	$ 34.95
	$ 15.00
	$ 12.00
	$ 5.00
	$ 5.00
	$ 10.00
	$ 9.99
Exp 4	$ 75.00
	$ 75.00
	$ 49.00
	$ 20.00
	$ 60.00
	$ 52.00
	$ 52.00
	$ 50.00
	$ 50.00
	$ 50.00
	$ 50.00
Exp 5	$ 250.00
	$ 165.81
	$ 150.00
	$ 150.00
	$ 64.00
	$ 40.00
	$ 35.00
	$ 20.00
	$ 20.00
	$ 19.00

[former_member182470 · Accepted solution]

Try this thing:--

In TOP N =10 condition, do not keep any char assignment. Just choose "All chars in drilldown independently" in char assignment.

Keep in Rows pane : Expense Type, Customer, Cost Center, Date

[former_member182467 · Accepted solution]

Suman,

I have analyzed tried diff combinations and found out another point. See for example same customer wtih same date and same cc has a combination of 1000 AUD + 500 AUD....the sum equal's 1500 aud right, and if suppose this 1500 AUD comes in the 2nd place, it should display display 1500 as 1 line item instead of 2 different line items. Because of this, the number of items is increasing more than 10. You know what I mean.

theoritically its right ? as i have selected customer, cc and date, the combination of all these 3 , has 2 rows, but it should sum up all the same condtions and dispaly a single line item. and it should come as 1500 as the same combination and similarly it should show only 10 items in total with the amount's increasing. Is there any KF property for the same ?

Calculation Result as, Calculate Single value as, Cumulated, Calcuation direction, row wise, colmn wise, Standard devation etc.

Thanks

DR

[former_member182470 · Accepted solution]

Is this data coming from CUbe or DSO? Because your data should be aggregated at infoprovider level to show in one line item.

Your unique combination of keys are Cust, CC and Date. How will you have duplicate records in CUbe/DSO? Aggregation should happen, right?

Conditions in reports are just to display data as per our wish but not to aggregate data. Conditions can be toggled on/off on the report instantly.

[former_member182467 · Accepted solution]

ok some how i have restricted it, but can you tell me how to get the combinations in a single row and the amount as ascending ?

For example,

The top 10 customer's for each of the below category is displaying correctly. in regards to the amount. but if we consider each line items' for any expense category, it should come in ascending order rite. I mean if we see the total amount's irrespective of the order. its correct.....as the combination of 100 +200 = 300 come's in 2nd place, and 150 comes in 3rd place. But we dont want individual line items for each expense category. We need a summarised line item amount as 1 single row. For example, I want the results as the 2nd set. Individual expense type's amount should be in ascending order. and the line items for each expense type should be in ascending order as well. I mean currently its getting sorted with the expense type in alphabetical order like apple ball zebra. I want the report to show in amount descending order which make sense for top 10 instead of sorting on expense type.

existing

expense	customer	Dept	date	amount
Exp 1	Name1	Dept 1	1st jan 2010	50
Exp 2	Name1	Dep5	1st Mar 2010	100	300
	Name2	Dept5	12nd Dec 2010	200
exp 3	Name1	Dept1	1st june 2010	150

result

expense	customer	Dept	date	amount
Exp 2	Name2	Dept5	12nd mar 2010	200
	Name1	Dep5	1stDec 2010	100
exp 3	Name1	Dept1	1st june 2010	150
Exp 1	Name1	Dept 1	1st jan 2010	50

[former_member182470 · Accepted solution]

You just need to create one more condition on your KF < = 99999 with out any char assignments. I mean you can choose your maximum number which your KF can show roughly. This will sort the data in descending order.

Date field brings the uniqueness to the second record and it shows as a second record. To avoid that, you should remove date field in the report.

[former_member182467 · Accepted solution]

Suman,

Thanks for the reply. I have created a new condition on the amount without any reference to char. Less than or equal to 9999 and all char independently property. and its not working. I dint select any char checkbox. both the conditions are active now. but its not getting in descending order. Please advise.

Thanks

DR

[former_member182470 · Accepted solution]

Select Date in char assignment in that new condition. Give a try..

[former_member182467 · Accepted solution]

Suman,

I have put top N - select 10 and its displaying independent line times in descending order. But the overall result is always getting sorted based on the expense type "text" ascending. Do we have any option to change it with condition ?

I have created a new condition KF amount <= 999999 and tried with different options like date without date, with independent selection and with all selection etc. But its not working.

The default version of the report sorts in ascending based on char by default. Even If i save the values in the workbook, when I open the workbook its again showing in ascending. > I can change this from the right click context menu to be in descending order, but I can not figure out how to make this the default setting. The properties in the query do not have an option to sort by this key figure in an ascending or descending fashion. Can anyone tell me how to sort by a key figure?

Thanks

DR

[former_member182470 · Accepted solution]

KF sorting can be done with Conditions in Bex. I have tried my level best to help you.

Please search with term in Google " Sorting in Bex "

[former_member182467 · Accepted solution]

changing the row's in the report has a lot of effect in the result row's while using the conditions. This is resolved. Thanks.