Can anybody help? Changing SQLQuery of rpt made with RDC component 2

[CrystalVisualBOracle]

X-)
Hi there. I'm in need of a little help. I've created a VB6 application I've created. In it, I have a report that is generated using the RDC component. It has a SQL Query already set. What I need to do is to add two lines to the where clause from the vb form before I export the report. For example, say the sql query of the report initially reads as,
"Select x.id,
y.date
From x, y
where x.id = y.id"

I want add two more lines to the where clause depending on what the user enters as follows,
"Select x.id,
y.date
From x, y
where x.id = y.id
and y.date > Date('2000,12,01')
and x.id > 0"

What is the best way to do this?

I tried using the report.recordselectionformula="..." but the report never reflected my added criteria. Then I tried using the report.SQLQueryString command but I could not figure out the correct syntax. (note - I used all the special chr(13) characters but it still wouldn't work) Could anyone please give my an example of where they might have done either of these before or some other method even. I just need to see how a working example looks to see what I'm doing wrong.

Thanks a lot
CrystalVisualBORacle

 

[Ruairi]

The RecordSelectionFormula is a formula used by crystal once the records are retrieved from the DB. I havent had much luck using that either. The SQLQuereyString is the actual querey that is sent to the DB. Below is the relevant code from one of my apps that uses the SQLQuereyString to print a report based on user entered parameters.

'Set Up the querey string 

StartDateStr = "#" & StartDate & "#"
EndDateStr = "#" & EndDate & "#"
        
sql = "SELECT Production.`dag`, Production.`thickness`, Production.`width`, Production.`pieces`, "
sql = sql & "Production.`length`, Production.`shift`, Production.`volume`, grade.`description`, "
sql = sql & "endtrim.`description` "

If EPG = "NONE" Then
    frmDailyProd.Excluded = "NONE"
    sql = sql & "From ((`Production` Production INNER JOIN `endtrim` endtrim ON Production.`endtrimrec` = endtrim.`dosrec`) "
    sql = sql & "INNER JOIN `grade` grade ON Production.`graderec` = grade.`rec`) "
    sql = sql & "INNER JOIN `ProductionCenters` ProductionCenters ON Production.`productioncenterrec` = ProductionCenters.`dosrec` "
Else
    frmDailyProd.Excluded = EPG
    sql = sql & "From (((`Production` Production INNER JOIN `endtrim` endtrim ON Production.`endtrimrec` = endtrim.`dosrec`) "
    sql = sql & "INNER JOIN `grade` grade ON Production.`graderec` = grade.`rec`) "
    sql = sql & "INNER JOIN `groups` groups ON Production.`grouprec` = groups.`dosrec`) "
    sql = sql & "INNER JOIN `ProductionCenters` ProductionCenters ON Production.`productioncenterrec` = ProductionCenters.`dosrec` "
End If

sql = sql &amp; &quot;Where Production.`dag` >= &quot; &amp; StartDateStr &amp; &quot; AND Production.`dag` <= &quot; &amp; EndDateStr
sql = sql &amp; &quot; AND ProductionCenters.`description` = '&quot; &amp; ProdCenter &amp; &quot;' &quot;
If EPG <> &quot;NONE&quot; Then
    sql = sql &amp; &quot;AND groups.`description` <> '&quot; &amp; EPG &amp; &quot;'&quot;
End If
sql = sql &amp; &quot; Order By Production.`width` ASC, grade.`description` ASC&quot;

'** sql string is passed to RecordSelFormula, a public 
'** variable of the CRViewer form 

CRViewer1.ReportSource = Report
Report.SQLQueryString = RecordSelFormula
CRViewer1.ViewReport

HTH
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[CrystalVisualBOracle]

Okay I'm comparing your syntax to mine and I can't see any real difference. My code keeps erroring out though saying invalid table name. However, when I pull this exact select query out of here and run it in oracle, it's fine. That leaves me thinking that Crystal must not be viewing my tables correctly due to a syntax error. Could you glance at this and see if you happen to notice what I'm doing wrong? I'm getting rather desperate so I GREATLY GREATLY GREATLY APPRECIATE your help. THANK YOU

strSelection1 = "SELECT 'R_PUBN_RPT_LOG'.'R_TIMEPERIOD_ID', " & _
" R_PUBN_RPT_LOG.'DATESTAMP', " & _
" R_PUBN_RPT_LOG.'UNIQ_USER', " & _
" R_PUBN_RPT_LOG.'HIT', " & _
" R_PUBN_RPT_LOG.'BAND_RANGE1', " & _
" R_PUBN_RPT_LOG.'BAND_RANGE2', " & _
" R_PUBN_RPT_LOG.'BAND_RANGE3', " & _
" R_PUBN_RPT_LOG.'BAND_RANGE4', & _
" D_SERVER.'COMPANYNAME', " & _
" M_PUBLICATION.'DESCRIPTOR', " & _
" M_PUBLISHER.'DESCRIPTOR' " & & _
"FROM 'D2ALLLOGS'.'R_PUBN_RPT_LOG' R_PUBN_RPT_LOG, " & _
"'DISTRIB'.'D_SERVER' D_SERVER, " & _
"'D2MAIN'.'M_PUBLICATION' M_PUBLICATION, " & _
"'D2MAIN'.'M_PUBLISHER' M_PUBLISHER" & Chr$(13) & _
"WHERE R_PUBN_RPT_LOG.'D_SRVR_ID' = D_SERVER.'ID' " & _
"AND R_PUBN_RPT_LOG.'M_PUBN_ID' = M_PUBLICATION.'ID' " & _
"AND M_PUBLICATION.'D_PUBLISHER_ID' = M_PUBLISHER.'ID' " & _
"AND M_PUBLISHER.'ID' = " & strPubrID & " " & _
"AND R_PUBN_RPT_LOG.'DATESTAMP' = DATE ('" & strPbrCrystalDate & "')" & Chr$(13) & Chr$(10) & _
"ORDER BY D_SERVER.'COMPANYNAME' ASC, M_PUBLICATION.'DESCRIPTOR' ASC, R_PUBN_RPT_LOG.'R_TIMEPERIOD_ID' ASC"

Report1.SQLQueryString = strSelection1
... Report1.Export

Thanks again for your help.

CrystalVisualBOracle

 

[Ruairi]

I don't see anything wrong there. Here's a couple of things i would try if you havent already.
Print the querey string to a text file and look for any sytax errors, theyre much harder to see in code that with a completed string. You've probably already done that. Next open your report in the crystal reports application (if you created it from scratch in the RDC you can still save it as a report file, just right click in the RDC window and choose 'save as report file'). From there you can choose 'Show SQL Querey' from the DB menu and paste your querey there, you may get a better error message there Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[CrystalVisualBOracle]

Thanks a lot. That helped. I don't know why I didn't do that before. One of those days I guess.

Thanks Again.

CrystalVisualBORacle

 

[CrystalVisualBOracle]

Okay so I fixed my problem, for some reason my RDC wants the sql handed off with double quotes already embedded - not single. Now it's giving me a "missing expression error" though. It only occurs when I have the date qualifier below included in the sqlquerystring that I send to the report. Any ideas on whether or not I can send a date condition to the report this way and if so, what I might be doing wrong below? I've bolded the section in question.

Thanks A lot
CrystalVisualBOracle

strSelection1 = "SELECT ""R_PUBN_RPT_LOG"".""R_TIMEPERIOD_ID"", " & _
" R_PUBN_RPT_LOG.""DATESTAMP"", " & _
" R_PUBN_RPT_LOG.""UNIQ_USER"", " & _
" R_PUBN_RPT_LOG.""HIT"", " & _
" R_PUBN_RPT_LOG.""BAND_RANGE1"", " & _
" R_PUBN_RPT_LOG.""BAND_RANGE2"", " & _
" R_PUBN_RPT_LOG.""BAND_RANGE3"", " & _
" R_PUBN_RPT_LOG.""BAND_RANGE4"", " & _
" D_SERVER.""COMPANYNAME"", " & _
" M_PUBLICATION.""DESCRIPTOR"", " & _
" M_PUBLISHER.""DESCRIPTOR"" " & Chr$(13) & _
"FROM " & Chr$(13) & " ""D2ALLLOGS"".""R_PUBN_RPT_LOG"" R_PUBN_RPT_LOG, " & _
"""DISTRIB"".""D_SERVER"" D_SERVER, " & _
"""D2MAIN"".""M_PUBLICATION"" M_PUBLICATION, " & _
"""D2MAIN"".""M_PUBLISHER"" M_PUBLISHER" & Chr$(13) & _
"WHERE R_PUBN_RPT_LOG.""D_SRVR_ID"" = D_SERVER.""ID"" " & _
"AND R_PUBN_RPT_LOG.""M_PUBN_ID"" = M_PUBLICATION.""ID"" " & _
"AND M_PUBLICATION.""D_PUBLISHER_ID"" = M_PUBLISHER.""ID"" " & _
"AND M_PUBLISHER.""ID"" = " & strPubrID & " " & _
"AND R_PUBN_RPT_LOG.""DATESTAMP"" = DATE ('" & strPbrCrystalDate & "')" & Chr$(13) & Chr$(10) & _
"ORDER BY D_SERVER.""COMPANYNAME"" ASC, M_PUBLICATION.""DESCRIPTOR"" ASC, R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"" ASC"


Note strPbrCrystalDate = 2000,12,01

 

[Ruairi]

Is Date(2000,12,01) a crystal formula syntax for the date? If so it would be legal in the .RecordSeletionFormula, which is processed by CR, but not in the .SQLQuereyString. Anything in the .SQLQuereyString is sent directly to the Oracle DB. The date expression you send needs to be in Oracle syntax(it may be already, as iv'e never worked with oracle i don't know) Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[CrystalVisualBOracle]

Yes that's what the problem was! Thank you!!!!! *<|

I was sending in the date in crystal syntax because when I had initially tried the &quot;recordselectionformula&quot; method, I had to. With SQLQueryString, I had to use the straight oracle syntax (DD-MON-YYYY) so it wouldn't work. That's strage since the whole rest of the expression had to be in crystal syntax. Thank you very much though. I'd surely be stuck indefinitely otherwise. (My boss was actaully talking about scraping CR all together and working around it. - NOT FUN since my deadline is a week and a half a way and I haven't tested yet.)

Thanks again,
CrystalVisualBORacle
*<| s-) :-Q :-V :-9 B-) |-0
When this project is over, I'm having a party!!!

 

[Ruairi]

no problem. ive spent many a late night trying to figure out errors with CR, i know how it feels. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com