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 issues for excel

hi,

I have a column of numbers which export fine to excel when i use the formatting '$0'.

If any of the values in the column = 0 i dont want them to show so i added this line to the value =iif(Fields!Budget.Value = 0, "", Fields!Budget.Value). This works fine except when i export to excel it now doesnt read them as values and you have to convert all the cells individually. I guess this is becuase i have substituted the string "" when the data = 0 and excel does the formatting for the whole column not each cell individually. What should i do so it exports to excel correctly? Is there another way to show nothing in a cell and it still registers as a number?

geoff

WHat about Nothing rather than "" ?

=iif(Fields!Budget.Value = 0, Nothing, Fields!Budget.Value).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

formatting for writing my query to an excel sheet

Hi ,

I am executing my query and writing it to an excel sheet by choosing "query" results to an excel sheet. It does not format them well. I have trimmed my fields too. There are about 10 fields in the database and I need to show them in the excel file all the ten fields adjacent to one another. Is there anyway I can format them other than the programming aspect.

ThanksHave you tried using DTS to export your query directly into a preformatted Excel spreadsheet? While this doesn't do a 100% quality transfer, it's pretty close. Just create your Excel spreadsheet with the formatting you want, Use an export DTS wizard to walk you through putting your query as the source to be exported, then select your spreadsheet, then see if it works. You can then save it and rerun it on a schedule or manually.sql

Formatting exported Reports in Excel

Hi All,
I'm using SQL Services 2000, and have a report with 3 tables. I export
this to Excel and use Subscriptions to email this report daily. I have
inserted page breaks after each table and so each table gets printed
on separate sheets in the Excel spreadsheet. All this is fine.
Now my problem are:
1) How could I format the sheets in such a way that they print neatly
on 1 page? Right now, they spill on to the next page when it is
printed.
2) Also, how could I name these sheets so that they represent the
report the user is viewing?
I tried many options in the table properties..but couldn't figure it
out!
Any help in these issues will be greatly appreciated!
Thanks in advance.
SSIf you have grouping in your report, when you export it goes into diferent
sheets depending on the pages, Dont give grouping if possible and no page
breaks then it prints in one page. you can check in the preview itself, that
it should show 1 of 1 page and asnwering your 2nd part, It is not possible to
do Sheet naming.
Amarnath, MCTS
"SMS" wrote:
> Hi All,
> I'm using SQL Services 2000, and have a report with 3 tables. I export
> this to Excel and use Subscriptions to email this report daily. I have
> inserted page breaks after each table and so each table gets printed
> on separate sheets in the Excel spreadsheet. All this is fine.
> Now my problem are:
> 1) How could I format the sheets in such a way that they print neatly
> on 1 page? Right now, they spill on to the next page when it is
> printed.
> 2) Also, how could I name these sheets so that they represent the
> report the user is viewing?
> I tried many options in the table properties..but couldn't figure it
> out!
> Any help in these issues will be greatly appreciated!
> Thanks in advance.
> SS
>|||On Mar 13, 12:18 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> If you have grouping in your report, when you export it goes into diferent
> sheets depending on the pages, Dont give grouping if possible and no page
> breaks then it prints in one page. you can check in the preview itself, that
> it should show 1 of 1 page and asnwering your 2nd part, It is not possible to
> do Sheet naming.
> Amarnath, MCTS
Amarnath,
I guess I'll need to remove the page breaks.
I'll try this. Thanks!

FormatNumber >> Excel export?

Hi all

I want my measures to be exported(Excel) as numbers instead of text.
When i simply use them as is it does this correct but as soon
as I as the following number formating it no longer exports
correctley.

=FormatNumber(Fields!Example.Value,2,True,True,True)

The same apllies to whe I set the formatting in the text box properties to N

Can some please tell me how I can acheive this result when exporting

Thanks in advance

G

Apply formatting using N in the Format property, then in the value textbox, apply an explicit cast of the value. E.g.

=CDbl(Fields!Example.Value)

Bizzare, but I've found this to work for me.

|||

Thanks Adam

I came accros the same thing today when i was casting
the values returned from custom code. I just hope the same
principal will aplly to currency.

In my case I had to figure to use N0
as format type to get 1,345,456

When using the cdbl() to do this you cant use FormatNumber(,,,)
that was my snag.

Thanks again for your time

G

Formating issues when exporting to excel

I have several columns in a report that have conditional formating. If
the criteria is met the data in the row is supposed to be green,
otherwise it is black. When I export this to excel, excel looks at the
format of the first row and then sets the formating for all the cells
to that color. Is there a way to make sure excel formats each
individual cell correctly?Also noticed that when i export the report when i run the report
through Visual Studio it exports it correctly. Is this a version issue
with RS?|||Hi, I have the same problems to set the color for each cell. Do you find the
solution? If does, please post back. Thank you.
"acahn@.planetmagpie.com" wrote:
> I have several columns in a report that have conditional formating. If
> the criteria is met the data in the row is supposed to be green,
> otherwise it is black. When I export this to excel, excel looks at the
> format of the first row and then sets the formating for all the cells
> to that color. Is there a way to make sure excel formats each
> individual cell correctly?
>|||Hi Jim,
I have not figured out the problem. I'm hopeing that when we migrate to
reporting services for 2005, that it fixes the issue...
"Jim Pan" wrote:
> Hi, I have the same problems to set the color for each cell. Do you find the
> solution? If does, please post back. Thank you.
> "acahn@.planetmagpie.com" wrote:
> > I have several columns in a report that have conditional formating. If
> > the criteria is met the data in the row is supposed to be green,
> > otherwise it is black. When I export this to excel, excel looks at the
> > format of the first row and then sets the formating for all the cells
> > to that color. Is there a way to make sure excel formats each
> > individual cell correctly?
> >
> >

Format_String causes crash excel 2007

Hello,

I have the following mdx script command :

SCOPE ({[Rubrieken].[Hierarchy].&[32]});

FORMAT_STRING(THIS) = "#,#.00";

End Scope;

when I now generates an excel 2007 olap report, I get the correct formatting.

But when I change the MDX to

SCOPE ({[Rubrieken].[Hierarchy].&[32]});

FORMAT_STRING(THIS) = "#,#.00 d";

End Scope;

So now I expect a 'd' after the figures.

But when I generate the same report in excel 2007, excel crashes.

Anyone an idea?

thx

This is only a guess but you can try "#,#.00d" .

It can be the space that is causing the problem.

When you write a percent format you can use "##.##%" so if my suggestion do not work SSAS2005 expects a code it understands and d is not supported.

HTH

Thomas Ivarsson

|||

Hi Thomas

I tried it, but it also cause excel to crash.

The weird thing is that in the 'browser' of Analysis Services the formatting is shown correct.

So I think that it is a bug in excel.

|||

I can confirm this problem with Excel 2007. It works fine in ProClarity Professional 6.3

Yes, it looks like a bug in Excel 2007.

Regards

Thomas Ivarsson

Format similar to Excel accounting format (paren around negatives, - for 0)

I know that it's possible to get () around negatives in Reporting
Services, but I have yet to find a way to have it display 0 as a -
(hyphen). I've found that I can use an IIF but that means that the -
will be a string and not actually representative of 0 when exported to
Excel. Anyone have a solution, or is this just a shortcoming of
Reporting Services?On Aug 17, 12:19 pm, TrueDis <swro...@.gmail.com> wrote:
> I know that it's possible to get () around negatives in Reporting
> Services, but I have yet to find a way to have it display 0 as a -
> (hyphen). I've found that I can use an IIF but that means that the -
> will be a string and not actually representative of 0 when exported to
> Excel. Anyone have a solution, or is this just a shortcoming of
> Reporting Services?
Have you set the format property using the iif statement and then set
the value property as Nothing or 0 if below zero? If so, you should be
able to avoid the string representation in Excel. You should use
something like this in the Format property.
=iif(Fields!SomeItem.Value < 0, "-(#,0)", "#,0")
and then use the regular expression in the Value property:
=Fields!SomeItem.Value
If this does not work out, you should be able to use the cast as int
functionality in the value property.
=iif(Fields!SomeItem.Value < 0, CInt(Nothing), Fields!SomeItem.Value)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 18, 9:21 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Aug 17, 12:19 pm, TrueDis <swro...@.gmail.com> wrote:
> > I know that it's possible to get () around negatives in Reporting
> > Services, but I have yet to find a way to have it display 0 as a -
> > (hyphen). I've found that I can use an IIF but that means that the -
> > will be a string and not actually representative of 0 when exported to
> > Excel. Anyone have a solution, or is this just a shortcoming of
> > Reporting Services?
> Have you set the format property using the iif statement and then set
> the value property as Nothing or 0 if below zero? If so, you should be
> able to avoid the string representation in Excel. You should use
> something like this in the Format property.
> =iif(Fields!SomeItem.Value < 0, "-(#,0)", "#,0")
> and then use the regular expression in the Value property:
> =Fields!SomeItem.Value
> If this does not work out, you should be able to use the cast as int
> functionality in the value property.
> =iif(Fields!SomeItem.Value < 0, CInt(Nothing), Fields!SomeItem.Value)
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
What I'm hoping to do is have 0 show up as a dash like this: -
Will this do that?

format print range when converted to Excel?

I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.

How can I get Excel to format the print version just as I have on the RS site?

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.|||

Anyone else?

Is there a way to format the report to print at 80% in Excel without the user having to make the change in Excel?

|||

KimberlyL wrote:

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.

I also have this problem. I have tried to minimize tables, move around objects, etc. but nothing works. The report looks good in direct print and export to other formats but when it comes to print from the exported excel report it is always to wide.

Has anyone got a solution or work around for this problem?

Thanks

/Mats

|||

I hope that Microsoft can fix this in a future release. I get many complaints about this. My users want to print from Excel without intervention and I cannot find a work around for this.

format print range when converted to Excel?

I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.

How can I get Excel to format the print version just as I have on the RS site?

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.|||

Anyone else?

Is there a way to format the report to print at 80% in Excel without the user having to make the change in Excel?

|||

KimberlyL wrote:

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.

I also have this problem. I have tried to minimize tables, move around objects, etc. but nothing works. The report looks good in direct print and export to other formats but when it comes to print from the exported excel report it is always to wide.

Has anyone got a solution or work around for this problem?

Thanks

/Mats

|||

I hope that Microsoft can fix this in a future release. I get many complaints about this. My users want to print from Excel without intervention and I cannot find a work around for this.

format print range when converted to Excel?

I have a report that the user can print right from the RS site on three pages (each page one page wide). However, when I have the server email the report as an attachment in Excel; Excel tries to print it on four pages with one of the pages spilling over into the fourth page.

How can I get Excel to format the print version just as I have on the RS site?

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.|||

Anyone else?

Is there a way to format the report to print at 80% in Excel without the user having to make the change in Excel?

|||

KimberlyL wrote:

I have had the same problem. It almost seems that anything that I export to excel looks like it grows when it is printed in excel. I posted a question referring to that and never got an answer.

I also have this problem. I have tried to minimize tables, move around objects, etc. but nothing works. The report looks good in direct print and export to other formats but when it comes to print from the exported excel report it is always to wide.

Has anyone got a solution or work around for this problem?

Thanks

/Mats

|||

I hope that Microsoft can fix this in a future release. I get many complaints about this. My users want to print from Excel without intervention and I cannot find a work around for this.

|||You can report the same in connect site by referring to Http://connect.microsoft.com.
FYI

format of point labels

Can I display number values with their corresponding percentage values in pie
charts like I can in Excel? (Ex. 45, 7.7%) If so, how? I've gotten either
one or the other to appear, but not both at the same time.You would need to write an expression to concatenate the calculated values.
E.g.:
=Sum(Fields!x.Value) & ", " & Format(Sum(Fields!x.Value) /
Sum(Fields!x.Value, "DataSetName"), "P1")
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dataGirl" <dataGirl@.discussions.microsoft.com> wrote in message
news:8025F487-40B6-46E8-97D4-FABA0507B9EB@.microsoft.com...
> Can I display number values with their corresponding percentage values in
> pie
> charts like I can in Excel? (Ex. 45, 7.7%) If so, how? I've gotten
> either
> one or the other to appear, but not both at the same time.

FORMAT ISSUE... what to do?

Hey,
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon

format excel output

In my report I have used the "jump to url" for the report output but when I export this to excel I get a column with the correct data and also the url.

Is there anyway of exporting the data without the url?

I want my users to be able to view the report and click on the column the want to URL jump to but also be able to export just the raw data without the URL?

No. For this I've had to create 2 versions of my reports, 1 with formatting, layout, links etc. and the other just plain data. I then add a link to the formatted version (attached to an excel icon) and just pass through the current parameter values i.e. this is done by constructing the URL using an expression and additionally appending rs:Format=EXCEL to the end of it.

|||Is there any way of having a column that shows up on the report be can't be exported to excel?|||

I thought I already answered that?

The answer is No. You cannot detect within the report which renderer was used.

Format a number for use as number in Excel

I want to format a number like "#,##0.00" in order to handle it in Excel as a number (i.e. compute a sum).

Excel is able to show a number in a specail format and still allow to compute with ...

Thanks in advance,

Peter

What problem are you having? If you have #,##0.00 set as your format code, you should be able to export to Excel and then do a sum on that column.

Jarret

|||

It doesn't work in my Excel neither with 1 value nor with many values - the sum is 0 anytime.

Peter

|||

I found this on another post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1072895&SiteID=1):

Apply formatting using N in the Format property, then in the value textbox, apply an explicit cast of the value. E.g.

=CDbl(Fields!Example.Value)

Hope this helps.

Jarret