Crystal Designer or Crystal Report problem?

[crystalnewbie]

CR 8.5 / SQL 2K

I have a sql statement that works fine on Query Analyzer. The sql is something like (select * from tblA where blah... UNION select * from tblA where blah...). I then paste it and save on Crystal Designer with no apparent problems.

I then try to output all the data into a report and for some reason the data that's coming from the bottom Select statement is not appearing in the report. There are no statements in the Record Selection at all.

Has anyone encountered a similar problem or knows of a problem with using UNION in crystal designer?

Sorry, search function at this site is not working at the moment thus not able to look for a posted resolution first.

Thanks in advance.

 

[synapsevampire]

Since you can't paste SQL into the Crystal designer, you'll need to state specifics about how this is being done.

Your probably best served to create a View with the Union Query to use as the data source.

Otherwise create the SQL by using the one table in the report. Then go into the Database->Show SQL Query and copy and paste the orginal SQL back in with a Union between. Change the name of the other table and fields, and it should work.

You can also paste SQL into an ADO connection, if that helps.

-k

 

[crystalnewbie]

I'm using Crystal SQL Designer 8.0. I am able to paste my sql by going to File / New / Enter SQL Statement Directly.

I moved the bottom select statement to the top and it is now showing all the data in Crystal Designer which matches the records as if I were to run the query in Query Analyzer.

However, I'm still not able to see all the records in the report.

I tried pasting the sql into an ADO connection and that is giving me a "Timeout Expired" message after a few seconds.

I also tried modifying the sql directly in the report after I made a query that calls one of the table that i'm using and selecting some of the fields that I need. After the report is generated, I go to SHOW SQL QUERY and deletes the current query and paste my original query with the UNION statement. By doing this, I get the following message "The column prefix "VP_TIMESHEETPUNCH" does not match with a table name or alias name used in the query.

Here is the query that I'm trying to use:

select TS.workrulename,TS.laborlevelname4,L.description,TS.personnum,TS.personfullname,TS.eventdate,datepart(q,TS.eventdate) as 'eventqtr', datepart(yy,TS.eventdate) as 'eventyear', 0 as 'breaktime', sum(TS.timeinseconds) as 'lengthOfShift',
0 as 'InCompliance'
from vp_timesheetpunch as TS inner join laborlevelentry as L
on TS.laborlevelname4 = L.name
group by
TS.workrulename,TS.laborlevelname4,L.description,TS.personnum,TS.personfullname,TS.eventdate,datepart(q,TS.eventdate), datepart(yy,TS.eventdate)

having count(TS.personnum) = 1 and
sum(TS.timeinseconds) >= 21601

UNION

select t1.workrulename,t1.laborlevelname4,t1.description,t1.personnum, t1.personfullname, cast(t1.eventdate as datetime) as 'eventdate', datepart(q,t1.eventdate) as 'eventqtr', datepart(yy,t1.eventdate) as 'eventyear', datediff(mi,t1.outpunchdtm, t2.inpunchdtm) as 'breaktime', (datediff(ss,t1.inpunchdtm,t2.outpunchdtm) - datediff(ss,t1.outpunchdtm, t2.inpunchdtm)) as 'lengthOfShift',(case when datediff(mi,t1.outpunchdtm, t2.inpunchdtm) >= 30 then 1 else 0 end) as 'InCompliance'
from
(select top 100 percent workrulename,laborlevelname4, description, personnum, personfullname, eventdate, startdtm, enddtm, inpunchdtm, outpunchdtm, startreason
from vp_timesheetpunch as A inner join laborlevelentry as L on A.laborlevelname4 = L.name
where exists
(select personnum, personfullname, eventdate, startreason
from vp_timesheetpunch b
where
b.personnum = a.personnum and
startreason like 'break:Meal%'
)
order by personfullname, inpunchdtm
) as t1

inner join

(

select top 100 percent workrulename,laborlevelname4,personnum, personfullname, eventdate, startdtm, enddtm, inpunchdtm, outpunchdtm, startreason
from vp_timesheetpunch as A
where exists
(select personnum, personfullname, eventdate, startreason
from vp_timesheetpunch
where
personnum = a.personnum and
startreason like 'break:Meal%'
group by
personnum, personfullname, eventdate, startreason
)
order by personfullname, inpunchdtm desc
) as t2

on t1.personnum = t2.personnum
and t1.eventdate = t2.eventdate
and t2.inpunchdtm >= t1.outpunchdtm
where
datediff(mi,t1.outpunchdtm, t2.inpunchdtm) < 60


group by
t1.workrulename,t1.laborlevelname4, t1.description, t1.personnum, t1.personfullname, t1.eventdate, t1.inpunchdtm, t1.outpunchdtm, t1.startreason, t2.inpunchdtm, t2.outpunchdtm, t2.startreason
having
(datediff(ss,t1.inpunchdtm,t2.outpunchdtm) - datediff(ss,t1.outpunchdtm, t2.inpunchdtm)) > 21601

 

[kskid]

I have cut and pasted SQL statements with unions from Crystal SQL Designer to Crystal Reports with CR 8.5. However, it is important to note that it is an absolute bear to maintain,

The SQL statements, when pasted, becomes just one long string when you click on Database -> Show SQL Query making it almost impossible to read.

You cannot add additional fields in Crystal Reports without redoing the ENTIRE SQL that you pasted. Need to always work changes in SQL Designer before updating the Crystal Reports. Even Crystal Reports will more or less tell you that you are ON YOUR OWN.

Forget about parameters. You don't want to go there. Trust me!

 

[crystalnewbie]

I, too, have given up on parameters.

Like I said on my previous post, I've been able to get the desired results on Crystal Designer. However when using that crystal query as a data source for a report. I am missing data.

I tried doing a "Save Data with Query" on crystal designer and recreated the report. This time it has all the information. My concern is, if I refresh my report, would it just use the saved data or would the query actually get re-executed?

 

[kskid]

Unfortunately, no. You have to refresh the Crystal query separately and then run the Crystal Report