Crystal reports: Access 2 database and use fields in 1 report

Decide which would be the main database where most of the fields are present. From the main database create a link to the table in the 2nd database. Create a query using the tables from both the database and then connect the query to Crystal Reports and you should get the result you are looking for.

The method preferred in Crystal is to use one database for the main report and use the other database will be used for the subreport.

mlmcc

Personally, I prefer the method simpsol recommended, because it's efficient and lets you pull all of the data into a single recordset.  mlmcc's correct, though, in that a pure Crystal Reports solution would be to place the second database in a subreport.  You can technically add them both in the main report, but there will be some significant design limitations.

~Kurt

Okay I was able to connect by writting a command. Now the issue is that I am using a parameter for my date range ( Begin Date and End Date).  So in my command SQL I have the normal date range that causes the paramter to pull different date ranges.  How can write in my Command to follow the parameter? I am pulling the field name "Completion Details"

By the way if I take out the Oracle command sql date range it will pull all records.

Any ideas on how to write this?
Here is my code.

SQL Query from oracle:

SELECT *
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
AND (CAB_APPROVED = 'Yes-National')


Select expert / Record/ Query:
{Maintenance.Scheduled Start Date/Time} in {?BEGIN DATE} to {?END DATE} and
{Maintenance.Status} in ["Aborted", "Cancelled", "Closed", "Completed", "Work In Progress"] and
{Maintenance.CAB Approved} = "Yes-National"

Crystal Parameter Name of date range:
{?BEGIN DATE} to {?END DATE}

Select allOpen in new window

When you use a command you don't use the same parameters.  There is the ability as you create or edit the command to add parameters.  If you add then there then what you wrote should be correct.

mlmcc

So do I need to create a new parameter on the right side 'Parameter list"? and delete the old Begin Date and End Date?

I am just not sure what I need to do to get both date ranges the same?

What if removed the date from the command and create a formula named "Completion Code". Then  add
If ISNULL({Command.COMPLETION_DETAIL}) then "No Comments Provided" Else {Command.COMPLETION_DETAIL}

From that how would I add the BEGIN DATE and END DATE to this code? Is this possible?

Where did you add the parameters you have?
If they are normal Crystal parameters created through the field explorer then yes you can delete thaose and use the COMMAND parameter create to add new ones.

If you added them through the COMMAND creation screen then you should be able to use them in the command by selecting them.

mlmcc

Nevermind.. I learned that this isn't the correct option. Will join forms in oracle.

Nevermind.. I learned that this isn't the correct option. Will join forms in oracle.

Okay I will give your last comment a try too

Okay so I am not done here and have been working on what you stated.  
I deleted the old date parameters and create new ones in the command parameter list.  I choose "Date" as type and added the command below.  I am getting an error message:  Literal does not match format string.

SELECT *
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
WHERE  to_date("RP_DAILY_MAINTENANCE_REPORT"."SCHEDULED_START_DATE_TIME",'YYYY/MM/DD')>={?Start} AND to_date("RP_DAILY_MAINTENANCE_REPORT"."SCHEDULED_START_DATE_TIME",'YYYY/MM/DD')<{?End}


Any ideas?

Why are you converting the fields to strings?

SELECT *
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
WHERE  "RP_DAILY_MAINTENANCE_REPORT"."SCHEDULED_START_DATE_TIME" >= {?Start} AND "RP_DAILY_MAINTENANCE_REPORT"."SCHEDULED_START_DATE_TIME" < {?End}

mlmcc

Great! Well that worked but now the issue is that the I need to join the other db in my command. How would I join these two?  

Here is the other SQL:

SELECT *
FROM ARADMIN.MAINTENANCEWHERE  "MAINTENANCE"."SCHEDULED_START_DATE_TIME" >= {?Start} AND "MAINTENANCE"."SCHEDULED_START_DATE_TIME" < {?End}

Are the fields the same or are you needing to link records between the 2?

Is there a common field?

mlmcc

There are some fields that are the same but for my purpose, I am just trying to be able to use all of them if I need to.

I am thinking a Union..

SELECT Simple_Status
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
WHERE  "RP_DAILY_MAINTENANCE_REPORT"."SCHEDULED_START_DATE_TIME" >= {?Start}
AND "RP_DAILY_MAINTENANCE_REPORT"."SCHEDULED_START_DATE_TIME" < {?End}
UNION
SELECT Simple_Status
FROM ARADMIN.MAINTENANCE WHERE  "MAINTENANCE"."SCHEDULED_START_DATE_TIME" >= {?Start} AND "MAINTENANCE"."SCHEDULED_START_DATE_TIME" < {?End}

Does this look correct?

Simple_Staus was just an example that I was trying because when I use "*" I get an error: Query block has incorrect number of result columns.

So Yes, I would like for it to show all fields..

That looks like it should work.

You just have to make sure the number of fields and the type matches.

You can pad one of the tables if need be

SELECT SimpleStatus, 0 as Cost
FROM  ....
UNION
SELECT SimpleStatus, Cost
FROM ....

mlmcc

I just realized you have 2 databases not 2 tables in the same database.

A COMMAND won't work because it is strictly related to the database of the connection.

mlmcc

Good Morning mlmcc:

So this is what I did, I used the Union ALL and added the fields that I needed.  It works but my issue now is that there is a duplicate for ech record unless, I specified NULL.  How can I tell it to only show 1 of each Ticket_ID, not 2.  Here is the code with the cropped output below..

STATUS	TICKET_ID	CAB_APPROVED	COMPLETION_DETAIL	ACTUAL_END_DATE_TIME
Closed	MNT-221051	Yes-National	Abdel Errounda	1301392080
Closed	MNT-221051	Yes-National	(null)	(null)
Closed	MNT-221056	Yes-National	Bert Bradbury	1301376960
Closed	MNT-221056	Yes-National	(null)	(null)
Closed	MNT-221058	Yes-National	Bert Bradbury	1301380560
Closed	MNT-221058	Yes-National	(null)	(null)
Closed	MNT-221061	Yes-National	Resolution Detail: Maintenance completed.

VOICE: NSO verification commands were ran off the CTS (show cable modem calls, show cable modem voice) to verify active voice calls post maintenance. 
VIDEO: NSO verification commands were ran to verify no residual maintenance issues.
HSD: HSD services were verified by taking modem count snapshots pre-to-post maintenance on the CMTS no differences were noted after the maintenance was completed. 
DA: DA was verified by the NSO verification commands (show layer2-transport connections, show vpls instance, etc..) no differences were noted post maintenance. Andrew/OPS also VERIFIED no DA impact at this time.
IT/UNN: IT was not affected by this maintenance.
SURVEILLANCE: Sean/BNOC verified no outstanding alarm in spectrum/monitoring tools due to this maintenance.	1301379300
Closed	MNT-221061	Yes-National	(null)	(null)
Closed	MNT-221062	Yes-National	Maintenance completed as planned, no impact was experienced. Sean from Surveillence verified all alarms are clear at hubs 20 and 86 after maintenance	1301382000
Closed	MNT-221062	Yes-National	(null)	(null)
Closed	MNT-221063	Yes-National	Shelf added and 2x10G cards added these cards are not turned up at this time	1301382600
Closed	MNT-221063	Yes-National	(null)	(null)
Closed	MNT-221067	Yes-National	Frank Hood	1301389200
Closed	MNT-221067	Yes-National	(null)	(null)
Closed	MNT-224344	Yes-National	QPSK IP re-addressing for SWOrange Mods 1 and 2 completed as planned.	1301382600
Closed	MNT-224344	Yes-National	(null)	(null)
Closed	MNT-224345	Yes-National	Recombines completed successfully, 99% online from original modem counts.	1301383920
Closed	MNT-224345	Yes-National	(null)	(null)
Closed	MNT-224346	Yes-National	MNT was completed successfully with no unexpected impact.	1301391180
Closed	MNT-224346	Yes-National	(null)	(null)
Closed	MNT-224350	Yes-National	MNT completed successfully. No impact as expected and new SER to CAR links (71 to 20) are up and active	1301387460

Select allOpen in new window

SELECT Status,Ticket_ID,CAB_APPROVED,COMPLETION_DETAIL,ACTUAL_END_DATE_TIME,ACTUAL_START_DATE_TIME,COMBINED_GROUP_NAME,COMPLETION_CODE,COMBINED_SERVICES,NULL,NULL,IMPACTED_DIVISIONS,MAINTENANCE_TYPE,SCHEDULED_START_DATE_TIME,SCHEDULED_END_DATE_TIME,STATUS,TICKET_ID,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
		WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
			AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
            AND CAB_APPROVED = 'Yes-National'
            AND STATUS = 'Closed'
UNION ALL
SELECT Status,Ticket_ID,CAB_APPROVED,NULL,NULL,NULL,COMBINED_GROUP_NAME,COMPLETION_CODE,COMBINED_SERVICES,DA_IMPACT,DA_VIP_IMPACT,IMPACTED_DIVISIONS,MAINTENANCE_TYPE,SCHEDULED_START_DATE_TIME,SCHEDULED_END_DATE_TIME,STATUS,TICKET_ID,CALLED_END_NOC_INITIATED,CALLED_END_TIME,CALLED_START_NOC_INITIATED,CALLED_START_TIME,SC_DA_CELL,SC_DATA,SC_HFC,SC_INFRASTRUCTURE,SC_VIDEO,SC_VOICE,DA_T_MOBILE_IMPACT
FROM ARADMIN.MAINTENANCE
		WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
		AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
        AND CAB_APPROVED = 'Yes-National'        
        AND STATUS = 'Closed'

Select allOpen in new window

Not sure how to add the group by.. Is it because I have aliases in my code? Not working... Should I post this in Oracle.  You did answer my question..

SELECT Status,Ticket_ID,CAB_APPROVED,UDF_CONVERT_UNIX_DATETIME(SCHEDULED_END_DATE_TIME, 'US/Eastern') AS SCHEDULED_END_DATE_TIME,UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern')AS SCHEDULED_END_DATE_TIME,COMPLETION_DETAIL,COMBINED_GROUP_NAME,COMPLETION_CODE,COMBINED_SERVICES,NULL as DA_IMPACT,NULL as DA_VIP_IMPACT,IMPACTED_DIVISIONS,MAINTENANCE_TYPE,STATUS,TICKET_ID,NULL as CALLED_END_NOC_INITIATED,NULL as CALLED_END_TIME,NULL as CALLED_START_NOC_INITIATED,NULL as CALLED_START_TIME,NULL as SC_DA_CELL,NULL as SC_DATA,NULL as SC_HFC,NULL as SC_INFRASTRUCTURE,NULL as SC_VIDEO,NULL as SC_VOICE,NULL as DA_T_MOBILE_IMPACT
FROM ARADMIN.RP_DAILY_MAINTENANCE_REPORT
		WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
			AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
            AND CAB_APPROVED = 'Yes-National'
            AND STATUS = 'Closed'

UNION ALL
SELECT Status,Ticket_ID,CAB_APPROVED,NULL,NULL,NULL,COMBINED_GROUP_NAME,COMPLETION_CODE,COMBINED_SERVICES,DA_IMPACT,DA_VIP_IMPACT,IMPACTED_DIVISIONS,MAINTENANCE_TYPE,STATUS,TICKET_ID,CALLED_END_NOC_INITIATED,CALLED_END_TIME,CALLED_START_NOC_INITIATED,CALLED_START_TIME,SC_DA_CELL,SC_DATA,SC_HFC,SC_INFRASTRUCTURE,SC_VIDEO,SC_VOICE,DA_T_MOBILE_IMPACT
FROM ARADMIN.MAINTENANCE
		WHERE UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '1' DAY)
		AND UDF_CONVERT_UNIX_DATETIME(SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
        AND CAB_APPROVED = 'Yes-National'        
        AND STATUS = 'Closed'

Select allOpen in new window

You add the group in the report not in the SQL

mlmcc

Your original question was very different and I provided feedback on what needs to be done. The answer you accepted deals with SQL and nothing to do connecting the table between two different database. So you should at least acknowledge it as an assisted solution.