Solved

do crystal report by different month

Posted on 2010-09-03
7
351 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 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

18 Experts available now in Live!

Get 1:1 Help Now