I cannot use pivot table as i am creating a inline table as per user requirement the scenario is like we have product name which comes under product grouping which comes under SBU wich comes under Division and then sub division,so client want to show this hierarchy in pivot table but in one column so i thoght to create an inline table and mention there product code along withe the products but the problem is the sum how to get the sum of all the products which fall under one sub division....
I cannot attach the qvw but can explain the scenario in detail
. The issue is like the client wants a report in which they want to view the hierarchy horizontally in pivot table .As far as my knowledge says that we can do this by creating an inline table with if then else statements but in this case the no. of rows are 1,117 so these many if ,else needs to be there and if I create an excel with this order and take product code or product id to map this table then there are many fields that do not have product id and they also want to view the total at Sub division level.
Here p1 is product name,pg1 is the product group,sub1 is the sub,div1 is the division ,subdiv1 is the subdivision ,this the hierarchy which user wants in the report .
There are multiple ways to do it, but the easiest way for me would be the creation of three (or more) new fields for the sum:
Newfield_1 contains the cumulative sum for Group 1 (WHERE Division IN (First set of values)).
Newfield_2 contains the cumulative sum for Group 2 (WHERE Division IN (Second set of values)).
Newfield_Summary adds Group 1 to Group 2 for each row, so it always contains the relevant total.
If you do this is script, you could drop the "Newfield_n" fields after loading.
During development, keeping them might be useful to verify totals and make sure it's working correctly.
But for the specific order i have to create an inline table as the view should have
product name which falls under one particular product grouping and then comes then SUB which contains the above values then sub division and then then division comes which contains above values and the same sequence repeats again so it is like they want to view an hierarchy which we can show the through pivot table in a singe column.