cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to Execute a report using Crystal Reports Viewer

Former Member
0 Kudos

Hi,

A report has been designed using Crystal Reports 2008.

The designed report is published on RAS and uses DSN to an SQLServer to execute. The executed report output file (.rpt)

is stored at a location on the disk.

I need to display the same report using Crystal Reports viewer (deployed as a WAR file on JBoss). However, when a user changes the report parameters and applies them, the report fails with the error

'JRCCommunicationAdapter - detected an exception: Error finding JNDI name (EKT_UAT)'

My questions are:

1. Can a report, designed to use DSN, be re-executed using JDBC parameters?

2. If so, how? How do I get round the JNDI name problem.

cheers,

Gaurav

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Spot on Ted,

I have changed the DB parameters prior to the refresh so that it should take in the new params for connectivity.

The method is pasted below. Please let me know if I am missing anything in it.

In spite of this it tries to locate the property which uses the DSN name (EKT_UAT). What am I doing wrong?

your help is much appreciated.

<P STYLE="margin-bottom: 0in"> public static void

changeDataSource(ReportClientDocument clientDoc,</P>

<P STYLE="margin-bottom: 0in"> String reportName,

String tableName,</P>

<P STYLE="margin-bottom: 0in"> String username, String

password, String connectionURL,</P>

<P STYLE="margin-bottom: 0in"> String

driverName,String jndiName) throws ReportSDKException {</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> PropertyBag propertyBag = null;</P>

<P STYLE="margin-bottom: 0in"> IConnectionInfo connectionInfo

= null;</P>

<P STYLE="margin-bottom: 0in"> ITable origTable = null;</P>

<P STYLE="margin-bottom: 0in"> ITable newTable = null;</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Declare variables to hold

ConnectionInfo values.</P>

<P STYLE="margin-bottom: 0in"> // Below is the list of values

required to switch to use a JDBC/JNDI</P>

<P STYLE="margin-bottom: 0in"> // connection</P>

<P STYLE="margin-bottom: 0in"> String TRUSTED_CONNECTION =

&quot;false&quot;;</P>

<P STYLE="margin-bottom: 0in"> String SERVER_TYPE = &quot;JDBC

(JNDI)&quot;;</P>

<P STYLE="margin-bottom: 0in"> String USE_JDBC = &quot;true&quot;;</P>

<P STYLE="margin-bottom: 0in"> String DATABASE_DLL =

&quot;crdb_jdbc.dll&quot;;</P>

<P STYLE="margin-bottom: 0in"> String JNDI_DATASOURCE_NAME =

jndiName;</P>

<P STYLE="margin-bottom: 0in"> String CONNECTION_URL =

connectionURL;</P>

<P STYLE="margin-bottom: 0in"> String DATABASE_CLASS_NAME =

driverName;</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // The next few parameters are

optional parameters which you may want to</P>

<P STYLE="margin-bottom: 0in"> // uncomment</P>

<P STYLE="margin-bottom: 0in"> // You may wish to adjust the

arguments of the method to pass these</P>

<P STYLE="margin-bottom: 0in"> // values in if necessary</P>

<P STYLE="margin-bottom: 0in"> // String TABLE_NAME_QUALIFIER

= &quot;new_table_name&quot;;</P>

<P STYLE="margin-bottom: 0in"> // String SERVER_NAME =

&quot;new_server_name&quot;;</P>

<P STYLE="margin-bottom: 0in"> // String CONNECTION_STRING =

&quot;new_connection_string&quot;;</P>

<P STYLE="margin-bottom: 0in"> // String DATABASE_NAME =

&quot;new_database_name&quot;;</P>

<P STYLE="margin-bottom: 0in"> // String URI = &quot;new_URI&quot;;</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Declare variables to hold

database User Name and Password values</P>

<P STYLE="margin-bottom: 0in"> String DB_USER_NAME = username;</P>

<P STYLE="margin-bottom: 0in"> String DB_PASSWORD = password;</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Obtain collection of tables

from this database controller</P>

<P STYLE="margin-bottom: 0in"> if (reportName == null ||

reportName.equals(&quot;&quot;)) {</P>

<P STYLE="margin-bottom: 0in"> Tables tables =

clientDoc.getDatabaseController().getDatabase().getTables();</P>

<P STYLE="margin-bottom: 0in"> for(int i = 0;i &lt;

tables.size();i++){</P>

<P STYLE="margin-bottom: 0in"> origTable =

tables.getTable(i);</P>

<P STYLE="margin-bottom: 0in"> if (tableName == null

origTable.getName().equals(tableName)) {</P>

<P STYLE="margin-bottom: 0in"> newTable =

(ITable)origTable.clone(true);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // We set the Fully

qualified name to the Table Alias to keep the</P>

<P STYLE="margin-bottom: 0in"> // method generic</P>

<P STYLE="margin-bottom: 0in"> // This workflow

may not work in all scenarios and should likely be</P>

<P STYLE="margin-bottom: 0in"> // customized to

work</P>

<P STYLE="margin-bottom: 0in"> // in the

developer's specific situation. The end result of this</P>

<P STYLE="margin-bottom: 0in"> // statement will

be to strip</P>

<P STYLE="margin-bottom: 0in"> // the existing

table of it's db specific identifiers. For example</P>

<P STYLE="margin-bottom: 0in"> //

Xtreme.dbo.Customer becomes just Customer</P>

<P STYLE="margin-bottom: 0in">

newTable.setQualifiedName(origTable.getAlias());</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Change

properties that are different from the original datasource</P>

<P STYLE="margin-bottom: 0in"> // For example, if

the table name has changed you will be required</P>

<P STYLE="margin-bottom: 0in"> // to change it

during this routine</P>

<P STYLE="margin-bottom: 0in"> //

table.setQualifiedName(TABLE_NAME_QUALIFIER);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Change

connection information properties</P>

<P STYLE="margin-bottom: 0in"> connectionInfo =

newTable.getConnectionInfo();</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Set new table

connection property attributes</P>

<P STYLE="margin-bottom: 0in"> propertyBag = new

PropertyBag();</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Overwrite any

existing properties with updated values</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Trusted_Connection&quot;, TRUSTED_CONNECTION);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Server Type&quot;, SERVER_TYPE);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Use JDBC&quot;, USE_JDBC);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Database DLL&quot;,DATABASE_DLL );</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;JNDI Datasource Name&quot;,JNDI_DATASOURCE_NAME

);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Connection URL&quot;, CONNECTION_URL);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Database Class Name&quot;,

DATABASE_CLASS_NAME);</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;Server Name&quot;, SERVER_NAME); //Optional

property</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;Connection String&quot;, CONNECTION_STRING);

//Optional property</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;Database Name&quot;, DATABASE_NAME); //Optional

property</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;URI&quot;, URI); //Optional property</P>

<P STYLE="margin-bottom: 0in">

connectionInfo.setAttributes(propertyBag);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Set database

username and password</P>

<P STYLE="margin-bottom: 0in"> // NOTE: Even if

the username and password properties do not change</P>

<P STYLE="margin-bottom: 0in"> // when switching

databases, the</P>

<P STYLE="margin-bottom: 0in"> // database

password is not saved in the report and must be set at</P>

<P STYLE="margin-bottom: 0in"> // runtime if the

database is secured.</P>

<P STYLE="margin-bottom: 0in">

connectionInfo.setUserName(DB_USER_NAME);</P>

<P STYLE="margin-bottom: 0in">

connectionInfo.setPassword(DB_PASSWORD);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Update the table

information</P>

<P STYLE="margin-bottom: 0in">

clientDoc.getDatabaseController().setTableLocation(origTable,

newTable);</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> // Next loop through all the

subreports and pass in the same</P>

<P STYLE="margin-bottom: 0in"> // information. You may

consider</P>

<P STYLE="margin-bottom: 0in"> // creating a separate method

which accepts</P>

<P STYLE="margin-bottom: 0in"> if (reportName == null

!(reportName.equals(&quot;&quot;))) {</P>

<P STYLE="margin-bottom: 0in"> IStrings subNames =

clientDoc.getSubreportController().getSubreportNames();</P>

<P STYLE="margin-bottom: 0in"> for (int

subNum=0;subNum&lt;subNames.size();subNum++) {</P>

<P STYLE="margin-bottom: 0in"> Tables tables =

clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().getDatabase().getTables();</P>

<P STYLE="margin-bottom: 0in"> for(int i = 0;i &lt;

tables.size();i++){</P>

<P STYLE="margin-bottom: 0in"> origTable =

tables.getTable(i);</P>

<P STYLE="margin-bottom: 0in"> if (tableName ==

null || origTable.getName().equals(tableName)) {</P>

<P STYLE="margin-bottom: 0in"> newTable =

(ITable)origTable.clone(true);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // We set the

Fully qualified name to the Table Alias to keep</P>

<P STYLE="margin-bottom: 0in"> // the method

generic</P>

<P STYLE="margin-bottom: 0in"> // This

workflow may not work in all scenarios and should likely</P>

<P STYLE="margin-bottom: 0in"> // be

customized to work</P>

<P STYLE="margin-bottom: 0in"> // in the

developer's specific situation. The end result of this</P>

<P STYLE="margin-bottom: 0in"> // statement

will be to strip</P>

<P STYLE="margin-bottom: 0in"> // the existing

table of it's db specific identifiers. For</P>

<P STYLE="margin-bottom: 0in"> // example

Xtreme.dbo.Customer becomes just Customer</P>

<P STYLE="margin-bottom: 0in">

newTable.setQualifiedName(origTable.getAlias());</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Change

properties that are different from the original</P>

<P STYLE="margin-bottom: 0in"> // datasource</P>

<P STYLE="margin-bottom: 0in"> //

table.setQualifiedName(TABLE_NAME_QUALIFIER);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Change

connection information properties</P>

<P STYLE="margin-bottom: 0in"> connectionInfo

= newTable.getConnectionInfo();</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Set new

table connection property attributes</P>

<P STYLE="margin-bottom: 0in"> propertyBag =

new PropertyBag();</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Overwrite

any existing properties with updated values</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Trusted_Connection&quot;, TRUSTED_CONNECTION);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Server Type&quot;, SERVER_TYPE);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Use JDBC&quot;, USE_JDBC);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Database DLL&quot;,DATABASE_DLL );</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;JNDI Datasource Name&quot;,JNDI_DATASOURCE_NAME

);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Connection URL&quot;, CONNECTION_URL);</P>

<P STYLE="margin-bottom: 0in">

propertyBag.put(&quot;Database Class Name&quot;,

DATABASE_CLASS_NAME);</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;Server Name&quot;, SERVER_NAME); //Optional

property</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;Connection String&quot;, CONNECTION_STRING);

//Optional property</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;Database Name&quot;, DATABASE_NAME); //Optional

property</P>

<P STYLE="margin-bottom: 0in"> //

propertyBag.put(&quot;URI&quot;, URI); //Optional property</P>

<P STYLE="margin-bottom: 0in">

connectionInfo.setAttributes(propertyBag);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Set database

username and password</P>

<P STYLE="margin-bottom: 0in"> // NOTE: Even

if the username and password properties do not</P>

<P STYLE="margin-bottom: 0in"> // change when

switching databases, the</P>

<P STYLE="margin-bottom: 0in"> // database

password is not saved in the report and must be</P>

<P STYLE="margin-bottom: 0in"> // set at

runtime if the database is secured.</P>

<P STYLE="margin-bottom: 0in">

connectionInfo.setUserName(DB_USER_NAME);</P>

<P STYLE="margin-bottom: 0in">

connectionInfo.setPassword(DB_PASSWORD);</P>

<P STYLE="margin-bottom: 0in"><BR>

</P>

<P STYLE="margin-bottom: 0in"> // Update the

table information</P>

<P STYLE="margin-bottom: 0in">

clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().setTableLocation(origTable,

newTable);</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> }</P>

<P STYLE="margin-bottom: 0in"> }</P>

Edited by: Gauravcj on Sep 13, 2010 8:39 AM

ted_ueda
Active Contributor
0 Kudos

So you want the report to refresh from the database?

If so, the best way is to design a report that connects to the database directly, and use that successful report as 'template' to specify the parameters for the report you want to change. There's info missing from your parameters.

Sincerely,

Ted Ueda

Former Member
0 Kudos

Thats brilliant.

I assumed that when you said

"If so, the best way is to design a report that connects to the database directly"

it meant, that I HAD to use a JDBC datasource and it worked.

In terms of parameters, when I view the report from InfoViewApp, a drop down appears but from the WebViewer, its only a text box. (The report designer did something to make the dropdown appear in RAS)

How do I get the dropdown of possible values in the webViewer?

cheers,

G

ted_ueda
Active Contributor
0 Kudos

I assumed that when you said

"If so, the best way is to design a report that connects to the database directly"

it meant, that I HAD to use a JDBC datasource and it worked.

To clarify, what I mean is that:

Because of all the different connectivities Crystal supports - you'll note that the Supported Platforms are very specific as to RDBMs vendor, version and driver - beyond the "Generic" ones, the connectivity parameters are difficult to get right.

So if someone wants to get a complete list of connection properties, it's best to let the CR Designer handle it. The people who wrote the Designer wrote code to figure out all the connectivity details, and encapsulate it in a finite set of connection parameters. Those are the values you see in the Designer. It's neither obvious or straightforward, since the supported connectivities aren't just "Generic JDBC/ODBC/etc".

So you'd design a report against the RDBMs you want. Then you'd write code to read the connection property strings back. You'd use that as a 'template', when you want to change from one connectivity to another.

This doesn't even get into the bit of complexity that you might encounter if two different drivers to the same RDBMs present data in slightly incompatible data types. Rare, but it happens. In that case, you have to remap db fields.

Missing parameters - set the parameters for the report, get the report source, and pass it to the viewer class (CrystalReportViewer). The Viewer won't ask for parameters already set, it'll only ask for parameters not set.

A few unintuitive things can case missing parameters to appear. An infrequent but common case is where you have subreport parameters linked to main report db fields, and changing database connection breaks that link. Again, passing the report to the viewer after making your changes should help identify such cases.

Sincerely,

Ted Ueda

Answers (1)

Answers (1)

ted_ueda
Active Contributor
0 Kudos

The change in parameters is triggering a database refresh attempt.

So the parameters aren't those used with saved data.

JNDI error comes up since when the Java CR engine sees a non-Java-based-data-connectivity, it tries to find a JNDI data source with the same name.

I'm assuming you want to use saved data filtering parameters.

Sincerely,

Ted Ueda