0

I am trying to pass information from an ssrs report to an excel spreadsheet. What I want to accomplish is clicking a link in an ssrs report that opens up an excel spreadsheet, but also populates a cell in that excel spreadsheet with a value from the ssrs report where it was opened from.

I have achieved opening the excel spreadsheet file from the ssrs report by adding an action to a textbox and using the Go to URL Action

=Fields!True_Path.Value & "\" & Fields!DOCUMENT_ID.Value

However, i want to pass the value of the textbox itself to the excel spread sheet. Is this possible? If so how could one accomplish this task? Any methods / suggestions are welcome!

Azuraith
  • 1,030
  • 14
  • 28

1 Answers1

1

Yes, you can create a macro enabled workbook and pass the value in a parameter. In your SSRS report, you will use a formula or variable. The format would look like this.

Server\ShareFolder\TheWorkbook.xlsm" /e/myParam

There is a great response to how to pass parameters to excel in the Office Microsoft Forums. You can find it here

https://social.technet.microsoft.com/Forums/office/en-US/bb9fa94f-a1d4-45cd-9279-b12e7a7e69c0/passing-a-parameter-to-an-excel-file-when-opening-it?forum=excel

kubasnack
  • 91
  • 4
  • Awesome, thank you so much for your quick response, i will try this today and mark your answer as correct if it works for me. – Azuraith Feb 14 '17 at 12:56
  • The problem with this proposed solution that i have been struggling with is that ssrs opens files using the file:// protocol. Using this protocol you cannot add parameters with methods such as /e/mypar. example: when opening a link with the url action in ssrs, the url looks like this, file://servername/sharefolder/workbook.xlsm /e/mypPar But as soon as there is a space after the xlsm the fille:// protocol can no longer handle what comes after and will look for a fille called workbook.xlsm /e/mypar instead of using the /e/mypar to actually pass a param it will check for literal value – Azuraith Feb 14 '17 at 20:08
  • I think you can combine my solutions with this old post of using relative URL hyperlinks in SSRS. There is a section on using Javascript. http://stackoverflow.com/questions/1597258/ssrs-relative-url-hyperlink – kubasnack Feb 14 '17 at 21:02
  • this solution would work great for me if i was linking to a url. But im using the url action to link to a file in a directory that is local to my client. So the url looks like file://server/directory/file.exe /e/parameter and DOES NOT look like http://mydirectory/reportcountry.aspx?CountryID= I am not trying to pass a parameter to another report with the URL action, im trying to pass a report to an EXCEL file, this method works when opening the file with cmd.exe but does not work when opening the file via url address with file:// – Azuraith Feb 16 '17 at 14:19
  • So, are you just trying to generate SSRS reports with a paramter exported to Excel dynamically? Basically, run report 1 to generate a list of reports to export. Report2with parameter x, Report2with parameter y, etc and then run those and export to excel? – kubasnack Feb 16 '17 at 14:47
  • No, i am trying to run a report that retrieves a list of excel files that are stored somewhere on the directory that are related to a certain criteria. This i have accomplished. Now i need to be able to click on those excel files and open them but when opening them from the ssrs report i need to pass a value from the ssrs report to the excel file as an excel parameter. As of right now i can click them and open them within the ssrs report. But i cannot pass those opened files a parameter based on the text box in the ssrs report that was clicked. – Azuraith Feb 16 '17 at 21:55
  • Have you tried the Javascript solution to open the file, with the parameters. You are going to have to use a custom solution since SSRS only supports the File:// and you can't pass parameters using that. – kubasnack Feb 18 '17 at 13:13
  • I was going to try it today, also i was thinking of a way to force command prompt to open with ssrs and pass the command prompt the URL, that way it could use parameters. – Azuraith Feb 20 '17 at 13:06
  • There is also an option of creating a stored proc within the report and send it parameters. I found an article about creating an Assembly to do exactly what you want, though you may have to probably stress test a system. If you start kicking off hundreds of reports, you may see some issues. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bcd19ab3-0705-4b31-8076-0a50048602a6/how-do-i-call-an-ssrs-report-from-a-stored-procedure-and-pass-parameters?forum=sqlreportingservices – kubasnack Feb 20 '17 at 16:08