<%@page contentType="text/html"%> <%@page pageEncoding="UTF-8"%> <%@ page import="com.crystaldecisions.sdk.occa.report.application.*"%> <%@ page import="com.crystaldecisions.sdk.occa.report.data.*"%> <%@ page import="com.crystaldecisions.sdk.occa.report.document.*"%> <%@ page import="com.crystaldecisions.sdk.occa.report.definition.*"%> <%@ page import="com.crystaldecisions.sdk.occa.report.lib.*" %> <% //Report can be opened from the relative location specified in the CRConfig.xml, or the report location //tag can be removed to open the reports as Java resources or using an absolute path (absolute path not recommended //for Web applications). final String REPORT_NAME = "C:\\Program Files\\Business Objects\\Tomcat55\\webapps\\CR4E\\crystalProc.rpt"; final String DBUSERNAME = "sa"; final String DBPASSWORD = "Password@123"; final String CONNECTION_STRING = "jdbc:sqlserver://172.16.10.98:1433;DatabaseName=AdventureWorks;user=sa;password=Password@123"; final String TRUSTEDCON = "false"; final String PREQESERVERNAME = "jdbc:microsoft:sqlserver://172.16.10.98:1433"; final String SERVERTYPE = "JDBC (JNDI)"; final String DATABASE_DLL = "crdb_jdbc.dll"; final String DATABASE = "AdventureWorks"; final String DBCLASSNAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; final String USEJDBC = "true"; final String DATABASE_NAME = "AdventureWorks"; final String SERVERNAME = "jdbc:sqlserver://172.16.10.98:1433"; final String CONNECTIONURL = "jdbc:sqlserver://172.16.10.98:1433"; final String SERVER = "172.16.10.98:1433"; %> <% try { //Open report. ReportClientDocument boReportClientDocument = new ReportClientDocument(); boReportClientDocument.open(REPORT_NAME, 0); // Set DB Username and Password //switch_tables(boReportClientDocument.getDatabaseController()); //Perform the same operation against all tables in the subreport as well. boReportClientDocument.getDatabaseController().logon(DBUSERNAME, DBPASSWORD); // Create the two connectioninfo objects to use IConnectionInfo oldConnectionInfo = new ConnectionInfo(); IConnectionInfo newConnectionInfo = new ConnectionInfo(); // Assign the old Connection info to the reports current info DatabaseController dbController = boReportClientDocument.getDatabaseController(); oldConnectionInfo = dbController.getConnectionInfos(null).getConnectionInfo(0); // If this connection needed parameters, we would use this field. Fields oFields = new Fields(); //NUMBER VALUE PARAMETER. Integer numberValue = new Integer("148"); Integer numberValue1 = new Integer("148"); //STRING VALUE PARAMETER. //String stringValue = "String parameter value."; //Set all of the parameter values using the utility function. setDiscreteParameterValue(oFields, "@EmployeeID", "", numberValue); setDiscreteParameterValue(oFields, "@EmployeeID", "test", numberValue1); // Create a new propertybag for the new location PropertyBag boPropertyBag1 = new PropertyBag(); // Set new table logon properties boPropertyBag1.put("JDBC Connection String", CONNECTION_STRING); boPropertyBag1.put("Trusted_Connection", TRUSTEDCON); boPropertyBag1.put("PreQEServerName", PREQESERVERNAME); boPropertyBag1.put("Server Type", SERVERTYPE); boPropertyBag1.put("Database DLL", DATABASE_DLL); boPropertyBag1.put("Database", DATABASE); boPropertyBag1.put("Database Class Name", DBCLASSNAME); boPropertyBag1.put("Use JDBC", USEJDBC); boPropertyBag1.put("Database Name", DATABASE_NAME); boPropertyBag1.put("Server Name", SERVERNAME); boPropertyBag1.put("Connection URL", CONNECTIONURL); boPropertyBag1.put("Server", SERVER); // Assign the properties to the connection info newConnectionInfo.setAttributes(boPropertyBag1); // Set the DB Username and Pwd newConnectionInfo.setUserName(DBUSERNAME); newConnectionInfo.setPassword(DBPASSWORD); // The Kind of connectionInfos is SQL newConnectionInfo.setKind(ConnectionInfoKind.SQL); // set the parameters to replace. // The 4 options are: // _doNotVerifyDB // _ignoreCurrentTableQualifiers // _mapFieldByRowsetPosition // _useDefault int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB; // Now replace the connections dbController.replaceConnection(oldConnectionInfo, newConnectionInfo, oFields, replaceParams); IStrings subreportNames = boReportClientDocument.getSubreportController().getSubreportNames(); //Set the datasource for all the subreports. for (int i = 0; i < subreportNames.size(); i++ ) { ISubreportClientDocument subreportClientDoc = boReportClientDocument.getSubreportController().getSubreport(subreportNames.getString(i)); //Switch tables for each subreport in the report using the same connection information. See utility //method below. switch_tables(subreportClientDoc.getDatabaseController()); } //Store the report source in session, will be used by the CrystalReportViewer. session.setAttribute("reportSource", boReportClientDocument.getReportSource()); session.setAttribute("parameterFields", oFields); //Launch CrystalReportViewer page that contains the report viewer. response.sendRedirect("CrystalReportViewer.jsp"); } catch(ReportSDKException ex) { out.println(ex); } catch(Exception ex) { out.println(ex); } %> <%! private static void switch_tables(DatabaseController databaseController) throws ReportSDKException { //Declare the new connection properties that report's datasource will be switched to. //NOTE: These are specific to using JDBC against a particular MS SQL Server database. Be sure to use the //DisplayConnectionInfo sample to determine what your own connection properties need to be set to. //final String REPORT_NAME = "C:\\Program Files\\Business Objects\\Tomcat55\\webapps\\CR4E\\jdbcReport.rpt"; final String DBUSERNAME = "sa"; final String DBPASSWORD = "Password@123"; final String TABLE_NAME_QUALIFIER="AdventureWorks.dbo."; final String CONNECTION_STRING = "jdbc:sqlserver://172.16.10.98:1433;DatabaseName=AdventureWorks;user=sa;password=Password@123"; final String TRUSTEDCON = "false"; final String PREQESERVERNAME = "jdbc:microsoft:sqlserver://172.16.10.98:1433"; final String SERVERTYPE = "JDBC (JNDI)"; final String DATABASE_DLL = "crdb_jdbc.dll"; final String DATABASE = "AdventureWorks"; final String DBCLASSNAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; final String USEJDBC = "true"; final String DATABASE_NAME = "AdventureWorks"; final String SERVERNAME = "jdbc:sqlserver://172.16.10.98:1433"; final String CONNECTIONURL = "jdbc:sqlserver://172.16.10.98:1433"; final String SERVER = "172.16.10.98:1433"; //Obtain collection of tables from this database controller. Tables tables = databaseController.getDatabase().getTables(); //Set the datasource for all main report tables. for (int i = 0; i < tables.size(); i++) { ITable table = tables.getTable(i); //Keep existing name and alias. table.setName(table.getName()); table.setAlias(table.getAlias()); //Change properties that are different from the original datasource. table.setQualifiedName(TABLE_NAME_QUALIFIER + table.getName()); //Change connection information properties. IConnectionInfo connectionInfo = table.getConnectionInfo(); //Set new table connection property attributes. PropertyBag boPropertyBag = new PropertyBag(); // Set new table logon properties boPropertyBag.put("JDBC Connection String", CONNECTION_STRING); boPropertyBag.put("Trusted_Connection", TRUSTEDCON); boPropertyBag.put("PreQEServerName", PREQESERVERNAME); boPropertyBag.put("Server Type", SERVERTYPE); boPropertyBag.put("Database DLL", DATABASE_DLL); boPropertyBag.put("Database", DATABASE); boPropertyBag.put("Database Class Name", DBCLASSNAME); boPropertyBag.put("Use JDBC", USEJDBC); boPropertyBag.put("Database Name", DATABASE_NAME); boPropertyBag.put("Server Name", SERVERNAME); boPropertyBag.put("Connection URL", CONNECTIONURL); boPropertyBag.put("Server", SERVER); connectionInfo.setAttributes(boPropertyBag); //Set database username and password. //NOTE: Even if these the username and password properties don't change when switching databases, the //database password is *not* saved in the report and must be set at runtime if the database is secured. connectionInfo.setUserName(DBUSERNAME); connectionInfo.setPassword(DBPASSWORD); connectionInfo.setKind(ConnectionInfoKind.SQL); table.setConnectionInfo(connectionInfo); //Update old table in the report with the new table. databaseController.setTableLocation(table, tables.getTable(i)); } } %> <%! /* * Utility function to set values for the discrete parameters in the report. The report parameter value is set * and added to the Fields collection, which can then be passed to the viewer so that the user is not prompted * for parameter values. */ private void setDiscreteParameterValue(Fields oFields, String paramName, String reportName, Object value) { //Create a ParameterField object for each field that you wish to set. ParameterField oParameterField = new ParameterField(); //You must set the report name. //Set the report name to an empty string if your report does not contain a //subreport; otherwise, the report name will be the name of the subreport oParameterField.setReportName(reportName); //Create a Values object and a ParameterFieldDiscreteValue object for each //object for each parameter field you wish to set. //If a ranged value is being set, a ParameterFieldRangeValue object should //be used instead of the discrete value object. Values oValues = new Values(); ParameterFieldDiscreteValue oParameterFieldDiscreteValue = new ParameterFieldDiscreteValue(); //Set the name of the parameter. This must match the name of the parameter as defined in the //report. oParameterField.setName(paramName); oParameterFieldDiscreteValue.setValue(value); //Add the parameter field values to the Values collection object. oValues.add(oParameterFieldDiscreteValue); //Set the current Values collection for each parameter field. oParameterField.setCurrentValues(oValues); //Add parameter field to the Fields collection. This object is then passed to the //viewer as the collection of parameter fields values set. oFields.add(oParameterField); } %>