SSRS Excel Reference WorksheetFunction
I'd like to use an Excel worksheet function in an SSRS report. I have the following in the code window:
Public Function ExcelGrowth (ByVal intX, intY) As Object
Dim xlApp As Object
Dim numGrowth As Double
xlApp = CreateObject("Excel.Application")
On Error GoTo ErrorHandler
numGrowth = xlApp .WorksheetFunction.Growth(intX,,intY)
ExcelGrowth = numGrowth
xlApp = Nothing
ErrorHandler:
'MsgBox "Error " & Err.Description & " Error Number " & Err.Number
xlApp = Nothing
End Function
To call this from a textbox, I need to send the array of values from the column in the report. How would/could this be done?
Thanks much,
Elaine
May 30th, 2012 2:25pm
Hi Eshafer,
From your description, you use an Excel worksheet function in an SSRS report, and you paste the custom code which works well in excel in the code window, right? You can add references to custom code embedded in a report and deploy to the report client and to
the report server. For more information about custom code, please see:
http://msdn.microsoft.com/en-us/library/ms155798(v=sql.100).aspx
There are no any references about WorksheetFunction in currently version. So the WorksheetFunction in you code is not support in SSRS. SSRS report support VB language only, the Excel worksheet functions itself are not part of the VBA language. For more
information about worksheet function, please see:
http://www.cpearson.com/excel/callingworksheetfunctionsinvba.aspx
Personally, I suggest you that provide more details about your requirement, so that we can make further analysis and give you a workaround.
Thanks for your understanding.
Regards,
Charlie Liao
Free Windows Admin Tool Kit Click here and download it now
June 7th, 2012 3:22am
Hi Charlie,
Thanks for your reply. I forgot to mention I'm using VS 2008. (not R2)
I did place the above code in the Report\Report Properties\Code\Custom Code window. I've tested the code by using VBA in Access, although I've not yet been able to get the correct output from the formula. I'm doing growth year over year, so the parameters
must be passed in as an array. That would mean in a report, to pass the numbers in that column into the formula as an array.
Are you saying that the Office object model is only available in VBA and not VB? I can view the Excel.WorksheetFunction.Growth in the SSRS object model, so I was thinking I could call it from the code window.
One other option is to re-write the Excel GROWTH() formula in SQL...it is complex so will take a bit of time to do.
Thanks if you have other input.
Regards,
Elaine
June 7th, 2012 7:52pm