on 12-24-2011 10:58 AM
Dear Experts
I created 2 queries because of technical constraint. 1 query is set with a certain setting that affects another display requirement. So, 2nd query is created.
Can 1 workbook use 2 queries but allow drilldown such that the formatting will not be affected adversely in the workbook?
That is, if a workbook is drilled-down, eg. show more information in the rows , by displaying additional characteristic columns, if there is formatting/Excel formula in certain cells, how can this formatting/cell formula be preserved?
For example, workbook achieves the display requirement by referrencing the cells from the 2 queries. But the user needs to drill down in the workbook. How will the referencing be affected or preserved?
Please let me know if this is possible.
Happy holidays.
regards
Pascal
Hi Pascal,
If the place of a cell changes by some more drill down or other things, then unfortunately excel formulas based on the cell may display wrong values.So workbook with such excel formulas is dangerous.However, you can create another sheet and add the same query to that sheet with different named(but the same) dataprovider.And do this for navigation pane,too.Then you will have two queries in different sheets and not affecting each other.And then allow the user to drill down on second sheet and not to change the first sheet.(So the formula in the first sheet will still show correct values)This is not a good solution but may satisfy your need.
Here is a step by step workbook document which may be helpful:
Regards,
Güneş
Edited by: sunnybt on Dec 24, 2011 6:45 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Sunnybt
Ok, thanks for confirming the understanding.
Seems like to meet complex requirements, BEx itself does not have sufficient functionality and we need to user Excel macro programming.
Any macro available for BEx in the market we can download and use instead of re-inventing the wheel?
Thanks!
regards
Pascal
Hi Pascal,
Unfortunately I don't think that such ready-to-use macros available in BEx like BI content(and I confirmed this by searching)It depends on the format of your table and requierements may be very different(one more reason is that this is an excel issue)
However,I found that link:http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm
In addition, in case of any problem while running the macro in BEx, you can find many threads in sdn.
Regards,
Güneş
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.