Solved

do crystal report by different month

Posted on 2010-09-03
7
355 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not Exists 3 74
Crystal Reports CSV data source, field types 6 137
Sum not calculating correctly 22 40
Crystal Report 13.19  not working with ASP.Net framework 4.5.2 4 71
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…

820 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