Most effective way of plotting a stacked bar with a trend line chart (1 Dimension + 2 Measures)
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?