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?

Improve this question
5
  • 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, 2012 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, 2013 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, 2019 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, 2019 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, 2019 at 23:03

3 Answers 3

Reset to default
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))
Improve this answer
0
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
Improve this answer
0

I too was affected by this strange behavior. I believe it's a bug with the rendering lifecycle. It would initially render correctly, but changing pages or changing the page layout would cause the #Error message with a rsRuntimeErrorInExpression warning. The only way to "fix" it was to manually refresh the page. But changing the page or changing the layout again would cause the bug to continue. Others' suggestions of potentially null data is nonsense with the following expression as a test

=LookupSet("", "", "", "YourDataSet").Length

This should theoretically return the length of the entire dataset, yet it's also subject to this bug.

After giving up, I found a breakthrough when I was trying my backup plan which involved this stackoverflow post. The LookupSet expression didn't fail when it was used as a group expression. This made me think to try create a group variable expression and sure enough, it didn't error!

So my solution is the following:

  1. Use the nearest parent group or create one (that groups on a constant)
  2. In the group properties, Create a group variable called YourVariable and set it to your expression =LookupSet(Fields!contactid.Value, Fields!ContactParticipantValue.Value, Fields!RowSummary.Value, "EventRequests")
  3. Use the group variable in the Textbox where your previous expression was and manipulate the data accordingly: =Join(Variables!YourVariable.Value, Environment.NewLine)

I know this post is old, but this bug still exists in Microsoft Report Viewer 15.0.1449.0 so I hope this helps future people who run across this

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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