Formulae for SQL Server table cells

Hi,

I know that we can have User Defined Functions (UDFs) for columns but is it possible to have UDFs for each cell (like we can define functions for each cell in excel). Heres the situation:

I have a database table that will have multiple fields. Some of the columns will be functions of other columns. One of the columns has variable functions.

Heres an example:

TABLE(A, B, C, D)

Total Records = 9;

A

BCDA1B1C1 = A1+ B1D1 = A1A2B2C2 = A2+ B2D2 = A2A3B3C3 = A3+ B3D3 = A3A4B4C4 = A4+ B4D4 = A4A5B5C5 = A5+ B5D5 = A5 + B5A6B6C6 = A6+ B6D6 = A6 + B6A7B7C7 = A7+ B7D7 = A7 + B7A8B8C8 = A8+ B8D8 = A8 + B8A9B9C9 = A9+ B9D9 = A9 + B9

If you notice, for Column D, some of the cells have a different formula than others. Is this doable?

Thanks...

One possible way would be there was an Id column with value 1-9, once the table was populated to make an update stored procedure calculate column unconditionally and column D according to a formula.

|||

Please let me know if I am going off-track here but this is what I understood from what you wrote.

Have an ID column (say autoincrement integers), then when all the values are being populated, I run an UPDATE stored procedure which whould check if ID is from 1-4, then assign the same value otherwise if ID = 5 to 9, perform calculation.

|||

Instead of an Identity column you could just populate it manually.

Incidentally as an alternative you could drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select.

|||

TATWORTH:

drop columns C and D from the table, sum A + B to get column C and use a function reading Id, A, B to get column D in the select

Does that mean I won't have columns C and D in the schema but will be generated dynamically when SELECTed (they won't ever be in the schema).

|||

>Does that mean I won't have columns C and D in the schema but will begenerated dynamically when SELECTed (they won't ever be in the schema).
Yes!

Format matrix subtotal column

I have a matrix that has columns for each quarter of the year and I added a
subtotal colum for the year total. I would like to have the data cells in the
subtotal column be a different color than that of the the quarter columns. I
thought that using the little green triangle in the subtotal cell would work,
but it doesn't. How can I do this?
Thanks...
Regards,
SteveIt is the little green triangle - BackgroundColor in Subtotal properties.
You also have to assign a BackgroundColor for the textbox that is your column
heading.
Do you have some expression overriding it?
daw
"Steve" wrote:
> I have a matrix that has columns for each quarter of the year and I added a
> subtotal colum for the year total. I would like to have the data cells in the
> subtotal column be a different color than that of the the quarter columns. I
> thought that using the little green triangle in the subtotal cell would work,
> but it doesn't. How can I do this?
> Thanks...
> Regards,
> Steve|||For some reason I can't get it to work on a column subtotal, but it works
fine for a row subtotal. No expressions overriding it far as I can see.
The BackgroundColor for the column heading is LightSkyBlue, but changing the
BackgroundColor on the subtotals textbox to, say Red, using the little green
arrow has no effect.
I was able to get it to work by setting the BackgroundColor property in the
data cell, i.e. the one with the sum in it, using this expression:
=iif(InScope("matrix3_ColumnGroup2"),"Quarter totals","Year totals")
Thanks,
Steve
"daw" wrote:
> It is the little green triangle - BackgroundColor in Subtotal properties.
> You also have to assign a BackgroundColor for the textbox that is your column
> heading.
> Do you have some expression overriding it?
> daw
> "Steve" wrote:
> > I have a matrix that has columns for each quarter of the year and I added a
> > subtotal colum for the year total. I would like to have the data cells in the
> > subtotal column be a different color than that of the the quarter columns. I
> > thought that using the little green triangle in the subtotal cell would work,
> > but it doesn't. How can I do this?
> >
> > Thanks...
> >
> > Regards,
> > Steve