I didn't understand how the combined request solved this problem but I tried my self creating a pivot table chart with new calculated column as 'Total'. The problem is I am getting value '0' on the X-axis of the pivot table bar chart instead of Total but values shows up correct. Can Someone let me know where I was doing wrong?
In short, union will put two different results together, to show under single view in OBI. Think of it like adding a row of 'Book, numbers' to report showing 'Employee, Salary'. Totally irrelevant, but matching data type puts them under single view.
You don't NEED to create pivot table to display these charts. If the results are appearing properly in normal table view, they should also appear in charts.
As per my guess, you haven't correctly named the dimension column correctly, which is why it might be appearing as '0'.
My requirement is to show values of each month along with Total value in the same bar chart. Month would be on X-axis. and on Y axis is the value. The problem with union report is,it would show total value along with each value for each month. I dont want that. I want just a separate single bar for each month for the corresponding value and at the last i want a separate bar which totals the entire value.
With the pivot view chat. I created a new calculated item and named it as Total and sum the values of all column. It shows the right value and I am getting the graph correctly like I wanted but instead of 'Total' label, i am getting the label as 'o'. this is not defined by me or a column in the dimension
Revenue_East = Case when Region = 'East' then Revenue else End
Revenue_West = Case when Region = 'West' then Revenue else End
Revenue_North = Case when Region = 'North' then Revenue else End
Revenue_South = Case when Region = 'South' then Revenue else End
Using Union report and changing in rpd would help but my requirement is based on each day of the month. I finally figured it out. I used pivot view chart and created new calculated column called 'Total' that sums up all the values of each day in a month. I got the label '0' instead of 'Total' on the graph because row colomn is in Data format so that is the reason it couldnt take varchar or char datatype value 'TOTAL' on the chart. So I ended up creating a column which is a rowcount for each record and another column which is a case statement. Then I pulled the newly created case statement column on to the X-axis and it display 'Total' just like I wanted.