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!

Formatting Total of Subtotals in Matrix

Need your help with formating color of subtotal cell.
Here is my scenario:
Matrix with are 2 row's groups:
- RowKat
- RowSubKat
and 2 column's groups:
- ColKat
- ColSubKat
I want to have my subtotal cell of RowKat, in ColKat subtotal scope
(most outer total of subtotals), to be green where it's value is > 55
Is it possible?
KamelIs it possible to define Row Subtotal in scope of Column Subtotal?|||Hi Kamel. I had a similiar scenario some time back. I could not find
anyway of doing this. the most one could do is to format the whole
subtotal, but it can't be conditionally formatted (as in differently
colored column subtotals). I had to modify the dataset-query to have
another row, and then put the conditional formatting in that row.
On Feb 7, 5:45 pm, "kamel" <kwic...@.gmail.com> wrote:
> Need your help with formating color of subtotal cell.
> Here is my scenario:
> Matrix with are 2 row's groups:
> - RowKat
> - RowSubKat
> and 2 column's groups:
> - ColKat
> - ColSubKat
> I want to have my subtotal cell of RowKat, in ColKat subtotal scope
> (most outer total of subtotals), to be green where it's value is > 55
> Is it possible?
> Kamel|||thanks, I try that way...
...but mayby somebody know the trick?|||Is there any way to format that using "InScope" function?

Formatting text within cell

I have multiple words within a single tablecell and need to be able to make one of them bold, not the entired contents of the tablecell, just one word within it. I can't find a way to do it.
Can anyone help me out?
Thanks.
As far as I am aware you can't differentially format text in a cell. Andrew Watt MVP - InfoPath wrote in message news:5fe80da2-bdd1-4e54-987d-31f576f8913c@.discussions.microsoft.com... >I have multiple words within a single tablecell and need to be able to
> make one of them bold, not the entired contents of the tablecell, just
> one word within it. I can't find a way to do it. >
> Can anyone help me out? >
> Thanks.|||I'm interested to see if that is possible, but all signs point to no. (maybe javascript injection or something?)sql

Formatting text in a single table cell

I have a two column report. However, in the footer, i'd like to present a
column similar too:
Total spent: $100
Total left: $1,230
So that the NUMBER values on the right are completely right justified, but
the text on the left is actually LEFT justified. Can this type of
formatting occurr within the same cell?
TIA,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.comJustification is done on a per cell basis. What you can do is use a
rectangle that contains two textboxes for each row. This would allow you to
apply the justification as needed.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:%23Kh2U%237fEHA.3476@.tk2msftngp13.phx.gbl...
> I have a two column report. However, in the footer, i'd like to present a
> column similar too:
> Total spent: $100
> Total left: $1,230
> So that the NUMBER values on the right are completely right justified, but
> the text on the left is actually LEFT justified. Can this type of
> formatting occurr within the same cell?
> TIA,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>

Formatting subtotals in Matrix reports

I am trying to alter the color of a cell of a subtotal based on the value of the subtotal.
Example: =IIF(Subtotal.Value < 20 ,"Brown","Blue")
This doesn't work. How do I refer to the value of a subtotal inside an expression' I think it has something to do with InScope(). I have definitely clicked the little green tab and am getting the correct properties window.Please see this posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c9b1dcda-cd35-4669-bb9b-4076ad21ed43
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Getting started and liking it" <Getting started and liking
it@.discussions.microsoft.com> wrote in message
news:5BC824BC-61A2-44A8-BA0B-FD0163954F8E@.microsoft.com...
> I am trying to alter the color of a cell of a subtotal based on the value
of the subtotal.
> Example: =IIF(Subtotal.Value < 20 ,"Brown","Blue")
> This doesn't work. How do I refer to the value of a subtotal inside an
expression' I think it has something to do with InScope(). I have
definitely clicked the little green tab and am getting the correct
properties window.

Formatting Problem

I would like to change the background of a cell if its value is 'Test'
I have tried this thru both custom code and just thru a simple iif statement both which produce errors. I put the following iif statemnt in

=iif(Fields!change_type.value="Test","Cyan","Red")

and get the following error:

c:\variset4\reports_microsoft\ToolPredByOp.rdl The background color expression for the textbox 'CHANGE_TYPE' refers to the field 'change_type'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Any suggestions?disregard my last message - my line was correct - the problem was that Reporting services is Case sensitive. I should have had my field names in all caps. I found a good ei was trying to do at:

http://www.sqlservercentral.com/columnists/bknight/reportingservicesconditionalformatting.asp

if anyone is interested...|||

Hi.....

May be ur Prblm is Caps letter..or U Can also try Like this....
=iif(Fields!change_type.value="Test","Cyan","Red")
As
use ur Text Box Name instead of Field value.... ok write this Code in BackGround -- <Expression...>

=iif(ReportItems!textbox1.value="Test","Cyan","Red")

Best Regards......

Formating number and code

Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.
I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)
The problem is that the function should return Integer and if the
value is zero I need to return empty string.
It does't say any thing about returning the string but when it trying
to use the format on a string it give me a worning and print #ERORR in
the cell.
is there a solution for this?
Do you know the syntax to format the number in the function before the
return?
(Sorry for the dumb question but I know C# not VB.net and there is no
intelisance in that editor).
Thanks a lot!On Jul 2, 8:35 am, nicknack <roezo...@.gmail.com> wrote:
> Hi, I have another question about code in RS.
> I have a table cell with FORMAT of "$#,##0,0".
> If I have any number in the cell I want to display it.
> If I have 0 (zero) I want the cell to be empty.
> I know how to do it with an expression:
> iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)
> Now I want to do it with a code section:
> =Code.hideIfZero(Fileds!myFiled.Value)
> The problem is that the function should return Integer and if the
> value is zero I need to return empty string.
> It does't say any thing about returning the string but when it trying
> to use the format on a string it give me a worning and print #ERORR in
> the cell.
> is there a solution for this?
> Do you know the syntax to format the number in the function before the
> return?
> (Sorry for the dumb question but I know C# not VB.net and there is no
> intelisance in that editor).
> Thanks a lot!
You will most likely want to return a null (or Nothing in terms of
SSRS-VB.NET), that way there is no error when trying to convert an
empty string to an integer/decimal/etc. Or you could try using
InScope. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||you could add 2 more fields, one with the formatted text, and another
that tests whether the original is 0, then displays an empty string or
the formatted field.

Formating drill down on SubTotal Column in Matrix

Hello Experts,

I have a Matrix in my report where on each each cell i have a drill down to another report. The Matrix also has SubTotal Column getting generated.

But the Problem is the drill down report gets carried on the SubTotal column also and the user gets vage results as proper information is not passed to another report.

Is there a way through which if its a subTotal cell then i can hide the drill down. some IIF() condition is there is

I appreciate if somebody can help me address this problem.

/Soni

The jump to report EXPRESSION should be something like:

=IIF(InScope("matrix1_YourGroupName"), "Other report name", Nothing)

Formating drill down on SubTotal Column in Matrix

Hello Experts,

I have a Matrix in my report where on each each cell i have a drill down to another report. The Matrix also has SubTotal Column getting generated.

But the Problem is the drill down report gets carried on the SubTotal column also and the user gets vage results as proper information is not passed to another report.

Is there a way through which if its a subTotal cell then i can hide the drill down. some IIF() condition is there is

I appreciate if somebody can help me address this problem.

/Soni

The jump to report EXPRESSION should be something like:

=IIF(InScope("matrix1_YourGroupName"), "Other report name", Nothing)

Formating a number on a cell

Hi,

I have the following problem, i want the number format to appear depending on the language that the IE is on once you are using the reports on the web.

The thing is that my reports have language = default.

Format of the numbers are N0 or ###,###,###

The result is

123,234,456 with pt language ou en-us language, and i would like to be 123.234.456 in pt and 123,234,456 on en.

What am i doing wrong?

Thank you

Sorry all, my mistake in the report language tag instead of "default" it must be used "=User!Language"|||

Hi,

Be aware that when working with currency fields, you need to watch out when using the user's setting. For example if you store your data in $ and add a currency field to the report, I will see it as € since I have Dutch settings.

To prevent this, you need to set the Language property of the Report. This way, you can force the report to be displayed in the selected Culture.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Formating a number in VB code

Hi, I have another question about code in RS.
I have a table cell with FORMAT of "$#,##0,0".
If I have any number in the cell I want to display it.
If I have 0 (zero) I want the cell to be empty.

I know how to do it with an expression:
iif (Fileds!myFiled.Value == 0,"", Fileds!myFiled.Value)

Now I want to do it with a code section:
=Code.hideIfZero(Fileds!myFiled.Value)

The problem is that the function should return Integer and if the value is zero I need to return empty string.

It does't say any thing about returning the string but when it tryingto use the format on a string it give me a worning and print #ERORR inthe cell.

is there a solution for this?
Do you know the syntax to format the number in the function before the return?
(Sorry for the dumb question but I know C# not VB.net and there is no intelisance in that editor).

Thanks a lot!

Hi,

had a similar problem recently.

Try returning "Nothing" (it's a keyword, sth like null), instead of empty string.

|||

Hi,

I tried that but if I return a number it showen as 0.0 instead of an empty cell :(

Any more ideas?

Thanks.

|||

Hmm,

it works from me. However, I set the value as an expression "Iif(...)" no custom functions defined.

And I've set the "Format" property of that cell to "n2".

|||

Well, its didn't worked for me. maybe it because I'm useing Cutom code function to return the value.

I also trued th N2 format but same resualt.

Thanks for the help!

format expression is being ignored in table cell

I am consolidating multiple reports into one and wish to set the formatting
on the cell dependent upon the data.
The following code does not work
= iif(Parameters!RevenueColumns.Value=1,
"#,#"
,"")
&
iif(Parameters!RevenueColumns.Value=2,
"N2"
,"")
&
iif(Parameters!RevenueColumns.Value=3,
"N2"
,"")
When RevenueColumns is 1, the number is being quoted to about 20 decimal
places.
There is nothing in 'format' for the column or row, this is the only place
where format is set. Even if I change it back to just "#,#" it is being
ignored but i know it is accepting changes as i have varied the colour. I
have also tried N0 as the format and also 0.0 but nothing seems to change it,
even changing it back to the original value has not solved the issue. I have
deleted and redeployed the report, cleared out my IE cache and rebooted but
to no avail.
Can anyone help?
thanksthe numeric and the two null strs are being converted to a string, on which
it is not able to perform the formatting
coerce my arce
"adolf garlic" wrote:
> I am consolidating multiple reports into one and wish to set the formatting
> on the cell dependent upon the data.
> The following code does not work
> => iif(Parameters!RevenueColumns.Value=1,
> "#,#"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=2,
> "N2"
> ,"")
> &
> iif(Parameters!RevenueColumns.Value=3,
> "N2"
> ,"")
> When RevenueColumns is 1, the number is being quoted to about 20 decimal
> places.
> There is nothing in 'format' for the column or row, this is the only place
> where format is set. Even if I change it back to just "#,#" it is being
> ignored but i know it is accepting changes as i have varied the colour. I
> have also tried N0 as the format and also 0.0 but nothing seems to change it,
> even changing it back to the original value has not solved the issue. I have
> deleted and redeployed the report, cleared out my IE cache and rebooted but
> to no avail.
> Can anyone help?
> thanks