Most effective way of plotting a stacked bar with a trend line chart (1 Dimension + 2 Measures)

Hi Support,

I do have a question regarding what is the most effective

way of plotting a stacked bar with a trend line chart. Currently, we have a

customer requirement we cannot meet directly with YF functionality (seems to be

a limitation) – based on your implementation experience, when customer ask for

this, what would you do or suggest as solution?

I have no problem creating a

stacked bar with a line chart trend if it is within the same dimension by using

a Pivot table. (Example: Columns -> (Company, Amount (Sum)) and Rows ->

Period) and YF allows me to compare within the company performance by choosing

within the chart series and select the stacked bar or line graph accordingly.

However, the problem comes when I want to compare all company performance in additional

to another measure from another field. This time will be (Example: Columns

-> (Company, Amount (Sum), FI Amount (Sum)) and Rows -> Period). From

here, I want to be able to compare the accumulated FI Amount (Sum) in each

period with respect to each period accumulated stacked bar for the companies.

In the chart section, YF does not seems to be able to allow me to drag and drop

the FI Amount (Sum) as the error shows Field Limit Reached.

To workaround this problem I am facing, I used sub queries

to append all the individual companies and finally the FI amount for the period.

This will allow me to create the stacked bar and line trend chart that I wanted

but in terms of filtering I will have a problem because during the append of

the sub queries I have already filter based on company name to get those data

and doing a LEFT OUTER JOIN to the MASTER QUERY which is my period in this case.

In terms of long term feasibly and maintainability, I

personally do not think my idea is the best way to do it because assuming we

have 10 reports that is using the sub queries method above and client wants to

add 3 more companies hence we will need to add 30 sub queries to the reports

and we could accidentally missed out some reports which could be a nightmare.

Is there a more efficient way of doing it? Seems that I am not

able to find an answer in the wiki and the community forum?