2

I have a dataset with a calculated field:

 = IIF(String.IsNullOrEmpty(Fields!Event.Value), "\t", Fields!Event.Value) & ", " &   
   IIF(String.IsNullOrEmpty(Fields!Action.Value), "\t", Fields!Action.Value) & ", " & 
   IIF(String.IsNullOrEmpty(Fields!RequestedBy.Value), "\t", Fields!RequestedBy.Value)

This field is then accessed inside a Table within the report using a lookup expression:

=Join(LookupSet(Fields!contactid.Value, Fields!ContactParticipantValue.Value, Fields!RowSummary.Value, "EventRequests"), Environment.NewLine)

When I run this, initially everything works fine and the correct data is shown in the cell. However there is an warning displayed:

Warning 1   [rsRuntimeErrorInExpression] The Value expression for the field ‘RowSummary’ contains an error: (processing): (field.ExprHost != null)  0   

Then if I go to the next page, all of the cells now contain #Error instead of the 'RowSummary' info. If I then go back to the previous page, which used to have all the correct data in it, all of the cells are now replaced with #Error.

I have found a work around whereby I add a hidden list container to the END of the report and set DataSet property to the 'EventRequests' dataset with the calculated field in it. I don't have to add any fields or make this list visible, but somehow just having the list on the report prevents the #Error from replacing all of my data. This does not solve the problem completely because when I go to export to PDF, I still receive errors.

Anyone know why this is happening?

migrated from stackoverflow.com Sep 23 '12 at 12:45

This question came from our site for professional and enthusiast programmers.

  • I have discovered, if I add a table with just the calculated field and make it visible, then I can export the Report without the errors appearing. So if I make the font the same color as the background and the row height as small as possible, I can almost get what I need but I have to live with some empty space at the bottom of my report. – BitFiddler Jul 6 '12 at 17:53
  • 1
    Thank you! I encountered the same problem and it was making my life miserable. Your workaround works pretty well. – confusedKid May 30 '13 at 15:10
  • Sorry to bump this after so long, but I experience the same thing. It appears to be related to the Lookup function, though no satisfactory answer can be found anywhere. Leaving in everything else and substituting a specific value in place of the lookup result resolves the issue entirely. – xDaevax May 17 at 18:49
  • @xDaevax not sure I understand your comment. Are you proposing a different solution or making an observation? If I understand you correctly, then I would expect that substituting a specific value (if by that you mean a constant like a number or string) would work. What you've done is made the report repeat the same value over and over. The lookup is crucial to my report being useful. – Bitfiddler May 17 at 21:05
  • I was only pointing out the part of the formula I could point to as the specific culprit by removing it. My solution in the end was to combine custom code with the use of the "First" expression instead of the lookup. I was also able to fix the issue (but not in export unfortunately) by setting the interactive height of the report to "0in". – xDaevax May 17 at 23:03
3

It is because it may not have a value at that pariticular field/place so first replace the value providing box with:

iif(IsNothing(sum(field)),"0",(sum(field))

Then replace row summary field with the below expression:

=iif(cInt(ReportItems!textbox1name.value)=0,100,(ReportItems!textbox2.value/iif(cInt(ReportItems!textbox1name.value)=0,100,ReportItems!textbox1name.value)*100))
0

Since comments do not have any formatting, I must use the 'answer' section to comment on Vandana's answer.

Vandana, I'm not sure I understand your answer. I am not using any numbers in my calculated field so I'm not sure how using the "sum" helps in any way?

Also, I am not using a summary row. The Join/LookupSet expression is in a regular row along with all the other data.

The output of my Kludge solution is something like:

First Name     |     Last Name     |     Event,Action,User
   John        |       Doe         |   Christmas, Remove, AdminUser
   Jane        |       Doe         |   Christmas, Add, AdminUser
   John        |       Doe         |   Team Build, Add, JDoeUser

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.