Henryk
Posts: 63
|
| Posted: 12/16/2004, 6:54 AM |
|
I built a custom sql grid, one which does a count of entries in a log based on username. For the life of me I can not figure out how to build a search which will pull based on username and I also can not figure out how to add a start date and end date to be able to get a count of entries for just a certain time frame. Any suggestions? Thank you!
Henryk
|
 |
 |
peterr
Posts: 5971
|
| Posted: 12/16/2004, 11:38 AM |
|
Do you need to use username, or could you use the user id? Most of the time the session variable "UserID" is used for this.
For date search please see http://forums.codecharge.com/posts.php?post_id=44758
http://forums.codecharge.com/posts.php?post_id=51382
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Henryk
Posts: 63
|
| Posted: 12/16/2004, 11:58 AM |
|
The field name in the table is actually submitter. The count is grouped by submitter. That is the only kind of grouping I can do on it to get a count by user name :)
Basically, there is a log which tracks entries into a db. I want be able to do a search using that count/group query with the search filtering out start date, end date as well as submitter (we have many users so a search for just a certain username is useful). As far as start date and end date are concerned, that search in a normal table is no problem, but with custom sql I can not for the life of me get it to work, and I have not found any good documentation on how to use parameters option from the custom sql. I do know how to use the parameters, which is a different format for the standard grid builder. Thank you!
Henryk
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/16/2004, 12:43 PM |
|
Henryk,
You would write the SQL in such format:
SELECT abc FROM xyz WHERE def = {username} AND date_start >= {s_date} AND date_end <= {e_date}
The values in curly brackets {} are the parameter names that you'd need to create (username, s_date, e_date). During run-time the bracketed parameters in the SQL will be replaced with the real values based on your SQL parameter configuration.
For the 'username' parameter you can use the "UserLogin" session variable, unless your user's login is different from the username. In that case you'd probably need to use an expression that retrieves the username from the database via the CCDLookUp function, or create own session or global variable.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Henryk
Posts: 63
|
| Posted: 12/16/2004, 1:06 PM |
|
No luck, getting a sql error.
What I have is a search grid and a data grid. I created the data grid by adding a grid then under the grid properties I chose sql for the data source type and the query is this:
SELECT submitter, Count(submitter) AS 'entries'
FROM Timesheet
GROUP BY submitter
I tried plugging that where into the sql query and it did not like it. Am I plugging it into the right spot?
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/16/2004, 1:35 PM |
|
What do you mean? Where are you plugging it? The WHERE should be just the part of your SQL statement above. I don't see the WHERE clause in it.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Henryk
Posts: 63
|
| Posted: 12/16/2004, 1:46 PM |
|
Sorry, I posted the query as it worked, put the where clause where it is supposed to go :)
SELECT submitter, Count(submitter) AS 'entries'
FROM Timesheet
WHERE def = {s_submitter}
GROUP BY submitter
Did not test it with the date function yet, just taking it one step at a time. I did not know if I needed to do something anywhere else besides adding the line of sql or even if I added it properly. Sorry for the confusion.
Henryk
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/16/2004, 1:54 PM |
|
This looks good. Do you have a field name called "def" in your database? I just provided an SQL format with fake field names.
Does your SQL still work if you replace {s_submitter} with some hard-coded value?
Also, I recommend that you be very specific about the errors, your parameter configuration, etc. Without having your project it can be very difficult to help, as you are the only person who sees those errors.
Anyway, I hope that I answered your initial question.
Cheers.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Henryk
Posts: 63
|
| Posted: 12/16/2004, 2:07 PM |
|
Form: Grid capapptimesheet
Error: [Microsoft][ODBC SQL Server Driver]Syntax error or access violation (Microsoft OLE DB Provider for ODBC Drivers)
I replaced def with the field name submitter, that is the error message I got.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/16/2004, 2:12 PM |
|
Please also specify how you configure that "s_submitter" parameter.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Henryk
Posts: 63
|
| Posted: 12/16/2004, 2:17 PM |
|
I don't except that I used the search grid to add it. I do not know how to configure the s_submitter parameter except that is the default name the search builder assigned it. I tried playing around with the parameters option on the window where you add the sql (click the + to add a parameter) and the few things I tried are a negatory :) Sorry to bother you with what is probably pretty straightforward. Thanks!
Henryk
|
|
|
|
peterr
Posts: 5971
|
| Posted: 12/16/2004, 2:27 PM |
|
Henryk,
You previously wrote: "As far as start date and end date are concerned, that search in a normal table is no problem, but with custom sql I can not for the life of me get it to work, and I have not found any good documentation on how to use parameters option from the custom sql."
Thus I assumed that you know how to configure the parameters. They are not really different for normal table vs. custom SQL. Then I responsed : "The values in curly brackets {} are the parameter names that you'd need to create (username, s_date, e_date)". Without this your SQL simply cannot work and will always error out.
If you know how to setup paramaters for normal tables then there is no difference here. Just imagine that the {parameter} will be replaced with the value based on that parameter that you setup.
If you need more information about configuring parameters you can refer to the documentation and examples. Also, I recommend that you debug your SQL statement to see what's wrong. The section on SQL debugging is included in the documentation as well.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
