Background images won't show when exporting to Excel

Hello there,
I have a table with several cells that contain a value. Also for those cells
there's a specific background image, which is displayed depending on the
value contained (displayed) in the cell. Everything works fine, but when I
export the report to excel the background image is not displayed, only the
value.
Is there any way to achieve this ? (display and background image along with
the value?)
Is this a Excel's limitation or is it Reporting Services' ?
Is there any work around ?
Will the RS SP2 add this feature ?
Thanks in advance
CesarCesar;
this is an Excel limitation and backgroup images are not supported.
Unfortunatelly, there is no work around I am aware of.
"Cesar" wrote:
> Hello there,
> I have a table with several cells that contain a value. Also for those cells
> there's a specific background image, which is displayed depending on the
> value contained (displayed) in the cell. Everything works fine, but when I
> export the report to excel the background image is not displayed, only the
> value.
> Is there any way to achieve this ? (display and background image along with
> the value?)
> Is this a Excel's limitation or is it Reporting Services' ?
> Is there any work around ?
> Will the RS SP2 add this feature ?
> Thanks in advance
> Cesar

Background expression

Hi All,

I tried using the following Expression in a field's background property:

=iif( Fields!ProjectedDate.Value between (getdate()+1) and (getdate()+7), Orange ,White )

And then I got this message, anyone got any ideas?

The background color expression for the textbox 'textbox49' contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

=iif( Fields!ProjectedDate.Value < (getdate()+1) AND Fields!ProjectedDate.Value > (getdate()+7), Orange ,White )

I dont think you can use between in expression.

sql

Background Colour with null values

I am trying to change the background colour of a textbox in a table to
display one colour if the value is null and another if there is a value. The
data is an integer type and when using isdbnull the colour displayed for the
null value columns is the colour for a value, not the null colour. If am
using '=nothing' it is displaying the null colour for the values of 0.
I am using RS 2000 SP2Try using the VB.NET syntax of 'is nothing' instead of '=nothing'
Matt A

Background colour

hi there ,
if a question abot ms report server.
i have two fields ,
field1 : name = gender value = 1 for men and 2 for woman
field 2 : name = lastname value = for example "jenkins"
so what i want to do is if gender = 1 (men) then i want to set the background colour of the field2(name) blue
else (gender = 2) i want to set the background colour of the field2(name) red.
do anyone know how i could do this ??
i'am a "newcommer" in the world of reporting services ,i always used crystal report for creating my reports.
thanks
patrickHi,
Here is a trick but most of programmers do not recommend it (Me too).
You fire the query as
Select [Name]=(Case When Gender=1 then '<font style=''backgroundcolor:blue;''>' + lastname + '<font>' Else '<font style=''backgroundcolor:red;''>' + lastname + '<font>' End )
Remember this is not recommended but you can use it in urgency.
Regards,
Hemchand.|||

Hello:
Is Gender ALWAYS a 1 or a 2?

In the field (Field2:name=LastName) within Report Layout - Select Properties (Right Side under Solutions Explorer) -

Click on BackGround (you will get a down arrow and scroll and look for Expression - Expressions is always at the top of the list)

In the Expression Box enter the following:

=iif(Field!:Gender.Value = "1", "Blue", "Red")
I probably do not have your field named correctly but you can enter =iif(THEN SELECT YOUR FIELD FROM THE DATASET and then append the remainder of the above statement.
Hope this helps!
Best Regards,
Joe

Bacground Color of group

How do a change the alternate rows within a group.
ie i am grouping in a product type that also has a product numbers & value
on the same line. Since there is more than 1 product type there is multiple
rows within the group.
I have tried the formula iif(rownumber(nothing) Mod 2, "Red", "White")
But this doesnt seem to work.Although I do not know the answer, perhaps the problem is related to
rownumber(nothing)... I think the rownumber function applies to rows in the
data set... and I wonder if the GROUP lines do not get a rownumber with
(nothing) as the parameter... You might add the function to display in the
group field to check its value...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:23069475-AA6E-42BA-9211-321A65D4C3CE@.microsoft.com...
> How do a change the alternate rows within a group.
> ie i am grouping in a product type that also has a product numbers & value
> on the same line. Since there is more than 1 product type there is
> multiple
> rows within the group.
> I have tried the formula iif(rownumber(nothing) Mod 2, "Red", "White")
> But this doesnt seem to work.|||Thanks Wayne,
I would have thought this to be a common problem...
"Wayne Snyder" wrote:
> Although I do not know the answer, perhaps the problem is related to
> rownumber(nothing)... I think the rownumber function applies to rows in the
> data set... and I wonder if the GROUP lines do not get a rownumber with
> (nothing) as the parameter... You might add the function to display in the
> group field to check its value...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:23069475-AA6E-42BA-9211-321A65D4C3CE@.microsoft.com...
> > How do a change the alternate rows within a group.
> >
> > ie i am grouping in a product type that also has a product numbers & value
> > on the same line. Since there is more than 1 product type there is
> > multiple
> > rows within the group.
> >
> > I have tried the formula iif(rownumber(nothing) Mod 2, "Red", "White")
> >
> > But this doesnt seem to work.
>
>

Axis Object - Auto Interval Error

Hello,
I am trying to plot a graph using the following data expression:
=(sum(Fields!Closed.Value)*7.5)/sum(Fields!FTE.Value)
and I get an error whenever I try to publish the graph:
An error has occured while rendering chart chart1. Details: Axis Object - Auto Interval Error Axis Object - Auto Interval Error
Anyone know what causes this and how it can be fixed?
Thanks
Wow... found the answer. So an error in my data caused this expression to be infinity. The Auto Interval Error can not handle a data point that is infinite so to fix this simply perform a check. In this case:
=iif(sum(Fields!FTE.Value)=0,"100",((sum(Fields!Closed.Value)*7.5)/sum(Fields!FTE.Value)))
I used the value 100 here becasue it is beyond my scall so a value of 100 will be very obvious.

Avoid splitting trees by value missing

Hi, could anyone kindly let me know how to prevent the Decision Trees model from using value missing as a criterion to split trees?

Thanks,

hz

Decision trees only splits on missing if you have sparse data. Are you using nested tables possibly? If not, then you have a column in your table with enough NULLs that are correlated with your target that there is enough information gain to cause a split. You can use NOT NULL, but this will just prevent the model from processing altogether if nulls are present|||

Yes, unfortunately, there are a lot of null values. I am not sure what you mean by "use NOT NULL". I have many predictor columns. Their null values are not in sync. If "NOT NULL" is used as a filter for every predictor, there may be no case left.

|||Unfortunately there's no way to not split by a value that appears in the data in SQL 2005. It's something for us to think about for future versions (expecially the "NULL" case)

Avoid splitting trees by value missing

Hi, could anyone kindly let me know how to prevent the Decision Trees model from using value missing as a criterion to split trees?

Thanks,

hz

Decision trees only splits on missing if you have sparse data. Are you using nested tables possibly? If not, then you have a column in your table with enough NULLs that are correlated with your target that there is enough information gain to cause a split. You can use NOT NULL, but this will just prevent the model from processing altogether if nulls are present|||

Yes, unfortunately, there are a lot of null values. I am not sure what you mean by "use NOT NULL". I have many predictor columns. Their null values are not in sync. If "NOT NULL" is used as a filter for every predictor, there may be no case left.

|||Unfortunately there's no way to not split by a value that appears in the data in SQL 2005. It's something for us to think about for future versions (expecially the "NULL" case)

Avoid Drill trough for zero value

Hi,
I am using Drillthrough in table Cell in one of my report.
Table cell displays sum which is sometimes Zero. And when sum is zero, I
don't want Drillthrough featur at that point, so that I can avoid unnecessary
click.
I tried using expression =IIF( sum =0, "", ReportName)
but this then take me report server and displays all the avaialble report on
RS, which is big security hole.
Please let me know the solution.
Many Thanks,
MaheshHi,
I am using Drillthrough in table Cell in one of my report.
Table cell displays sum which is sometimes Zero. And when sum is zero, I
don't want Drillthrough featur at that point, so that I can avoid unnecessary
click.
I tried using expression =IIF( sum =0, "", ReportName)
but this then take me report server and displays all the avaialble report on
RS, which is big security hole.
Please let me know the solution.
Many Thanks,
Mahesh|||Instead of "", use the keyword Nothing: =iif( Sum(...) = 0, Nothing,
ReportName)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mahesh Gaware" <MaheshGaware@.discussions.microsoft.com> wrote in message
news:B92CEC44-8A06-47A2-930D-E0463834DB32@.microsoft.com...
> Hi,
> I am using Drillthrough in table Cell in one of my report.
> Table cell displays sum which is sometimes Zero. And when sum is zero, I
> don't want Drillthrough featur at that point, so that I can avoid
> unnecessary
> click.
> I tried using expression =IIF( sum =0, "", ReportName)
> but this then take me report server and displays all the avaialble report
> on
> RS, which is big security hole.
> Please let me know the solution.
> Many Thanks,
> Mahesh
>

Avg function

The following function returns an average in minutes between to dates.
=Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
Fields!TODO_ACTL_END_DT.Value ))/60
My problem is in figuring out how to truncate decimal places for example the
report returns 36.2795358649783 when all I want is 36.
If any one has any ideas that would be greatly appreciated.
--
kmatth007use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60,"0")
"kmatth007" wrote:
> The following function returns an average in minutes between to dates.
> =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60
> My problem is in figuring out how to truncate decimal places for example the
> report returns 36.2795358649783 when all I want is 36.
> If any one has any ideas that would be greatly appreciated.
> --
> kmatth007|||Thanks! This worked.
kmatth007
"ש×?×?×?" wrote:
> use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60,"0")
> "kmatth007" wrote:
> > The following function returns an average in minutes between to dates.
> >
> > =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60
> >
> > My problem is in figuring out how to truncate decimal places for example the
> > report returns 36.2795358649783 when all I want is 36.
> >
> > If any one has any ideas that would be greatly appreciated.
> >
> > --
> > kmatth007

avg

when I use =avg( Fields!age.Value ) I get an #Error in the report. The same
thing happens when I use =StDev( Fields!age.Value ).Check the type of the field "age". Avg and StDev only work on numeric
values.
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:5E018B18-65A1-4E31-AFC2-E7A5CA36B0B5@.microsoft.com...
> when I use =avg( Fields!age.Value ) I get an #Error in the report. The
same
> thing happens when I use =StDev( Fields!age.Value ).|||what is the sytax for converting varchar to numeric
"Fang Wang (MSFT)" wrote:
> Check the type of the field "age". Avg and StDev only work on numeric
> values.
> Fang Wang (MSFT)
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:5E018B18-65A1-4E31-AFC2-E7A5CA36B0B5@.microsoft.com...
> > when I use =avg( Fields!age.Value ) I get an #Error in the report. The
> same
> > thing happens when I use =StDev( Fields!age.Value ).
>
>|||E.g. =CDbl(Fields!SomeNumericAsString.Value)
Documentation on data type conversion functions is available at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:6721A911-BF53-4E54-82D2-2E0FB670CA3C@.microsoft.com...
> what is the sytax for converting varchar to numeric
> "Fang Wang (MSFT)" wrote:
>> Check the type of the field "age". Avg and StDev only work on numeric
>> values.
>> Fang Wang (MSFT)
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:5E018B18-65A1-4E31-AFC2-E7A5CA36B0B5@.microsoft.com...
>> > when I use =avg( Fields!age.Value ) I get an #Error in the report. The
>> same
>> > thing happens when I use =StDev( Fields!age.Value ).
>>

Average Value Script

The table listed below is sampled every minute with a [CounterDateTime]
[char] (24) format.
I would like to create a script that will average the MarketValue,
FirstMarketValueA, and MarketCount fields in hourly format using the same
counterIDs.
Please help me create a script for average values.
Thanks,
CREATE TABLE [dbo].[Market_Jun2005] (
[MarketID] [int] NOT NULL ,
[RecordIndex] [int] NOT NULL ,
[CounterDateTime] [char] (24) NOT NULL ,
[MarketValue] [float] NOT NULL ,
[FirstMarketValueA] [int] NULL ,
[MarketCount] [int] NULL
)On Tue, 9 Aug 2005 14:35:44 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>The table listed below is sampled every minute with a [CounterDateTime]
>[char] (24) format.
>I would like to create a script that will average the MarketValue,
>FirstMarketValueA, and MarketCount fields in hourly format using the same
>counterIDs.
>Please help me create a script for average values.
Hi Joe,
First, change your CounterDateTime column to datatype datetime.
After that, you can use this query:
SELECT MarketID,
DATEADD(hour,
DATEDIFF(hour, '20040101', CounterDateTime),
'20040101') AS Hour,
AVG (MarketValue) AS AvgMarketValue,
AVG (FirstMarketValueA) AS AvgFirstMarketValueA,
AVG (MarketCount) AS AvgMarketCount
FROM Market)Jun2005
GROUP BY MarketIT,
DATEADD(hour,
DATEDIFF(hour, '20040101', CounterDateTime),
'20040101')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Average of Average

Hi guys,

i need to calculate the average of the average in a hierarchy

my hierarchy looks like this

1

1.1 Average: 3

1.1.1 Value: 1

1.1.2 Value: 5

1.1.3 Value: 1

1.1.4 Value: 5

1.1.5 Value: 1

1.1.6 Value: 5

1.2 Average: 5

1.2.1 Value: 5

1.2.2 Value: 5

now i would like to calculate the average of node 1 as avg(1.1, 1.2) what would be 4

on the 2nd level in can simply use

Avg([Item].[Item].currentmember.children, [Measures].[Value])

but how to calculate the average of that average?

the hierarchy i use is an attribute hierarchy with 5 levels and i need the calculation on each level

further i would need this caluclation for all my dimensions/hierarchies...

but i guess that wont be possible without far too much effort

any ideas how i could do this?

Here's an example of a recursive approach in Adventure Works, applied to the [Product Categories] hierarchy:

Code Snippet

With

Member [Measures].[AvgStockLevel] as

iif(IsLeaf([Product].[Product Categories].CurrentMember),

[Product].[Safety Stock Level].MemberValue,

Avg([Product].[Product Categories].Children,

[Measures].[AvgStockLevel])),

FORMAT_STRING = '#.0'

select

{[Measures].[AvgStockLevel]} on 0,

[Product].[Product Categories].Members on 1

from [Adventure Works]

|||

nice, thx

works perfectly!!

i just changed Avg([Product].[Product Categories].Children,

to Avg([Product].[Product Categories].CURRENTMEMBER.Children

so that it is easier to read (for me) - the result is the same

Code Snippet

With

Member [Measures].[AvgStockLevel] as

iif(IsLeaf([Product].[Product Categories].CurrentMember),

[Product].[Safety Stock Level].MemberValue,

Avg([Product].[Product Categories].CURRENTMEMBER.Children,

[Measures].[AvgStockLevel])),

FORMAT_STRING = '#.0'

select

{[Measures].[AvgStockLevel]} on 0,

[Product].[Product Categories].Members on 1

from [Adventure Works]

i didn't even know that it is possible to use recursive calculations within mdx

thx

Gerhard