Home > SSRS > Advanced javascript in Reporting Services

 

Advanced javascript in Reporting Services

September 27th, 2008

Often our requirements demand from us to use javascript in reporting services to navigate between reports. There are various reasons for using javascript, among others:

  • Pop-ups – when we need to spawn another window for simultaneous viewing of lower level reports, or for displaying a full list of items (such as lists), which could be limited on our main report
  • Navigating from a PerformancePoint dashboard Reporting Services report to a purely Reporting services report and escaping from the PerformancePoint layer
  • Opening an custom html page (with or without passing parameters to it), which can then provide some additional functionality to our report (e.g. comments, editing fields…)
  • Providing a printable pop-up version of our report

To achieve this functionality we need to use the Navigate to URL option in our report Navigation properties and we need to provide a javascript link to our target report.

Through using javascript we can pass parameters to the targeted URL and we can also choose how to display the targeted page. In example, to open a simple html page the javascript code we need to use in our Navigate to URL expression is:
 
=”javascript:void(window.open(‘http://myserver/mypath/myasppage.html’, ‘_blank’))”
 
In this simple example we provide the javascript expression as a simple string without passing any report parameters or fields as page parameters. The result of clicking on our report link will be opening a new pop-up window, which will then display the html page at the specified address.
 
First, lets explore the target javascript parameter. _blank means that every time we click on the link, we will spawn a new pop-up window. If we supply _parent as a value for the target parameter, javascript will pass the URL to the current window and will render the html page in the already open browser window, thus allowing us to replace the content of the current tab/window with new content. Another option is to provide a custom javascript property:
 
=”javascript:void(window.open(‘http://myserver/mypath/myasppage.html’, subreport))”
 
which will result in opening a pop-up which we can reuse afterwards from any javascript link. If we have more than one javascript link on our report and we want to open some or all of those in the same pop-up window, we can name our target windows and then use the name in multiple javascript links, which then render their target pages in the same pop-up.
Furthermore, we can specify other javascript properties. An example of using some of these is:
 
=”javascript:void(window.open(‘http://myserver/mypath/myasppage.html’, ‘_blank’, width=300,height=300,top=300,left=500,toolbar=1,menubar=1,location=1,status=1,scrollbars=1,resizable=1′))”
 
For us as Reporting Services developers the more important ones are the width, height, toolbar, menubar and resizable (not resizeable). The toolbar and menubar have slightly different meanings in different browsers. In IE 6 and IE 7 enabling and disabling (setting them to 0 or 1) these result in different browser elements to get displayed, so testing them should be done in all the browsers our users could use. The resizable property allows/disallows resizing (including maximising) the pop-up window.
 
To form the URL we can either point to Report Manager or to the Report Server directly. Pointing to Report Manager will display the report with the report manager wrapper, which is in most cases not required. Supplying the Report Server URL results in rendering only the actual report in the javascript window. To form the ReportServer URL we need to supply the full path to it and the report. An example of a complete URL is:
 
http://myserver/ReportServer?/myfolder/myreport
 
or as a part of a javascript pop-up expression in Reporting Services:
 
=”javascript:void(window.open(‘http://myserver/ReportServer?/myfolder/myreport’,’_blank’))”
 
It is always a good idea to parametrise the URL and to supply the server and the path as a Reporting Services field or parameter to the expression:
 
=”javascript:void(window.open(‘” & Parameters!ServerAddress.Value & “?” & Parameters!ReportPath.Value & “myreport,’_blank’))”
 
which allows us to maintain the report in our relational back-end easing the deployment of our solution to different environment without changing all our reports.
 
We can also specify Report Server commands in the URL:
 
=”javascript:void(window.open(‘” & Parameters!ServerAddress.Value & “?” & Parameters!ReportPath.Value & “myreport&rs:Command=render&rc:Parameters=true‘,’_blank’))”
 
 
These two commands make Report Server render the report without evaluating the item first (improving performance), and also show the report parameters to the user. There is a thorough article on MSDN showing the various commands that can be passed to the reportserver and I will not go any further in explaining these in this post.
 
We can also pass report parameters to our target reports:
 
=”javascript:void(window.open(‘” & Parameters!ServerAddress.Value & “?” & Parameters!ReportPath.Value & “myreport&rs:Command=render&rc:Parameters=true&Period=” & Parameters!Period.Value & “‘,’_blank’))”
 
This expression will then pass our Period parameter to the subreport through the URL. We can specify multiple parameters like this:
 
=”javascript:void(window.open(‘” & Parameters!ServerAddress.Value & “?” & Parameters!ReportPath.Value & “myreport&rs:Command=render&rc:Parameters=true&Period=” & Parameters!Period.Value & “&Business_Unit=” & Parameters!Business_Unit.Value & “‘,’_blank’))”
 
concatenating them with the & character. Please note that there is a 255 character restriction on the length of the URL, so multi-value parameters can be problematic to pass if the set of values is too large.
Another point to note here is that we should not pass the UserId of our users as a parameter. Instead of doing this we should always capture the UserId of the person viewing the report with User!UserID.Value in each report, which minimises the security risk of someone gaining access to sensitive report data through passing an edited URL to the browser with someone else’s user id in the parameters section.
As a last section I would like to address the problem with passing a MDX expression as a parameter value. In case we have an MDX value such as:
 
[Business Unit].[Business Unit Name].&[Australia]
 
the & character will be interpreted as a concatenation between parameters and the URL will be parsed with this presumption. To avoid this we can use the escape() function in javascript to substitute the ampersand character with its URI value of %26:
 
=”javascript:void(window.open(‘” & Parameters!ServerAddress.Value & “?” & Parameters!ReportPath.Value & “myreport&rs:Command=render&rc:Parameters=true&Period=” & Parameters!Period.Value & “&Business_Unit=’ + escape(‘” & Parameters!Business_Unit.Value & “‘),’_blank’))”
 
This way we can pass any MDX or any other special or reserved character through the URL.
In conclusion, javascript in Reporting Services widens our set of tools to tackle difficult requirements in a relatively simple way. The combination of the powerful javascript language and its widespread support and the options that Reporting Services provides for report rendering control through the URL is very powerful and report developers can benefit through exploring both areas.
If you enjoyed this post, make sure you subscribe to my RSS feed!

 

SSRS ,

  1. Digger-O-Dell
    | #1

    Has anyone had the issue whereby the javascript is being popped into a new browser window and then fired? I get a new window with the url as the javascript, then the javascript opens the window correctly but the extra window still exists. This seemed to start when we moved to SSRS 2008.

  2. KEKC
    | #2

    Here is what I just tested:

    ="javascript:void(window.open('" & Replace(Fields!File_Path.Value, "\", "\\") & "','_blank'))"

  3. Mark Smith
    | #3

    From Social.msdn.microsoft.com forums sqlreportingservices – user FormView – thread – Open New Window 9/14-17
    I have run into the problem that the “&” is used in one of the fields (KPI)and I get “this error The path of the item ‘/SPARS/DetailRpt, Imp’ is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)”

    I tried using the method in http://www.bp-msbi.com/2008/09/advanced-javascript-in-reporting/ in my code and I don’t get a syntax error in SSRS but javascript doesn’t like something in this and just gives me a syntax error wanting an ‘)’ – any clues or help would be great

    =”javascript:void(window.open(‘http://velma.fmr.com/ReportServer/Pages/ReportViewer.aspx?%2fTest%2fDetailRpt&rs:Command=Render&rc:Parameters=true&RptMonth=” & Parameters!RptMonth.value & “&Reports=” & Parameters!Reports.value & “&ID=” & Fields!ID.Value & “&BD=” & Parameters!BD.Value & “&ED=” & Parameters!ED.Value & “&Alias=’ + escape(‘” & Fields!KPI.Value & “‘) + ‘,’_blank’,'width=875, height=400, top=75, left=35, toolbar=1, menubar=1, location=1, status=1, scrollbars=1, resizable=1′))”

  4. | #4

    Hi Mark,

    This works:

    javascript:void(window.open(‘http://velma.fmr.com/ReportServer/Pages/ReportViewer.aspx?%2fTest%2fDetailRpt&rs:Command=Render&rc:Parameters=true&RptMonth=1&Reports=1&ID=1&BD=1&ED=1&Alias=’+escape(’1′),’_blank’,'width=875,height=400,top=75,left=35,toolbar=1,menubar=1,location=1,status=1,scrollbars=1,resizable=1′))

  5. Mark Smith
    | #5

    Boyan,

    You are so patient. I truly appreciate your help. I copied your response exactly and added ” at the beginning and end of the string. I get this error.
    Line 1
    Char 19
    error invalid character
    code 0

  6. Mark Smith
    | #6

    Boyan,

    This script works fine until I encounter the character “&” in the KPI field or Reports field

    =”javascript:void(window.open(‘http://velma.fmr.com/ReportServer/Pages/ReportViewer.aspx?%2fTest%2fDetailRpt&rs:Command=Render&rc:Parameters=true&RptMonth=” & Parameters!RptMonth.value & “&Reports=” & Parameters!Reports.value & “&ID=” & Fields!ID.Value & “&Alias=” & Fields!KPI.Value & “&BD=” & Parameters!BD.Value & “&BD=” & Parameters!ED.Value &” ‘,’_blank’,'width=875, height=400, top=75, left=35, toolbar=1, menubar=1, location=1, status=1, scrollbars=1, resizable=1′))”

  7. | #7

    You may need to change the single quotes to proper single quotes in Notepad or similar. It seems like WordPress is changing them into a different ASCII/Unicode character. Please try that first as the javascript which I posted earlier most definitely works here and if I replace the escape(’1′) with escape(’1&1′) it shows me the string correctly encoded.

  8. Michael
    | #8

    Hey Boyan,
    is it possible to do a text search within a PDF that opens through javascript?

    =”javascript:void(window.open(‘WebsiteLink’)

    I was thinking adding search.query would work but so far I’m having no luck. Any ideas?

    I know this post is old but figure I try.

    Michael

  9. | #9

    I guess it would be possible, but I am more of an SSRS rather than javascript guy, so it would be better if you address the question to someone more knowledgeable on that side of the topic.

  10. Michael
    | #10

    @Boyan Penev
    This is within a SSRS. I’m using it as part of the navigation to jump to URL.

  11. | #11

    What I meant was that I don’t know how this is done in js. I would assume that it would be possible in SSRS if it is possible with js because all SSRS does is send you to an URL, which in this case happens to be a js expression.

  12. David X
    | #12

    Can anyone advise me on this failing script?
    =”javascript:void(window.open(‘” & Globals!ReportServerUrl & “?” & Globals!ReportFolder & “/Dashboard Sub Indecator PopUp.rdl &ParmDate=” & Parameters!ParmDate.Value &” &ParmProjectPhase=” & Parameters!ParmProjectPhase.Value &” &ParmISDepartment=” & Parameters!ParmISDepartment.Value &”&ParmRegion=” & Parameters!ParmRegion.Value &” &ParmIndVal=” & Fields!Stakeholders_Committed.Value &” &ParmColumn=” & “[Stakeholders Committed]” &”&rc:Parameters=False”& ” ‘,’_blank’,'scrollbars=auto, resizable=yes, status=no, top=100, left=250, width=500, height=500′))”

  13. | #13

    The easiest way to debug would be to copy-paste the link resulting from this expression in the report into notepad or similar (right-click on the link in the report and then copy it). It will show you what the actual expression SSRS constructs with parameters, etc. Then you can easier check what is wrong with it.

  14. Aruna Gaddam
    | #14

    Hi Boyan,
    This is Aruna. I am new for ssrs 2008 .
    My reqirement is same like this article http://www.sharepointks.com/post/How-to-open-a-browser-window-from-SSRS-2008-report.aspx
    But here they were clicked textbox url
    In my requirement with out click textbox url then automatically open a browser
    Textbox url is =”javascript:void(window.open(http://www.microsoft.com/en-us/default.aspx‘,’Window1′,’menubar=yes,width=1000,height=1000,toolbar=yes’));”
    For Testing i took this url

    Appreciate help.

  15. | #15

    Hi Aruna,

    I don’t understand your requirement. On what action/event do you want to open a new window?

  16. | #16

    I have an issue where a javascript link on my report will not fire. Nothing happens. It works fine if I just paste it into IE and go.
    Javascripting is enabled in the browser, and javascript works outside of the report viewer. Anybody else have this problem?

    javascript:void(window.open(‘http://www.microsoft.com’,'_blank’))

  17. Shane
    | #17

    Thanks for the article!

    I have a command from a Crystal report that looks like this;
    “\\novoserv\ACCPAC\Macros\NovOE.exe ” It opens the NovOE.exe screen and loads the order specified in {OEORDH.ORDNUMBER} from the report.

    I need to replicate this in SSRS. I can load the screen using the following;
    =”javascript:void(window.open(‘file://novoserv/accpac/macros/novoe.exe’,'_blank’,'ORD25332′))”

    The issue is that it is not passing / reading the order number as a parameter.

    Additional:
    The vb command that does work in Windows is;
    Shell “\\novoserv\ACCPAC\Macros\NovOE.exe ” & strDocNum, vbNormalFocus

    Any suggestions would be GREATLY appreciated!

    Thanks in advance,

    Shane

  18. | #18

    Hi Shane,

    I don’t think it’s possible to pass parameters through the file:// protocol. Maybe you can create an web app which you can call through http:// and pass the parameter to it. This app can then handle the scenario you are describing.

  19. David X
    | #19

    Boyan:

    I’m using the following in the action for chart in SSRS:

    =”javascript:void(window.open(‘” & Globals!ReportServerUrl & “?” & Globals!ReportFolder & “/PMChart_of_Monthly_performance_popup.rdl&rs:Command=render&rc:Parameters=true &ParmMonth=” & Fields!month_num.Value &” &ParmType=” & Fields!Type.Value &” &ParmYear=” & Fields!year_num.Value & “&ParmOwner=’ + escape(‘” & Parameters!ParmOwner.Value & “‘),’_blank’))”

    ParmOwner= DOMAIN\USERID is the result, where it should be DOMAIN\USERID. Escape don’t seem to work with the “\” value. Is this the case, or what am I doing wrong?

  20. David X
    | #20

    Ok, solved it. This works.

    The solution was in the Replace() function:

    =”javascript:void(window.open(‘” & Globals!ReportServerUrl & “?” & Globals!ReportFolder & “/PMChart_of_Monthly_performance_popup.rdl &ParmMonth=” & Fields!month_num.Value &” &ParmType=” & Fields!Type.Value &” &ParmYear=” & Fields!year_num.Value &” &ParmOwner=” & Replace(Parameters!ParmOwner.Value,”\”,”\\”) &”&rc:Parameters=False”& ” ‘,’_blank’,'scrollbars=auto, resizable=yes, status=no, top=100, left=250, width=500, height=500′))”

  21. Rodrigo
    | #21

    Hei Boyan,

    Interesting you article, I am facing a issue with SSRS 2008 R2 and a Dashboard in Sharepoint 2010.
    I am not sure if you are familiar with Sharepoint but anyway it looks like you know a lot, what I am trying to do is to have a dashboard with 2 reports (SSRS webparts) and when the user clicks on one of the rows on the first report, the other report should be updated based on the clicked value. I am looking for a way to link the reports and not using drilldown or drillthrough with a subreport is not an option in this case. So if you know a way to accomplish this task let me know.

  22. | #22

    Hi Rodrigo,

    As far as I am aware, if you are using just the BI stack, the only option you have is to use PerformancePoint Services. It allows passing filters between its parts and you can build grids and charts which allow this sort of functionality. Have a look at Richard Lees’ site for a live demo (http://richardlees.com.au/Sites/Demonstrations/Shared%20Documents/Web%20Dashboard%20-%20close%20to%20real%20time%20analytics%20of%20this%20internet%20site/Internet%20Activity%20Today.aspx). I don’t think Richard has linked any of the parts, but it is possible to click on a cell or a chart and pass the selection to other parts. If you already have SharePoint 2010 you already have PPS, and SSRS reports can be parts in PPS, but I don’t think you can click on a report and pass the selection to another one.

  23. | #23

    I used the same way. but its not working in performance point reports. any reasons for that?

  24. MSBI
    | #24

    Hi All,

    We Have Build one Report, Report Exported through PDF its showing Drill down Format (Customer
    Name
    Age
    Address…) thing is that am using Subscriptions report send a mail to user .But that PDF format report is not show this format (Customer
    Name
    Age
    Address…) only showing Just customer only. I want drilldown also .please help him any suggestions.Is there any customization code is available for Controlling the PDF Export for Expanded format

    Regards
    MSBI

  25. robin
    | #25

    thank you so much for writing this up, it’s invaluable!!

  26. Karan Verma
    | #26

    @Boyan Penev
    Hi Boyan,My Self Karan,My Question Same as Arunna Que,I elaborate what she want to say, I have created rdlc report in .net Business intelligence project and give hyperlink like =”javascript:void(window.open(‘http://www.google.com’,'_blank’))” to text box.When i deploy .rdlc report on report server and run through IE web browser,then it will work fine and google page open.But When call this SSRS report on .aspx .net page through report viewer and click on hyperlink, then new popup windows open with url and this should not happen.Page require to open with url not

    Kindly give solution as early as possible

  27. Karan Verma
    | #27

    Any one can tell me,
    My Question Same as Arunna Que,I elaborate what she want to say, I have created rdlc report in .net Business intelligence project and give hyperlink like =”javascript:void(window.open(‘http://www.google.com’,'_blank‘))” to text box.When i deploy .rdlc report on report server and run through IE web browser,then it will work fine and google page open.But When call this SSRS report on .aspx .net page through report viewer and click on hyperlink, then new popup windows open with url and this should not happen.Page require to open with url not

    Kindly give solution as early as possible

  28. | #28

    Hi Karan,

    If you read:

    http://www.w3schools.com/jsref/met_win_open.asp

    You will notice that “The open() method opens a new browser window.”

    If you are using that method you can expect a new window to open. If you don’t want to open a pop-up, why don’t you remove all javascript and just point to the URL directly? (e.g. =”www.bing.com” instead of =”javascript…”)

  29. Ben S
    | #29

    I can’t get this link to work as a pop-up. It just opens in a separate tab/window. I really want it to be a pop up window.

    Any suggestions?

    My code is: =”javascript:void(window.open(‘http:…URL…’,'_blank’,'height=200, width=200′))”

    I’ve tried all sorts of other specs. Nothing seems to open just a pop up window.

  30. | #30

    Hi Ben,

    It may be due to browser settings. Please check the Tabs settings on the General Internet Options tab.

Comments are closed.