Link to home
Create AccountLog in
Avatar of mauralartin
mauralartin

asked on

Error from InScope() technique - Operator '=' is not defined for type 'Integer' and type 'Object()'.

Hi, I have built a report off of a published document which I will attach.

I am getting an error for my inscope() expressions.

For  instance,

=IIF(Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1"),Sum(Fields!SumAdjUnits.Value),Sum(IIF(Year(Fields!DOS.Value)=Parameters!Year.Value, CDbl(Fields!SumAdjUnits.Value) ,CDbl(Fields!SumAdjUnits.Value) * -1 )))

=IIF(Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1"),"",IIF(SUM(IIF(Year(Fields(Fields!DOS.Value).Value)=Parameters!Year.Value, cdbl(0), cdbl(Fields!SumAdjUnits.Value)))=0,0,cdbl(ReportItems!QTY_COL.Value) / SUM(IIF(Year(Fields(Fields!DOS.Value).Value)=Parameters!Year.Value, cdbl(0), cdbl(Fields!SumAdjUnits.Value)))))

All of the calculated fields have an error because of this message:  : Operator '=' is not defined for type 'Integer' and type 'Object()'.
Advanced-Matrix-Reporting-Techni.htm
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Can you pinpoint what part of these expressions is causing the problem. For example for the first expression, is any of the following expressions giving an error:
=IIF(Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1"),1,2)
=Sum(Fields!SumAdjUnits.Value)
=Sum(IIF(Year(Fields!DOS.Value)=Parameters!Year.Value, CDbl(Fields!SumAdjUnits.Value) ,CDbl(Fields!SumAdjUnits.Value) * -1 )

Open in new window

The syntax for these expressions might not be correct. It's just an example of how you can break down your expressions to pinpoint where the problem is.
I think the problem may be this part:
Year(Fields!DOS.Value)=Parameters!Year.Value

Open in new window

The Year() function returns an integer while .Value returns a string.  Try the following:
Year(Fields!DOS.Value)=CInt(Parameters!Year.Value)

Open in new window

or
CStr(Year(Fields!DOS.Value))=Parameters!Year.Value

Open in new window

Avatar of mauralartin
mauralartin

ASKER

One thing I did see that was obviously wrong was that I'd put in my value field rather than the parameter field for TimeSlicer.

Changing the year value to INT unfortunately did not remove the error. But great suggestion!

=IIF(Inscope("matrix1_SLICER") AND InScope("matrix1_ColumnGroup1"),Sum(Fields!SumAdjUnits.Value),Sum(IIF(Year(Fields(Parameters!TimeSlicer.Value).Value)= CINT(Parameters!Year.Value), CDbl(Fields!SumAdjUnits.Value) ,CDbl(Fields!SumAdjUnits.Value) * -1 )))
I did some testing and started with this expressions:
=iif(fields(Parameters!FieldName.Value).value=1, "Yes", "No")

Open in new window

When the parameters refers to an non-existing fieldname I get this warning (the report runs but with #Error in the text box):
...The expression that references the field 'Error' does not exist in the Fields collection.....
So I don't think using the wrong parameter was the cause of the error.
Did you make any progress on this. I'm curious what causes this error.
Thanks, I am looking forward to getting to play with it some more. Aren't meetings inconvenient!
I was sure hoping that waws the answer!  If my fields and parameters were not named or brought into the expression correctly, I'd have had the squiggly lines indicating so, wouldn't I?

Another thought. Is InScope used in SSRS 2008R2? The example OI built off of attached was 2005 I believe.
InScope is used in 2008. See http://technet.microsoft.com/en-us/library/dd255255(SQL.100).aspx
Were you able to pinpoint the part of the expression that causes the problem?
Will look today, thanks. I had quite a few expressions in the report and have made a copy so that I can deal with just a few textboxes to work this out. Thanks for the follow up!
I was finally able to break out the expression  and of the three this part is what is erroring,
3: =Sum(IIF(Year(Fields!DOS.Value)=Parameters!Year.Value, CDbl(Fields!SumAdjUnits.Value) ,CDbl(Fields!SumAdjUnits.Value) * -1 ))
Aha. There is only 1 = in your expression so (Year(Fields!DOS.Value) is the integer part of the error message and Parameters!Year.Value is the object part. I can reproduce the error when I make the Year parameter multi value.
Is your Year parameter a multi value parameter?
If so Parameters!Year.Value doesn't return a singe integer but an array of integers and you can't use that in an = expression. You can use this as your expression instead:
=Sum(IIF((";" & join(Parameters!Year.Value,";") & ";") like ("*;" & year(Fields!DOS.Value) & ";*"), CDbl(Fields!SumAdjUnits.Value) ,CDbl(Fields!SumAdjUnits.Value) * -1 ))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nico Bontenbal
Nico Bontenbal
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I just realized that the original report that I used as my model did not allow for multi select years. Your solution worked great. I am going back to the non multi select year and the expressions appear to be sorking fine so far. Great help, I could not have gotten past that without your help!
I need to grade you now, thank you so much!  Laura
thanks so much for the help. I appreciate your follow thru on this one, as it took a bit to get back to. Now I wish I could play with it rather than other reports today.  Happy Holidays, Laura