Excel formula references

I have a data list that will grow over time. The values are listed vertically in a column; most recent value at the bottom. I am trying to figure out how to setup a formula to figure out the standard deviation on the most recent 30 values automatically. For instance if the column contains 30 values and I add the 31st value, I'd like to have to have the standard deviation displayed in a cell and automatically shift from calculating on values 1-30 to values 2-31. Is this possible?

Jeff.

Looking in Books On Line (http://technet.microsoft.com/en-us/library/ms159220.aspx), I see that StDev is not a supported expression for export to Excel. Specifically:

For all other cases, such as an unsupported expression, a formula is not generated for Excel. Instead, the value is placed in directly in the cell.

This means that you would be required to calculate your standard deviation 'by hand' for the last 30 of the items in your list.

Is it a requirement that the calculation be done in the spreadsheet? How about having the standard deviation calculation done by a SQL query? TSQL has the STDEV to calculate a sample standard deviation and STDEVP to calculate a population standard deviation. Add a new dataset that calculates the correct standard deviation from the last 30 entries (sort in decending order and use the top command) and then display the value where the RowNumber equals the CountRows in your table.

Larry

excel formatting problem... could this be a bug?

Okay, I'll try my best to explain this....
I have a table on my report. Above the header for 5 of my columns, I need
to place a cell that spans all those columns. It looks like this:
--
| h |
--
| h | h | h | h | h |
--
| d | d | d | d | d |
--
h = header row
d = detail row
so, to do this I took the header cell for the left most column and merged it
with the
4 other header cells. Then I put a rectangle into that cell, and drew a
line through the middle to create the two level effect in the single header
cell. Then, where each 'h' is in the above diagram, I put a text box with
some text in it that serves as a label. So, basically all 5 of my detail
columns have their own individual header, as well as a header that applies to
all of them.
When I run the report, and export it to PDF, everything looks great. When I
export to excel, I get a strange effect. SRS seems to want to put the cell
for the textbox in the bottom right most 'cell' in the header in a row below
the other 4 in the second header 'row'.. So, it looks like this in Excel:
--
| h |
--
| h | h | h | h | |
--
| h |
| --|
| d | d | d | d | d |
--
h = header row
d = detail row
Anybody have any idea what's going on here? If I remove the 5th text box in
the header (the one that's causing the problem), excel formats fine.
It would be nice if I could merge cells across rows as well as columns,
because that would make this problem trivial.
Any help is greatly appreciated.
Jeff
JeffJeff,
Have you tried inserting a second table header row above the original table
header row? Then do the merging on the inserted header row. I just did this
on a report at my work. Doing so gave me this effect:
| H1 |
|H1|H2|H3|H4|
|D1|D2|D3|D4|
H1 = Table header inserted by me.
H2 = Orginal header inserted by RS
D..= Detail records
To do this right click on the handle for the exisitng table header row and
click Insert Row Above. The formatting stayed when I downloaded the report
to Excel. Hope this helps.
"Jeff" wrote:
> Okay, I'll try my best to explain this....
> I have a table on my report. Above the header for 5 of my columns, I need
> to place a cell that spans all those columns. It looks like this:
> --
> | h |
> --
> | h | h | h | h | h |
> --
> | d | d | d | d | d |
> --
> h = header row
> d = detail row
> so, to do this I took the header cell for the left most column and merged it
> with the
> 4 other header cells. Then I put a rectangle into that cell, and drew a
> line through the middle to create the two level effect in the single header
> cell. Then, where each 'h' is in the above diagram, I put a text box with
> some text in it that serves as a label. So, basically all 5 of my detail
> columns have their own individual header, as well as a header that applies to
> all of them.
> When I run the report, and export it to PDF, everything looks great. When I
> export to excel, I get a strange effect. SRS seems to want to put the cell
> for the textbox in the bottom right most 'cell' in the header in a row below
> the other 4 in the second header 'row'.. So, it looks like this in Excel:
> --
> | h |
> --
> | h | h | h | h | |
> --
> | h |
> | --|
> | d | d | d | d | d |
> --
>
> h = header row
> d = detail row
> Anybody have any idea what's going on here? If I remove the 5th text box in
> the header (the one that's causing the problem), excel formats fine.
> It would be nice if I could merge cells across rows as well as columns,
> because that would make this problem trivial.
> Any help is greatly appreciated.
>
> Jeff
> Jeff|||Hmmm.
I vaguely remember trying something like this, but maybe I didn't set it up
right. I'll give it another shot. Thanks.
"bsod55" wrote:
> Jeff,
> Have you tried inserting a second table header row above the original table
> header row? Then do the merging on the inserted header row. I just did this
> on a report at my work. Doing so gave me this effect:
> | H1 |
> |H1|H2|H3|H4|
> |D1|D2|D3|D4|
> H1 = Table header inserted by me.
> H2 = Orginal header inserted by RS
> D..= Detail records
> To do this right click on the handle for the exisitng table header row and
> click Insert Row Above. The formatting stayed when I downloaded the report
> to Excel. Hope this helps.
> "Jeff" wrote:
> > Okay, I'll try my best to explain this....
> >
> > I have a table on my report. Above the header for 5 of my columns, I need
> > to place a cell that spans all those columns. It looks like this:
> >
> > --
> > | h |
> > --
> > | h | h | h | h | h |
> > --
> > | d | d | d | d | d |
> > --
> >
> > h = header row
> > d = detail row
> >
> > so, to do this I took the header cell for the left most column and merged it
> > with the
> > 4 other header cells. Then I put a rectangle into that cell, and drew a
> > line through the middle to create the two level effect in the single header
> > cell. Then, where each 'h' is in the above diagram, I put a text box with
> > some text in it that serves as a label. So, basically all 5 of my detail
> > columns have their own individual header, as well as a header that applies to
> > all of them.
> >
> > When I run the report, and export it to PDF, everything looks great. When I
> > export to excel, I get a strange effect. SRS seems to want to put the cell
> > for the textbox in the bottom right most 'cell' in the header in a row below
> > the other 4 in the second header 'row'.. So, it looks like this in Excel:
> >
> > --
> > | h |
> > --
> > | h | h | h | h | |
> > --
> > | h |
> > | --|
> > | d | d | d | d | d |
> > --
> >
> >
> > h = header row
> > d = detail row
> >
> > Anybody have any idea what's going on here? If I remove the 5th text box in
> > the header (the one that's causing the problem), excel formats fine.
> >
> > It would be nice if I could merge cells across rows as well as columns,
> > because that would make this problem trivial.
> >
> > Any help is greatly appreciated.
> >
> >
> > Jeff
> > Jeff

Excel formatting - currency & percentages

Hi fellas,

this is another one of those "RS to Excel formatting" questions :)

I have reports with a large number of columns containing either percentages or currency figures. These numbers show up in Excel with the General format - how can i get Excel to recognise them for what they are? I preferably want to keep the $ and % signs included, as it makes the report a lot easier to read if they are retained, so formatting the numbers as decimals etc. (as other answers have suggested) is probably a last resort.

As a related question, is it possible to write a custom filter or renderer that can be inserted between RS and Excel?

Thanks for any answers/ideas!

sluggy

I believe if you use the c and p format that Excel will render it correctly. c# for currency where the # is the decimals to carry. p# for percentage where the # is the number of decimals to carry. Another useful one is n# (general number format with commas).|||

Thanks for the answer Lonnie, but it doesn't work. Using the currency fields as an example, i have always used FormatCurrency() to format them, and changing this to Format(value, "c0") has no effect on the problem.

Cheers

sluggy

|||Use the Format field on the textbox, and put the "c0" in there. This way the renderer can apply the formatting; in Excel's case, it will translate the value of the Format field to an Excel format and apply it to the cell that the textbox appears in.|||

Geoff,

thanks, that was the answer. There was a subtlety in there that tripped me up, and i'm going to mention it because it could help someone else.

In the Value property of the textboxes in the report, i was using FormatPercent() to format the results of an expression, but the output of that function (and its sisters) is a string, which meant that when i used a format code in the Format property it was not being applied. The solution was to get rid of any formatting in the Value property, move the formatting to the Format property, and ensure that the Value field only evaluated to numeric values so that the formatting could be applied. It might sound simple, but i never saw anything mentioning this in any of the doco i read :)

Many thanks,

sluggy

Excel formatting - currency & percentages

Hi fellas,

this is another one of those "RS to Excel formatting" questions :)

I have reports with a large number of columns containing either percentages or currency figures. These numbers show up in Excel with the General format - how can i get Excel to recognise them for what they are? I preferably want to keep the $ and % signs included, as it makes the report a lot easier to read if they are retained, so formatting the numbers as decimals etc. (as other answers have suggested) is probably a last resort.

As a related question, is it possible to write a custom filter or renderer that can be inserted between RS and Excel?

Thanks for any answers/ideas!

sluggy

I believe if you use the c and p format that Excel will render it correctly. c# for currency where the # is the decimals to carry. p# for percentage where the # is the number of decimals to carry. Another useful one is n# (general number format with commas).|||

Thanks for the answer Lonnie, but it doesn't work. Using the currency fields as an example, i have always used FormatCurrency() to format them, and changing this to Format(value, "c0") has no effect on the problem.

Cheers

sluggy

|||Use the Format field on the textbox, and put the "c0" in there. This way the renderer can apply the formatting; in Excel's case, it will translate the value of the Format field to an Excel format and apply it to the cell that the textbox appears in.|||

Geoff,

thanks, that was the answer. There was a subtlety in there that tripped me up, and i'm going to mention it because it could help someone else.

In the Value property of the textboxes in the report, i was using FormatPercent() to format the results of an expression, but the output of that function (and its sisters) is a string, which meant that when i used a format code in the Format property it was not being applied. The solution was to get rid of any formatting in the Value property, move the formatting to the Format property, and ensure that the Value field only evaluated to numeric values so that the formatting could be applied. It might sound simple, but i never saw anything mentioning this in any of the doco i read :)

Many thanks,

sluggy

Excel Formatting

I have a report that I exported to Excel which contains dates in one column.
About 10% of the dates show up as ######## accross the width of the cell.
The column is plenty wide enough and if you double-click one of these cells
the date shows up fine. It also does it on a column that has percentages.
Does anyone know how to prevent this from happening?What happens when you make the column very wide? Could it be trying to
format time in there as well?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:C87B46BC-65B1-4AB5-901E-E1707AFF4DA9@.microsoft.com...
>I have a report that I exported to Excel which contains dates in one
>column.
> About 10% of the dates show up as ######## accross the width of the cell.
> The column is plenty wide enough and if you double-click one of these
> cells
> the date shows up fine. It also does it on a column that has percentages.
> Does anyone know how to prevent this from happening?|||No. I made the column width 150 and it just added more # signs to fill up
the column. Is there a way I can send you the Excel file?
"Jeff A. Stucker" wrote:
> What happens when you make the column very wide? Could it be trying to
> format time in there as well?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:C87B46BC-65B1-4AB5-901E-E1707AFF4DA9@.microsoft.com...
> >I have a report that I exported to Excel which contains dates in one
> >column.
> > About 10% of the dates show up as ######## accross the width of the cell.
> > The column is plenty wide enough and if you double-click one of these
> > cells
> > the date shows up fine. It also does it on a column that has percentages.
> > Does anyone know how to prevent this from happening?
>
>sql

Excel Format different from PDF and Image

I have a report that looks good in .PDF and .tif format, but really bad
in Excel format. Can anything be done about this?
Thank you!Carol,
At the heart of things is the fact that .PDF and .TIF simply render an
image of the report while Excel is trying to insert the entire report,
including headers and footers, into a viable spreadsheet.
Without seeing the Excel output, it's tough to know for sure what
issues you're having but typically the problem with Excel is it inserts
additional columns (and sometimes rows as well) that don't exist in the
original report design.
The cause is that the left and right borders of some report objects
(text boxes, etc.) are not lining up in the different report sections
and/or data regions. For example, if there's a text box in the page
header that's 1" wide and then a 1.25" wide table column in the body
(under the 1" header text box), Excel will render that into two
columns. That's a pretty basic example but the problem greatly
exacerbates if you're using free-form data regions or multiple tables
or tables with subreports.
Unfortunately, the only way to resolve this is to manipulate everything
in the report design to line up as much as possible. It helps to use
the various width and height properties to set things to match up
exactly as opposed to relying just on "eyeballing" it.
Hope this helps.

Excel format

Hi,
Anyone know how can I export data to Excel 97/2000?,
because in my company everybody has Excel 97/2000 and it's
impossible change version.
Thanks and sory for my English :)Install SP1. From SP1 Readme:
<
Rendered reports can now be opened in Excel 97 and later. Previously, the
file format was MIME Encapsulation of Aggregate HTML (MHTML), which could
only be opened in Excel 2002 and later. The format for rendered reports is
now Binary Interchange File Format (BIFF), which can be opened in Excel 97
and later.
/>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<jcrf@.discussions.microsoft.com> wrote in message
news:3c5c01c4ac41$35d10900$a501280a@.phx.gbl...
> Hi,
> Anyone know how can I export data to Excel 97/2000?,
> because in my company everybody has Excel 97/2000 and it's
> impossible change version.
> Thanks and sory for my English :)