Crystal report VB and subreports

Tere · November 14th, 2003, 01:56 PM

Hi!!
VB 6.0
Crystal 8.0
Windows 2000

I uses the next code to uses crystal report with visual basic, now I want to uses a subreport but I don't have a clue of how to start.

1) First in VB I go to Project+ Add Crystal Report 8, I add a report that I previous create in CR, this add on the Designer Project a file with DSR extention.
2) In a form where I have a CRViewer control I have this code:

Dim crRep_Analisis As cr_Analisis

Private Sub Form_Load()
Select Case gstrForm

'**** Reports
Case "cr_Analisis"
        ' Saca query para el reporte, articulos con existencia en 0
        Set adoRs = New ADODB.Recordset
        adoRs.Open gstrSQL, gcnOracle, adOpenForwardOnly, adLockReadOnly, adCmdText
        'adoRS.Open gstrSQL, cnSQL, adOpenForwardOnly, adLockReadOnly, adCmdText
        If adoRs.EOF And adoRs.BOF Then 'No encontro
            adoRs.Close
            Set adoRs = Nothing
            Screen.MousePointer = vbDefault
            Exit Sub
        Else
            Set crRep_Analisis = New cr_Analisis

            crRep_Analisis.ParameterFields(1).AddCurrentValue gstrParameter1
            crRep_Analisis.ParameterFields(2).AddCurrentValue gstrParameter2
            crRep_Analisis.Database.SetDataSource adoRs
            CRViewer1.ReportSource = crRep_Analisis
            If gblnGenArchivo = True Then
                If gstrTipoArch = "RPT" Then
                    crRep_Analisis.ExportOptions.DestinationType = crEDTDiskFile
                    crRep_Analisis.ExportOptions.DiskFileName = "T:\CODIGOO\QUATRO\" + gstrParameter3 + "." + gstrTipoArch
                    crRep_Analisis.ExportOptions.FormatType = crEFTCrystalReport 'crEFTExcel50
                    crRep_Analisis.Export False
                Else
                    crRep_Analisis.ExportOptions.DestinationType = crEDTDiskFile
                    crRep_Analisis.ExportOptions.DiskFileName = "T:\CODIGOO\QUATRO\" + gstrParameter3 + "." + gstrTipoArch
                    crRep_Analisis.ExportOptions.FormatType = crEFTExcel50
                    crRep_Analisis.Export False
                End If
            End If
        End If
4) This is the code that I uses in the button that call the report:

Sub Imprimir()
Dim strFecha1 As String
Dim strFecha2 As String

Dim strLoc As String
Dim CIA As Integer
   If Left(cboSucursal.Text, 3) = "TJU" Then
      CIA = 2000
   ElseIf Left(cboSucursal.Text, 3) = "TCH" Then
      CIA = 2002
   Else
      CIA = 2001
   End If

  strFecha1 = Format$(dtpFecha1.Value - 6, "MM-DD-YYYY")
  strFecha2 = Format$(dtpFecha1.Value, "MM-DD-YYYY")

   gstrParameter1 = "Reporte de Analisis Edo. Resultado del " & strFecha1 & " al " & strFecha2
    gstrParameter2 = cboSucursal.Text
   gstrForm = "cr_Analisis"

gstrSQL = " SELECT * FROM (select tb_edoresult.COMPANY,tb_edoresult.location, " & _
" F_CLMAYOR(ITEMMAST.INVEN_MAJCL,SUBSTR(tb_edoresult .select_id,1,3)) AS CL_MAYOR, " & _
" NVL((SELECT DESCRIPTION FROM MAJORCL WHERE ITEM_GROUP='SMART' AND MAJOR_CLASS=ITEMMAST.INVEN_MAJCL AND CLASS_TYPE='I' AND ACTIVE_STATUS='A'),'NONE') DESC_CLMAYOR, " & _
" F_CLMENOR(ITEMMAST.INVEN_MINCL,SUBSTR(tb_edoresult .select_id,1,3)) AS CL_MENOR, " & _
" NVL((SELECT DESCRIPTION FROM MINORCL WHERE ITEM_GROUP='SMART' AND MAJOR_CLASS=ITEMMAST.INVEN_MAJCL AND MINOR_CLASS=ITEMMAST.INVEN_MINCL AND CLASS_TYPE='I' AND ACTIVE_STATUS='A'),'NONE') DESC_CLMINOR, " & _
" tb_edoresult.select_id, substr(TB_EDORESULT.ITEM,1,10),TB_EDORESULT.FECHA, ITEMMAST.DESCRIPTION, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTRANS.ICTSET2_SS_SW='Y' AND ICTRANS.company=TB_EDORESULT.COMPANY and ICTRANS.location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND (ICTRANS.doc_type='IT' OR ICTRANS.doc_type='PO' OR ICTRANS.doc_type='RT') AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_ENT, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND doc_type='IS' AND REASON_CODE='VTCL' AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_VTA, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND doc_type='IS' AND (REASON_CODE='MERM' OR REASON_CODE=' ') AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_MERM, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND doc_type='PI' AND QUANTITY>0 AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_SOBR, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND doc_type='PI' AND QUANTITY<0 AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_FALT, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND QUANTITY>0 AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_POS, " & _
" nvl((SELECT sum(quantity) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND QUANTITY<0 AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS TOT_NEG, " & _
" f_invini(TB_EDORESULT.COMPANY,tb_edoresult.locatio n,TB_EDORESULT.ITEM,'" & strFecha1 & "') invini, " & _
" nvl((SELECT sum(quantity*unit_cost) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND doc_type='PI' AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS CTO_FALT, " & _
" nvl((SELECT sum(quantity*TO_NUMBER(LINE_COMMENT)) from ictrans where ICTSET2_SS_SW='Y' AND company=TB_EDORESULT.COMPANY and location=tb_edoresult.location AND ICTRANS.ITEM=TB_EDORESULT.ITEM AND " & _
" doc_type='IS' AND REASON_CODE='VTCL' AND ICTRANS.UPDATE_DATE BETWEEN TO_DATE('" & strFecha1 & "','MM-DD-YYYY') AND TO_DATE('" & strFecha2 & "','MM-DD-YYYY')),0) AS VTA_VTA " & _
" FROM TB_EDORESULT INNER JOIN ITEMMAST ON TB_EDORESULT.ITEM=ITEMMAST.ITEM WHERE TB_EDORESULT.COMPANY=" & CIA & " AND TB_EDORESULT.LOCATION='" & strLoc & "' AND TB_EDORESULT.FECHA = TO_DATE('" & strFecha2 & "','MM-DD-YYYY') AND ITEMMAST.ITEM_GROUP='SMART' and SUBSTR(ITEMMAST.ITEM,1,3)<>'070'" & _
") TX Where TX.TOT_POS <> 0 Or TX.TOT_NEG <> 0"
  Load frmReport

   Load frmReport
   frmReport.Show vbModal
End Sub

The way that I created the report is using a Database Field ( a txt that have the structure of what I will send them in the query of VB, this file that TTX extention)
I hope you understand my English