Solved

do crystal report by different month

Posted on 2010-09-03
7
352 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Report pass parameters with script 10 64
Selection formula 7 49
Crystal Reports - group header 2 38
Crystal reports - Formula Field code need assistance with code 17 52
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…
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 …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

920 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

13 Experts available now in Live!

Get 1:1 Help Now