How do I export a report with code calculated values in the cells?
I am trying to correlate values in 2 datasets beyond the standard Report Builder functions. When I do so the report comes up fine, but when trying to export I get an #Error in the cell. For example:
After I choose Actions --> Export --> Word, TIFF, PDF I get this in the file. Excel gets #Value:
Here is code and function call:
= Code.MakeList(LookupSet("", "", Fields!NamesFocus.Value, "MOV"), Fields!Name.Value, "Phoenix")
Function MakeList (ByVal items As Object(), ByVal Name as String, ByVal Focus as String) As Integer
Dim Count as integer = 0
'For each array of MOV dataset values
For Each item as Object In items
' If name occurs in this element, count # of times Focus occurs in it.
if instr(item, Name) > 0 Then
dim tmp as string = item.replace(Focus, "")
'Add to Count the # of times Focus occurs
Count = Count + (item.Length - tmp.Length) / Focus.Length
I have tested it on my local environment using your custom code (SQL Server Reporting Services 2008 R2 and Microsoft Office 2013 on Windows 7), the results display properly after export it to EXCEL. So there is no problem on the custom code. It's hard to give you the detail reason because of limited information about your report. In order to narrow down this issue, please try to apply the latest service package and latest cumulative package base on your own SQL Server version. Which version of Office you used? Please try to others versions and check if this issue persists or not.
If the issue persists, please describe your report structure, so that we try to reproduce this issue and make further analysis.
If you have any feedback on our support, please click here.
TechNet Community Support
Thanks for helping Charlie,
The report is running on a SharePoint 2010 Server, SQL Server 2008 R2. The Report connects to 2 datasets from SharePoint lists. One is a simple list of names and the other is a list of city names and focus areas. The code generates the report with counts. I am running Office 2010 on Windows 7 system. I am also using IE 10. Yet when I try to save it to my local system I get the #error. Could this be some setting on the SharePoint Server?
Even when I run it in Report Builder 3.0 locally I get the same errors.
- Edited by LALion Wednesday, November 13, 2013 11:51 PM