Using of variables and input controls in web intelligence rich client

[former_member283957]

Hello Everybody,

I have to calculate a ratio as :

=([TOTAL] En ([YEAR];[Name_MONTH])/[FACTURACION MARCH 2014] )*100

[TOTAL] IS THE VALUE OF THE COLUMN Cant.Reclamada which shown the TOTAL receipts per month and year

[FACTURACION MARCH 2014] IS A VARIABLE WHICH GET THE VALUE FROM AN INPUT CONTROL OF TYPE INPUT FIELD where I can input the TOTAL billing per month and year.

This formula :

=([TOTAL] En ([YEAR];[Name_MONTH])/[FACTURACION MARCH 2014] En ([YEAR];[Name_MONTH) )*100

Should be dynamic changing the variable [FACTURACION MARCH 2014] for EACH month,

Example FORMULA RATIO FOR MARCH 2014

=([TOTAL] En ([YEAR];[Name_MONTH])/[FACTURACION MARCH 2014] En ([YEAR];[Name_MONTH) )*100 should

Example FORMULA RATIO FOR APRIL 2014

=([TOTAL] En ([YEAR];[Name_MONTH])/[FACTURACION APRIL 2014] En ([YEAR];[Name_MONTH) )*100 should

THIS FORMULA RATIO is WRITTEN in the RATIO3 VARIABLE which is inserted in the column RATIO

HOW CAN I SOLVE THIS TROUBLE  ?:

HOW CAN I INSERT DIFFERENTS FORMULAS FOR EACH MONTH IN THE COLUMN FACTURACION (Billing) of the CROSS TABLE

=Si (Year([Day Contabilizacion]) =2014) Y (Month([Day Contabilizacion])="marzo" ) Then [FACTURACION MARZO 2014] Else ""

...........................

=Si (Year([Day Contabilizacion]) =2014) Y (Month([Day Contabilizacion])="abril" ) Then [FACTURACION ABRIL 2014] Else ""

and

HOW CAN I INSERT differents variables IN the column RATIO of the CROSS TABLE. ?

NOW I CAN INSERT ONLY THE VARIABLE RATIO3 ,

Can I INSERT DIFFERENTS VARIABLES IN DIFFERENTS CELLS OF THE SAME COLUMN OF THE CROSS TABLE  ?

RATIO3 = ([TOTAL] En ([YEAR];[Name_MONTH])/[FACTURACION MARCH 2014] )*100

NEW VARIABLE FOR APRIL 2014

RATIO4 = ([TOTAL] En ([YEAR];[Name_MONTH])/[FACTURACION APRIL 2014] )*100  ???

THANKS IN ADVANCE.

[former_member283957 · Accepted solution]

Hi Rogerio Plank ,

I have attached a PNG file where you can see what I want to develop as a a cross table report.

BILLING COLUMN GET THE VALUE FROM AN INPUT CONTROL OF TYPE INPUT FIELD where I can input the TOTAL billing per month and year.

I DO NOT HAVE BILLING IN THE DATABASE.

RECEIPTS COLUMN GET THE VALUE FROM THE DATABASE.

RATIO COLUMN CONTAINS VALUES CALCULATE  (RECEIPTS/BILLING)*100

THANKS IN ADVANCE

i CANNOT SEND YOU AN EXCEL FILE PLEASE LET ME KNOW HOW TO SEND YOU AN EXCEL FILE

[tanveer1 · Accepted solution]

Hi ,

How do you want to enter the Billing data through input controls ??

1300;1400;1500 etc like that ??

Thanks,

Tanveer.

[former_member283957 · Accepted solution]

Hi Tanveer,

I have to enter Billing data for each month of each year, starting at 2014.

In this case I start in 2014.

Enero->January

......................................

Diciembre -> December

I thought about to create 12 variables but may be it is much better only one variable for only one

input control where I can enter 1300;1400;1500.........

The only problem is that  I must select each amount separated by ; How can i do that?

Let me know please how can I develop the report using BOXI 3.1

Thanks in advance.

Best Regards

Antonio

[tanveer1 · Accepted solution]

Hi Antonio,

I think in one Input control you may not be able to enter all 12 months.

Better create 1 for every qurter

So You have three Input Controls.

Follow below steps to get your requirement

1. Create a 4 new variables with formulae as below

          a. Q1 =" "

          b. Q2 =" "

          c. Q3 =" "

          d. Q4 =" "

2. Create Input Controls on top of them. The Input Control should be of Entry Type and remove default values if any.

3. Now Type your Billing Values in Input Controls for each quarter in four input control boxes as shown below

4. now create a variable as below

BILLING = If ([Month] = "January") Then Substr([Q1];1;4) Else If ([Month] = "February") Then Substr([Q1];Pos([Q1];";")+1;4)Else  If ([[Month]] = "March") Then Right([Q1];4) else If ([Month] = "April") Then Substr([Q2];1;4) Else If ([Month] = "May") Then Substr([Q2];Pos([Q2];";")+1;4)Else  If ([[Month]] = "June") Then Right([Q2];4) else If ([Month] = "July") Then Substr([Q3];1;4) Else If ([Month] = "August") Then Substr([Q3];Pos([Q1];";")+1;4)Else  If ([[Month]] = "September") Then Right([Q3];4) else If ([Month] = "October") Then Substr([Q4];1;4) Else If ([Month] = "November") Then Substr([Q4];Pos([Q4];";")+1;4) Else  If ([[Month]] = "December") Then Right([Q4];4)

Add this variable in your cross table to get the result you wanted.

Thanks,

Tanveer.

[former_member283957 · Accepted solution]

Hi Tanveer,

Thank you very much foy your answer.

I will test it on monday and I will tell you if everything is OK.

Following your explanation:

Finally  Will RATIO COLUMN contain a RATIO variable as this?:

RATIO = ([TOTAL] En ([YEAR];[Name_MONTH])/[BILLING])*100 

Am I right?

I remind you that:

[TOTAL] IS THE VALUE OF THE COLUMN Cant.Reclamada which shown the TOTAL receipts per month and year, THIS "TOTAL"  IS CALCULATED FROM THE DATABASE

You wrote:

"The Input Control should be of Entry Type and remove default values if any"

Does Entry type is equal to Input field?

I do not  know how to choose and Input Control being of type Entry Type and remove default values if any

I am using Business Objects XI 3.1

Thanks in advance.

Best Regards,

Antonio

[former_member283957 · Accepted solution]

Hi Tanveer,

Thank you very much .

Now my report is working.

I have updated the variable following your advices.

= Si ([Nombre_MES] = "enero")

Then Subcadena(Eliminar([Q1]);1;7) ElseIf ([Nombre_MES] = "febrero")

Then Subcadena(Eliminar([Q1]);Pos(Eliminar([Q1]);";")+1;7)ElseIf ([Nombre_MES] = "marzo")

Then Derecha(Eliminar([Q1]);7) ElseIf ([Nombre_MES] = "abril")

Then Subcadena(Eliminar([Q2]);1;7) ElseIf ([Nombre_MES] = "mayo")

Then Subcadena(Eliminar([Q2]);Pos(Eliminar([Q2]);";")+1;7)ElseIf ([Nombre_MES] = "junio")

Then Derecha(Eliminar([Q2]);7) ElseIf ([Nombre_MES] = "julio")

Then Subcadena(Eliminar([Q3]);1;7) ElseIf ([Nombre_MES] = "agosto")

Then Subcadena(Eliminar([Q3]);Pos(Eliminar([Q1]);";")+1;7)ElseIf ([Nombre_MES] = "septiembre")

Then Derecha(Eliminar([Q3]);7) ElseIf ([Nombre_MES] = "octubre")

Then Subcadena(Eliminar([Q4]);1;7) ElseIf ([Nombre_MES] = "noviembre")

Then Subcadena(Eliminar([Q4]);Pos(Eliminar([Q4]);";")+1;7) ElseIf ([Nombre_MES] = "diciembre") Then Derecha(Eliminar([Q4]);6)

[former_member283957 · Accepted solution]

Hi Tanveer,

Could you please tell me how to calculate the length of the Billing Values ?

I mean instead of put 4 manually Substr([Q1];1;4) calculate automatically.

Do you know how to update your formula for getting the values of Billing automatically ?

Thanks in advance.

Best Regards.

Antonio

BILLING = If ([Month] = "January") Then Substr([Q1];1;4) Else If ([Month] = "February") Then Substr([Q1];Pos([Q1];";")+1;4)Else  If ([[Month]] = "March") Then Right([Q1];4) else If ([Month] = "April") Then Substr([Q2];1;4) Else If ([Month] = "May") Then Substr([Q2];Pos([Q2];";")+1;4)Else  If ([[Month]] = "June") Then Right([Q2];4) else If ([Month] = "July") Then Substr([Q3];1;4) Else If ([Month] = "August") Then Substr([Q3];Pos([Q1];";")+1;4)Else  If ([[Month]] = "September") Then Right([Q3];4) else If ([Month] = "October") Then Substr([Q4];1;4) Else If ([Month] = "November") Then Substr([Q4];Pos([Q4];";")+1;4) Else  If ([[Month]] = "December") Then Right([Q4];4)

[tanveer1 · Accepted solution]

Hi ,

Instead of 4 you can use Pos() function

Pos([Q1];";") will return he position of ";" in your string. Subtract 1 from that value to get the value untill ; automatically.

Thanks,

Tanveer.

[former_member283957 · Accepted solution]

Hi Tanveer,

Sorry, I do not know how to do it. ->"Subtract 1 from that value to get the value untill ; automatically"  ????????

Please could you rewrite your formula using what you mean.

The problem is that the length of each Billing value is UNKNOWN.

Thanks in advance.

Best Regards.

Antonio

[former_member283957 · Accepted solution]

Hi Tanveer,

I do not understand what you mean.

Pos([Q1];";") will return he position of ";" in your string. Subtract 1 from that value to get the value untill ; automatically.

[former_member283957]

IMPORTANT THINGS:

[TOTAL] IS THE VALUE OF THE COLUMN Cant.Reclamada which shown the TOTAL receipts per month and year, THIS "TOTAL"  IS CALCULATED FROM THE DATABASE

We input the Billing of each month per YEAR  FROM  AN INPUT CONTROL OF TYPE INPUT FIELD, NOT FROM DATABASE.

WE DON NOT HAVE BILLING IN THE DATABASE.

[Former Member]

Hi, can you give an example of what you're looking for as a excel or text file ?

Regards,

Rogerio

[former_member283957]

Hi Rogerio,

Is my example clear?

Do you need more information?

I am using Business Objects XI 3.1 .

Can I develop this report with the new "RATIO" using BOXI 3.1 ?

I DO NOT HAVE BILLING IN THE DATABASE.

Please let me know your point of view.

Thanks in advance.

Best Regards.

Antonio