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.

Constant, Formula, or Variable?

A constant is a value that is never going to change. Constants are probably quite rare, to be honest. I could hard-code a sales tax rate into my report but it is far more likely that the tax rate will come from the database. Same for commission percentage or project deadline dates or any other data along those lines. In any case, they’re not really complex and they’re certainly not calculations so I will move on. :)

That leaves formulas and variables. How are they different? How are they the same?

Both can use the full range of report engine functions. Both can be used just about anywhere on the report. Is there any reason to select one technique over the other?

Yes, yes there is.

The Four Cs

Years ago when I wrote my first Variables presentation I came up with what I called “The Four Cs” to describe why variables are better than formulas. They are:

  • Convenience
    A variable can be used with every report feature. I can’t build an Alerter using a formula, as shown here:

    alerter screen

    I can’t rank on a formula. In Web Intelligence I get a list of query objects and a list of variables, but not formulas. The list probably goes on from there but that’s probably enough important features to prove the point. Variables can be used in places where formulas cannot be used.

  • Clarity
    Simply put, a variable is a formula with a name. A name defines the purpose of the calculation. It is much easier to understand a complex calculation like this:

    =((100-[Discount Rate])/100)*[List Price]*[Qty Sold]

    … when that formula is also named “Sale Value After Discount” and saved as a variable.

  • Complexity
    Sometimes it’s hard to figure out the syntax for a complex calculation. I will often break something down into simple steps and store the results as variables. Because variables can be reused (formulas cannot since they don’t have a name and therefore cannot be referenced) I can check my work at various points and make sure I am seeing the expected results. In another post related to this conference presentation I showed an alternative way to calculate the number of business days between two dates. If you review that post you will see that the formulas get quite complex indeed! :shock: You can bet that I didn’t come up with the final version without working carefully through each step. Using variables, of course.
  • Consistency
    Once a variable is built it appears on the list of report “components” saved as part of the document. I have touched on this aspect of variables already in this post. Because variables appear on this list they are reusable throughout the report. Because I can reuse them throughout the report I can edit them in one place and have the changes propagate everywhere I use that particular calculation. If I spend the time and effort to create and test a complex calculation, it’s nice to know that it is reusable and will therefore provide consistent results throughout my document.

Wish List: Universe (or Universal) Variable Repository

At one point I had started writing a VBA script that would allow me to create report variables and check them into a “variable repository” that I stored in a Microsoft Access database. The idea was that once a variable was created I could then easily reuse it in other documents. This worked fine for desktop documents. Web Intelligence documents were not supported at all since the utility was based on VBA. It would be nice if we could see this feature supported directly in the universe framework.

Building Variables

Building variables in a document is fairly simple. I can click the proper button on the toolbar (shown below).

invoke variable editor

After clicking this button I am presented with the following screen that allows me to create my local calculation. (Note: this screen is from XI 3.0 and is different from what you will see in prior versions.)

variable editor

This screen allows me to enter a name for my variable, set the qualification (dimension, detail, or measure), and access the full range of report functions.

There is another way that I can create a calculation; I can use the formula bar much like I can in a spreadsheet. Here I have activated the formula bar using the appropriate button and have clicked inside the edit area and started to create my formula.

formula bar

Is this a problem?

Formula Challenges

For this demonstration I have created the following simple formula.

=[Revenue] / 100

I can use this formula in a report block. In fact I can copy it to multiple report blocks. The problem is, after I have done this I have three (or more) different copies of the same formula. If I click on the initial cell where I created the formula and edit it (perhaps I meant to type 1000 instead of 100 earlier) then only that instance of the formula is changed. The rest of the formulas are stored elsewhere in the document and are not affected by the edit.

This is a problem.

Converting a Formula to a Variable

If I made this mistake and want to convert my wayward formula into a variable I can do that. There is a button on the formula toolbar here:

toolbar button

When I click that button I am presented with the following dialog box that allows me to assign a name to my formula.

Naming a variable

Once a formula has a name it becomes a variable and shows up in the document data tab.

Data tab

Summary

This part of the presentation was probably considered to be fairly basic by most (if not all) of the folks in the room. However, I always repeat this same information every time I deliver a variables presentation. Why? Because of what happened years ago when I presented “Variables and Moreā€¦ Fourth Edition” at the conference in Las Vegas. I thought that by then everyone knew about the toolbar button that provided a way to convert a formula into a variable but it seems that was not the case. The very last question that I took from the audience was from a woman who wanted to know what to do with a formula she had created by mistake. I quickly created a formula and then showed how to convert it into a variable using the process I outlined here. I then found out just how many folks didn’t know about this technique, as it seemed the entire audience went “oooh, aaaah” and proceeded to give me the highest rated presentation marks that I have ever received. :lol:

It just goes to show that anything and everything that you know is probably worth sharing. It is very likely that there is somebody, somewhere that doesn’t know what you consider to be basic information. Share those simple tricks and they are much better off. Plus you earn some good karma. :)

So go ahead, answer a few questions on BOB. Start a blog. Share what you know.

17 Responses to “Constants, Formulas, and Variables, Oh My!”

  1. Comment by Sandi

    Hi Dave

    I just spotted your blog today – fantastic.

    Oh how I agree with you on the wish list!
    I deliver customised WebI training to our customers and the times I have to say that once they have created a variable it is only available for use in that document.

    We have suggested that the customer sets up a BO User group of their own (they have over 300 power users across the country) and to document ‘commonly used formulae’ on a page on their intranet. Not as good as a user updatable variable repository but the best we can come up with currently.

    Cheers, Sandi

    PS – I also include a warning in my training when creating variables for percentages – to omit the final (* 100) if the % number formatting is to be applied but to remeber that when applying an alerter to the cell you have to consider the underlying number and not the displayed % value!

  2. Comment by nod

    I’ll share a tip I got from a colleague that is now the standard in my group. Name your variables in all upper case so it’s easy to spot which objects are report based and which are coming in from your query. When something isn’t working right a year from now, it’s easy to spot and you can get right down to fixing it.

  3. Comment by Dave Rathbun

    nod, I have seen a number of different suggestions for naming conventions. Some folks preface all variables with v_, others do something with case (as you suggested). As reports get more complex there is certainly some value in having some way to differentiate between variables and query objects.

  4. Comment by Dave Rathbun

    Sandi, welcome. :) While I cannot make specific comments regarding the wish list item, I can say that Business Objects appears to have been listening. 8-)

  5. Comment by Sandi

    Hi Dave
    Well that sounds as if it may be promising!
    Re naming conventions, I tend to use (and advise my customers) the v_ prefix so that all variables appear together.
    I also recommend they create a v_Report_Title variable (set to DocumentName()& the report effective date) as they do a lot of report cloning and I was finding that they had a habit of forgetting to retitle their reports. :)

  6. Comment by Simon

    On the subject of calculations, have you written up, or are you considering writing up – in your inimitable style – calculation contexts? I don’t find the vendor documentation particularly helpful.

  7. Comment by Dave Rathbun

    Hi, Simon, welcome. To be honest, I’m not sure why I haven’t covered that subject already. :) I will try to address that topic as soon as I get time.

  8. Comment by nod

    Ooh, Sandi, I like that idea, I think I’ll use it.

    For reports run in non-Prod environments, I like to include the SID (we use oracle) so that there are no panicked phone calls when the numbers look wrong. Saves a lot of headaches!

    I use a universe object (we have oracle) defined as CASE WHEN SYS_CONTEXT(’USERENV’, ‘DB_NAME’)=’ sid ‘ THEN ” ELSE SYS_CONTEXT(’USERENV’, ‘DB_NAME’) END

  9. Comment by gobinath

    What is user object in report level?
    What is the use of that?
    variable and user objects are same or diffrent?
    If i create variable the valueswhere it get stored ?

  10. Comment by Sheryl Browning

    I need to return records for each unq_id where the variable = 0. The variable is as follows: If Not IsNull(Previous()) Then If -Previous()=1 Then 1 Else 0 Else 1. I am unable to filter on the variable and can’t use the variable in the condition. Can you show me how I can return records for minimum consecutive year for each unq_id?

  11. Comment by Dave Rathbun

    The problem with Previous() is that it works after the filters have been applied. There is no way to use a the results of a Previous() calculation within a filter expression.

  12. Comment by Jeewan

    Interesting blog. Maybe you could answer my question. Could you tell what could cause the use of “Detail Variable” in a report to only display values from one data source and skip the other when combining 2 data sources to create a combined report? Thanks.

  13. Comment by Dave Rathbun

    Assume you have two data providers linked D1 to D1. Assume that the second data provider has a detail “d2″ attached to dimension D1. After linking the two dimensions, you might see this:

    D1 D1 d2
    D1 D1 d2
    D1
       D1 d2

    What that shows are the two first rows occur in both data providers, the next appears only the the first, and the last row appears only in the second. If you can filter on detail object “d2″ you can show only values that come from the second data provider.

  14. Comment by Mary

    Can you create a variable that gets a result from one worksheet in WEBI and use that result in a calculation on another worksheet in the same report?

  15. Comment by Dave Rathbun

    Yes, of course, because the variable isn’t part of a “sheet” or report, it’s part of the overall document. There should be no issues doing what you ask.

  16. Comment by Tim Gallivan

    Hey Dave – Thanks for this helpful website. It has saved my bacon a couple times.

    I have an interesting problem that I can’t seem to solve.

    Scenario:

    I have a budget report with estimates and actuals similar to below:

    Estimates Actuals
    50 40
    60 0
    ——————
    110 40

    I have created a variable to calculate the Spent % (Actuals / Estimates) and I need to use the “Total” amount (in this example the total amount would be 36%) in another calculation.

    I can’t seem to figure out how to ‘call’ this constant into another formula without actually hardcoding this into a dummy variable.

    Any ideas on how to do this?

  17. Comment by Tim Gallivan

    Nevermind….Found it using the In Report function. Duh…

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.