more than two query results on crystal report

[wizkid]

Hi,
This is working fine for me.
now i want to put more than one query results on crystal report.
is it possible?because i want one result from 2 tables and one result from other 2 tables.
for example here i am giving one sql query result on crystal, same way i want
other query result on the same report.

Dim db_conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Form_Load()
   
    Dim CRReport As CRAXDRT.Report
    Dim CRApp As New CRAXDRT.Application
    
    'Open the Crystal Report
    Set CRReport = CRApp.OpenReport("C:\Program Files\Microsoft Visual Studio\VB98\newreport\Report2.rpt")
    Set rs = New ADODB.Recordset
    db_conn.Open "Driver={Microsoft ODBC for Oracle};" & _
    "Server=Test;" & _
    "Uid=Scott;" & _
    "Pwd=tiger"
    Dim sqlq As String
    sqlq = "Select empid from employee where empid like 'M4%'"
    With CRReport
        'Set the Access database path
        .Database.Tables(1).Location = App.Path & "\MaData"
        rs.Open sqlq, db_conn, adOpenStatic, adLockReadOnly
         .Database.SetDataSource rs, 3, 1
        CrystalActiveXReportViewer1.ReportSource = CRReport
       CrystalActiveXReportViewer1.ViewReport
  End With
        
End Sub

thank you
wizkid

[wizkid]

Any help Please!!!!!!!!!

[ganeshmoorthy]

use some joins in the query or else create a temp table with all the required fields and pass the records to that table. use temptable in the crystall report to display

[wizkid]

Hi ganeshmoorthy,
thanks for your reply.
but i want the values in runtime.
querys joining more than 4 different tables in total 2 querys.so i can't join.
how to create a runtime table
please give me some clue,i think each time when i run the form we need to drop the temp table before creating a new one.

SELECT SUM(SALES_ITEM.ORDER_QUANTITY),PRODUCT.STYLE_CODE FROM PRODUCT , SALES_ITEM Where (PRODUCT.PRODUCT_CODE = SALES_ITEM.PRODUCT_CODE) AND (SUBSTR(PRODUCT.STYLE_CODE,1,2)='MT' OR SUBSTR(PRODUCT.STYLE_CODE,1,2)='MX') AND SALES_ITEM.ORDER_TYPE=0 AND order_date>(new_time('31-aug-2005','GMT','GMT') - to_date('01-jan-1970','dd-mon-yyyy')) * (24*60*60) group BY PRODUCT.STYLE_CODE...this is one query


select product.style_code,count(product.style_code) from sales_item , product where (substr(product.style_code,1,2)='MX' or substr(product.style_code,1,2)='MT') and (PRODUCT.PRODUCT_CODE=SALES_ITEM.PRODUCT_CODE) and substr(sales_item.product_code,-2)<>substr(orig_product,-2) and order_date>((new_time('31-aug-2005','GMT','GMT') - to_date('01-jan-1970','dd-mon-yyyy')) * (86400)) group by product.style_code...this is another query


i want these two querys result on crystal report

thanks
wizkid

[brucevde]

Is this basically two reports? Ie show all the results from the first query then show all the results from the second query.

Regardless, adding a SubReport may help.

[wizkid]

hi brucevde,
really i don't know how to use subreport.
i want these both querys result on same report.
like

style_code ....................... this is from any one of the query
SUM(SALES_ITEM.ORDER_QUANTITY) ...this is from first query
count(product.style_code) ...this is from second query

these three fields should be in same report.
any suggestions please
thanks
wizkid

[wizkid]

hi frds,
i was created two different tables for two different querys.
and i was created on more table using the above two tables, with 3 fields.
and i place the table on report.one way i got the solution.but it is not good.
i want to create so many reports like that.
but it is some what complicated.
is there any other way to get the two query result directly on report
any idea??
thanks
wizkid

[brucevde]

I tend to create everything in Crystal and just use VB to launch the report (passing parameters if necessary). This way I can run the report in Crystal without having to launch the application.

You cannot have multiple datasources in a report unless you use a SubReport.

I took a closer look at your queries and noticed they are exactly the same, except one does a Sum the other a Count. You could get both using

select product.style_code,count(product.style_code), SUM(SALES_ITEM.ORDER_QUANTITY) ...' rest of query

Or you could let Crystal handle everything by setting up summary fields in your report.

[wizkid]

hi brucevde,
yes ,i tried the query like...

SQL> select SUM(SALES_ITEM.ORDER_QUANTITY),product.style_code,count(product.style_code) from sales_item,product
where (substr(product.style_code,1,2)='MX' or substr(product.style_code,1,2)='MT')
and substr(sales_item.product_code,-2)<>substr(orig_product,-2)
and ("PRODUCT"."PRODUCT_CODE"="SALES_ITEM"."PRODUCT_CODE")
and order_date>((new_time('31-aug-2005','GMT','GMT') - to_date('01-jan-1970','dd-mon-yy (24*60*60)) group by product.style_code;


o/p is :

SUM(SALES_ITEM.ORDER_QUANTITY) STYLE_CODE COUNT (PRODUCT.STYLE_CODE)
------------------------------ ---------- -------------------------
2081 MT004 2054
79 MT005 78
565 MT025 564
308 MT040 297
112 MT059 112
355 MT100 350
57 MT119 53
396 MT128 391
436 MT129 434
67 MT140 62
265 MT164 261

but this is not correct for SUM(SALES_ITEM.ORDER_QUANTITY)

the SUM(SALES_ITEM.ORDER_QUANTITY) should be in the following way.
but i am getting different values for SUM(SALES_ITEM.ORDER_QUANTITY) if i combining in one query.,

SUM(SALES_ITEM.ORDER_QUANTITY) STYLE_CODE
---------- ----------
45475 MT004
802 MT005
4440 MT025
1 MT035
5122 MT040
3253 MT059
3295 MT100
1274 MT119
6551 MT128
7930 MT129
3534 MT140

SOLDITEMS STYLE_CODE
---------- ----------
6164 MT164
19923 MT178
2115 MT186
0 MT187
0 MT195
13 MT196
2511 MT197
0 MT198
2188 MT201
74 MT202
95 MT204

so i split the query.
ok i am learning crystal ,so i will try to design a crystal report and passing parameters from vb.
if possible tell me how can i work with subreport.if possible give me some example
thanks a lot for your help.
wizkid