Sep 16 2007

Variable Relative Block Positioning

Categories: Full Client, Report Techniques, Variables! Dave Rathbun @ 11:19 pm

The Challenge

Both Desktop Intelligence and Web Intelligence provide options to coordinate the placement of one block based on the size of another. This technique is used when you have more than one block on a page and you want the lower blocks to move up and down (or left and right) on the page based on the volume of data shown in the “relative” block. This works quite well with two blocks as shown below.

Sample two-block layout

The block on the left in this case (the blue box) would be the anchor block. The block on the right (the red block) would float relative to the anchor block. I can accomplish this by right-clicking on the floating block and selecting Format Block. The Appearance tab includes the following settings that I can tweak:

Setting the floating attributes

This technique works well for a chain of tables either horizontally or vertically. But what if I have the following scenario where I need to float blocks both horizontally and vertically?

Sample three-block layout

The screen shots that will be included in this post are taking from Business Objects 6.5. The same concept should work in Web Intelligence as well, although I don’t at this time intend to post those instructions. If you don’t know how to simulate the “hide block” option in Web Intelligence you can review my prior post on Chart Alerters in Web Intelligence to see how that could work. Now on with the show.

The Setup

I created a query with Year, City, Store Name, Lines, and Revenue from the eFashion universe. My requirement calls for a three-block layout with the top-left block being store revenues, the top-right block is annual revenues, and the lower chart block will show lines revenues in a bar chart. Either the store block or the annual sales block could grow vertically. My requirement also calls for a prompt on the state. My challenge will be to figure out how to make the chart shift up or down based on either of the two top blocks. There is no option to make one block relative to two other blocks, so I will have to be creative. I like challenges like this. :)

Take Care of the Basics First

The first step I take to solve this challenge is to give each of my blocks a descriptive name. Since the annual sales block is to the right of the store sales block I will let it float horizontally based on the width of the store block. That’s fairly straightforward. Here is what the data looks like when I refresh the report for Colorado:

Colorado Sales Results

Everything looks fine. But when I select Texas for my second refresh notice the problem that I have:

Texas Sales Results

I could solve this by making the chart block float relative to the store revenue block. But that won’t help if there is only one store that has ten years of sales. In that scenario the annual sales block will encroach on the lines chart just like the store sales block has done in this case. I need to make the chart block float relative to two blocks rather than just one.

Variables to the Rescue

The solution requires me to be able to tell which block has more data. In this simple scenario it will not be too hard. I have two blocks each with one dimension each. If I count how many dimension values there are and compare the two, I will be able to tell which block is going to be bigger. Here is the variable that I created, called Which Chart:

Flag variable screen shot

This flag returns two distinct values: Year Chart or Store Chart. The formula is structured in such a way that the flag returns the name of the chart that I want to hide. But wait a minute, I only have one chart, right? How does one chart end up with two names?

Double Charts

In my earlier post on Chart Alerters I showed how to create two duplicate charts, one green and one red, and then set up formulas to only show one or the other. I think I can use that trick again here. :) To solve this challenge I will create two charts. In this case the charts are identical other than their names. They even share the exact same location on the page, as shown here.

Overlay charts screen shot

By the way, the Structure Mode option from Business Objects really helps in completing this report.

The final steps are to float each chart relative to one of the two upper blocks, and then hide based on the variable that I created earlier. If the annual block has more rows than the store block, then the chart that floats relative to the store block needs to hide since it won’t move down far enough. If the store block has more rows then the chart related to the annual block needs to hide for the same reason. Here are the screen shots of those settings:

Hide year block screen shot
Notice that the float is relative to the Annual Sales block, and the hide formula is active if the chart to hide is the Year Chart.

Hide store block screen shot
For the second chart the float is relative to the Store Revenue block, and the hide formula is active if the chart to hide is the Store Chart.

Test the Results

Once I finish creating my two charts, configure the relative float numbers, and create my hide conditions, how does it all look? The pictures tell the tale…

Colorado working screen shot

Texas working screen shot

Summary

The purpose of this article was to show how to simulate the ability to float a block relative to two anchor blocks rather than just one. Since Business Objects does not provide this feature directly I used a variable, two charts, and a “hide block” setting instead.

Associated Downloads

  • BusinessObjects 6.5 Sample
    This download was built using the eFashion sample database provided by Business Objects. It should be accessable to anyone using version 6.5 or higher. The source file is zipped so you will need an unzipping utility to extract the file.

24 Responses to “Variable Relative Block Positioning”

  1. Comment by Vipul

    Hi Dave,
    I need some help…
    I need to make my table block floating based on the upper to table blocks.
    Please guide me, how can I acheive this.

    Thanks,
    Vipul

  2. Comment by Dave Rathbun

    That’s exactly what this post is about. Did you read and understand it? If not, that’s your first step. :) There’s even a sample to download…

  3. Comment by Andreas

    Nice one, Dave!

    Now, I am using WebIntelligence XI R2 heavily at the current project (Can you hear me now…), and I am positioning tables and charts relatively to each other… so all seems well…

    Ya think, huh?

    Well, change one of the anchor tables such as removing rows/footers/headers, etc. and it blows the dependent tables/charts out of the water.

  4. Comment by Brian Malecha

    Dave – this is great information. I’m struggling doing the same formatting in Webi – BOXI…. where do I find this information?

    Thanks,

    Brian

  5. Comment by Brian Malecha

    dave – sorry disregard – just found it – need to hit the miniscule “…” button… guess I need a bigger monitor.

  6. Comment by Dave Rathbun

    Brian, thank you for your comments. In my opinion, it’s always nice to have an excuse to justify a hardware purchase. :-)

  7. Comment by Wasi

    Hi Dave,
    Using your method really helped me but I have one question if both upper blocks are empty / blank than both conditional block (Graph in your example) appears on the report. Can you please help me with any work around.

    Thanks,
    Wasi

  8. Comment by Dave Rathbun

    Hi, Wasi, sounds like you have a problem. :) I think for one graph you would have to check only one block, and for the other you would have to check both.

  9. Comment by Wasi

    Thanks Dave, I found a work around. In one of the SQLs I add a union query with one blank record thus one block will always have some data.

    Thanks :)

  10. Comment by JJ

    Dave,

    My experience is primarily with Webi on XIr2 and above.

    I’ve solved this problem in a different way. I create an empty section based on a dummy variable. As the blocks above push down, the section moves and moves the blocks below it.

    Pseudocode:
    Section 1
    Block 1 Block 2
    Section 2
    /Section 2
    Block 3 Block 4
    section 3
    /Section 3
    Block 5
    /section 1

    Everything is part of section one, but the blocks are outside of Section 2 and Section 3

    The downside to this is Webi isn’t really happy embedded sections. If you add a new one above, everything gets mashed together. Adding below is okay.

  11. Comment by Dave Rathbun

    Hi, JJ, and welcome to my blog. It’s great to see other solutions for any of the technical challenges being faced, so thanks for sharing your tip. 8-)

  12. Comment by Raja

    Can anybody tell me how to create a dummy variable in xi universe. What must be its select statement and where statement. I think it must be Character datatype.

  13. Comment by Dave Rathbun

    Hi, Raja, if you have general questions not related to a blog post I suggest that you post on the community board BOB instead, thanks. :)

  14. Comment by deepak

    Hi,

    Ever tried waterfall charts using this trick??

  15. Comment by Dave Rathbun

    Hi, deepak, and welcome to my blog. To answer your question, no, I have never tried to create waterfall charts with this technique.

  16. Comment by Shiva

    Hey Dave Thank you very much for sharing an ounce of knowledge from the Chunks you have.

    My question… is not different.. its the same Wasi asked. I’m not able to understand your answer to Wasi. though the work around by Wasi works fine…. but i just want to avoid that Union.

    Regards,
    Shiva

  17. Comment by Dave Rathbun

    The technique involves checking to see if a block was empty. What I suggested was that for one graph it should continue to work the same way, but the other graph would have to check for both “above” blocks, and if both are empty (the situation outlined in a prior question) then it should not appear at all. That would prevent both “lower” blocks from showing up at the same time if both “upper” blocks were empty.

  18. Comment by Shiva

    Thanks for the quick response.

    Regards,
    Shiva

  19. Comment by Krishna

    Hi Dave

    The relative blog post is absolutely great. I have a slightly different requirement. I am positioning a pie chart adjacent(towards right) of the table in the report. Report has 2 secrions. I have enabled the Avoid Page Break proeprty for the table. I want both the graph & table to be displayed together in same page. Sometimes the table is displayed in next page & chart in upper page & vice versa

    Any work around for this

    Regards

  20. Comment by Dave Rathbun

    Hi, I would try something sneaky like forcing the pie to be positioned 1 pixel below the top of the table block. I expect you already have relative positioning set to the right of the table. By setting a position relative to the top of the table block, when it (the table) moves to a new page then the pie should also.

  21. Comment by Mike

    Hi Dave,
    Very useful post. I have a different problem. The report (BI 3.1) is comprised of cross tab block on the left and a horizontal block on the right so to the user it appears as one table. The horizontal block has a “Notes” object (column) with up to 500 characters which is set to autofit height, so the user can see all the comment. To get the crosstab table to align correctly, I added a summary column and replaced the sum with the Notes object and set its width to same width as it is in the horizontal table. Now everything lines up fine no matter on long a note is. However, I would like to “hide” the notes column in the cross tab report. Unfortunately the number of columns in the cross tab varies between 4 and 16 columns depending on the data selected. Is there a way to relatively position negatively the horizontal table over the Notes column added in the cross tab? The only thing I can think of is to make a duplicate of the crosstab, limit it to just one row shade and font it white, drop the Notes column and then relative position the horizontal block to that “hidden” cross tab report horizontally and then to the same thing the vertically that the crosstab is vertically position to.
    Regards,
    Mike

  22. Comment by Swapnil

    Thanks for the wonderful article! I had similar requirement from the client and your solution worked absolutely fine. Thank you.

  23. Comment by Mahboob Mohammed

    Hi Dave,

    Would you be able to provide some feedback on a similar blog that I wrote? It’s just that, I didn’t create multiple charts, I’m using a Blank cell as the Container component (of Dashboard/Xcelsius) here.
    https://blogs.sap.com/2016/08/30/relative-positioning-of-a-block-with-respect-to-2-or-more-blocks/

    Thanks,
    Mahboob Mohammed

  24. Comment by Tushar Soni

    Hi. Please help me. I need to add a left footer to a crosstab. Its kinda similar to right footer but I need a column on left hand side.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

 []                 

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.