Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
I have a project using a dataset that I fill dynamically from a DataContext using a SQL query. Everything works as expected except for the Crystal Report.
 
The report shows all the data perfectly, but the Date (which is my group field) is off by one day (-1) than the actual date stored in the table.
 
I have been a FoxPro programmer trying to switch to Visual Studio 2008 and I have struggled with Crystal Reports for ever. Everything is simple but there is always SOMETHING amiss.
Posted 2-Jun-12 11:17am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Comments
Andrew Alix at 4-Jun-12 20:13pm
   
Done all of the above already. The sql query I use displays correctly in a datagriview with the proper date. See my comment in Solution 1 and the code in solution 3 that clarifies the problem and the work around I used. This didn't solve the problem. It just worked around it. Thanks for you help though.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Andrew, open "Group Expert" window, select your group (schDate) in right panel, then press "Options..." On Common tab select combobox under the "The section will be printed:" to value "for each day."
I hope it will solve your problem.
  Permalink  
Comments
Andrew Alix at 6-Jun-12 13:20pm
   
Thank you. It worked perfect. That was irritatingly simple. It seems that you have to jump through a lot of simple hoops to get Crystal reports to work the way it should. FoxPro reports were a lot more simplified and versatile.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Please check SQL Query which will show results.
For example
 
SELECT name,id,dt_loss
  FROM [trial_Data]
  where dt_loss ='2012-03-01' and dt_loss='2012-03-06'
group by name,id,dt_loss
OR
SELECT  name,id,dt_loss
  FROM [trial_Data]
  where dt_loss between '2012-03-01' and '2012-03-06'
group by name,id,dt_loss
Also Tell me that what u mean date off one day is it shows wrong date(less than actual stoted in db) or total data displayed less than one day less
  Permalink  
v2
Comments
Andrew Alix at 4-Jun-12 20:14pm
   
Using your example, I group by dt_loss. The date from the database is for ex. #05/28/12#, but displays on the form as #05/27/12#.
 
Using the same query for a datagriview, it displays the date correctly as #05/28/12#, not #05/27/12# as the form shows.
 
There is a work around that I have found. See the code in my answer in solution 3.
Thanks for your reply.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Private Sub Form1_Load(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles MyBase.Load
 
' The following works perfectly. The only problem is that CrystalReportViewer1
'     uses a Group #1 Name (Group on schDate) yet it displays one day less 
'     than what is actually in the database. Ex. #May 28, 2012# displays as
'     #May 27, 2012#
'
'     Displaying Group #1 Name (schDate) and the schDate field beside one another
'     on the report, I get #May 27, 2012#, and #May 28, 2012# in each field
'     respectively. The solution was simple. Delete the Group #1 Name from the 
'     report and use schDate field in its place. The report still groups by
'     Group #1 Name but doesn't display it.

' Would still like to know why it does this though

        Dim db As TM_SchoolDataContext = New TM_SchoolDataContext
        Dim ds As New ReportDataSet 'the report I created

        Dim t As DataTable = ds.TmsSchedule 'Data table 1
        Dim s As DataTable = ds.School      'Data table 2 
        Dim sc = From tms In db.TmsSchedules _
                 Join sch In db.Schools On sch.SchoolDate Equals tms.schDate _
                 Where tms.schDate >= firstDate And tms.schDate <= lastDate _
                 Select tms.schDate, tms.schHlights, tms.schT1, tms.schT2, _
                        tms.schT3, tms.TMSReview, _
                        sch.SchoolDate, sch.HLights, sch.T1, sch.T2, sch.T3
 
        Dim r As DataRow
        Dim rs As DataRow
 
        For Each dr In sc
            r = t.NewRow()    'Start a new DataRow in ds.TmsSchedule
            r("schDate") = dr.schDate
            r("schHLights") = dr.schHlights
            r("schT1") = dr.schT1
            r("schT2") = dr.schT2
            r("schT3") = dr.schT3
            r("TmsReview") = dr.TMSReview
 
            rs = s.NewRow()
            rs("SchoolDate") = dr.SchoolDate
            rs("HLights") = dr.HLights
            rs("T1") = dr.T1
            rs("T2") = dr.T2
            rs("T3") = dr.T3
            t.Rows.Add(r)
            s.Rows.Add(rs)
        Next
 
        Dim objRpt As New rptSchoolReport
        objRpt.SetDataSource(ds)
        CrystalReportViewer1.ReportSource = objRpt
        CrystalReportViewer1.Refresh()
    End Sub
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 378
1 OriginalGriff 230
2 DamithSL 200
3 Zoltán Zörgő 149
4 Dave Kreskowiak 120
0 OriginalGriff 7,575
1 DamithSL 5,529
2 Sergey Alexandrovich Kryukov 5,279
3 Maciej Los 4,961
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 5 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100