Report Viewer for Saved Data and On-Demand Subreports

[mrdjsm1th]

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.

 

[MJRBIM]

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...?

 

[mrdjsm1th]

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).

 

[MJRBIM]

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...?

 

[mrdjsm1th]

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

 

[MJRBIM]

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.

 

[chelseatech]

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

http://www.chelseatech.co.nz/pubs.htm

 

[mrdjsm1th]

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.