Formula in Excel Sheet

Hello,

I have one simple Report in SQL Reporting Services.

there are only Three Columns GroupName, Description, Amount.

In this report there is Grouping on GroupName Field.

In Group Footer in want Group Total. i'm writting here like =Sum(Fields!Amount.Value)

Now, while preview of this report I'm exporting the same to Excel File.

While opening that Excel file in Group Footer there is no Formula which i written in .rdl file.

If any one can help me out on this Formula field it will be great.

Thank You.

Unfortunately the Excel export doesn't support this.

Formulas are only translated to Excel formulas when they use references to report items instead of fields. For example, instead of "=Fields!Amount.Value" you would use "=ReportItems!AmountTextBox.Value". You can use these to get add two values together on the same row, for instance.

However, you can only use aggregate functions such as SUM on report items when the expression is in the page header or footer. So you won't be able to use, say "=SUM(ReportItems!AmountTextBox.Value" in the table footer.

We are aware that this severely limits the utility of formulas in Excel, and are considering ways to improve it. We do not have a timeframe for this at this time.

Formatting cents

I am using RS 2000 and I am trying to format a total amount. I want the
total amount to be separted into two separate text boxes, one with dollars
and one with cents. I am able to get the dollars portion but unable to get
the cents portion.
For example: If I have 123.05 I want 123 in box A and 05 in box B. The
problem I have is that instead of 05 I get 5.
This is the expression I am currently using and not getting what I want :
= Round(((SUM(Fields!Amount.Value) - FLOOR(SUM(Fields!Amount.Value)))*100),0).
Any help would be greatly appreciated.
Scott BennettsTry this way
= (Fields!Amount.Value) - INT(Fields!Amount.Value)
Amarnath
"Scott" wrote:
> I am using RS 2000 and I am trying to format a total amount. I want the
> total amount to be separted into two separate text boxes, one with dollars
> and one with cents. I am able to get the dollars portion but unable to get
> the cents portion.
> For example: If I have 123.05 I want 123 in box A and 05 in box B. The
> problem I have is that instead of 05 I get 5.
> This is the expression I am currently using and not getting what I want :
> = Round(((SUM(Fields!Amount.Value) - FLOOR(SUM(Fields!Amount.Value)))*100),0).
> Any help would be greatly appreciated.
> Scott Bennetts

Formating numbers in SSIS

I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152

output need it to be -

00000.1520

How can I do this?

You'll have to convert it a string and prepend/append the zeroes.

-Jamie

|||Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.|||

Godai B wrote:

Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.

Too many steps? Really?

"0000" + (DT_STR, 20, 1252) 0.1520

That doesn't seem like too many to me. Can you give me examples of format functions like you require?

-Jamie

|||

this field is derived from 2 columns in a sql server table and the datatype is decimal(18,3)

the output format needs to be 000000.0000 .

the input field can have a single digit or upto six digits before the decimal point. if it has a single digit then i need to prepend "00000", if there are 2 digits then i prepend "0000" and so on.

same with digits after the decimal point.

ex: Input -->10.120 Output --> 000010.1200

Input --> .1 Output --> 000000.1000

what i did was, used the findstring function in the derived column to find the decimal point position and then got the predecimal digits and postdecimal digits. then with the help of length function i prepended or appended the zero's and then finally concatenated the predecimal and postdecimal digits.

Hope u can help me with an easy way to do this.

|||

I would use the FINDSTRING function as you have done to get the whole and the mantissa. But instead of using LENGTH I would just do this:

RIGHT("000000" + [wholepart], 6) + "." + REVERSE(RIGHT(REVERSE([mantissapart] + "0000"), 4))

Yeah, maybe a format function would be good.

-Jamie

Formating Ntext

Hi,
I am using SQLServer 2000
I currently have a table with just one field in it of type (ntext), the
reason for this is there is a very large amount of data in it of an XML
format. The data looks something like below althought this is only a small
sample there is actually a lot more in this field:-
<Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
house in the UK now requires a very large deposit, more than 5 times the
amount that was necessary in 1999 <Paragraph/><House
Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locatio
n/>
<Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
What I want is to break out the data into columns so you have something like
Paragraph HouseType Price Location
Garage Bedrooms
To buy a house... Detached ￡300,000 Berkshire Yes 4
I want to ignore the data between the <Main> Tags
Generally I think I need to break the data up based on the foward slash and
work left from there, I think it's going to be based on a left/charindex
principal but I also have to do a convert to varchar in the first place as
you are unable to do much with the data in it's current data type.
If anyone can help it would be much appreciated.
Thanks PDAre you going to do this exactly once, or regularly?
In either case, I think it would be better to have an app pull the whole
value out, parse it using XML or RegEx or whatever, and generate the INSERT
statement or procedure call necessary to break the data out.
Going forward, it would probably be better to store the individual pieces of
data and build the XML when selecting. Or, if you can move to SQL Server
2005, you can use the new XML datatype, which provides a whole slew of
options to make your implementation choice harder.
A
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
> Hi,
> I am using SQLServer 2000
> I currently have a table with just one field in it of type (ntext), the
> reason for this is there is a very large amount of data in it of an XML
> format. The data looks something like below althought this is only a
> small
> sample there is actually a lot more in this field:-
> <Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
> house in the UK now requires a very large deposit, more than 5 times the
> amount that was necessary in 1999 <Paragraph/><House
> Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locat
ion/>
> <Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
> What I want is to break out the data into columns so you have something
> like
> Paragraph HouseType Price Location
> Garage Bedrooms
> To buy a house... Detached ￡300,000 Berkshire Yes
> 4
> I want to ignore the data between the <Main> Tags
> Generally I think I need to break the data up based on the foward slash
> and
> work left from there, I think it's going to be based on a left/charindex
> principal but I also have to do a convert to varchar in the first place as
> you are unable to do much with the data in it's current data type.
> If anyone can help it would be much appreciated.
> Thanks PD|||Hi Aaron,
Thanks for the reply, I was going to go down the RegEx route but as this is
only a one of I just wanted to make it as quick and simple as possible so I
wanted to opt for querying it straight out of the field!!!.
Thanks PD
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you going to do this exactly once, or regularly?
> In either case, I think it would be better to have an app pull the whole
> value out, parse it using XML or RegEx or whatever, and generate the INSER
T
> statement or procedure call necessary to break the data out.
> Going forward, it would probably be better to store the individual pieces
of
> data and build the XML when selecting. Or, if you can move to SQL Server
> 2005, you can use the new XML datatype, which provides a whole slew of
> options to make your implementation choice harder.
> A
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
>
>