how to make this report?

on a timesheet system, if I want a sum of a user on each project over a given period, how can I do this without creating a view? I mean creating a view will just do the sum for that given period, but not prompting the user for the period.

the table looks like this:

empid, projid, hours,closingDate

now I want the sum of the hours on two closing dates, can crystal report prompt the user for two closing dates then do the sum for each employee on each project?You want to sum the hours on 2 closing dates.
Create 2 parameters for each closing date (in 8.0: Insert | Field Object ... on the menu, then create a new parameter value for each date that the user can enter. Be sure to select Date for data type.)

You want the report to prompt the user, so use the parameters in the report and in the select statement or Select Expert. If you named your parameters Date1 and Date2: ({closingDate} = {?Date1}) or ({closingDate} = {?Date2}).

Insert a Group on the ID.
Insert a Group under that on the projid.
Sum the hours for all groups and you will get a total for each projid and a total for the id.

Format your report to make it look like you want it to.

I hope this helps!
Kymberlie|||thx kstefanski

How to make a page break...correctly

I am creating a report that uses a list control and have the break at end of
list. This does not work on all pages. I have some sections that spill over
to another page. This would not be a problem if the list control would insert
a page break prior to starting the next section. Also, it seems that some
reports that are shorter, the next section starts on the same page. Is there
some trick to making a report insert a page break where you want it to
everytime? I am using SSRS 1 w/SP2.On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> I am creating a report that uses a list control and have the break at end of
> list. This does not work on all pages. I have some sections that spill over
> to another page. This would not be a problem if the list control would insert
> a page break prior to starting the next section. Also, it seems that some
> reports that are shorter, the next section starts on the same page. Is there
> some trick to making a report insert a page break where you want it to
> everytime? I am using SSRS 1 w/SP2.
I have not used SP2 yet; however, prior to that, if you right click
the list control and go to Properties on the General tab below Page
Breaks you should be able to select 'Insert a page break after this
list.' Also, if you are using a table inside the list you should be
able to insert a page break after the table; as well, as after any
groups defined in the table. That said, sometimes the grouping in SSRS
is a little funny when it comes to lists and tables. Hope this is
helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On the list control, I do have the property checked to break after the list.
Just it does not always seem to work. I have one list control in the body of
the page. In this list control, I have many textbox controls. The list
control is tied to a datasource and I have ten records in this datasource, so
I expect 10 pages from my report. But what happens, is that records one and
two will display on a page by themselves, but record three will have part of
record four at the bottom of it, then record four finishes, and record five
will be back to a page of its own. This happens every so often and I do not
see a pattern. Hope this makes sense. Thanks for looking into this.
"EMartinez" wrote:
> On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> > I am creating a report that uses a list control and have the break at end of
> > list. This does not work on all pages. I have some sections that spill over
> > to another page. This would not be a problem if the list control would insert
> > a page break prior to starting the next section. Also, it seems that some
> > reports that are shorter, the next section starts on the same page. Is there
> > some trick to making a report insert a page break where you want it to
> > everytime? I am using SSRS 1 w/SP2.
>
> I have not used SP2 yet; however, prior to that, if you right click
> the list control and go to Properties on the General tab below Page
> Breaks you should be able to select 'Insert a page break after this
> list.' Also, if you are using a table inside the list you should be
> able to insert a page break after the table; as well, as after any
> groups defined in the table. That said, sometimes the grouping in SSRS
> is a little funny when it comes to lists and tables. Hope this is
> helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 1, 9:01 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> On the list control, I do have the property checked to break after the list.
> Just it does not always seem to work. I have one list control in the body of
> the page. In this list control, I have many textbox controls. The list
> control is tied to a datasource and I have ten records in this datasource, so
> I expect 10 pages from my report. But what happens, is that records one and
> two will display on a page by themselves, but record three will have part of
> record four at the bottom of it, then record four finishes, and record five
> will be back to a page of its own. This happens every so often and I do not
> see a pattern. Hope this makes sense. Thanks for looking into this.
> "EMartinez" wrote:
> > On Mar 1, 7:06 am, Wannabe <Wann...@.discussions.microsoft.com> wrote:
> > > I am creating a report that uses a list control and have the break at end of
> > > list. This does not work on all pages. I have some sections that spill over
> > > to another page. This would not be a problem if the list control would insert
> > > a page break prior to starting the next section. Also, it seems that some
> > > reports that are shorter, the next section starts on the same page. Is there
> > > some trick to making a report insert a page break where you want it to
> > > everytime? I am using SSRS 1 w/SP2.
> > I have not used SP2 yet; however, prior to that, if you right click
> > the list control and go to Properties on the General tab below Page
> > Breaks you should be able to select 'Insert a page break after this
> > list.' Also, if you are using a table inside the list you should be
> > able to insert a page break after the table; as well, as after any
> > groups defined in the table. That said, sometimes the grouping in SSRS
> > is a little funny when it comes to lists and tables. Hope this is
> > helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
Unfortunately, SSRS is a bit flaky sometimes when it comes to grouping
and page breaks. The only other thing I can think of is to try to put
the textbox controls inside a rectangle and then inside a list
control.
Regards,
Enrique Martinez
Sr. SQL Server Developer

How to load database name at runtime?

I m creating a report using data view.
its working fine. BUt i want to load db name at run time, I tried a sample and which does it very fine.

But when i use the same code in my project, its not loading the database name at runtime, old name is being used.

What i m doing is like this in visual basic.net

rptCustomersOrders.Load("..\CustomerOrders.rpt")

' Set the connection information for all the tables used in the report
' Leave UserID and Password blank for trusted connection
For Each tbCurrent In rptCustomersOrders.Database.Tables
tliCurrent = tbCurrent.LogOnInfo
With tliCurrent.ConnectionInfo
.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"
End With
tbCurrent.ApplyLogOnInfo(tliCurrent)
Next tbCurrent

ReportViewer.ReportSource = rptCustomersOrders

Plz. tell me ; what property i have to set, to load the database name at runtime.

Thanks in adavancehttp://www.dev-archive.com/forum/archive/index.php/t-293276.html|||try adding

.location = .name

after

.ServerName = ServerName
.UserID = ""
.Password = ""
.DatabaseName = "Northwind"

so that it will erase previous location details ....

if u have any other methords ... plzz let me know too dude .........|||Here's my code which you can use to change Database Name, User Name, Password and SQL Server Name at run time. This code is written in VB6 and works with Crystal Reports 10.

Copy this code in a Module in VB and I used frm Report where I have placed crystal report viewer control.

Public Sub DisplayReport(ReportFileName As String)
Dim app2 As CRAXDRT.Application
Dim rap As CRAXDRT.Report

Set app2 = New CRAXDRT.Application
Set rap = New CRAXDRT.Report
Set rap = app2.OpenReport(ReportFileName)

rap.EnableParameterPrompting = False

For Each CRXDatabaseTable In rap.Database.Tables
STORED_DATABASE_NAME = CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") ''JUST TO READ DATABASE NAME STORED IN CRYSTAL REPORT FILE
Exit For
Next

For Each CRXDatabaseTable In rap.Database.Tables
CRXDatabaseTable.ConnectionProperties("Data Source") = SQLServerName
CRXDatabaseTable.ConnectionProperties("INITIAL CATALOG") = DatabaseName
CRXDatabaseTable.ConnectionProperties("USER ID") = UserName
CRXDatabaseTable.ConnectionProperties("PASSWORD") = LoginPassword
If Not CRXDatabaseTable.TestConnectivity Then
MsgBox "Error connecting to database table." & vbCrLf & "Please contact Adminstrator to validate Report Database"
Exit Sub
End If
Next

rap.EnableParameterPrompting = True
On Error Resume Next
Ret = rap.SQLQueryString 'THIS WILL CALL USER TO INPUT PARAMETERS FOR REPORT (IF ANY)
On Error GoTo 0

If Err.Number = -2147206395 Then 'CHECK IF USER PRESSED CANCEL
Err.Clear
Exit Sub
ElseIf Err.Number <> 0 Then 'CHECK IF ANY OTHER ERROR OCCURED
MsgBox Err.Number & " : " & Err.Description
Err.Clear
Exit Sub
End If

If UCase(STORED_DATABASE_NAME) <> UCase(DatabaseName) Then
''IF DATABASE WHILE CREATING REPORT WAS DIFFERENT THEN THE CURRENT DATABASE.
''UPDATE DATABASE NAME IN CONNECTION STRING, ELSE CONNECTION STRING READS DATA FROM DATABASE USED WHILE CREATING REPORT
DoEvents
rap.EnableParameterPrompting = False
Ret = rap.SQLQueryString
Ret = Replace(Ret, STORED_DATABASE_NAME, DatabaseName)
rap.SQLQueryString = Ret
rap.SQLQueryString = Ret ''IT DOESNOT WORK IF I SET THIS ONCE (PARAMETERS ARE NOT SET). :-( CRYSTAL BUG
DoEvents
End If

frmReport.Show
frmReport.CrystalActiveXReportViewer1.ReportSource = rap
frmReport.CrystalActiveXReportViewer1.ViewReport
frmReport.CrystalActiveXReportViewer1.Refresh
End Sub|||Sorry forget to add this declaration in my above function:

Dim CRXDatabaseTable As CRAXDRT.DatabaseTable