Solved

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

Posted on 2003-11-13
6
796 Views
Last Modified: 2012-06-27
Hello,
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
0
Comment
Question by:Ficus
6 Comments
 
LVL 77

Expert Comment

by:peter57r
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.

Pete
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 9747893
0
 
LVL 3

Author Comment

by:Ficus
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:Ficus
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:

http://support.crystaldecisions.com/library/kbase/articles/c2003148.asp

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
CRViewer1.ViewReport

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
0
 
LVL 3

Author Comment

by:Ficus
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
   Else
    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
   Else
    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") & ")"
     Else
      '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
  Next
 
 
  '//refresh
  Me.CRViewer.Refresh
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
   Else
    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.fra_scope.Height
  Me.CRViewer.Width = Me.ScaleWidth
  Me.fra_scope.Width = Me.CRViewer.Width
End Sub
=============================================================
0
 

Accepted Solution

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

PashaMod
Cs Moderator
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now