Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Server Reporting Services

Please correct syntax

hi there

RS2005 - Layout tab

I have a field that says

txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I want another field that says

txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))

obviously this is going to error

how would I do this please

thanks

[467 byte] By [jewelfire] at [2007-12-27]

«« Frauds of Test King and other Brain Dump Sites
»» Welcome

# 1

jewelfire,

I am not 100% what you are trying to get help with here.

I assume you have a field (txtTrackerTrue) that has the expression (starting with an equal sign)

I think you need a double equals sign like this...

=Sum(Iif (Fields!Tracker.Value == Fields!RecvdBy.Value , 1,0 ))

In your second example you were missing closing paren. and I am not sure what you are trying to do,

Can you explain in words what you are wanting to do in your second example and I will try to help you out!

Thanks

BretUpdegraff at 2007-9-4 >

top of Msdn Tech,SQL Server,SQL Server Reporting Services...

# 2

Hi,

you are missing a ")" before the 1,0.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

JensK.Suessmeyer at 2007-9-4 >

# 3

hey there thanks for jumping in, Reading back I didn't explain myself very well at all - sorry about that

RS2005 - layout tab

Name of txt box = txtTrackerTrue

Value of this txt box = Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

This is correct - this is just for guidelines.

I need another field

Lets call the txt box = txtAllTrue

Value of this txt box to be

Sum If Tracker/RecvdBy/ClosedBy all the same then true.

I thought if I could reference the 1st txt box as part of the sum is already done and then just add the closedby into the equasion?

But not sure how to reference a txt box.

thanks

Jewel

jewelfire at 2007-9-4 >

# 4

Hi,

You will have to use the prefix ReportItems!SomeItem to use the value of that field, I am not sure if that works for expressions, but It hink the processor will take care of the informationen and the precendence that the expression has to be evaluated.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

JensK.Suessmeyer at 2007-9-4 >

# 5

thanks Jens

I tried that but it came up with

ReportItem is a type and cannot be used as an expression.

any other suggestions?

cheers

jewelfire at 2007-9-4 >

# 6

You need to use the Value property on the ReportItem. For example, ReportItems!txtTrackerTrue.Value

IanRoof-MSFT at 2007-9-4 >

# 7

thanks Ian

I thought I could as well but as mentioned when Jens suggested same I am getting an error (Report Item is a type and cannot be used as an expression)

this is what I need to do

=Sum(Iif ((ReportItem!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

with ReportItem!txtTrackerTrue - already a combination of two fields being equal.

cheers

Jewel

jewelfire at 2007-9-4 >

# 8

Jens suggested that you do the same thing, but Jens did not mention that you need to use the .Value property. Make sure that the .Value property is used on the ReportItem, otherwise you will get the this error.

Ian

IanRoof-MSFT at 2007-9-4 >

# 9

thanks but still get the error with using the .Value

cheers

jewelfire at 2007-9-4 >

# 10

I noticed that in the expression "ReportItem" is used, try using "ReportItems" instead.

Ian

IanRoof-MSFT at 2007-9-4 >

# 11

thanks Ian

yes I tried that as well - I noticed that and thought I would give it a go - error is:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

so am just trying to see if there is anything else I can do.

cheers for your help though

jewel

jewelfire at 2007-9-4 >

# 12

hey there

thanks all for participating but I still have not solved this problem. Is this something I cannot do?

so I have a text box called txttrackertrue

the expression in this box is

=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I have another text box called txtClosedtrue

the expression needed for this box is

=Sum(Iif ((ReportItems!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

error is

[rsAggregateReportItemInBody] The Value expression for the textbox 'txtClosedTrue' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsReportItemReference] The Value expression for the textbox ‘txtClosedTrue’ refers to the report item ‘txtTrackerTrue’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

any suggestions please.

thanks

jewel

jewelfire at 2007-9-4 >

# 13

Where is the textbox 'txtClosedtrue' in relation to the textbox 'txtTrackerTrue'? The textbox 'txtClosedtrue' needs to be in the same scope or a child scope of the scope that contains the textbox 'txtTrackerTrue'. In other words, textboxes can only refer to other text boxes above them in the grouping hierarchy--Only one-to-one or many-to-one relationships can exist.

Also, you cannot refer to textboxes in an Aggregate function in the body of the report. This is only allowed in the page header and footer.

Ian

IanRoof-MSFT at 2007-9-4 >

# 14

thanks Ian for your input

both text boxes are on a details line of a table - there is no group in this table

txtTrackerTrue comes before txtClosedTrue

I am presuming by Aggregate you mean the (sum) part.

so if this is so is there another way I should do this then?

cheers

Jewel

jewelfire at 2007-9-4 >

SQL Server

SQL Server Reporting Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

it's Archive about 'Please correct syntax...'

Please correct syntax

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Site Classified