Table with Formula Values as output table?

[Former Member]

Hi,

I want to create a table and define formula of each cell in the table.

e.g below is how we need output table (3 Rows & 4 columns);all Fs mentioned below will be calculated formula. Input to this table will be Form output.

(ROWS) Base GAP Margin Lenght (4 Cols)

SALES F11 F12 F13 F14

PRICE F21 F22 F23 F24

VOL F31 F32 F33 F34

Can we achive this using VC? & How?

Thanks,

Murtuza.

[Former Member]

Hi

Are you using BI query as 'Data Service'? If yes then you can use cell refernce for your requirement where you can create formula for each cell in the table. There is another way for this, In VC each column will take default as 'Input Field'. Instead of this make column as 'Expression Box' with data type as 'Num', here also you can write required formula.

Regards

Sandeep

[Former Member]

Thank you ALL for the reply,

I created a structure of table (Key Figures) in Column of query designer and then use that structure in VC to output table.

There I had used formula for each KeyFigures using IF ...something like this:-

NVAL(IF(@Key_Figures=="Prior Sales",0,IF(@Key_Figures=="Price",NVAL(IF(#ID[ACA257]@Price_Impact_value>=0,#ID[ACA257]@Prior_Sales_Value,#ID[ACA257]@Prior_Sales_Value+#ID[ACA257]@Price_Impact_value)),IF(@Key_Figures=="FX",#ID[ACA257]@GAP_3,IF(@Key_Figures=="Volume",#ID[ACA257]@GAP_4,IF(@Key_Figures=="Lost Mix",#ID[ACA257]@GAP_5_6,IF(@Key_Figures=="Gain Mix",#ID[ACA257]@GAP_5_6,IF(@Key_Figures=="Current Sales",0,123))))))))

This way I got the output table as required.

[Former Member]

Hello

the way i understand it. you have 2 problems.. how to use formulas in a table and how to represent 3 chars against 4 Chars in the same table?

The using the formula part int eh table is simple. just add a formula box to the table as a new column and add the necessary formula and it will calculate it for each line in the table.

the 3 chars X 4 Chars is a little complicated and can be achieved with a combination of formulas and HTML text placed next to the table

please reply back if you need further clarification

[Former Member]

Hi...Yes your understanding is correct.

I can create formula but formula will be unique for each cell of the table. I was trying by using "Expression bar"

Also can you throw some lights on how to use HTML with table?

I'm new to VC .

please advice.

Thanks,

Murtuza.

[Former Member]

you are corect expression box would be the more appropriate choice here.

before we explore a possible solution, i just wanted to know whether having three tables side by side would work for your requirement?

Table 1 - Sales - Base, GAP, Margin, Length

Table 2 - Price - Base, GAP, Margin, Length

Table 3 - Vol - Base, GAP, Margin, Length

Instead of one table with

(ROWS) Base GAP Margin Lenght (4 Cols)

SALES F11 F12 F13 F14

PRICE F21 F22 F23 F24

VOL F31 F32 F33 F34

if you split up the data , it might be easier to understand for the user and easier to maintain?

[Former Member]

This is seems a good idea ...I will try it out...

Finally I shud be able to create a graph with the table where SALES,PRICE,VOL be category axis(X-axis) and Formula as data in Stacked bar graph.

I am not sure If I will be able to achieve this graph with 3 different table. If we can have 1 table with 3 rows as mentione wub great.

Thanks,

Murtuza.

Edited by: Murtuza Morwala on Feb 4, 2010 3:19 PM

[Former Member]

Hello

Having three chars to report against in the same chart can be achieved with creating multiple series in the chart.

as long as you have charesterictics in you query in the COLUMNS and the key figure exist there as well you can create multiple series against the same Key figure (Formula as Y axis)

[Former Member]

Actually I have created 3 tables with 1 row in each.

Now I need to combine or do union to build the graph for 3 rows to be able to show in one graph.

I am not able to do this.

Any suggestion or work around ?Pls advice

Thanks,

Murtuza.

[Former Member]

I tried combining 3 table using Combine and Union operator but it doesn;t seem to be able to do with that.