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 table columns invisible

Hope this is the correct forum for this question. I have set up membership and roles, and have a login function. I am using a grid view and a form view to show some information that is unique to each user. In particular, I want the grid view to show the user’s client list (client first name, last name and email), and the form view to allow input by the user of client names and emails.

I have set up the grid view and connected it to a table in my data base. This is a table that includes UserName (which is also in the aspnet_Users table), Client ID (which gets set automatically by the program), clientFirstName, ClientLastname, and ClientEmail. I have a select statement of:

SELECT * FROM [Clients] WHERE ([UserName] = @.UserName)

I have checked off “Generate Insert, Update, and Delete Statements” and optimistic concurrency.

In the grid view, I’ve enabled editing and deleting in the menu task bar, and I’ve edited the columns to make ClientID and UserName “false” for the visible property (I don’t want them to show up when the user is viewing the web page).

But the problem is, when I run the application, when I try to edit a line and hit update, it won’t take the change. This does not happen if I set the UserName visible property to “true” (and so have UserName show up).

For the form view, I similarly want to have UserName and ClientID be invisible to the user.

I'm using Visual Studio 2005 Pro, and SQL Developer Edition.

Any tips on how I might do this would be appreciated! Thanks.

Tom,

You want to post this to asp.net forum. Basically, if you just bind your data resultset to a gridview, all columns will show up. To suppress the column, you need to modify the grid's column property or don't ask for the column in your sql query.|||

OJ,

Thanks much for your reply. I'll post on asp.net. It's strange, I tried to suppress the UserName column (through using "false" for the visible property), but when I do that I'm not able to edit entries when I run the application. And if I don't ask for the UserName column with the sql query, I run into the same problem. I tried using "UserId" instead of UserName in the query, but when I ran the app I got an error message that it's not part of the profile common class. I'll keep trying, thanks again.

Tom

|||Tom,

It's worth checking out info here. There are lots of info and examples for you to pick up.
http://www.asp.net/QuickStart/aspnet/doc/ctrlref/data/gridview.aspx

g'luck.|||Great, thanks OJ! I'll delve into that and see what I can come up with.

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server

Please help

thanks

You have to add you second server to linked servers (server objects-> linked servers-> right click new linked server) and next in your query use server name in table name like:

select * from [server].[database].dbo.[tablename]

or use open query

Select * from OPENQUERY([servername],'SELECT * from [database].dbo.[tablename]')

second is faster but use it only if your query will hit only data on second server.

Thanks

sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help

thanks

there are many ways to access other server.database.table

(a) Configure LinkedServer

b)OPENDATASOURCE

(c) OpenQuery

read about this in BOL

Madhu

How to make hiding the parameter row as default?

When I ran the report from the browser (not from the preview when designing the report), the parameters and the View Report button still displayed. I noticed clicking a double arrow button can hide or show the parameter section . How can I make hiding the parameter section as default? Currently showing the section is the default.

Thanks.

DanYeung

At the end of the URL, just set the paramaters to false, like so:

http://375868d/ReportServer/Pages/ReportViewer.aspx?/Exhumation/ops_samples_all&rcarameters=false

What is with the smiley face? It should be Parameters=false, not arameters=false...

|||

Set the parameter to false can only hide the value of the parameter. I want to hide the who section that shows all parameter names and values, and also the Preview Report button.

Thanks.

DanYeung

|||

I figured it out and wanted to share. In the Report Parameters dialog, check the Hidden checkbox for all parameters. The whole section will be hidden as default.

DanYeung

How to make distributed query by SQL Express

I am going to make a distributed query, but in the query design view I cannot add the tables from another server, why?

hi,

AFAIK, the designers only show objects local to the connected server (and database)... but you can easely insert the required code pointing to remote linked servers...

regards

|||Thanks, i will try.

how to make a textbox invisible

hi
how can i make a textbox invisible ?
here is the scenario.
i have a textbox to remind user to select some parameters to view the graph.
once the user selects the paramteters - the graph shows up.
once the graph shows up -the textbox should disappear.
how can i do thatright click the text box, select Properties
click Advanced >>
in the Advanced Textbox Properties
click the Visibility tab
in the Initial visibility radio button group check the Visible radio button
and below check the Visibility can be toggled by another report item
in the Report item drop down list pick your chart
i haven't tried this, it's off the top of my head, but i hope it's a good
first step,
regards,
kowlasky
"RP" wrote:
> hi
> how can i make a textbox invisible ?
> here is the scenario.
> i have a textbox to remind user to select some parameters to view the graph.
> once the user selects the paramteters - the graph shows up.
> once the graph shows up -the textbox should disappear.
> how can i do that|||But we can only see textboxes in the dropdown of "visibility can be toggled
by another report item"
:-( any other suggestions ''''
Thanks
"kowalsky" wrote:
> right click the text box, select Properties
> click Advanced >>
> in the Advanced Textbox Properties
> click the Visibility tab
> in the Initial visibility radio button group check the Visible radio button
> and below check the Visibility can be toggled by another report item
> in the Report item drop down list pick your chart
> i haven't tried this, it's off the top of my head, but i hope it's a good
> first step,
> regards,
> kowlasky
>
> "RP" wrote:
> > hi
> > how can i make a textbox invisible ?
> > here is the scenario.
> > i have a textbox to remind user to select some parameters to view the graph.
> > once the user selects the paramteters - the graph shows up.
> > once the graph shows up -the textbox should disappear.
> > how can i do thatsql

how to lock a row when perform SELECT statement

Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that?
i tried

SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@.type;

but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..

Please help me with this..thank youCheck out this page, it shows you how to do what you're asking:

http://www.sql-server-performance.com/at_sql_locking.asp|||What you are asking to do is a sort of pessimistic lock, and this can be very dangerous in a web application. What happens e.g. when a user SELECTs the record and then their browser crashes?

You can read about concurrency control here:ASP.NET Optimistic Concurrency Control

Maybe if you explain more about what you are trying to do we can offer some suggestions.

Terri|||Thanks for helping ,

First of all i have a table to stores running number

tblRunningNumber
ID Desc LatestNumber
1 ModuleA 01
2 ModuleB 12

Then i wanna gat the latest runningnumber from tblRunningNumber n stores it in to my other table. For example i wanan get the latestNumber for ModuleA and stores it into ModuleA table.

To i need to have a stored procedure to lock the ModuleA rows n then update the latestNumber from 01 to 02 and at the same time update the 01 in my ModuleA database Table.

That y i need to lock the row..my issues is that when i do the select * statement it just hang there...until i close the connection or transactin is commited...

that the whole senarios that i m doing..can u give me any good suggestion on that coz i cannot use asp.net to control the running number so i decided to use storedprocedure .

Thanks alot.|||"can be very dangerous in a web application"

That's an understatement! Especially with a relatively unguarded select statement!|||But to ensure consistency, 'sometimes' you will have to enforce locking for the duration of a transcation. Can you tell us what would happen when a user SELECTs the record and then their browser crashes? The SELECT statement uses a HOLDLOCK. Isn't there a way to handle this crash scenario?|||

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

Also, the client crashing, disconnecting, going off-line, getting hit by lightning, etc means nothing. The server will continue executing it's page to completion no matter if the client is there or not, unless of course the page crashes itself, in which case the connection will be closed, and SQL Server will rollback it's transactions for those connections. Of course if the web server machine takes a complete dump (power loss, blue screen of death, etc), then SQL Server (if remote from the web server), will hold the lock until it realizes the connection is dead, and I'm not sure how quickly SQL Server detects a dead connection, but I'm guessing it'll be longer than you'd want in a web farm type environment.

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

How to know which fields are identity?

Hi. I need to know if a table has identity fields. Is there any view to get
that information?
Regards,
Diego F.SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
column_name,'IsIdentity')=1 ;
David Portas
SQL Server MVP
--|||Thanks a lot!
Regards,
Diego F.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
> SELECT table_schema, table_name, column_name
> FROM information_schema.columns
> WHERE COLUMNPROPERTY(OBJECT_ID(
> QUOTENAME(table_schema)+'.'+QUOTENAME(table_name)),
> column_name,'IsIdentity')=1 ;
> --
> David Portas
> SQL Server MVP
> --
>|||I found a problem with that. It doesn't work if I try to use a table from
other database. Is there another possibility?
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribi en el mensaje
news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Thanks a lot!
> --
> Regards,
> Diego F.
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escribi en el
> mensaje news:1123150974.908211.221990@.g14g2000cwa.googlegroups.com...
>|||SELECT
U.name AS table_schema,
T.name AS table_name,
C.name AS column_name
FROM database_name.dbo.syscolumns AS C
JOIN database_name.dbo.sysobjects AS T
ON C.id = T.id
JOIN database_name.dbo.sysusers AS U
ON T.uid = U.uid
WHERE C.status & 0x80 = 0x80 ;
David Portas
SQL Server MVP
--|||Type 'USE AnotherDatabaseName' before that SELECT command.
Or 'AnotherDatabaseName.information_schema.columns' instead of 'information_
schema.columns'.
Diego F. wrote:
> I found a problem with that. It doesn't work if I try to use a table from
> other database. Is there another possibility?
> --
> Regards,
> Diego F.
>
> "Diego F." <diegofrNO@.terra.es> escribi en el mensaje
> news:OGuMt9NmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>
>|||> 'AnotherDatabaseName.informati=ADon_schema.columns' instead of 'informati=
on_schema.columns'.
That gets you the column names but unfortunately the metadata functions
I used are scoped to the current DB so the WHERE clause won't find the
correct columns.
--=20
David Portas=20
SQL Server MVP=20
--|||David Portas wrote:
>
> That gets you the column names but unfortunately the metadata functions
> I used are scoped to the current DB so the WHERE clause won't find the
> correct columns.
Ah, you are right indeed.|||Thank you all. I'll try to deal with that.
Regards,
Diego F.
"Sericinus hunter" <serhunt@.flash.net> escribi en el mensaje
news:VarIe.953$646.265@.newssvr22.news.prodigy.net...
> David Portas wrote:
> Ah, you are right indeed.