Prev: Confused by Role Assignment for individual reports
Next: Error Message:
From: Bruce L-C [MVP] on 10 Feb 2010 16:25
About formatting. You do not have to use a particular formatting code. You
can use custom and put it in yourself. For instance for numbers: #,##-0
provides thousand separator no decimal points. #,##0.0 shows 1 digit.
#,##0.# shows one digit if it isn't 0.

# means show if non-zero. 0 means show even if zero.

You can also do this for date time. I have formats I like that they don't
show.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Andre" <no(a)spam.com> wrote in message
news:uiGvTznqKHA.3792(a)TK2MSFTNGP05.phx.gbl...
> Uri - clicking save password works - I wasn't entering my credentials in
> the Edit section, but rather I was entering them in the Credentials tab,
> where they weren't saved. I do use BIDS.
>
> Also thanks for the tip on RunningValue. I looked all over RS but didn't
> find this. Just like last night I couldn't find a way to format a number
> as "thousand separator with no decimals". A quick search of the web found
> the Format Code N0 does what I wanted. It's disappointing that the
> regular format editor doesn't have this as a choice and I was forced to
> scan the web for such a simple thing. So my question is where do I find a
> good tutorial/resource for such things as RunningValue? I wonder what
> other things are available that I don't know about?
>
> If I may ask another question you can probably help with...
> In Crystal I have a formula field with this code:
>
> If {rs;1.difference} = 0 Then 0
> Else
> if {rs;1.total} > 0 then
> ({rs;1.difference} / {rs;1.total})
>
> I tried to convert that to an expression in a field using this code:
>
> =IIF(Fields!difference.Value = 0, 0,
> IIF(Fields!total.Value > 0, (Fields!difference.Value /
> Fields!total.Value), 0))
>
>
> The problem is every time the report is run, it gives me a divide by zero
> error.
> The values where it has #Error are:
> Total: 0
> Difference: -6857
>
> From what I can tell the IIF formula should work; am I overlooking
> something?
>
>
> Thanks for your help - it's much appreciated.
>
> Andre

From: Andre on 11 Feb 2010 01:40
Another question...

I use formula fields in my headers in CR. I tried to do the same in RS and
it threw an error that "fields cannot be used in page headers and footers".
An example of what I do in CR with a formula field that is used in my header
is to concatenate the word "Total" with the year the report is being run
for. This works if it's a title in my RS table, but I need it repeated on
each page. Below is my code that works great in my table but not in my page
header. How should I handle this?
="Total " & Fields!yr.Value

Thanks, Andre

From: Bruce L-C [MVP] on 11 Feb 2010 12:00
I am assuming that either you have one row returned or the yr field has the
same value in all rows.

Either way what you need to do is use an aggregate statement. In this case
you use First.
="Total " & First(Fields!yr.Value,datasetname)

Use the expression builder to help you with this.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Andre" <no(a)spam.com> wrote in message
news:ePKHTUuqKHA.4752(a)TK2MSFTNGP04.phx.gbl...
> Another question...
>
> I use formula fields in my headers in CR. I tried to do the same in RS
> and it threw an error that "fields cannot be used in page headers and
> footers". An example of what I do in CR with a formula field that is used
> in my header is to concatenate the word "Total" with the year the report
> is being run for. This works if it's a title in my RS table, but I need
> it repeated on each page. Below is my code that works great in my table
> but not in my page header. How should I handle this?
> ="Total " & Fields!yr.Value
>
> Thanks, Andre

From: Andre on 12 Feb 2010 01:30
Thanks Bruce. Yes, all the fields have the same value. I tried the formula
below, but I still get an error with it.

My formula:
="Total " & First(Fields!yr.Value, "dsBudgets")

The error:
[rsFieldInPageSectionExpression] The Value expression for the textbox
'textbox81' refers to a field. Fields cannot be used in page headers or
footers.

Andre

From: Andrew Karcher [SQL] on 12 Feb 2010 13:44
Andre,

That is a pretty common thing that is not something you can do out of
the box. Typically the only way around this is some creative use of
Parameters. So to do what you want above, I did through the
following:

1. Create an Internal Parameter. I called mine "myHeaderValue"
2. For the available values for the Parameter use your dataset
"dsBudgets". For the Value and Label choose your field "yr"
3. For the defaults, also use your dataset "dsBudgets". By default,
the default will use the first record in the dataset which is what you
want below. for the value choose your field "yr"

Now your formula will be ="Total " & Parameters!myHeaderValue.Value

Now when you run your report you should see the First record from that
dataset in your header now.

Like I said not elegant, but it works in this scenario.

Hope that helps,
Andrew Karcher
SQL Server MVP

On Thu, 11 Feb 2010 22:30:05 -0800, "Andre" <no(a)spam.com> wrote:

>Thanks Bruce. Yes, all the fields have the same value. I tried the formula
>below, but I still get an error with it.
>
>My formula:
>="Total " & First(Fields!yr.Value, "dsBudgets")
>
>The error:
>[rsFieldInPageSectionExpression] The Value expression for the textbox
>'textbox81' refers to a field. Fields cannot be used in page headers or
>footers.
>
>Andre
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Confused by Role Assignment for individual reports
Next: Error Message: