Solved

do crystal report by different month

Posted on 2010-09-03
7
354 Views
Last Modified: 2012-05-10
Hi,
From my crystal report,
I want to be able to change the date from oracle select statement

*(SELECT ADD_MONTHS(sysdate, -10) FROM DUAL)*

select sum(invoice_total), c.com_name,c.com_area from invoice i, customers c
where invoice_date > (SELECT ADD_MONTHS(sysdate, -10) FROM DUAL) and c.tid=i.com_id
group by c.com_name,c.com_area
order by c.com_name ;

is crystal report able to adding two buttons (previous, next month)?

Thank you!
Francis SZE
MonthlyReport.rpt
Untitled.png
0
Comment
Question by:fsze88
  • 5
7 Comments
 
LVL 15

Author Comment

by:fsze88
ID: 33602291
Hi,
a way to change crystal report sql statement on vb.net code?
Thank you!

Private Sub nextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextButton.Click
        Dim oradb As String = "Data Source=kennam;User Id=kennam;Password=kennam007;" ' VB.NET
        Dim conn As New OracleConnection(oradb)

        Dim reportdoc As New ReportDocument

        reportdoc.Load("U:\kennam\cr\MonthlyReport.rpt")
        m += 1
        MessageBox.Show(m)
        conn.Open()


        Dim sql As String = "select sum(invoice_total), c.com_name,c.com_area from invoice i, customers c " & _
            "where invoice_date between (SELECT ADD_MONTHS(sysdate, -" & m & ") FROM DUAL) and (SELECT ADD_MONTHS(sysdate, -" & (m + 1) & ") FROM DUAL) and c.tid=i.com_id " & _
            "group by c.com_name,c.com_area " & _
            "order by c.com_name "


        Dim cmd As New OracleCommand(sql, conn)
        cmd.CommandType = CommandType.Text
        Dim dr As OracleDataReader = cmd.ExecuteReader()
        Dim ds As New DataSet()
        Dim adapter As New OracleDataAdapter(sql, conn)
        adapter.Fill(ds)

        dr.Close()
        conn.Close()

        '        

        CrystalReportViewer1.ReportSource = reportdoc

    End Sub

Open in new window

0
 

Expert Comment

by:npotter
ID: 33602888
Use to Seperate Commands
for Previous Month ,
last_day(add_months(sysdate, -1))
For Next Month,
(last_day(add_months(sysdate, 1)) )
in your Query & In Crystal report If Else Condition.
Like this Eg Code
If {?Myparameter} = "Previous Month' then
Command.YOURCOLUMN
else
Command1.YOURCOLUMN
0
 
LVL 15

Author Comment

by:fsze88
ID: 33602961
are you means adding parameter on oracle select statement?
how it works?

like this?

select sum(invoice_total), c.com_name,c.com_area from invoice i, customers c
where invoice_date between {?@dateForm} and {?@dateTo} and c.tid=i.com_id
group by c.com_name,c.com_area
order by c.com_name ;

dateForm = (SELECT ADD_MONTHS(sysdate, -2) FROM DUAL)
dateTo = (SELECT ADD_MONTHS(sysdate, -1) FROM DUAL)

it given an error
Untitled.png
Untitled2.png
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 15

Author Comment

by:fsze88
ID: 33603469
what's the problem of vb.net code, it doesn't refresh the new data from oracle
Please help
Private Sub nextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextButton.Click


        Dim ParamterFields As New CrystalDecisions.Shared.ParameterFields

        Dim ParameterField1 As New CrystalDecisions.Shared.ParameterField

        Dim ParameterField2 As New CrystalDecisions.Shared.ParameterField

        Dim ParamterDescreteValue1 As New CrystalDecisions.Shared.ParameterDiscreteValue

        Dim ParamterDescreteValue2 As New CrystalDecisions.Shared.ParameterDiscreteValue

        m -= 1
        MessageBox.Show(m)
        ParameterField1.ParameterFieldName = "dateForm"

        ParameterField2.ParameterFieldName = "dateTo"

        ParamterDescreteValue1.Value = "(SELECT ADD_MONTHS(sysdate, " & m & ") FROM DUAL)"

        ParamterDescreteValue2.Value = "(SELECT ADD_MONTHS(sysdate, " & (m + 1) & ") FROM DUAL)"

        ParameterField1.CurrentValues.Add(ParamterDescreteValue1)

        ParameterField2.CurrentValues.Add(ParamterDescreteValue2)

        ParamterFields.Add(ParameterField1)

        ParamterFields.Add(ParameterField2)


        CrystalReportViewer1.ParameterFieldInfo = ParamterFields
        CrystalReportViewer1.Show()

    End Sub

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33604050
Is the report saved with data?

mlmcc
0
 
LVL 15

Author Comment

by:fsze88
ID: 33605199
oh.. sorry, I solved as code attachment
Private Sub customerMonthlyReportsForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        Dim reportdoc As New ReportDocument
        Dim logoninfo As New TableLogOnInfo

        m = -1

        reportdoc.Load("U:\kennam\cr\MonthlyReport.rpt")

        Dim myTables As Tables = reportdoc.Database.Tables
        For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables

            Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
            '            For Each Table In reportdoc.Database.Tables
            myTableLogonInfo = myTable.LogOnInfo
            myTableLogonInfo.ConnectionInfo.ServerName = "kennamora"
            myTableLogonInfo.ConnectionInfo.DatabaseName = "kennam"
            myTableLogonInfo.ConnectionInfo.UserID = "kennam"
            myTableLogonInfo.ConnectionInfo.Password = "kennam007"
            myTable.ApplyLogOnInfo(myTableLogonInfo)
        Next myTable

        CrystalReportViewer1.ShowRefreshButton = False
        CrystalReportViewer1.ReportSource = reportdoc
    End Sub

Open in new window

0
 
LVL 15

Accepted Solution

by:
fsze88 earned 0 total points
ID: 33605208
.
Private Sub nextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextButton.Click
        Dim reportdoc As New ReportDocument

        Dim ParamterFields As New CrystalDecisions.Shared.ParameterFields

        Dim ParameterField1 As New CrystalDecisions.Shared.ParameterField

        Dim ParameterField2 As New CrystalDecisions.Shared.ParameterField

        Dim ParamterDescreteValue1 As New CrystalDecisions.Shared.ParameterDiscreteValue

        Dim ParamterDescreteValue2 As New CrystalDecisions.Shared.ParameterDiscreteValue

        reportdoc.Load("U:\kennam\cr\MonthlyReport.rpt")

        m -= 1
        ParameterField1.ParameterFieldName = "dateForm"

        ParameterField2.ParameterFieldName = "dateTo"

        ParamterDescreteValue1.Value = "(SELECT ADD_MONTHS (sysdate, " & m & ") FROM DUAL)"

        ParamterDescreteValue2.Value = "(SELECT ADD_MONTHS (sysdate, " & (m + 1) & ") FROM DUAL)"

        ParameterField1.CurrentValues.Add(ParamterDescreteValue1)

        ParameterField2.CurrentValues.Add(ParamterDescreteValue2)

        ParamterFields.Add(ParameterField1)

        ParamterFields.Add(ParameterField2)

        '        MessageBox.Show(m)
        '        MessageBox.Show(ParamterDescreteValue1.Value)
        '        MessageBox.Show(ParamterDescreteValue2.Value)




        Dim myTables As Tables = reportdoc.Database.Tables
        For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables

            Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
            '            For Each Table In reportdoc.Database.Tables
            myTableLogonInfo = myTable.LogOnInfo
            myTableLogonInfo.ConnectionInfo.ServerName = "kennamora"
            myTableLogonInfo.ConnectionInfo.DatabaseName = "kennam"
            myTableLogonInfo.ConnectionInfo.UserID = "kennam"
            myTableLogonInfo.ConnectionInfo.Password = "kennam007"
            myTable.ApplyLogOnInfo(myTableLogonInfo)
        Next myTable


        CrystalReportViewer1.ParameterFieldInfo = ParamterFields
        CrystalReportViewer1.ReportSource = reportdoc
        CrystalReportViewer1.Refresh()


    End Sub

Open in new window

0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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