Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Report Viewer for Saved Data and On-Demand Subreports

Report Viewer for Saved Data and On-Demand Subreports

Report Viewer for Saved Data and On-Demand Subreports

We are using Crystal Reports XI with Microsoft SQL Server 2005 using SQL Server Authentication.

We have reviewed some of the Crystal Report viewers on http://www.kenhamady.com/bookmarks.html and would like to select one that allows users to drill-down on data that is saved in a report in addition to launching on-demand subreports within the same report.  The on-demand subreports display sensitive information which is controlled by the user credentials (SQL Server 2005 database with SQL Server Authentication), so we want the user to be prompted for credentials when the on-demand subreport is used.

A common feature among some of the viewers allows a database password to be saved.  We do not want this feature because each user's credentials control the information they can view within the subreports.

Is there a Crystal Reports viewer that allows users to drill-down on saved data and forces them to enter their SQL Server Authentication credentials when launching on-demand subreports?

Thanks in advance for any suggestions you may have.

RE: Report Viewer for Saved Data and On-Demand Subreports

So you want the Main-Report to run under Login-A (which is common to everyone) - but have the On-Demand Sub-Report run under Login-B (which is unique per person) to limit access on the details...?

Is that a correct summary..?

How many users are you serving these reports to currently..?

Do you just have them on a network shared-drive...?

RE: Report Viewer for Saved Data and On-Demand Subreports

Thanks for the response.

The report is currently distributed to 10 people via email each week.  The report file contains saved data.

The users currently drill-down on several summary groups within the saved data of the main report.  When they get to the last level of drill-down detail, they can click the on-demand subreport links to query the database for additional information.  This is when we want the database credentials to be entered, so the user can view the information allowed by their credentials.

One of the key benefits with this report (and its saved data) is the users don't have to enter any parameters to view and interact with their information.  The on-demand subreport parameters are derived from the main report, and the main report parameters are defined by our report scheduling software (Crystal Delivery).

RE: Report Viewer for Saved Data and On-Demand Subreports

When you e-mail the RPT-file with "saved data", are the sub-reports already populated with data (but hidden from view) - or are the sub-reports EMPTY so that they need to make a NEW query to the database...?

RE: Report Viewer for Saved Data and On-Demand Subreports

The subreports are on-demand, so they are empty.

RE: Report Viewer for Saved Data and On-Demand Subreports

I think you should be able to do this with ANY 3rd-Party Crystal Report viewer if you use a different ODBC connection to the database for the sub-report.

What is happening now, is that because you have already connected via ODBC "named x" to the MS-SQL DB for the Main-Report, those credentials are being re-used when the sub-report uses ODBC "named x" to access the MS-SQL DB.

If the sub-report uses a different ODBC connection to the same database (eg. "named y") then the end-user should be prompted.

I haven't tested this, but it seems like it should work.

RE: Report Viewer for Saved Data and On-Demand Subreports

MJRBIM is correct in what they say above.  Our cView report viewer will save the database passwords encrypted in a local file.  So each user can have their own passwords saved there.

The viewer will display the report with saved data when the rpt file is opened, and then when the on demand subreport is clicked, it will process the report.  I'd recommend a different ODBC connection in the subreport if you want different credentials for the subreport.

Editor and Publisher of Crystal Clear

RE: Report Viewer for Saved Data and On-Demand Subreports

Thanks MJRBIM and chelseatech for your suggestions.

MJRBIM - I tried your suggestion, but unfortunately it just added another data source that required credentials.

chelseatech - I evaluated cView, but it did not prompt for a password.  It generated an error message when I launched the subreport ("Failed to open the connection.") and required me to go to File -> Settings -> Database Connection to enter credentials and save them.  Although the subreport worked with saved credentials, I don't want to save the credentials.

I discovered that Crystal Corral by Groff Automation does exactly what I want.  It allows the user to interact with the saved data in the main report and prompts them for a password when the on-demand sub-reports are accessed.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close