Intergrating SQL Server Reporting Services into an ASP.NET app

Discussion in 'ASP .Net' started by Brendan Reynolds, Aug 17, 2005.

  1. I'm trying to integrate SQL Server Reporting Services reports into an
    ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using direct URL
    addressing, but this exposes in the query string parameters that should not
    be exposed. Each user is associated with a school, and should see only that
    school's data. When the user logs in, I retrieve the SchoolID associated
    with that user, and that SchoolID is used as a parameter in all stored
    procedures to return only that school's data. It is this SchoolID that needs
    to be passed to the report. It doesn't matter if users see the SchoolID, but
    they must not be able to change the SchoolID, as they can if it forms part
    of the URL.

    From the documentation and from previous discussions in the SRS newsgroup,
    it appears that there are four possible solutions to this:

    1) Wait for SRS 2005 which I understand is due in November.

    2) Use the web service instead of URL access and write a lot of code to
    provide functionality you get built-in when using URL access.

    3) Use URL access through a form POST method, as suggested in SRS Books
    Online
    (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)

    4) Require users to log in again when accessing reports, despite having
    already logged in to the ASP.NET app. The report would prompt the user for
    user name and password, and pass these to the stored procedures instead of
    the SchoolID. This would avoid having to pass the SchoolID from the ASP.NET
    app to the reports, but I'm not sure that the users will accept the
    inconvenience. (The passwords are of course stored in encrypted form, so
    I'll need to add code to the reports to encrypt the password supplied by the
    user, but that's a separate issue.)

    My questions are:

    1) Can anyone confirm that the next version really does solve this problem?

    2) Any pointers to resources on using the web service, particularly on how
    to control rendering and how to reproduce the functionality of the toolbar
    you get when using URL access, would be welcome. (I already know about SRS
    Books Online and the articles reachable from the MSDN SQL Server Developer
    Center).

    3) Can I use the POST method as suggested in SRS Books Online from within an
    ASP.NET app? I thought an ASP.NET page could only post back to itself, is
    that not so?

    Please don't feel that you have to have answers to all of these questions
    before responding - suggestions or advice regarding any part of the problem
    would be most welcome.

    --
    Brendan Reynolds
    Brendan Reynolds, Aug 17, 2005
    #1
    1. Advertising

  2. Brendan Reynolds wrote:

    5). Do the users call the report with their own credentials? If yes you
    could read the username via USER!UserID and pass that to the database where
    you hopefully have a table with mapping SchoolID <-> Username.

    regards

    frank
    Frank Matthiesen, Aug 17, 2005
    #2
    1. Advertising

  3. hi brendan

    here's the solution I used (with a little bit of verbage to describe my
    scenario):

    1. I've built an ASP.NET site (in addition to the regular SSRS interface for
    reports).
    2. This addition site looks almost like the browser interface for this
    discussion group (ie. Treeview control on the left which groups 'like'
    reports and an iframe right half which brings up the report criteria form
    associated with the selected report, from the Treeview control on the left).
    3. on 'View Report' click from the iframed report(s) criteria page, I do the
    following in ASP.NET:

    Dim popupScript As String = "<script language='javascript'>"
    & _
    "var newwin =
    window.open('ReportViewer.aspx?id=myreportid&qs=myquerystring',
    'ReportViewer', 'menubar=no, toolbar=no, resizable=no');
    newwin.window.moveTo(0,0);
    newwin.window.resizeTo(screen.availWidth,screen.availHeight);
    newwin.focus();</script>"
    Page.RegisterStartupScript("PopupScript", popupScript)

    4. This opens up ReportViewer.aspx which iframes the call to SSRS. The
    benefits of this is different report interfaces can be built on differents
    sites AND there are no issue with this approach as far as 'cross-site
    scripting'. For instance, my financial group has the own SSRS ASP.NET
    interface, as well as other groups... They ALL point in the pop-up to the
    SSRS site and get a previewed report (with report parameter fields if they
    want to make changes). Also, ReportViewer.aspx changes the iframe src value
    to what was passed to it from the reportcriteria .aspx.

    5. Since the way the pop-up is created, there's no URL Address info and the
    ReportViewer.aspx masks the URL by replacing the title.

    Rob

    "Brendan Reynolds" wrote:

    >
    > I'm trying to integrate SQL Server Reporting Services reports into an
    > ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using direct URL
    > addressing, but this exposes in the query string parameters that should not
    > be exposed. Each user is associated with a school, and should see only that
    > school's data. When the user logs in, I retrieve the SchoolID associated
    > with that user, and that SchoolID is used as a parameter in all stored
    > procedures to return only that school's data. It is this SchoolID that needs
    > to be passed to the report. It doesn't matter if users see the SchoolID, but
    > they must not be able to change the SchoolID, as they can if it forms part
    > of the URL.
    >
    > From the documentation and from previous discussions in the SRS newsgroup,
    > it appears that there are four possible solutions to this:
    >
    > 1) Wait for SRS 2005 which I understand is due in November.
    >
    > 2) Use the web service instead of URL access and write a lot of code to
    > provide functionality you get built-in when using URL access.
    >
    > 3) Use URL access through a form POST method, as suggested in SRS Books
    > Online
    > (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)
    >
    > 4) Require users to log in again when accessing reports, despite having
    > already logged in to the ASP.NET app. The report would prompt the user for
    > user name and password, and pass these to the stored procedures instead of
    > the SchoolID. This would avoid having to pass the SchoolID from the ASP.NET
    > app to the reports, but I'm not sure that the users will accept the
    > inconvenience. (The passwords are of course stored in encrypted form, so
    > I'll need to add code to the reports to encrypt the password supplied by the
    > user, but that's a separate issue.)
    >
    > My questions are:
    >
    > 1) Can anyone confirm that the next version really does solve this problem?
    >
    > 2) Any pointers to resources on using the web service, particularly on how
    > to control rendering and how to reproduce the functionality of the toolbar
    > you get when using URL access, would be welcome. (I already know about SRS
    > Books Online and the articles reachable from the MSDN SQL Server Developer
    > Center).
    >
    > 3) Can I use the POST method as suggested in SRS Books Online from within an
    > ASP.NET app? I thought an ASP.NET page could only post back to itself, is
    > that not so?
    >
    > Please don't feel that you have to have answers to all of these questions
    > before responding - suggestions or advice regarding any part of the problem
    > would be most welcome.
    >
    > --
    > Brendan Reynolds
    >
    >
    >
    =?Utf-8?B?Um9i?=, Aug 17, 2005
    #3
  4. They don't, but I could change that. Thanks for the suggestion, I'll look
    into it.

    --
    Brendan Reynolds


    "Frank Matthiesen" <> wrote in message
    news:...
    > Brendan Reynolds wrote:
    >
    > 5). Do the users call the report with their own credentials? If yes you
    > could read the username via USER!UserID and pass that to the database
    > where you hopefully have a table with mapping SchoolID <-> Username.
    >
    > regards
    >
    > frank
    >
    Brendan Reynolds, Aug 17, 2005
    #4
  5. Thanks Rob. That looks promising. Unfortunately, my client-side scripting
    skills are almost non-existent, and I'm having a hard time getting to grips
    with this. Here's what I've done so far ...

    I have an ASP.NET page with a listbox that displays available reports and a
    literal control for the iframe. Here's the relevant part of the page's HTML
    ....

    <TR>
    <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    <asp:ListBox id="lstReports" runat="server" Width="100%"
    Height="295px" AutoPostBack="True">
    </asp:ListBox>
    </TD>
    <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    <asp:Literal id="MyLiteral" runat="server">
    <IFRAME id="MyIFrame"></IFRAME>
    </asp:Literal>
    </TD>
    </TR>

    I've translated your code into C#, which is what my page is using, and put
    it in the SelectedIndexChanged event procedure of my listbox ...

    private void lstReports_SelectedIndexChanged(object sender, System.EventArgs
    e)
    {
    string popupScript = @"<script language='javascript'> "
    + @"var newwin = window.open('ReportViewer.aspx?"
    + @"id=myreportid&qs=myquerystring', 'ReportViewer', "
    + @"'menubar=no, toolbar=no, resizable=no'); "
    + @"newwin.window.moveTo(0,0); newwin.window.resizeTo"
    + @"(screen.availWidth, screen.availHeight);"
    + @"newwin.focus();<script>";
    this.RegisterStartupScript("PopupScript", popupScript);
    }

    My first question is - what do I need to change to load 'ReportViewer.aspx'
    into the iframe?

    --
    Brendan Reynolds (MVP)


    "Rob" <> wrote in message
    news:...
    > hi brendan
    >
    > here's the solution I used (with a little bit of verbage to describe my
    > scenario):
    >
    > 1. I've built an ASP.NET site (in addition to the regular SSRS interface
    > for
    > reports).
    > 2. This addition site looks almost like the browser interface for this
    > discussion group (ie. Treeview control on the left which groups 'like'
    > reports and an iframe right half which brings up the report criteria form
    > associated with the selected report, from the Treeview control on the
    > left).
    > 3. on 'View Report' click from the iframed report(s) criteria page, I do
    > the
    > following in ASP.NET:
    >
    > Dim popupScript As String = "<script
    > language='javascript'>"
    > & _
    > "var newwin =
    > window.open('ReportViewer.aspx?id=myreportid&qs=myquerystring',
    > 'ReportViewer', 'menubar=no, toolbar=no, resizable=no');
    > newwin.window.moveTo(0,0);
    > newwin.window.resizeTo(screen.availWidth,screen.availHeight);
    > newwin.focus();</script>"
    > Page.RegisterStartupScript("PopupScript", popupScript)
    >
    > 4. This opens up ReportViewer.aspx which iframes the call to SSRS. The
    > benefits of this is different report interfaces can be built on differents
    > sites AND there are no issue with this approach as far as 'cross-site
    > scripting'. For instance, my financial group has the own SSRS ASP.NET
    > interface, as well as other groups... They ALL point in the pop-up to the
    > SSRS site and get a previewed report (with report parameter fields if they
    > want to make changes). Also, ReportViewer.aspx changes the iframe src
    > value
    > to what was passed to it from the reportcriteria .aspx.
    >
    > 5. Since the way the pop-up is created, there's no URL Address info and
    > the
    > ReportViewer.aspx masks the URL by replacing the title.
    >
    > Rob
    >
    > "Brendan Reynolds" wrote:
    >
    >>
    >> I'm trying to integrate SQL Server Reporting Services reports into an
    >> ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using direct
    >> URL
    >> addressing, but this exposes in the query string parameters that should
    >> not
    >> be exposed. Each user is associated with a school, and should see only
    >> that
    >> school's data. When the user logs in, I retrieve the SchoolID associated
    >> with that user, and that SchoolID is used as a parameter in all stored
    >> procedures to return only that school's data. It is this SchoolID that
    >> needs
    >> to be passed to the report. It doesn't matter if users see the SchoolID,
    >> but
    >> they must not be able to change the SchoolID, as they can if it forms
    >> part
    >> of the URL.
    >>
    >> From the documentation and from previous discussions in the SRS
    >> newsgroup,
    >> it appears that there are four possible solutions to this:
    >>
    >> 1) Wait for SRS 2005 which I understand is due in November.
    >>
    >> 2) Use the web service instead of URL access and write a lot of code to
    >> provide functionality you get built-in when using URL access.
    >>
    >> 3) Use URL access through a form POST method, as suggested in SRS Books
    >> Online
    >> (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)
    >>
    >> 4) Require users to log in again when accessing reports, despite having
    >> already logged in to the ASP.NET app. The report would prompt the user
    >> for
    >> user name and password, and pass these to the stored procedures instead
    >> of
    >> the SchoolID. This would avoid having to pass the SchoolID from the
    >> ASP.NET
    >> app to the reports, but I'm not sure that the users will accept the
    >> inconvenience. (The passwords are of course stored in encrypted form, so
    >> I'll need to add code to the reports to encrypt the password supplied by
    >> the
    >> user, but that's a separate issue.)
    >>
    >> My questions are:
    >>
    >> 1) Can anyone confirm that the next version really does solve this
    >> problem?
    >>
    >> 2) Any pointers to resources on using the web service, particularly on
    >> how
    >> to control rendering and how to reproduce the functionality of the
    >> toolbar
    >> you get when using URL access, would be welcome. (I already know about
    >> SRS
    >> Books Online and the articles reachable from the MSDN SQL Server
    >> Developer
    >> Center).
    >>
    >> 3) Can I use the POST method as suggested in SRS Books Online from within
    >> an
    >> ASP.NET app? I thought an ASP.NET page could only post back to itself, is
    >> that not so?
    >>
    >> Please don't feel that you have to have answers to all of these questions
    >> before responding - suggestions or advice regarding any part of the
    >> problem
    >> would be most welcome.
    >>
    >> --
    >> Brendan Reynolds
    >>
    >>
    >>
    Brendan Reynolds, Aug 17, 2005
    #5
  6. As an aside, we happen to use an implementation in which users do in
    fact have to authenticate a second time in order to request a report.
    It has had but a minor impact on users in our experience, generating no
    complaints. I wouldn't rule out that possibility for fear of user
    backlash.

    Brendan Reynolds wrote:
    > Thanks Rob. That looks promising. Unfortunately, my client-side scripting
    > skills are almost non-existent, and I'm having a hard time getting to grips
    > with this. Here's what I've done so far ...
    >
    > I have an ASP.NET page with a listbox that displays available reports and a
    > literal control for the iframe. Here's the relevant part of the page's HTML
    > ...
    >
    > <TR>
    > <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    > <asp:ListBox id="lstReports" runat="server" Width="100%"
    > Height="295px" AutoPostBack="True">
    > </asp:ListBox>
    > </TD>
    > <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    > <asp:Literal id="MyLiteral" runat="server">
    > <IFRAME id="MyIFrame"></IFRAME>
    > </asp:Literal>
    > </TD>
    > </TR>
    >
    > I've translated your code into C#, which is what my page is using, and put
    > it in the SelectedIndexChanged event procedure of my listbox ...
    >
    > private void lstReports_SelectedIndexChanged(object sender, System.EventArgs
    > e)
    > {
    > string popupScript = @"<script language='javascript'> "
    > + @"var newwin = window.open('ReportViewer.aspx?"
    > + @"id=myreportid&qs=myquerystring', 'ReportViewer', "
    > + @"'menubar=no, toolbar=no, resizable=no'); "
    > + @"newwin.window.moveTo(0,0); newwin.window.resizeTo"
    > + @"(screen.availWidth, screen.availHeight);"
    > + @"newwin.focus();<script>";
    > this.RegisterStartupScript("PopupScript", popupScript);
    > }
    >
    > My first question is - what do I need to change to load 'ReportViewer.aspx'
    > into the iframe?
    >
    > --
    > Brendan Reynolds (MVP)
    >
    >
    > "Rob" <> wrote in message
    > news:...
    > > hi brendan
    > >
    > > here's the solution I used (with a little bit of verbage to describe my
    > > scenario):
    > >
    > > 1. I've built an ASP.NET site (in addition to the regular SSRS interface
    > > for
    > > reports).
    > > 2. This addition site looks almost like the browser interface for this
    > > discussion group (ie. Treeview control on the left which groups 'like'
    > > reports and an iframe right half which brings up the report criteria form
    > > associated with the selected report, from the Treeview control on the
    > > left).
    > > 3. on 'View Report' click from the iframed report(s) criteria page, I do
    > > the
    > > following in ASP.NET:
    > >
    > > Dim popupScript As String = "<script
    > > language='javascript'>"
    > > & _
    > > "var newwin =
    > > window.open('ReportViewer.aspx?id=myreportid&qs=myquerystring',
    > > 'ReportViewer', 'menubar=no, toolbar=no, resizable=no');
    > > newwin.window.moveTo(0,0);
    > > newwin.window.resizeTo(screen.availWidth,screen.availHeight);
    > > newwin.focus();</script>"
    > > Page.RegisterStartupScript("PopupScript", popupScript)
    > >
    > > 4. This opens up ReportViewer.aspx which iframes the call to SSRS. The
    > > benefits of this is different report interfaces can be built on differents
    > > sites AND there are no issue with this approach as far as 'cross-site
    > > scripting'. For instance, my financial group has the own SSRS ASP.NET
    > > interface, as well as other groups... They ALL point in the pop-up to the
    > > SSRS site and get a previewed report (with report parameter fields if they
    > > want to make changes). Also, ReportViewer.aspx changes the iframe src
    > > value
    > > to what was passed to it from the reportcriteria .aspx.
    > >
    > > 5. Since the way the pop-up is created, there's no URL Address info and
    > > the
    > > ReportViewer.aspx masks the URL by replacing the title.
    > >
    > > Rob
    > >
    > > "Brendan Reynolds" wrote:
    > >
    > >>
    > >> I'm trying to integrate SQL Server Reporting Services reports into an
    > >> ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using direct
    > >> URL
    > >> addressing, but this exposes in the query string parameters that should
    > >> not
    > >> be exposed. Each user is associated with a school, and should see only
    > >> that
    > >> school's data. When the user logs in, I retrieve the SchoolID associated
    > >> with that user, and that SchoolID is used as a parameter in all stored
    > >> procedures to return only that school's data. It is this SchoolID that
    > >> needs
    > >> to be passed to the report. It doesn't matter if users see the SchoolID,
    > >> but
    > >> they must not be able to change the SchoolID, as they can if it forms
    > >> part
    > >> of the URL.
    > >>
    > >> From the documentation and from previous discussions in the SRS
    > >> newsgroup,
    > >> it appears that there are four possible solutions to this:
    > >>
    > >> 1) Wait for SRS 2005 which I understand is due in November.
    > >>
    > >> 2) Use the web service instead of URL access and write a lot of code to
    > >> provide functionality you get built-in when using URL access.
    > >>
    > >> 3) Use URL access through a form POST method, as suggested in SRS Books
    > >> Online
    > >> (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)
    > >>
    > >> 4) Require users to log in again when accessing reports, despite having
    > >> already logged in to the ASP.NET app. The report would prompt the user
    > >> for
    > >> user name and password, and pass these to the stored procedures instead
    > >> of
    > >> the SchoolID. This would avoid having to pass the SchoolID from the
    > >> ASP.NET
    > >> app to the reports, but I'm not sure that the users will accept the
    > >> inconvenience. (The passwords are of course stored in encrypted form, so
    > >> I'll need to add code to the reports to encrypt the password supplied by
    > >> the
    > >> user, but that's a separate issue.)
    > >>
    > >> My questions are:
    > >>
    > >> 1) Can anyone confirm that the next version really does solve this
    > >> problem?
    > >>
    > >> 2) Any pointers to resources on using the web service, particularly on
    > >> how
    > >> to control rendering and how to reproduce the functionality of the
    > >> toolbar
    > >> you get when using URL access, would be welcome. (I already know about
    > >> SRS
    > >> Books Online and the articles reachable from the MSDN SQL Server
    > >> Developer
    > >> Center).
    > >>
    > >> 3) Can I use the POST method as suggested in SRS Books Online from within
    > >> an
    > >> ASP.NET app? I thought an ASP.NET page could only post back to itself, is
    > >> that not so?
    > >>
    > >> Please don't feel that you have to have answers to all of these questions
    > >> before responding - suggestions or advice regarding any part of the
    > >> problem
    > >> would be most welcome.
    > >>
    > >> --
    > >> Brendan Reynolds
    > >>
    > >>
    > >>
    Ernie Gutierrez, Aug 17, 2005
    #6
  7. hi

    (hopefully, this sample code will make it thru to the forum)

    ReportViewer.codebehind (relevant part)...

    Private Sub ViewReport(ByVal key As String, ByVal reporttype As String)
    Dim iReportViewer As HtmlControl =
    CType(Me.FindControl("iReportViewer"), HtmlControl)
    Dim rptid As String = Request.QueryString("id")
    Dim qs As String = Request.QueryString("qs")
    Select Case rptid
    Case (this is my report number, ie. "id")
    Case (whatever the report # is....)
    Case 808 ' my Build Schedule Report
    If qs = "All" Then ' they f'd up using All, can't be quoted...
    iReportViewer.Attributes("src") =
    "http://localhost/ReportServer?%2f8_Build_Schedule%2frptBuildSchedule_8&BMP=All&rs:Command=Render"
    Else
    iReportViewer.Attributes("src") =
    "http://localhost/ReportServer?%2f8_Build_Schedule%2frptBuildSchedule_8&BMP='" + qs + "'&rs:Command=Render"
    End If
    iReportViewer.Attributes("width") = "100%"
    iReportViewer.Attributes("height") = "100%"
    Case Else
    ' your exception code
    End Select
    End Sub

    Shell of what you need for ReportViewer.aspx

    <%@ Page Language="vb" AutoEventWireup="false"
    Codebehind="ReportViewer.aspx.vb" Inherits="Robs.ReportViewer"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <HTML>
    <HEAD>
    <title>My Little Report Viewer - House or Horror</title>
    <meta name="vs_targetSchema"
    content="http://schemas.microsoft.com/intellisense/ie5">
    </HEAD>
    <body>
    <iframe runat="server" id="iReportViewer" style="BACKGROUND-COLOR:
    #990000" name="doc" frameBorder="no"
    width="100%" scrolling="auto" height="100%"></iframe>
    </body>
    </HTML>

    That's it...

    If you have AD for authentication, it'll take what you have (if SSRS
    configured). When I play at home, it puts an 'in my face' login (since I'm
    not running AD at home).

    Good Luck - Rob

    "Brendan Reynolds" wrote:

    > Thanks Rob. That looks promising. Unfortunately, my client-side scripting
    > skills are almost non-existent, and I'm having a hard time getting to grips
    > with this. Here's what I've done so far ...
    >
    > I have an ASP.NET page with a listbox that displays available reports and a
    > literal control for the iframe. Here's the relevant part of the page's HTML
    > ....
    >
    > <TR>
    > <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    > <asp:ListBox id="lstReports" runat="server" Width="100%"
    > Height="295px" AutoPostBack="True">
    > </asp:ListBox>
    > </TD>
    > <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    > <asp:Literal id="MyLiteral" runat="server">
    > <IFRAME id="MyIFrame"></IFRAME>
    > </asp:Literal>
    > </TD>
    > </TR>
    >
    > I've translated your code into C#, which is what my page is using, and put
    > it in the SelectedIndexChanged event procedure of my listbox ...
    >
    > private void lstReports_SelectedIndexChanged(object sender, System.EventArgs
    > e)
    > {
    > string popupScript = @"<script language='javascript'> "
    > + @"var newwin = window.open('ReportViewer.aspx?"
    > + @"id=myreportid&qs=myquerystring', 'ReportViewer', "
    > + @"'menubar=no, toolbar=no, resizable=no'); "
    > + @"newwin.window.moveTo(0,0); newwin.window.resizeTo"
    > + @"(screen.availWidth, screen.availHeight);"
    > + @"newwin.focus();<script>";
    > this.RegisterStartupScript("PopupScript", popupScript);
    > }
    >
    > My first question is - what do I need to change to load 'ReportViewer.aspx'
    > into the iframe?
    >
    > --
    > Brendan Reynolds (MVP)
    >
    >
    > "Rob" <> wrote in message
    > news:...
    > > hi brendan
    > >
    > > here's the solution I used (with a little bit of verbage to describe my
    > > scenario):
    > >
    > > 1. I've built an ASP.NET site (in addition to the regular SSRS interface
    > > for
    > > reports).
    > > 2. This addition site looks almost like the browser interface for this
    > > discussion group (ie. Treeview control on the left which groups 'like'
    > > reports and an iframe right half which brings up the report criteria form
    > > associated with the selected report, from the Treeview control on the
    > > left).
    > > 3. on 'View Report' click from the iframed report(s) criteria page, I do
    > > the
    > > following in ASP.NET:
    > >
    > > Dim popupScript As String = "<script
    > > language='javascript'>"
    > > & _
    > > "var newwin =
    > > window.open('ReportViewer.aspx?id=myreportid&qs=myquerystring',
    > > 'ReportViewer', 'menubar=no, toolbar=no, resizable=no');
    > > newwin.window.moveTo(0,0);
    > > newwin.window.resizeTo(screen.availWidth,screen.availHeight);
    > > newwin.focus();</script>"
    > > Page.RegisterStartupScript("PopupScript", popupScript)
    > >
    > > 4. This opens up ReportViewer.aspx which iframes the call to SSRS. The
    > > benefits of this is different report interfaces can be built on differents
    > > sites AND there are no issue with this approach as far as 'cross-site
    > > scripting'. For instance, my financial group has the own SSRS ASP.NET
    > > interface, as well as other groups... They ALL point in the pop-up to the
    > > SSRS site and get a previewed report (with report parameter fields if they
    > > want to make changes). Also, ReportViewer.aspx changes the iframe src
    > > value
    > > to what was passed to it from the reportcriteria .aspx.
    > >
    > > 5. Since the way the pop-up is created, there's no URL Address info and
    > > the
    > > ReportViewer.aspx masks the URL by replacing the title.
    > >
    > > Rob
    > >
    > > "Brendan Reynolds" wrote:
    > >
    > >>
    > >> I'm trying to integrate SQL Server Reporting Services reports into an
    > >> ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using direct
    > >> URL
    > >> addressing, but this exposes in the query string parameters that should
    > >> not
    > >> be exposed. Each user is associated with a school, and should see only
    > >> that
    > >> school's data. When the user logs in, I retrieve the SchoolID associated
    > >> with that user, and that SchoolID is used as a parameter in all stored
    > >> procedures to return only that school's data. It is this SchoolID that
    > >> needs
    > >> to be passed to the report. It doesn't matter if users see the SchoolID,
    > >> but
    > >> they must not be able to change the SchoolID, as they can if it forms
    > >> part
    > >> of the URL.
    > >>
    > >> From the documentation and from previous discussions in the SRS
    > >> newsgroup,
    > >> it appears that there are four possible solutions to this:
    > >>
    > >> 1) Wait for SRS 2005 which I understand is due in November.
    > >>
    > >> 2) Use the web service instead of URL access and write a lot of code to
    > >> provide functionality you get built-in when using URL access.
    > >>
    > >> 3) Use URL access through a form POST method, as suggested in SRS Books
    > >> Online
    > >> (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)
    > >>
    > >> 4) Require users to log in again when accessing reports, despite having
    > >> already logged in to the ASP.NET app. The report would prompt the user
    > >> for
    > >> user name and password, and pass these to the stored procedures instead
    > >> of
    > >> the SchoolID. This would avoid having to pass the SchoolID from the
    > >> ASP.NET
    > >> app to the reports, but I'm not sure that the users will accept the
    > >> inconvenience. (The passwords are of course stored in encrypted form, so
    > >> I'll need to add code to the reports to encrypt the password supplied by
    > >> the
    > >> user, but that's a separate issue.)
    > >>
    > >> My questions are:
    > >>
    > >> 1) Can anyone confirm that the next version really does solve this
    > >> problem?
    > >>
    > >> 2) Any pointers to resources on using the web service, particularly on
    > >> how
    > >> to control rendering and how to reproduce the functionality of the
    > >> toolbar
    > >> you get when using URL access, would be welcome. (I already know about
    > >> SRS
    > >> Books Online and the articles reachable from the MSDN SQL Server
    > >> Developer
    > >> Center).
    > >>
    > >> 3) Can I use the POST method as suggested in SRS Books Online from within
    > >> an
    > >> ASP.NET app? I thought an ASP.NET page could only post back to itself, is
    > >> that not so?
    > >>
    > >> Please don't feel that you have to have answers to all of these questions
    > >> before responding - suggestions or advice regarding any part of the
    > >> problem
    > >> would be most welcome.
    > >>
    > >> --
    > >> Brendan Reynolds
    > >>
    > >>
    > >>

    >
    >
    >
    =?Utf-8?B?Um9i?=, Aug 17, 2005
    #7
  8. Thanks Ernie. What about the encryption? The passwords are stored in
    encrypted form in the database, so I can't just let the report prompt for
    the password as an ordinary report parameter - it has to prompt for it, then
    encrypt it, then assign that encrypted value to the report parameter. I know
    how to do the encryption, what I don't know is how to 'intercept' the
    parameter?

    --
    Brendan Reynolds

    "Ernie Gutierrez" <> wrote in message
    news:...
    > As an aside, we happen to use an implementation in which users do in
    > fact have to authenticate a second time in order to request a report.
    > It has had but a minor impact on users in our experience, generating no
    > complaints. I wouldn't rule out that possibility for fear of user
    > backlash.
    >
    > Brendan Reynolds wrote:
    >> Thanks Rob. That looks promising. Unfortunately, my client-side scripting
    >> skills are almost non-existent, and I'm having a hard time getting to
    >> grips
    >> with this. Here's what I've done so far ...
    >>
    >> I have an ASP.NET page with a listbox that displays available reports and
    >> a
    >> literal control for the iframe. Here's the relevant part of the page's
    >> HTML
    >> ...
    >>
    >> <TR>
    >> <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    >> <asp:ListBox id="lstReports" runat="server" Width="100%"
    >> Height="295px" AutoPostBack="True">
    >> </asp:ListBox>
    >> </TD>
    >> <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    >> <asp:Literal id="MyLiteral" runat="server">
    >> <IFRAME id="MyIFrame"></IFRAME>
    >> </asp:Literal>
    >> </TD>
    >> </TR>
    >>
    >> I've translated your code into C#, which is what my page is using, and
    >> put
    >> it in the SelectedIndexChanged event procedure of my listbox ...
    >>
    >> private void lstReports_SelectedIndexChanged(object sender,
    >> System.EventArgs
    >> e)
    >> {
    >> string popupScript = @"<script language='javascript'> "
    >> + @"var newwin = window.open('ReportViewer.aspx?"
    >> + @"id=myreportid&qs=myquerystring', 'ReportViewer', "
    >> + @"'menubar=no, toolbar=no, resizable=no'); "
    >> + @"newwin.window.moveTo(0,0); newwin.window.resizeTo"
    >> + @"(screen.availWidth, screen.availHeight);"
    >> + @"newwin.focus();<script>";
    >> this.RegisterStartupScript("PopupScript", popupScript);
    >> }
    >>
    >> My first question is - what do I need to change to load
    >> 'ReportViewer.aspx'
    >> into the iframe?
    >>
    >> --
    >> Brendan Reynolds (MVP)
    >>
    >>
    >> "Rob" <> wrote in message
    >> news:...
    >> > hi brendan
    >> >
    >> > here's the solution I used (with a little bit of verbage to describe my
    >> > scenario):
    >> >
    >> > 1. I've built an ASP.NET site (in addition to the regular SSRS
    >> > interface
    >> > for
    >> > reports).
    >> > 2. This addition site looks almost like the browser interface for this
    >> > discussion group (ie. Treeview control on the left which groups 'like'
    >> > reports and an iframe right half which brings up the report criteria
    >> > form
    >> > associated with the selected report, from the Treeview control on the
    >> > left).
    >> > 3. on 'View Report' click from the iframed report(s) criteria page, I
    >> > do
    >> > the
    >> > following in ASP.NET:
    >> >
    >> > Dim popupScript As String = "<script
    >> > language='javascript'>"
    >> > & _
    >> > "var newwin =
    >> > window.open('ReportViewer.aspx?id=myreportid&qs=myquerystring',
    >> > 'ReportViewer', 'menubar=no, toolbar=no, resizable=no');
    >> > newwin.window.moveTo(0,0);
    >> > newwin.window.resizeTo(screen.availWidth,screen.availHeight);
    >> > newwin.focus();</script>"
    >> > Page.RegisterStartupScript("PopupScript", popupScript)
    >> >
    >> > 4. This opens up ReportViewer.aspx which iframes the call to SSRS. The
    >> > benefits of this is different report interfaces can be built on
    >> > differents
    >> > sites AND there are no issue with this approach as far as 'cross-site
    >> > scripting'. For instance, my financial group has the own SSRS ASP.NET
    >> > interface, as well as other groups... They ALL point in the pop-up to
    >> > the
    >> > SSRS site and get a previewed report (with report parameter fields if
    >> > they
    >> > want to make changes). Also, ReportViewer.aspx changes the iframe src
    >> > value
    >> > to what was passed to it from the reportcriteria .aspx.
    >> >
    >> > 5. Since the way the pop-up is created, there's no URL Address info and
    >> > the
    >> > ReportViewer.aspx masks the URL by replacing the title.
    >> >
    >> > Rob
    >> >
    >> > "Brendan Reynolds" wrote:
    >> >
    >> >>
    >> >> I'm trying to integrate SQL Server Reporting Services reports into an
    >> >> ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using
    >> >> direct
    >> >> URL
    >> >> addressing, but this exposes in the query string parameters that
    >> >> should
    >> >> not
    >> >> be exposed. Each user is associated with a school, and should see only
    >> >> that
    >> >> school's data. When the user logs in, I retrieve the SchoolID
    >> >> associated
    >> >> with that user, and that SchoolID is used as a parameter in all stored
    >> >> procedures to return only that school's data. It is this SchoolID that
    >> >> needs
    >> >> to be passed to the report. It doesn't matter if users see the
    >> >> SchoolID,
    >> >> but
    >> >> they must not be able to change the SchoolID, as they can if it forms
    >> >> part
    >> >> of the URL.
    >> >>
    >> >> From the documentation and from previous discussions in the SRS
    >> >> newsgroup,
    >> >> it appears that there are four possible solutions to this:
    >> >>
    >> >> 1) Wait for SRS 2005 which I understand is due in November.
    >> >>
    >> >> 2) Use the web service instead of URL access and write a lot of code
    >> >> to
    >> >> provide functionality you get built-in when using URL access.
    >> >>
    >> >> 3) Use URL access through a form POST method, as suggested in SRS
    >> >> Books
    >> >> Online
    >> >> (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)
    >> >>
    >> >> 4) Require users to log in again when accessing reports, despite
    >> >> having
    >> >> already logged in to the ASP.NET app. The report would prompt the user
    >> >> for
    >> >> user name and password, and pass these to the stored procedures
    >> >> instead
    >> >> of
    >> >> the SchoolID. This would avoid having to pass the SchoolID from the
    >> >> ASP.NET
    >> >> app to the reports, but I'm not sure that the users will accept the
    >> >> inconvenience. (The passwords are of course stored in encrypted form,
    >> >> so
    >> >> I'll need to add code to the reports to encrypt the password supplied
    >> >> by
    >> >> the
    >> >> user, but that's a separate issue.)
    >> >>
    >> >> My questions are:
    >> >>
    >> >> 1) Can anyone confirm that the next version really does solve this
    >> >> problem?
    >> >>
    >> >> 2) Any pointers to resources on using the web service, particularly on
    >> >> how
    >> >> to control rendering and how to reproduce the functionality of the
    >> >> toolbar
    >> >> you get when using URL access, would be welcome. (I already know about
    >> >> SRS
    >> >> Books Online and the articles reachable from the MSDN SQL Server
    >> >> Developer
    >> >> Center).
    >> >>
    >> >> 3) Can I use the POST method as suggested in SRS Books Online from
    >> >> within
    >> >> an
    >> >> ASP.NET app? I thought an ASP.NET page could only post back to itself,
    >> >> is
    >> >> that not so?
    >> >>
    >> >> Please don't feel that you have to have answers to all of these
    >> >> questions
    >> >> before responding - suggestions or advice regarding any part of the
    >> >> problem
    >> >> would be most welcome.
    >> >>
    >> >> --
    >> >> Brendan Reynolds
    >> >>
    >> >>
    >> >>

    >
    Brendan Reynolds, Aug 17, 2005
    #8
  9. Thanks Rob. That almost works. I can display a report in the IFrame without
    exposing the SchoolID to the user. Unfortunately, as soon as the user
    chooses to export to PDF or Excel, Internet Explorer opens a new window,
    with the SchoolID in the URL. Did I overlook something in your explanation
    that would have avoided this?

    --
    Brendan Reynolds (MVP)

    "Rob" <> wrote in message
    news:...
    > hi
    >
    > (hopefully, this sample code will make it thru to the forum)
    >
    > ReportViewer.codebehind (relevant part)...
    >
    > Private Sub ViewReport(ByVal key As String, ByVal reporttype As String)
    > Dim iReportViewer As HtmlControl =
    > CType(Me.FindControl("iReportViewer"), HtmlControl)
    > Dim rptid As String = Request.QueryString("id")
    > Dim qs As String = Request.QueryString("qs")
    > Select Case rptid
    > Case (this is my report number, ie. "id")
    > Case (whatever the report # is....)
    > Case 808 ' my Build Schedule Report
    > If qs = "All" Then ' they f'd up using All, can't be
    > quoted...
    > iReportViewer.Attributes("src") =
    > "http://localhost/ReportServer?%2f8_Build_Schedule%2frptBuildSchedule_8&BMP=All&rs:Command=Render"
    > Else
    > iReportViewer.Attributes("src") =
    > "http://localhost/ReportServer?%2f8_Build_Schedule%2frptBuildSchedule_8&BMP='"
    > + qs + "'&rs:Command=Render"
    > End If
    > iReportViewer.Attributes("width") = "100%"
    > iReportViewer.Attributes("height") = "100%"
    > Case Else
    > ' your exception code
    > End Select
    > End Sub
    >
    > Shell of what you need for ReportViewer.aspx
    >
    > <%@ Page Language="vb" AutoEventWireup="false"
    > Codebehind="ReportViewer.aspx.vb" Inherits="Robs.ReportViewer"%>
    > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    > <HTML>
    > <HEAD>
    > <title>My Little Report Viewer - House or Horror</title>
    > <meta name="vs_targetSchema"
    > content="http://schemas.microsoft.com/intellisense/ie5">
    > </HEAD>
    > <body>
    > <iframe runat="server" id="iReportViewer" style="BACKGROUND-COLOR:
    > #990000" name="doc" frameBorder="no"
    > width="100%" scrolling="auto" height="100%"></iframe>
    > </body>
    > </HTML>
    >
    > That's it...
    >
    > If you have AD for authentication, it'll take what you have (if SSRS
    > configured). When I play at home, it puts an 'in my face' login (since I'm
    > not running AD at home).
    >
    > Good Luck - Rob
    >
    > "Brendan Reynolds" wrote:
    >
    >> Thanks Rob. That looks promising. Unfortunately, my client-side scripting
    >> skills are almost non-existent, and I'm having a hard time getting to
    >> grips
    >> with this. Here's what I've done so far ...
    >>
    >> I have an ASP.NET page with a listbox that displays available reports and
    >> a
    >> literal control for the iframe. Here's the relevant part of the page's
    >> HTML
    >> ....
    >>
    >> <TR>
    >> <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    >> <asp:ListBox id="lstReports" runat="server" Width="100%"
    >> Height="295px" AutoPostBack="True">
    >> </asp:ListBox>
    >> </TD>
    >> <TD style="HEIGHT: 251px" vAlign="top" width="50%">
    >> <asp:Literal id="MyLiteral" runat="server">
    >> <IFRAME id="MyIFrame"></IFRAME>
    >> </asp:Literal>
    >> </TD>
    >> </TR>
    >>
    >> I've translated your code into C#, which is what my page is using, and
    >> put
    >> it in the SelectedIndexChanged event procedure of my listbox ...
    >>
    >> private void lstReports_SelectedIndexChanged(object sender,
    >> System.EventArgs
    >> e)
    >> {
    >> string popupScript = @"<script language='javascript'> "
    >> + @"var newwin = window.open('ReportViewer.aspx?"
    >> + @"id=myreportid&qs=myquerystring', 'ReportViewer', "
    >> + @"'menubar=no, toolbar=no, resizable=no'); "
    >> + @"newwin.window.moveTo(0,0); newwin.window.resizeTo"
    >> + @"(screen.availWidth, screen.availHeight);"
    >> + @"newwin.focus();<script>";
    >> this.RegisterStartupScript("PopupScript", popupScript);
    >> }
    >>
    >> My first question is - what do I need to change to load
    >> 'ReportViewer.aspx'
    >> into the iframe?
    >>
    >> --
    >> Brendan Reynolds (MVP)
    >>
    >>
    >> "Rob" <> wrote in message
    >> news:...
    >> > hi brendan
    >> >
    >> > here's the solution I used (with a little bit of verbage to describe my
    >> > scenario):
    >> >
    >> > 1. I've built an ASP.NET site (in addition to the regular SSRS
    >> > interface
    >> > for
    >> > reports).
    >> > 2. This addition site looks almost like the browser interface for this
    >> > discussion group (ie. Treeview control on the left which groups 'like'
    >> > reports and an iframe right half which brings up the report criteria
    >> > form
    >> > associated with the selected report, from the Treeview control on the
    >> > left).
    >> > 3. on 'View Report' click from the iframed report(s) criteria page, I
    >> > do
    >> > the
    >> > following in ASP.NET:
    >> >
    >> > Dim popupScript As String = "<script
    >> > language='javascript'>"
    >> > & _
    >> > "var newwin =
    >> > window.open('ReportViewer.aspx?id=myreportid&qs=myquerystring',
    >> > 'ReportViewer', 'menubar=no, toolbar=no, resizable=no');
    >> > newwin.window.moveTo(0,0);
    >> > newwin.window.resizeTo(screen.availWidth,screen.availHeight);
    >> > newwin.focus();</script>"
    >> > Page.RegisterStartupScript("PopupScript", popupScript)
    >> >
    >> > 4. This opens up ReportViewer.aspx which iframes the call to SSRS. The
    >> > benefits of this is different report interfaces can be built on
    >> > differents
    >> > sites AND there are no issue with this approach as far as 'cross-site
    >> > scripting'. For instance, my financial group has the own SSRS ASP.NET
    >> > interface, as well as other groups... They ALL point in the pop-up to
    >> > the
    >> > SSRS site and get a previewed report (with report parameter fields if
    >> > they
    >> > want to make changes). Also, ReportViewer.aspx changes the iframe src
    >> > value
    >> > to what was passed to it from the reportcriteria .aspx.
    >> >
    >> > 5. Since the way the pop-up is created, there's no URL Address info and
    >> > the
    >> > ReportViewer.aspx masks the URL by replacing the title.
    >> >
    >> > Rob
    >> >
    >> > "Brendan Reynolds" wrote:
    >> >
    >> >>
    >> >> I'm trying to integrate SQL Server Reporting Services reports into an
    >> >> ASP.NET app (SRS 2000, ASP.NET 1.1). I know how to do this using
    >> >> direct
    >> >> URL
    >> >> addressing, but this exposes in the query string parameters that
    >> >> should
    >> >> not
    >> >> be exposed. Each user is associated with a school, and should see only
    >> >> that
    >> >> school's data. When the user logs in, I retrieve the SchoolID
    >> >> associated
    >> >> with that user, and that SchoolID is used as a parameter in all stored
    >> >> procedures to return only that school's data. It is this SchoolID that
    >> >> needs
    >> >> to be passed to the report. It doesn't matter if users see the
    >> >> SchoolID,
    >> >> but
    >> >> they must not be able to change the SchoolID, as they can if it forms
    >> >> part
    >> >> of the URL.
    >> >>
    >> >> From the documentation and from previous discussions in the SRS
    >> >> newsgroup,
    >> >> it appears that there are four possible solutions to this:
    >> >>
    >> >> 1) Wait for SRS 2005 which I understand is due in November.
    >> >>
    >> >> 2) Use the web service instead of URL access and write a lot of code
    >> >> to
    >> >> provide functionality you get built-in when using URL access.
    >> >>
    >> >> 3) Use URL access through a form POST method, as suggested in SRS
    >> >> Books
    >> >> Online
    >> >> (http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_intro_35pi.asp)
    >> >>
    >> >> 4) Require users to log in again when accessing reports, despite
    >> >> having
    >> >> already logged in to the ASP.NET app. The report would prompt the user
    >> >> for
    >> >> user name and password, and pass these to the stored procedures
    >> >> instead
    >> >> of
    >> >> the SchoolID. This would avoid having to pass the SchoolID from the
    >> >> ASP.NET
    >> >> app to the reports, but I'm not sure that the users will accept the
    >> >> inconvenience. (The passwords are of course stored in encrypted form,
    >> >> so
    >> >> I'll need to add code to the reports to encrypt the password supplied
    >> >> by
    >> >> the
    >> >> user, but that's a separate issue.)
    >> >>
    >> >> My questions are:
    >> >>
    >> >> 1) Can anyone confirm that the next version really does solve this
    >> >> problem?
    >> >>
    >> >> 2) Any pointers to resources on using the web service, particularly on
    >> >> how
    >> >> to control rendering and how to reproduce the functionality of the
    >> >> toolbar
    >> >> you get when using URL access, would be welcome. (I already know about
    >> >> SRS
    >> >> Books Online and the articles reachable from the MSDN SQL Server
    >> >> Developer
    >> >> Center).
    >> >>
    >> >> 3) Can I use the POST method as suggested in SRS Books Online from
    >> >> within
    >> >> an
    >> >> ASP.NET app? I thought an ASP.NET page could only post back to itself,
    >> >> is
    >> >> that not so?
    >> >>
    >> >> Please don't feel that you have to have answers to all of these
    >> >> questions
    >> >> before responding - suggestions or advice regarding any part of the
    >> >> problem
    >> >> would be most welcome.
    >> >>
    >> >> --
    >> >> Brendan Reynolds
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
    Brendan Reynolds, Aug 18, 2005
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark
    Replies:
    1
    Views:
    437
    Natty Gur
    Nov 25, 2003
  2. Patrick.O.Ige
    Replies:
    3
    Views:
    5,435
    Patrick.O.Ige
    Oct 31, 2005
  3. RSH
    Replies:
    0
    Views:
    2,326
  4. Blasting Cap
    Replies:
    3
    Views:
    801
    Blasting Cap
    Jul 30, 2007
  5. SP
    Replies:
    0
    Views:
    740
Loading...

Share This Page