Pulling Data for every Friday!

[Kinl]

I'm working with Crystal 8.0 and I'm pulling data from a SQL 6.x database. At this time I can pull data for 3 groupings. Once I try to access data for a friday, or basically every friday, I get data for 2 groupings. The 3 groupings are included in the report, and I'm using the select expert to just choose a date field to select by.

I have accessed the database manually to make sure all the data is in there for every friday. And it is in there.

Does anyone have any idea on how/why it will not pull data for a friday?

Any suggestions are greatly appreciated. My team is stumped on this one.

Donn

 

[kenhamady]

Post the selection formula you are using to get every friday. Also, post the SQL query that it generates. These are found in:

Report - Edit Selection formula - Record.
and
Database - Show SQL Query.

Respectively Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Kinl]

Selection Forumla:

{mo_job_hier.hierID} in ["Virtual Spanish", "Virtual Late Stage", "Virtual Age 1"] and
{mo_job.jobDate} = "2001/02/09"


SQL Query:

SELECT
mo_job."jobDate", mo_job."jobActive",
msv_job."outPlaced",
msv_job_code."code06", msv_job_code."code18", msv_job_code."code21", msv_job_code."code22", msv_job_code."code24", msv_job_code."code25", msv_job_code."code27", msv_job_code."code28", msv_job_code."code29", msv_job_code."code30", msv_job_code."code31", msv_job_code."code32", msv_job_code."code46", msv_job_code."code56", msv_job_code."code61", msv_job_code."code68", msv_job_code."code71",
msv_job_agent."jobClock", msv_job_agent."jobIdle", msv_job_agent."jobTalk", msv_job_agent."jobUpdate", msv_job_agent."jobWork", msv_job_agent."jobCount", msv_job_agent."jobWorked",
mo_job_hier."hierID",
mo_DTA_Record_sizes."RecSize"
FROM
{ oj (((("CampaignAnalyst"."reports"."mo_job" mo_job INNER JOIN "CampaignAnalyst"."reports"."msv_job_code" msv_job_code ON
mo_job."sysName" = msv_job_code."sysName" AND
mo_job."jobNum" = msv_job_code."jobNum&quot
INNER JOIN "CampaignAnalyst"."reports"."msv_job_agent" msv_job_agent ON
mo_job."sysName" = msv_job_agent."sysName" AND
mo_job."jobNum" = msv_job_agent."jobNum&quot
INNER JOIN "CampaignAnalyst"."reports"."mo_job_hier" mo_job_hier ON
mo_job."jobName" = mo_job_hier."jobNameLink&quot
INNER JOIN "CampaignAnalyst"."dbo"."mo_DTA_Record_sizes" mo_DTA_Record_sizes ON
mo_job."jobName" = mo_DTA_Record_sizes."JobName" AND
mo_job."jobDate" = mo_DTA_Record_sizes."jobDateConv&quot
INNER JOIN "CampaignAnalyst"."reports"."msv_job" msv_job ON
mo_job."sysName" = msv_job."sysName" AND
mo_job."jobNum" = msv_job."jobNum"}
WHERE
(mo_job_hier."hierID" = 'Virtual Age 1' OR
mo_job_hier."hierID" = 'Virtual Late Stage' OR
mo_job_hier."hierID" = 'Virtual Spanish') AND
mo_job."jobDate" = '2001/02/09'
ORDER BY
mo_job."jobDate" ASC,
mo_job_hier."hierID" ASC


-------End Query------------------

Thanx again!

-Donn

 

[kenhamady]

Nothing looks unusual.

So, if you used another date (non-friday) it would find 3 groups, but if you use any Friday it doesn't work? Is this SQL modified at all or was it generated by CR? And you are sure that there are records in all 3 groups on Friday? Would this happen in a new report, or just this one?

Try these tests:

First leave the Friday rule and delete the first rule (HeirID) and see what happens.

Second, instead of an "equal to", use "between" 2/9/2001 and 2/10/2001. See if you get any Friday records.

Third, use a range of 7 days and try adding a cross-tab by Date (column) and by HeirID (row). Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Kinl]

What do you mean by the first rule ? (I'm sorry, this project was kind of thrown my way, and I have a basic knowlege of CR, but not enough.)

Would I delete this from the actual record selection?

Thanx,

donn

 

[kenhamady]

The selection formula is made up of two rules. Delete the part up to the word AND as the test. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)