How to set date period at runtime from VB6 (Eg.. 'Weekly' to 'Monthly'

Posted on 2003-11-13
Last Modified: 2012-06-27
I am trying to adjust the date range or period of a group in a crystal report at run time.
I am using a .RPT file, saved without the data, as a template. I was able to figure out how to adjust the date scope of the whole report but now I want to be able to have the user adjust the summary period interval at run time - say from weekly to monthly.

I am sure this has been addressed before so If someone can provide a link to the answer and it works for me that wwould be great.

I am doing this in VB6 and Crystal Reports 8.5

I explored a bit and found a collection, ReportObject.GroupNameFields, the 'name' property had the word 'weekly' at the end of a string but I do not know if I just need to replace that part of the string or are there more factors than just this property?

Thanks in advance for any help,
Regards, Tom
Question by:Ficus
LVL 77

Expert Comment

ID: 9746066
I don't know how to adjust the grouping from VB although I guess it must be possible.
But it would be easy to add another group based on the same date field and copy the header and footer details (where required) from the existing group to the new group.  So you would base one group on weekly intervals and one on monthly intervals.  Use a parameter field to indicate which to use/suppress.  Then suppress the Group header and footer of the one you don't want based on the parameter.

LVL 100

Expert Comment

ID: 9747893

Author Comment

ID: 9749322
mlmcc, There are easier methods to change the sort order and I do not need to change the sort field.  I need to change the period in days that the report groups.  Thanks anyway though.

Thanks Pete, Not sure thats the best method but i'll consider it.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 9749849
I think I found it.  After hours on the internet...  I'm going to go test it but here is what I found:

I figure I'll delete all the groups each time the user changes the value of the group scope and then add the group with the new value to the report.  Hopefully this will work.  Maybe my sub totals and other calculated fields will be messed up?

2. Adding a New Group to the Report

For this scenario, let's say a report is created which does not contain any groups. To add a new group to the report at runtime, the following code can be used.

* Please note that using this method will not display the group name on the report.

'General Declarations
Dim Report As New CrystalReport1 'The existing report (ActiveX Designer)
Dim crxApp As CRAXDRT.Application
Dim crxDBField As CRAXDRT.DatabaseFieldDefinition

'In the FORM_LOAD event of the form
Private Sub Form_Load()

'Want to add a group which is based on the REGION field.
'Basically, accessing the first table to get the 12th field,
'which is the REGION field.
set crxDBField = Report.Database.Tables.Item(1).Fields.Item(12)

'Add the new group to the report.
' 0 - is the GroupNumber in case you add more than one group
' crxDBField - is the ConditionField
' crGCAnyValue - is the condition on which the group changes
' crAscendingOrder - is the SortDirection
Report.AddGroup 0, crxDBField, crGCAnyValue, crAscendingOrder

'Display the report
CRViewer1.ReportSource = Report

End Sub


This is from the pdf files that came with CR

Constant       Value
crGCAnnually      7
crGCAnyValue    14
crGCBiweekly     2
crGCByAMPM     18
crGCByHour       17
crGCByMinute    16
crGCBySecond   15
crGCDaily          0
crGCEveryNo     11
crGCEveryYes    10
crGCMonthly      4
crGCNextIsNo    13
crGCNextIsYes   12
crGCQuarterly      5
crGCSemiAnnually 6
crGCSemimonthly  3
crGCToNo            9
crGCToNo            8
crGCWeekly          1

Author Comment

ID: 9751209
Well I found a method that so far seems to work.  I would like to comment that Crystal Reports is an awsome tool for those how need to create reports from databases (really spruces up an application), but there code examples and documentation kind of suck.

There is some extra junk on the code pasted below that pretains to my project but the example shows a method to what I set out to do.  There may be other better solutions...

My project used a mdi child form with the CRViewer Control on it.   These objects form a collection:

Public Type CRV_FormState
    Deleted As Boolean
    ReportStyle As Integer
    ReportObject As CRAXDRT.Report
    DefaultFormula As String
End Type
Public CRV_FormState() As CRV_FormState
Public CRV_Form() As New Frm_CrView

In the above some of the properties of my struct are obtained from a database with information about my available reports (.RPT templates) as well as the default recordselectionformula and the actual file name while others are from the actual CRXReport object...

Below is the code on the actaul child form.  I always use Me.object to prevent refrencing the wrong or non init. collection member:

'***MDI Child CRViewer Form***
'TNG Nov 12, 2003
' - Includes Code to Change Scope of Crystal Reports group summary period
' - As well as over all date scope of records to include in report
' - (Does not trigger any prompts about non inclusion of function in runtime licsense)

Option Explicit

Private Sub chk_alldates_Click()
'/ Toggle all dates / specify dates
  If Me.chk_alldates.Value = 1 Then
    Me.DTP_Start.Visible = False
    Me.DTP_stop.Visible = False
    Me.lbl_date(0).Visible = False
    Me.lbl_date(1).Visible = False
    Me.DTP_Start.Visible = True
    Me.DTP_stop.Visible = True
    Me.lbl_date(0).Visible = True
    Me.lbl_date(1).Visible = True
  End If
End Sub

Private Sub Cmd_refresh_Click()
'/ Update RecordSelectionFormula based on scope of 2 DatePicker Controls
'/ Update GroupCondition (weekly, monthly etc...) based on Combobox listindex value
'/ Also place the RecordSelectionFormula in our formulafield ({@Title}) so we know the query behind the report
'/ Finally Refresh Report Viewer

  '*** CRGroupCondition                     ***
  'Constant          Value
  'crGCAnnually      7
  'crGCAnyValue      14
  'crGCBiweekly      2
  'crGCByAMPM        18
  'crGCByHour        17
  'crGCByMinute      16
  'crGCBySecond      15
  'crGCDaily         0
  'crGCEveryNo       11
  'crGCEveryYes      10
  'crGCMonthly       4
  'crGCNextIsNo      13
  'crGCNextIsYes     12
  'crGCQuarterly     5
  'crGCSemiAnnually  6
  'crGCSemimonthly   3
  'crGCToNo          9
  'crGCToNo          8
  'crGCWeekly        1
  '*** Our Combo Box Indexs -> Value        ***
  '0 = Daily
  '1 = Weekly
  '2 = Bi-Weekly
  '3 = Semi Monthly
  '4 = Monthly
  Dim LStr_DateScope As String
  Dim ff
  Dim i As Integer
  Dim LInt_NewGC As Integer
  '//Set Record Selection (two methods; one for when date is from caja table, one for date from venta table)
  If Me.chk_alldates.Value = 1 Then
    LStr_DateScope = CRV_FormState(Me.Tag).DefaultFormula
    If CRV_FormState(Me.Tag).DefaultFormula <> "" Then
      LStr_DateScope = CRV_FormState(Me.Tag).DefaultFormula & " AND "
    End If
    If CRV_FormState(Me.Tag).ReportStyle = 1 Then
      'caja madedate range
       LStr_DateScope = LStr_DateScope & "{cajas.madestamp} >= DATE(" & _ Format$(DTP_Start.Value, "yyyy,mm,dd") & ") AND {cajas.madestamp} <= DATE(" & _ Format$(DTP_stop.Value, "yyyy,mm,dd") & ")"
      'Tour date range
      LStr_DateScope = LStr_DateScope & "{ventas.tourdate} >= DATE(" & _ Format$(DTP_Start.Value,

"yyyy,mm,dd") & ") AND {ventas.tourdate} <= DATE(" & _ Format$(DTP_stop.Value, "yyyy,mm,dd") & ")"
    End If
  End If
  'set the record selection critera for report and refresh
  CRV_FormState(Me.Tag).ReportObject.RecordSelectionFormula = LStr_DateScope
  '//Adjust the date period of our groups summary
  LInt_NewGC = Me.cmb_GroupPeriodType.ListIndex
  For i = 1 To CRV_FormState(Me.Tag).ReportObject.Areas.Count
    If CRV_FormState(Me.Tag).ReportObject.Areas(i).Kind = crGroupFooter Or _
CRV_FormState(Me.Tag).ReportObject.Areas(i).Kind = crGroupHeader Then
      'We are in the Group Header or Group Footer Area
      If CRV_FormState(Me.Tag).ReportObject.Areas(i).GroupCondition <> crGCAnyValue Then
        ' The current group condition is of a date type (it is not 'AnyValue' which in our case is  
        ' anything besides a date type)
        CRV_FormState(Me.Tag).ReportObject.Areas(i).GroupCondition = LInt_NewGC
      End If
    End If
  Next i
  '//place report scope subtitle
  For Each ff In CRV_FormState(Me.Tag).ReportObject.FormulaFields
    If ff.Name = "{@Title}" Then
      ff.Text = Chr$(34) & LStr_DateScope & Chr$(34)
    End If
End Sub

Private Sub Form_Activate()
'/ Depending on the attributes of this report display the apropriate controls on the form
  If CRV_FormState(Me.Tag).ReportStyle > 0 Then
    chk_alldates.Visible = True
    cmb_GroupPeriodType.Visible = True
    'TO DO: Set DTPs to High and Low Ranges of availabe Records
    '       (not the stupid default value of the date when control was placed on form
    chk_alldates.Visible = False
    cmb_GroupPeriodType.Visible = False
  End If
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
'/ Key Preview -> Display Report Wizard on F2
  If KeyCode = vbKeyF2 Then frm_SKYRmain.Show
End Sub

Private Sub Form_Load()
'/ set combo to 'weekly' our default value
  cmb_GroupPeriodType.ListIndex = 1
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
'/ Clean up Instance of Type CRV_FormState and flag Deleted = True to Recalim on next Spawning
  Set CRV_FormState(Me.Tag).ReportObject = Nothing
  CRV_FormState(Me.Tag).Deleted = True
End Sub

Private Sub Form_Resize()
'/ Resize Scope Frame and CR Viewer Obj
  Me.CRViewer.Top = Me.fra_scope.Height
  Me.CRViewer.Left = 0
  If Me.ScaleHeight > Me.fra_scope.Height Then Me.CRViewer.Height = Me.ScaleHeight -

  Me.CRViewer.Width = Me.ScaleWidth
  Me.fra_scope.Width = Me.CRViewer.Width
End Sub

Accepted Solution

PashaMod earned 0 total points
ID: 9759509
Question closed and points refunded

Cs Moderator

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports remove alpha characters only 5 51
Mask on Parameter CR2008 28 52
Visual Studio .Net 2012 and Crystal Reports 9 68
Field in crystal 2011 is being cut off. 9 33
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question