none
SSRS 2005 SP3 - Instr function with Global Variable fails in Subscriptions

    Question

  • Hi

    I have a very simple expression in a text box in my report: 

    INSTR(Globals!ReportServerUrl,"Report"). 

    This expression should return the integer value of where the string "Report" appears in the Global variable for the ReportServerUrl.  Simple enough.  This works fine for generating the report directly through report manager, and in my case returns a value of 20. However when run as a subscription and emailed to myself the value returned is 0. This suggests that the search string does not appear in the Global variable.

    In the reportserver config file the URLroot tag contains the correct server address - http://myserver/reportserver.

    Does anyone know why i should be experiencing this issue with my subscriptions?  Your help would be much appreciated!

    Thanks

    Guy

     


    Guy
    Thursday, August 26, 2010 9:16 AM

Answers

  • Hi Jin,

     

    Thanks for confirming its a bug - i'll submit it shortly.  As for upgrading to 2008, thats a pretty tough workaround when i'm working on a production system with separate UAT/QA/Dev servers!  But your point is taken.

     

    A simpler workaround (if upgrading is not possible) to achieve the same integer result is to do the following code:

     

     LEFT(Globals!ReportServerUrl,LEN(Globals!ReportServerUrl)-13)

     

    ie "/ReportServer" is 13 characters, so taking the full length of the global parameter and subtracting 13 will return the same result as the original InStr expression. In my case 20. Tested and confirmed that this works with subscriptions.

     

    Thanks

     

    Guy


    Guy
    • Marked as answer by Guyus Tuesday, August 31, 2010 9:20 AM
    Tuesday, August 31, 2010 9:19 AM

All replies

  • I ought to add that i have tested just using the Globals!ReportServerUrl on its own and with other functions such as LEFT and it works as expected.  It appears to only be the INSTR function that has this issue.

     

    Guy


    Guy
    Thursday, August 26, 2010 9:29 AM
  • Hi Guyus,

    Thank you for your posting.

    I can reproduce the issue in SQL Server Reporting Services 2005(both SP3 and SP3 CU10). This should be a known issue. I would suggest you submitting a feedback at https://connect.microsoft.com/sql.

    Additionally, the issue is fixed in SQL Server Reporting Services 2008 and 2008 R2. As a workaround, could you please upgrade the SQL Server Reporting Services 2005 to 2008 or 2008 R2?

    Please feel free to ask, if you have any more questions.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by Guyus Tuesday, August 31, 2010 9:20 AM
    • Unmarked as answer by Guyus Tuesday, August 31, 2010 9:20 AM
    Tuesday, August 31, 2010 2:43 AM
  • Hi Jin,

     

    Thanks for confirming its a bug - i'll submit it shortly.  As for upgrading to 2008, thats a pretty tough workaround when i'm working on a production system with separate UAT/QA/Dev servers!  But your point is taken.

     

    A simpler workaround (if upgrading is not possible) to achieve the same integer result is to do the following code:

     

     LEFT(Globals!ReportServerUrl,LEN(Globals!ReportServerUrl)-13)

     

    ie "/ReportServer" is 13 characters, so taking the full length of the global parameter and subtracting 13 will return the same result as the original InStr expression. In my case 20. Tested and confirmed that this works with subscriptions.

     

    Thanks

     

    Guy


    Guy
    • Marked as answer by Guyus Tuesday, August 31, 2010 9:20 AM
    Tuesday, August 31, 2010 9:19 AM