Unlinked Subreport - Insert into Time Series in Main 1

[shenay921]

Hi there,

I am working with Crystal Reports 8.5 in Windows XP with an ODBC.

I have a Central Schedule Report that pulls by date then it lists APPTS by time.

I have to pull in a separate table that pulls STAFFMTGBLOCKS that has no table field links to APPTS. I've pulled it in as a subreport passing pulldate parameter from the main to it.

I want the subreport to print only when it is not null and only in the right chronological order in the Main. I can get it to do this if the Main time = the blocks time. I cannot figure out how to get it to pull in the correct chronological spot if the Main report APPTS does NOT have the subreport time. If I put it in the header, obviously it won't print in chronological order. I.E.: 8am APPT, 9am APPT, 10am STAFFMTGBLOCKS, 11am APPT.

Thanks for any insight!

 

[lbass]

I would try a Union All statement to combine the two time fields, instead of using a subreport. In 8.5 you would need to start by adding the fields from Table1 to your report. Then go to database->show SQL query. At the end of the query, add "Union All" and then add a second query that uses the second table, with the same number of fields and of matching datatype. The fields will be combined with the fields in the first query, according to their sequence in the statement. Your statement might look something like this:

SELECT Table1.`Date`,Table1.`Time`,Table1.`ApptName`
FROM `Table1` Table1
UNION ALL
SELECT Table2.`Date`,Table2.`Time`,Table2.`StaffMtg`
FROM `Table2` Table2

You should then be able to group on date, then on time, and add the "ApptName" field to the report. The Table1 time field will contain times for both tables, and the ApptName field will contain the description for the staff meetings.

-LB

 

[shenay921]

Thanks LB, I'll give it a shot!

 

[shenay921]

Hi LB,

I recreated report in a simpler version just using the two tables and what I am finding is:
1. I have to use UNION not UNION ALL (even though I want to keep duplicates, I can't get UNION ALL to work) and
2. that I cannot use a parameter if I want to select a specific date to pull from. It errors out as soon as I try to apply a parameter.

Also, the full report pulls from a number of tables -- the only one not related is the STAFFMTGBLOCKS. And I can't figure out where to insert the UNION stmt.

The current SQL code without the STAFFMTGBLOCKS is (and it contains selection parameters):

SELECT
appt."start_datetime", appt."entered_datetime", appt."long_comment",
patbooking."diagnosis",
probooking."name",
apptack."change_datetime",
res."res_id",
visit."admit_reason",
pat."birthdate", pat."home_phone", pat."name_legal",
res_1ordering."name",
res_2referring."name"
FROM
{ oj ((((((((("prod"."dbo"."appt" appt INNER JOIN "prod"."dbo"."patbooking" patbooking ON
appt."appt_id" = patbooking."appt_id")
INNER JOIN "prod"."dbo"."resbooking" resbooking ON
appt."appt_id" = resbooking."appt_id")
INNER JOIN "prod"."dbo"."probooking" probooking ON
appt."appt_id" = probooking."appt_id")
INNER JOIN "prod"."dbo"."visitapptlist" visitapptlist ON
appt."appt_id" = visitapptlist."appt_id")
LEFT OUTER JOIN "prod"."dbo"."apptack" apptack ON
appt."appt_id" = apptack."appt_id")
INNER JOIN "prod"."dbo"."visit" visit ON
visitapptlist."visit_id" = visit."visit_id")
INNER JOIN "prod"."dbo"."pat" pat ON
patbooking."pat_id" = pat."pat_id")
INNER JOIN "prod"."dbo"."res" res_1ordering ON
patbooking."ordering_phys_id" = res_1ordering."res_id")
INNER JOIN "prod"."dbo"."res" res ON
resbooking."res_id" = res."res_id")
LEFT OUTER JOIN "prod"."dbo"."res" res_2referring ON
patbooking."referring_phys_id" = res_2referring."res_id"}
WHERE
appt."start_datetime" >= {ts '2005-10-11 00:00:00.00'} AND
appt."start_datetime" < {ts '2005-10-12 00:00:59.00'} AND
(res."res_id" = 1282 OR
res."res_id" = 1279 OR
res."res_id" = 1109 OR
res."res_id" = 1107 OR
res."res_id" = 1103 OR
res."res_id" = 1099 OR
res."res_id" = 1094 OR
res."res_id" = 1042 OR
res."res_id" = 1101)
ORDER BY
appt."start_datetime" ASC,
probooking."name" ASC

Appreciate any further help!

 

[lbass]

Try something like:

SELECT
appt."start_datetime", appt."entered_datetime", appt."long_comment",
patbooking."diagnosis",
probooking."name",
apptack."change_datetime",
res."res_id",
visit."admit_reason",
pat."birthdate", pat."home_phone", pat."name_legal",
res_1ordering."name",
res_2referring."name"
FROM
{ oj ((((((((("prod"."dbo"."appt" appt INNER JOIN "prod"."dbo"."patbooking" patbooking ON
appt."appt_id" = patbooking."appt_id")
INNER JOIN "prod"."dbo"."resbooking" resbooking ON
appt."appt_id" = resbooking."appt_id")
INNER JOIN "prod"."dbo"."probooking" probooking ON
appt."appt_id" = probooking."appt_id")
INNER JOIN "prod"."dbo"."visitapptlist" visitapptlist ON
appt."appt_id" = visitapptlist."appt_id")
LEFT OUTER JOIN "prod"."dbo"."apptack" apptack ON
appt."appt_id" = apptack."appt_id")
INNER JOIN "prod"."dbo"."visit" visit ON
visitapptlist."visit_id" = visit."visit_id")
INNER JOIN "prod"."dbo"."pat" pat ON
patbooking."pat_id" = pat."pat_id")
INNER JOIN "prod"."dbo"."res" res_1ordering ON
patbooking."ordering_phys_id" = res_1ordering."res_id")
INNER JOIN "prod"."dbo"."res" res ON
resbooking."res_id" = res."res_id")
LEFT OUTER JOIN "prod"."dbo"."res" res_2referring ON
patbooking."referring_phys_id" = res_2referring."res_id"}
WHERE
appt."start_datetime" >= {ts '2005-10-11 00:00:00.00'} AND
appt."start_datetime" < {ts '2005-10-12 00:00:59.00'} AND
(res."res_id" = 1282 OR
res."res_id" = 1279 OR
res."res_id" = 1109 OR
res."res_id" = 1107 OR
res."res_id" = 1103 OR
res."res_id" = 1099 OR
res."res_id" = 1094 OR
res."res_id" = 1042 OR
res."res_id" = 1101)
union all
SELECT
table2."start_datetime", table2."entered_datetime", table2."staffmeeting",
patbooking."diagnosis",
probooking."name",
apptack."change_datetime",
res."res_id",
visit."admit_reason",
pat."birthdate", pat."home_phone", pat."name_legal",
res_1ordering."name",
res_2referring."name"
FROM
{ oj ((((((((("prod"."dbo"."table2" table2 INNER JOIN "prod"."dbo"."patbooking" patbooking ON
table2."appt_id" = patbooking."appt_id")
INNER JOIN "prod"."dbo"."resbooking" resbooking ON
table2."appt_id" = resbooking."appt_id")
INNER JOIN "prod"."dbo"."probooking" probooking ON
table2."appt_id" = probooking."appt_id")
INNER JOIN "prod"."dbo"."visitapptlist" visitapptlist ON
table2."appt_id" = visitapptlist."appt_id")
LEFT OUTER JOIN "prod"."dbo"."apptack" apptack ON
table2."appt_id" = apptack."appt_id")
INNER JOIN "prod"."dbo"."visit" visit ON
visitapptlist."visit_id" = visit."visit_id")
INNER JOIN "prod"."dbo"."pat" pat ON
patbooking."pat_id" = pat."pat_id")
INNER JOIN "prod"."dbo"."res" res_1ordering ON
patbooking."ordering_phys_id" = res_1ordering."res_id")
INNER JOIN "prod"."dbo"."res" res ON
resbooking."res_id" = res."res_id")
LEFT OUTER JOIN "prod"."dbo"."res" res_2referring ON
patbooking."referring_phys_id" = res_2referring."res_id"}
WHERE
table2."start_datetime" >= {ts '2005-10-11 00:00:00.00'} AND
table2."start_datetime" < {ts '2005-10-12 00:00:59.00'} AND
(res."res_id" = 1282 OR
res."res_id" = 1279 OR
res."res_id" = 1109 OR
res."res_id" = 1107 OR
res."res_id" = 1103 OR
res."res_id" = 1099 OR
res."res_id" = 1094 OR
res."res_id" = 1042 OR
res."res_id" = 1101)
ORDER BY
1 ASC, 5 ASC


Basically, copy the first part of the query and then change only the Table1 table names to Table2. Also note that the order by should be after the second query, using the sequential order of the fields, rather than the field names. I'm unclear what corresponds to your "Appt" field, but assumed it was the long_comment field, which it might not be.

You should be able to create a parameter in the usual way, but you cannot build it directly into the SQL, but instead would add it to the selection criteria formula area. This will process locally, but at least allows you to use a parameter.

-LB

 

[shenay921]

Hi LB,

I appreciate your response. I tried it and am getting error messages. So I am trying just to create the basic with the two tables:

SELECT Table1.`Date`,Table1.`Time`,Table1.`ApptName`
FROM `Table1` Table1
UNION ALL
SELECT Table2.`Date`,Table2.`Time`,Table2.`StaffMtg`
FROM `Table2` Table2

mine is:

SELECT
appt."start_datetime", appt."thinuser"
FROM
"prod"."dbo"."appt" appt
union all
SELECT
block."start_datetime", block."block_name"
FROM
"prod"."dbo"."block" block


...that you recommended. I found that the field in appt had to be change to one that matched in length as well as type to the block field. This is working -- it pulls but errors as soon as I try to use a select on it. It says the APPT table does not exist. If I try to select on the BLOCK (staffmeeting) table it runs but no data returns.

Any ideas on getting the select to work?

Thanks!

 

[lbass]

The only select you should be using in the record selection area should be to incorporate a parameter. Other selects belong in the union statement.

When you tried the earlier more elaborate union statement, what error messages did you receive?

-LB

 

[shenay921]

Hi LB,

On the first part, correct, I set up a parameter and then attached it in Select Expert. But it won't work even when setting equal to something already in the list it is pulling from.

On the second part, I get an error of invalid column name appt_id.

So if I add a Block column (block_id) that is of same database type to appt_id and update the code, then I get the error "column prefix res does not match with a table name."

Thanks for your help!

 

[lbass]

Please copy and paste SQL query using the union all into the thread, and also copy the record selection formula into the thread.

-LB

 

[shenay921]

Hi LB,

Ok, here's the Union Thread:
SELECT
appt."appt_id", appt."start_datetime", appt."entered_datetime", appt."thinuser",
patbooking."diagnosis",
probooking."name",
apptack."change_datetime",
res."res_id",
visit."admit_reason",
pat."birthdate", pat."home_phone", pat."name_legal",
res_1ordering."name",
res_2referring."name"
FROM
{ oj ((((((((("prod"."dbo"."appt" appt INNER JOIN "prod"."dbo"."patbooking" patbooking ON
appt."appt_id" = patbooking."appt_id")
INNER JOIN "prod"."dbo"."resbooking" resbooking ON
appt."appt_id" = resbooking."appt_id")
INNER JOIN "prod"."dbo"."probooking" probooking ON
appt."appt_id" = probooking."appt_id")
INNER JOIN "prod"."dbo"."visitapptlist" visitapptlist ON
appt."appt_id" = visitapptlist."appt_id")
LEFT OUTER JOIN "prod"."dbo"."apptack" apptack ON
appt."appt_id" = apptack."appt_id")
INNER JOIN "prod"."dbo"."visit" visit ON
visitapptlist."visit_id" = visit."visit_id")
INNER JOIN "prod"."dbo"."pat" pat ON
patbooking."pat_id" = pat."pat_id")
INNER JOIN "prod"."dbo"."res" res_1ordering ON
patbooking."ordering_phys_id" = res_1ordering."res_id")
INNER JOIN "prod"."dbo"."res" res ON
resbooking."res_id" = res."res_id")
LEFT OUTER JOIN "prod"."dbo"."res" res_2referring ON
patbooking."referring_phys_id" = res_2referring."res_id"}
WHERE
appt."start_datetime" >= {ts '2005-10-11 00:00:00.00'} AND
appt."start_datetime" < {ts '2005-10-12 00:00:59.00'} AND
(res."res_id" = 1282 OR
res."res_id" = 1279 OR
res."res_id" = 1109 OR
res."res_id" = 1107 OR
res."res_id" = 1103 OR
res."res_id" = 1099 OR
res."res_id" = 1094 OR
res."res_id" = 1042 OR
res."res_id" = 1101)
union all
SELECT
block."block_id", block."start_datetime", block."created_datetime", block."block_name",
patbooking."diagnosis",
probooking."name",
apptack."change_datetime",
res."res_id",
visit."admit_reason",
pat."birthdate", pat."home_phone", pat."name_legal",
res_1ordering."name",
res_2referring."name"
FROM
{ oj ((((((((("prod"."dbo"."block" block INNER JOIN "prod"."dbo"."patbooking" patbooking ON
block."block_id" = patbooking."appt_id")
INNER JOIN "prod"."dbo"."resbooking" resbooking ON
block."block_id" = resbooking."appt_id")
INNER JOIN "prod"."dbo"."probooking" probooking ON
block."block_id" = probooking."appt_id")
INNER JOIN "prod"."dbo"."visitapptlist" visitapptlist ON
block."block_id" = visitapptlist."appt_id")
LEFT OUTER JOIN "prod"."dbo"."apptack" apptack ON
block."block_id" = apptack."appt_id")
INNER JOIN "prod"."dbo"."visit" visit ON
visitapptlist."visit_id" = visit."visit_id")
INNER JOIN "prod"."dbo"."pat" pat ON
patbooking."pat_id" = pat."pat_id")
INNER JOIN "prod"."dbo"."res" res_1ordering ON
patbooking."ordering_phys_id" = res_1ordering."res_id")
INNER JOIN "prod"."dbo"."res" res ON
resbooking."res_id" = res."res_id")
LEFT OUTER JOIN "prod"."dbo"."res" res_2referring ON
patbooking."referring_phys_id" = res_2referring."res_id"}
WHERE
block."start_datetime" >= {ts '2005-10-11 00:00:00.00'} AND
block."start_datetime" < {ts '2005-10-12 00:00:59.00'} AND
(res."res_id" = 1282 OR
res."res_id" = 1279 OR
res."res_id" = 1109 OR
res."res_id" = 1107 OR
res."res_id" = 1103 OR
res."res_id" = 1099 OR
res."res_id" = 1094 OR
res."res_id" = 1042 OR
res."res_id" = 1101)
ORDER BY
1 ASC, 5 ASC

And here is the selection formula:

(it work one time for a specific date and time and then the next time said the "appt" does not exit)

SELECT
appt."start_datetime", appt."thinuser"
FROM
"prod"."dbo"."appt" appt
union all
SELECT
block."start_datetime", block."block_name"
FROM
"prod"."dbo"."block" block
WHERE
appt."start_datetime" >= {ts '2005-04-11 11:00:00.00'} AND
appt."start_datetime" < {ts '2005-04-11 11:01:00.00'}

Thank you!

 

[lbass]

I'm sorry, but I don't think a parameter can be added in the record selection formula area after all. It appeared to work in one test I did, but not in another. However, I think you could create a parameter and use it in the section expert to suppress records that don't meet your criteria. Go to section expert->details->suppress->x+2 and enter:

not({appt.start_datetime} in {?daterange})

Does the union statement work okay when you don't try to add a parameter to the record selection area? You might need to rebuild it, since making changes in the record selection formula area can make unwanted changes in the union statement.

-LB

 

[shenay921]

Hi LB,

The union statement works in the smaller code if I don't try to add the parameter statement. I think I will use your suggestion of suppressing in the section expert and try to rebuild from there.

Thanks for your help and patience!