Dynamic workbook allowing drilldown not affecting format?

[Former Member]

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

[gunesbt · Accepted solution]

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:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d032116a-1ba6-2e10-8db6-d84e7dcc9...

Regards,

Güneş

Edited by: sunnybt on Dec 24, 2011 6:45 PM

[Former Member · Accepted solution]

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

[gunesbt · Accepted solution]

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ş

[Former Member · Accepted solution]

Dear Güneş

çok teşekkür ederim .

Yeni Yılınız Kutlu Olsun 2012 !

Cheers

Pascal

[gunesbt · Accepted solution]

Hey Pascal,

il n'y a pas de quoi

bonne année

Regards,

Güneş