how to calculate a boolean based on data from a table fed by a BI Query

[Former Member]

Hi Gurus,

I am currently struggling with the following issue:

- I have a query in my VC model wich returns 4 ratios

- I put the result of the query in a table in the VC model

- then, I added a column in the table. this column is a boolean which should be true if the value of one of my 4 ratios is not 0, otherwise, it is false

- I want that boolean to be "updatable" by the users.

I tried to define the default value of the boolean based on the value from the signficant ratio... no luck

I tried to change the value from the configuration tab of the column, but then I can not update afterwards....

Has anyone ever seen that issue and found a solution ?

Thansk for your help

PY

[Former Member]

Hello PY,

Use a "Data Share" connector between the table and the Query "Data" output. " Define Data...." on the Data Share as "Boolean" or "Text" which ever works. Then RMC on the line between the Query and the "Data Share" and choose "Map Data..." . Define the logic =IF(@RATIO1!='0'OR RATIO2!='0'OR RATIO3!='0'OR RATIO4!='0',"1","0"). Now refresh the table by choosing "Define Data" by RMC on table so you can see the new field that you defined in the Data Share. This will populate the Boolean when the form is executed and the users will be able to edit the field as well.

Thanks

Deep

[Former Member]

Hi Deep!

Thanks for your feedback! I felt a bit alone in my world before you answered

Your idea is great and it would work if I hadn't to display the data with a hierarchy.

Actually, the only thing I did not mention is that we are posting data against fiscal period, and we are budgeting for the next 10 years (no comment on that, and the precision we could reach ). So, to display the information we are using a cusomized hierarchy on 0FISCPER using 0FISCYEAR.

So when I used your option, the hierarchy has been deactivated on the table. But to be sure I correctly implemented your proposition, here are the steps we used :

1) connect the query output port "data" to the share

2) add the boolean in the share

3) map the boolean with the rule based on the data coming from the query

4) connect the share to the table.

Note that we the current version we use of VC I am not able to edit the field mapping between the share and the table...

Did I miss something ?

If yes, could you drive me to the correct procedure ?

If no, would you have any idea ? I already tried to change the default value of the field in the "define Data" maintenance popup of the table. I also tried to assign a value to the boolean based on the Action option of the column used in my condition...

Thanks for your help

PY

[Former Member]

1) Define Data (add new column) in the Table called "Boolean"

2) Connect the query output port "data" to the share

3) Add the "Boolean" in the share can be the same definition as the one you defined in the table

4) Map the boolean with the rule based on the data coming from the query between the share and the query

5) Connect the share to the table were boolean = boolean (Source = Target).

6) The field will be now editable.

This is for CE VC 7.1 or Higher

Thanks

Deep

[Former Member]

=IF(@RATIO1!='0'OR RATIO2!='0'OR RATIO3!='0'OR RATIO4!='0',"1","0")

Sorry replace the above "1","0" with true,false

=IF(@RATIO1!='0'OR RATIO2!='0'OR RATIO3!='0'OR RATIO4!='0',true,false)

[Former Member]

Hi

Actually, when I connect the datashare to the table, I can not RMC on the link to the table.

So the field mapping is defined automatically, and is working properly...

But, then the display of the data is not with the hierarchy as in the query...

we are currently using VC 7.1 EHP1, SP4...should I go higher ?

Py

[Former Member]

Please download the attached model and let me know if you have any questions.

[Sample Model |http://www.2shared.com/file/pLShM2EP/one.html]

[anja_engelhardt2]

Hi,

can you please attach it to the thread not by mail? It might also be helpful for others.

Anja

[Former Member]

Find the model in the link provided. Download the file and import it into your local repository.

[Link to the site to download|http://www.2shared.com/file/pLShM2EP/one.html]

[Former Member]

Hi Gurus

thanks for the support and the model unfortunatelly, the usage of hierarchy does not allow me to use the share data...

Thus we changed our mind, and firstly ask the user to select a date that we will use later in hte process...

Thanks

PY