jacem
Posts: 87
|
| Posted: 11/09/2008, 4:42 PM |
|
Hi,
Am I missing something or is CCS missing the BeforeShowRow event in reprots? The event trigger exists in grid and editable grid but not reports.
I need it to skip of records I do not wish to report that can't be handled in the Where of the SQL that the report is based on. (Before displaying a row I have to look up to see if a record in another table exists and if so then skip the row in the main report depending if a field contains a certain value. I intend to set a panel to visable=false)
I am just about to redo the reprot as a grid to overcome the problem but thought I would just check first. Is it just a oversight in report events in CCS or is there some other way to trigger an event before each row is formed and displayed in reprots?
CCS 3.2.0.4 PHP Mysql.
|
 |
 |
maxhugen
Posts: 272
|
| Posted: 11/10/2008, 2:07 PM |
|
I'm just guessing here but.... I suspect that there is no BeforeShowRow event to prevent you doing just what you describe above, because the CCS report mechanism has already calculated the report totals. If you 'skip' records, then the totals displayed would be incorrect.
As I said, I'm guessing.... if this is incorrect maybe someone can set the record straight?
I am curious though, as to why you can't exclude records using the Where clause?
HTH
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
Gena
Posts: 591
|
| Posted: 11/10/2008, 2:23 PM |
|
Yes, there is no BeforeShowRow event in reports. Because there are no rows at all. There are report sections. For example, Detail section. For each section there are a few events, including BeforeShow event, that means before show current section. Take a look.
_________________
Gena |
|
|
|
jacem
Posts: 87
|
| Posted: 11/10/2008, 3:08 PM |
|
Hi maxhugen, thanks for replying.
To answer your question: I usually exclude unwanted records using the Where clause however in this case I am doing something a little more complicated. I am only excluding records if a matching record exists in another table and a field on that matching record is set to a certain value (a flag). If a matching record does not exist or the flag value is not set then I want to display the record in the main table as is.
While I suppose it is possible to construct a where clause using IN() it is not straight forward in CCS for custom Where's beyond this=that type conditions. I thought the easiest way was to do a lookup for each valid record in the main table and set the row to display or not.
The application is a price list that displays default wholesale prices for sellers but special price lists can be created for certain groups of sellers that show different prices (to the default price) for only certain products or if the flag is set, not to display certain products at all for certain groups of sellers. This is all without maintaining completely separate price lists for each seller group - keeping pricing admin low as possible.
This lookup approach works very well as a Grid and I have changed the report to be a grid of labels.
The totals being out of whac doesn't seem to me to be the reason why a beforeshowrow event is not available - but then again I probably don't understand reports well enough to make such a statement. (This para is an invitation to be straightened out quick smart! 
Jacem.
|
|
|
|
jacem
Posts: 87
|
| Posted: 11/10/2008, 3:15 PM |
|
Hi Gena, thanks for replying.
Yes - that's what got me stumped: event triggers for the entire section but not the rows within the section. I am glad I wasn't missing something.
I suspect my question comes from ignorance of how reporting has to be implemented by the clever CCS developers.
I changed the report over to a Grid of labels and works with a trigger on the beforeshowrow setting visible to false when my conditions are triggered for exclusion of the record in the table.
As usual CCS gives us a couple of ways to skin the same cat.
Cheers...Jacem.
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 11/10/2008, 3:58 PM |
|
Jacem, as you say, ...many ways to skin a cat... One thing you might not be aware of, is that when you set a Parameter (Where clause) in the Visual Query Builder, you can change the Condition Type from the default 'Parameter' to 'Expression'. This allows you to enter more complex clauses, like:
record_id NOT IN (select record_id from myOtherTable where myFlag=false)
I haven't tested this particular expression though. 
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
jacem
Posts: 87
|
| Posted: 11/10/2008, 4:11 PM |
|
Thanks Maxhugen,
I was not aware that 'expression' was talking about SQL expressions - I thought it must have been the underlying langauge expression (PHP in my case). Well that would be the way to go it is cleaner if unwanted records aren't in the record set to begin with... as long as I can construct a suitable IN statement.
I beleive you are a fellow Aussie. How's the weather where you are? 
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 11/10/2008, 4:23 PM |
|
Yep, Mid North Coast of NSW, near Port Macquarie. Drizzly and overcast....
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
