Solved

do crystal report by different month

Posted on 2010-09-03
7
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 101

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 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