Variable and Formulas Question

[Former Member]

Hi folks,

I use BOXI r2 sp 4 and I have a general question re: variables and formulas in Webi reports.

Billing Year Month and Cost of Service are standard time dimensions and measures that I use in my Webi reports and they were created at the semantic level.

Normally, I create cross tab reports with the Billing Year Month along the horizontal axis and the Cost of Service in the Body (Business Unit or Department would be placed along the vertical axis).

For some reason, when I use formulas/variables to modify the standard time dimensions (like changing Billing Year Month to Year), the values for the Cost of Service are returned incorrectly.

Example.

Semantic object: Billing Year Month = (yyyy-MM) returns correct values

Report variable: Substr([Billing Year Month;1;4) returns incorrect values

Can anyone please explain this problem?

Thanks,

Wannetta

[Former Member]

Wannetta,

Question: is the "Billing Year Month" attribute a "cleartext" value yyyy-MM or is it a representation of the date value? For example, in Excel we have date, and its a serial number, thus Apr 29, 2009 is represented as 39932 (in Excel, serial date number 1 denotes 1 Jan 1900). So if you simply apply a format to the date field, it still knows the serial number in the background, hence performing a substring() on it might yield "3993", which is wrong. If the attribute is a character-type and literally has yyyy-MM, then substr() on it should yield the correct year portion. One way to tell is to add a new report to your project and insert a table, then populate it with two columns, your "Billing Year Month" attribute and the substr() on it and see what you get....

Thanks,

John

[Former Member]

Hi John,

The attribute of the Billing Year Month at the semantic level is a character type.

After reading your response, I realize that I may not have been clear regarding the problem.

The following formula does return the correct value in and of itself.

>=Substr([Billing Year Month];1;4

If I create a variable and then drag it onto my crosstab, it returns the correct value ONLY within the header.

When I drag the cost of service measure onto the crosstab; the header values remain the same but values in the body either disappear or the change to reflect incorrect values.

I thought one of the prime features of Webi was the ability of the Report Developer to create variables/formulas and use them in the headers to manipulate the semantic "measures" in the body (read: output) at the report level.

Is this a matter of semantic design or does Webi not possess this capability?

Or, is the issue entirely different?

Thanks!

W.

[Former Member]

W.

I thought one of the prime features of Webi was the ability of the Report Developer to create variables/formulas and use them in the headers to manipulate the semantic "measures" in the body (read: output) at the report level.

Absolutely! I've done it many times and never experienced a problem. In order to help you think the problem through more deeply, we may need to peel back the onion a bit further. If you can capture the generated SQL code, and run it in another application (like Query Analyzer -- SQL Server; or, TOAD -- Oracle, etc, etc), does the code produce valid output? How about trying to re-work the query by substring on the Billing-Year-Month and produce an aggregate on it, does it still produce a valid result? Another thought, how many Data Providers are there on your report in WebI (i.e. 1 or more than 1). If more than 1, then you probably need to see how dimensions are getting merged -- are they being merged correctly (automatically or manually), etc, etc. Lots of stuff to check on and discover, please respond when you have the time.

Thanks,

John