Search code examples
numbersscalewebi

Webi: How to let user scale numbers in a table (thousands, millions, ...) (not in charts)

(SAP BusinessObjects BI Platform 4.3 Support Pack 1 Patch 11 Version : 14.3.1.4142.11)

Hello everyone,

Let's say I have this table with

  • On the left, an identifier representing a product
  • On the right, the amount of money gained from selling this product

Example of products identifier and their corresponding amount of money gained from selling them.

Now, I also have an information somewhere on the document telling the user in which currency and "scaling" these amount are displayed: e.g. "amounts in $" or "amounts in 1'000$", etc.

What I want is to let the user choose whenever he wants in which scaling these amounts should be displayed and I don't want him to refresh the data each time he wants to rescale. So that means that if the value is 1'234'567'890$, the user should be able to scale the value and display e.g. 1'234,57 Mio.$. In fact, the value does not really change. It is only the way it is displayed that changes.

I searched a lot and tried a lot of things. What I managed to do so far is close, but not good enough yet.

Working solutions (but not good enough)

  1. Scaling values 1 to 1 billion

I could create a derived table in our Information Design Tool with these values: 1, 1'000, 1'000'000, 1'000'000'000, as numbers. I then imported these values in our Webi-Document and try to create an input control with it. It's working, but since these are "numerics", I couldn't choose the input control "list" as expected, because, I learned then, that this input control is working only with "dimensions" (Input control types). The problem is, that with a slide we can choose all values between the minimum and the maximum value (1 to 1'000'000 in the example below). It makes no sense to scale with a factor of 213'000 for example. So it's working, but it is not a "professional" solution.

Scaling with slider.

  1. Scaling values 1 to 9

The other idea I had was to use powers of 10: e.g. 10^1, 10^3, 10^6 and 10^9. This is also working and I don't have the issues of the value, which makes no sense, but it is less clear, I think. I also don't think that anyone will use 10^4 or 10^7 to scale the data, but it is less problematic. The thing is... I don't know if the people, who will be using this report are familiar with the powers of 10.

Not working solutions

The first thing I tried was using the custom format options (#,##, etc.), but I could not make it work. I also tried with the K or M for example, which are Excel keywords, and since Webi has similarities with Excel in the formulas, I thought, that it could work, but it did not.

Another thing I tried was to create a derived table with the values 1, 1'000, etc. as "dimensions" and convert them in the formula directly in webi, but the cells are then blank and I don't know why. The formula is simply

=[Amount]/[Scaling factor]

And I tried to use the ToDecimal or ToNumber functions, but without success. It may be linked to the fact, that the structure of the table is very complexe with several groups to display their sums (something like a balance sheet with subcategories and everything).

Anyway... does somebody know how to do it, if it is possible? I found it quite frustrating to not be able to use the input control list on numerics. I don't understand, why I should not be able to choose between specific values if they are numbers and not dimension.

Of course, I'm still here if you have questions and/or if something is not clear enough.

Thanks for your time!

PS: I don't have the reputation to post images, so you only will have the links, sorry

Solution

  • I actually met someone today, who found a solution to my problem, so I am sharing it with you in case others are having the same problem and are also struggling.

    The idea is to do the contrary of what I tried in my second "not working" attempt. The thing is to have the scaling factors as numbers. Then we create a variable and convert the scaling factor in a Dimension. We then use this variable to create an input control list.

    We are using Information Design Tool (IDT) to connect Webi and Teradata at my work, so this answer will be using these tools.

    In IDT

    1. Create a derived table in your data foundation with the values you want. For me it was 1, 1'000, 1'000'000 and 1'000'000'000. I used this SQL:
    SELECT CAST('1' AS NUMBER) AS Skalierung  FROM (SELECT 1 AS "DUMMY") AS "DUAL" UNION ALL
SELECT CAST('1000' AS NUMBER) AS Skalierung  FROM (SELECT 1 AS "DUMMY") AS "DUAL" UNION ALL
SELECT CAST('1000000' AS NUMBER) AS Skalierung  FROM (SELECT 1 AS "DUMMY") AS "DUAL" UNION ALL
SELECT CAST('1000000000' AS NUMBER) AS Skalierung  FROM (SELECT 1 AS "DUMMY") AS "DUAL"
    1. Drag and drop the newly created table in your business layer and publish your Universum.

    In Webi

    1. Open your data provider and make a new query with the corresponding Universum.
    2. Create a new variable Scaling_Dimension_for_Input_Control with the Formula: =FormatNumber([ScaleMeasure]; "##########"). It must be a Dimension. Configuration of the Dimension variable
    3. Create a new Input Control with this new Dimension. Choose the list type and prevent user from selecting multiple values. You must choose a Default value. Configuration of the Input Control

    The scaling input control will look like this Scaling input control

    1. Use the scaling Measure directly in a table or use a variable to divide the Amount you want by this scaling factor: =[Amount]/[Scaling factor]

    Now, when you change the scaling factor, it will change the values in you table.

    Enjoy!