SSRS VLookup possible?

Question

Is it possible to use VLookup in SSRS ? I know we can use lookups but I want to create a SSRS report with Vlookup then export it to excel ?

If you don't know difference between lookup and Vlookup then, Vlookup works dynamically, e.g. if you enter value in a textbox, it gets results from datasets in the report.

E.g.

TextBox for OrderId : ______

TextBox for Location : *****

When user adds a orderId location text box gets updated, and it all happens dynamically.

SSRS doesn't do dynamically updated text boxes. It's a report tool not an interactive tool. You can use parameters to do something similar to this but with a combo box not a text box. Is that what you're after? — Nick.Mc, Feb 29 '16 at 09:22
@Nick.McDermaid I want to embed excel VLookup formula into SSRS so that when I export the report into excel, our clients would be able to do VLookups — Mathematics, Feb 29 '16 at 09:28
As far as I know that's not possible. You could export the SSRS report to an Excel file then run some script over to add the VLookup afterwards but that's really over the top. — Nick.Mc, Feb 29 '16 at 09:30
@Nick.McDermaid that trick might work, going to find a tut for it — Mathematics, Feb 29 '16 at 09:31
You cannot edit a textbox during run time. But it seems like what you're trying to do is a parameter. You can do it in parameters, that is a Cascading parameter -- the value of the other one depends on the other. — Kim, Feb 29 '16 at 10:04
@Kim parameters are useless when I will export my report to excel, won't it be ? — Mathematics, Feb 29 '16 at 11:12

score 1 · Accepted Answer · edited May 23 '17 at 10:28

Unfortunately, this isn't possible with SSRS. SSRS will not let you export Excel formulas.

The closest you may be able to get is to have SSRS export the file to a location then have an SSIS job update your file with the formula you want in a Script Task:

...
ExcelFormula = "=SUM(" & SumColumn & "45:" & SumColumn & Row.ToString & ")"
Worksheet.Cells(Row, Column - 1).Formula = ExcelFormula
...

http://bidn.com/Blogs/updating-a-single-excel-cell-using-ssis

Refresh Excel using SSIS script task

https://msdn.microsoft.com/en-us/library/ms403358.aspx

SSRS VLookup possible?

1 Answers1