Jul 30 2013

Pivot UserResponse() Values Into Rows

Categories: Report Techniques, Variables! Dave Rathbun @ 6:15 am

Several years ago I wrote a post that has generated a fair number of follow-up questions and comments. The post was related to splitting user response values onto separate rows and it used some basic string operations to do that. The important distinction is that the values were on different rows but remained in a single cell, which meant the output was suitable for a header cell.

Recently I got a comment that posed this question:

In one of my reports there is prompt to select the Fiscal Year and the user can select multiple LOVs. Prompt Name is “Year”. Say for example the user enters 2011,2012 and 2013. On using the function userresponse the report will show 2011;2012;2013

My requirement is to identify minimum value from the LOVs that the user has entered. In this case the report should show the mininum value as 2011. Can you please guide me on how to achieve this?

Continue reading “Pivot UserResponse() Values Into Rows”


Mar 30 2012

Drilling to Details

Categories: Report Techniques, Variables!, Web Intelligence Dave Rathbun @ 8:30 am

One of the frequent requests that I see goes something like this:

I have a hierarchy set up. When I drill to the bottom of the hierarchy, I want extra detail objects to show up.

This seems like it should be the default behavior, right? If I take the time as a universe designer to properly classify objects as dimensions or details, and also take the time to set up hierarchies, then it would seem that reports would recognize and utilize that information.

Unfortunately they don’t. That means I have to use some report functions and set up some variables and do some creative formatting to make it work the way I think it should. I first showed how to do this with Desktop Intelligence way back in 2000 at the Business Objects conference in Washington D.C. Today I will update the technique and show how it can be done in Web Intelligence. Continue reading “Drilling to Details”


Jul 07 2010

Want To Remove Drill Indicators? Use A Variable…

Categories: Report Techniques, Variables! Dave Rathbun @ 7:38 am

In some cases I have created reports that were in drill mode that were not really drillable. For example, I showed a technique a few years back that would let a report developer swap measures out on a chart using a drill filter. It has been working great for quite a few years now. However, as a side effect any dimension on the report blocks that participates in a hierarchy becomes drillable. Sometimes I don’t want that, and there is an easy fix. Continue reading “Want To Remove Drill Indicators? Use A Variable…”


Nov 25 2009

Displaying UserResponse() Values On Separate Rows

Categories: Report Techniques, Variables!, Web Intelligence Dave Rathbun @ 7:00 am

I am often asked why I still participate so much on BOB after all of these years. The main reason is I still get inspired by questions that make me think a little bit. The puzzle for today’s post was presented as a question something like this:

How can I display each individual value selected in the UserResponse() function on its own row in a table?

I believe the reason for wanting this behavior is fairly clear. If you have a bunch of complex data it would be much easier for the report consumer to read this:

XYZ123ABC
DEF456HIJ
BOB111BOB

Instead of this:

XYZ123ABC;DEF456HIJ;BOB111BOB

As is often the case, the strict answer to the question is “you can’t do it that way” with Web Intelligence. Despite the “you can’t…” answer I am going to show how it can be done.

Continue reading “Displaying UserResponse() Values On Separate Rows”


Nov 03 2009

Constants, Formulas, and Variables, Oh My!

Categories: 2009 GBN - Dallas, Report Techniques, Variables! Dave Rathbun @ 6:39 am

In the first post in this series I talked about different places I could place complex calculations. I reviewed some pros and cons of doing calculations during ETL, in the universe, or on a report. In this post I am going to focus only on report calculations. I will cover the formulas versus variables debate and show a couple of tricks to make working with variables a little bit easier along the way.

This presentation covers slides 10 through 12 from my 2009 GBN presentation titled “Return of the Variables” which can be downloaded from my conference page. Continue reading “Constants, Formulas, and Variables, Oh My!”


Oct 23 2009

Calculating Business Days Between Two Days Via Report Functions

Categories: 2009 GBN - Dallas, Report Techniques, Variables!, Web Intelligence Dave Rathbun @ 7:57 pm

A frequently asked question on BOB is:

How can I calculate the number of business days between two dates?

The easiest answer is to use a calendar table with a flag for business days because that takes care of everything quite easily. You can even mark holidays along with weekend days and make the results more useful.

But suppose you don’t have a calendar table. Suppose you don’t care about holidays, you simply want to count the number of Monday – Friday days between a range of dates. Can you do that?

The answer is coming if you read the rest of the post. :)

Note: This technique was shown in public on Monday at the GBN conference. I had written this blog post some time ago but waited to release it until after the presentation had been delivered. The presentation has been uploaded to my blog and is available for download from the conference page.

Continue reading “Calculating Business Days Between Two Days Via Report Functions”


Oct 22 2009

2009 GBN Presentations Posted

Categories: 2009 GBN - Dallas, Report Techniques, Variables! Dave Rathbun @ 7:24 am

I have uploaded the latest pdf files for my presentations from the 2009 GBN conference. You can download them from my conference presentations page. I did two presentations this year.

Return of the Variables

Presentation Abstract

It’s time to pull out some variables again as we revisit a popular topic from past conferences. Report variables can provide a more creative, efficient, and effective means of completing the detailed analysis required for your reports. The focus of this presentation is building documents, using report functions, and reviewing variable tricks that can be used to solve reporting challenges. The presentation this year will focus primarily on the SAP BusinessObjects Web Intelligence product and include some of the newest features that have recently become available, but there will be some goodies for users of prior versions as well.

Universe Models for Recursive Data

Presentation Abstract

Recursive data can present a special challenge to SAP BusinessObjects developers because SQL is not natively able to process the relationships. This presentation will show several different methods for modeling recursive data along with the pros and cons of each. All of the methods shown have been used on real-world projects. Attendees will gain a better understanding of the complexities of dealing with inventory models, organizational hierarchies, and other types of recursive data.

As time allows I will be adding detailed blog posts for each of these presentations. I am cheating a bit this year. :) I have already written two blog posts that detail most of the “Variables” presentation:

That leaves only the initial portion of the presentation to write up, and I hope to have it done fairly soon. The detailed posts for the Designer presentation will take a bit longer.


Sep 03 2009

Using ForceMerge() To Fix Unbalanced Data Providers

Categories: Full Client, Report Techniques, Variables!, Web Intelligence Dave Rathbun @ 7:30 am

I have discussed the MultiCube() function a number of times in my series of Variables presentations. In certain situations it allows you to fix measure objects when you have two (or more) unbalanced data providers in a full-client document. ForceMerge() is the equivalent for Web Intelligence.

So how do they work, exactly? What do I mean by unbalanced data providers? How is it fixed with these functions? Are there any special conditions to be aware of? Continue reading “Using ForceMerge() To Fix Unbalanced Data Providers”


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

Continue reading “Variable Relative Block Positioning”


Jul 31 2007

Alerters On Charts Part II

Categories: Report Techniques, Variables!, Web Intelligence Dave Rathbun @ 1:16 pm

The Challenge

In a previous post I showed a way to create the illusion of an alerter on a chart using the full client product. In this post I will reuse the same technique with a slight twist so that it works in Web Intelligence as well. If you have not already read Part I please do so first so you will be familiar with the basic steps for this technique.

Here is a summary of the steps that I used to solve this in Business Objects:

  • Create two versions of the chart: one green and one red
  • Create a variable that shows the trend of the data
  • Use the Hide Block feature of Business Objects to keep one of the charts from being displayed
  • Position both charts in exactly the same space, providing the illusion that the chart is changing colors based on the data

This is all fine… but Web Intelligence does not have the “hide block” feature. So how do I solve this issue?

Continue reading “Alerters On Charts Part II”


Next Page »