Smarty Forum Index Smarty
The discussions here are for Smarty, a template engine for the PHP programming language.
Banded Report Generator
Goto page Previous  1, 2, 3, 4, 5, 6, 7  Next
 
Post new topic   Reply to topic    Smarty Forum Index -> Plugins
View previous topic :: View next topic  
Author Message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Thu Jun 23, 2005 6:16 pm    Post subject: Great job! Reply with quote

Hi guys,

Thanks for the solution to this vexing issue and the warning about using strip_slashes() on the buffer.

I think this plugin set is a really cool learning tool for new/advancing Smarty plugin programmers.

Now, on to the next question that may result in weeks of struggle and toil. Or you might just tell me, it's not possible: Shocked

How can I get a count of the *number* of groupings at one level above that grouping? That is, I want the to show the count of the unique *groups* encountered inside the header of the previous group. Note that I already know how to get the count of unique *records" encountered in a group - that is simply the $count.group tag. What I want to count is the number of "groups in a group" not the number of "records in a group".

To illustrate:
I have a recordset with 3 fields. I want to keep track of facial expressions over time.
['name']=>"joe"
['Event']=>"smile"
['Time']=>1200

['name']=>"joe"
['Event']=>"smile"
['Time']=>1300

['name']=>"joe"
['Event']=>"frown"
['Time']=>1400

Let's say I'm reporting on the number of times joe smiles in a day. If I want to show *that*, I can just use a normal $count.Event tag inside a report_header group block.

But, let's say I want to report on how many *different* facial expressions joe had during the day. How do I get at that number? (In this case, 2.) Is it possible within the current plugin structure?

It seems to me that it is meta-data that only arises from the grouping which is not recorded at the record level. AFAICT, summary/meta data is not kept with regards to groups; it is only kept with regards to records.

Can this information be calculated, stored, and ultimately assigned to smarty scope by using a technique similar to the one used for record level meta-data? Is the report_header/report_footer blocks where this would happen? I.e., Would I look to model these a posteriori group stats on the ones collected for records in the report block?

Thanks for any feedback, assumming you aren't too fed up with this thread already!
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Thu Jun 23, 2005 6:42 pm    Post subject: Re: Great job! Reply with quote

sophistry wrote:
I think this plugin set is a really cool learning tool for new/advancing Smarty plugin programmers.


That's very pleasing to know since it was one of the motivations for writing and providing these in the first place Smile Thanks!

sophistry wrote:
How can I get a count of the *number* of groupings at one level above that grouping? That is, I want the to show the count of the unique *groups* encountered inside the header of the previous group. Note that I already know how to get the count of unique *records" encountered in a group - that is simply the $count.group tag. What I want to count is the number of "groups in a group" not the number of "records in a group".


I've been thinking about ways to deal with some of the issues of a full reporting solution including some of the ideas you had and some of the ones I'm familiar with. Its not easy with the current implementation or with Smarty in general because of the way things are processed and described. Smarty processes as it goes and it doesn't keep structure as it goes (nor is it aware of structure prior to processing) so that means you can't know everything beforehand unless you process it first and then pass it through (where it is processed again, but with summaries and groupings already known). The problem with this is that it means you would have to define the structure of the recordset twice -- once in the pre-calculation and once in the presentation. I have some ideas on how to overcome this to some degrees but nothing satisfactory and nothing that I have time to work on for now. Another thing I am tossing around is a set of tags that more explicitly reflected grouping by making it a block tag of its own which would contain header/footer/detail or other grouping tags. Again, there are issues because of the way Smarty processes so I am still at odds. Yet another idea was to implement the recordset grouping features as a special assigned object and then using normal foreach loops in the templates instead of specialized tags. Again, it requires more consideration than I have time for at the moment and in someways, it is a little superfluous -- I don't want to re-invent the spreadsheet nor crosstabs (which I see as the modern replacement for banded reports).

That said, there is a possiblity that you can exploit the current setup a little bit now that we have added in the header summaries; however, it would only apply to headers (at least, that's what occurs to me on a cursory glance). As you know, I've structured the header group buffer as an associative array of arrays. The size of each of these sub-arrays should tell you the number of groupings that occurred for that header (ie: grouping) level. Unfortuantely, I can't gaurantee that the order of the groupings are accurate but that is okay as the grouping order is already known and the sub arrays are accessed by the group name. Hopefully that will be enough to get you going. On the face of it, it shouldn't be too hard to code--but then again, the last time I said that I was suffering from too much huberis! [edit: indeed, on second thought, you may need to track group changes more directly in __process_next()]

One of the big issues I am having is how to refer to things. Right now I have the $sum, $avg (etc) vars that are autopopulated...how should we refer to a groups totals? Or the report totals? Again, I have ideas but comments are always welcome.
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Thu Jun 23, 2005 8:54 pm    Post subject: Reply with quote

Quote:
[edit: indeed, on second thought, you may need to track group changes more directly in __process_next()]


Ah yes. The arrays built by the report_header block are not grouped by the nesting group. It is just a straight array so at the end of the line there is no indication of where a group one level up broke.

So, how do I get the data from the report block (where the group breaks are calculated) back down to the header block?

I would think this code would be a good place to store some additional data points about group counts:

[php:1:4a5506af3c] if ($record['last']) {
$group[$_group]['last'] = true;
} else if (($record['curr'][$_group] == $record['next'][$_group]) AND ($record['curr'][$prev_group] != $record['next'][$prev_group])) {
$group[$_group]['last'] = true;
} else if (($record['curr'][$_group] != $record['next'][$_group])) {
$group[$_group]['last'] = true;
} else {
$group[$_group]['last'] = false;
} [/php:1:4a5506af3c]

But, when I add this code it doesn't work: (which i expected to be accessible via a simple {$groupcount} tag within a report_header block)

[php:1:4a5506af3c]if ($group[$_group]['last']==TRUE) {
// 20050623 stuff groupcount only on last
// reach into the report_header block and get
// the count for this group in the header buffer
// doesn't work as expected
$group[$_group]['stats']['groupcount'] = count($params['header']['group']['buffer'][$_group]);
}[/php:1:4a5506af3c]

It just increments 0, 1, 2.

Any ideas?
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Fri Jun 24, 2005 5:00 am    Post subject: Reply with quote

Hey Sophistry.

I tried an experiment but it is not successful either. My strategy was to not use [$group]['stats'] but rather [$group]['count']. I started by initalizing all the groups to 0 (in __initialize) and then incrementing [$group]['count'] on the first record of every group. Finally, in header (and footer) I assign [$params['group']]['count'] to 'groupcount'. This didn't work. Groups are processed as they are encountered. There is no way currently for a group to know how many siblings it has. From what I can tell, the best we can do for now is track the group count but in the parent group and have it report the results there because only when the parent finishes will we know the group count. Rather unsatisfactory.

Our discussion has got me thinking about more potential abuses of the block functionality Smile One thing that occurred to me is that we don't necessarily have to start processing our iterations right away. By that I mean, on the first call into {report} we can setup a flag that says we are in a preparation mode. The remaining blocks would detect this flag and if encountered, instead of doing normal processing would instead pop information about themselves onto a stack setup in the main {report} tag. The next time we enter {report} we would then have information about the layout of the plugins and what they expect so we could then proceed to precalculate, build proper hierarchies and potentially limit the number of calculation done to only those required. We would then start a normal recordset iteration but would have all sorts of precalculated goodies in hand. I like this idea but I have to admit that it is a rather exotic use of the block plugin functionality.

It also implies more setup, more syntax and a different structure for the related tags. I have been trying to figure the tag structure out and while still early, I do have a sample of what it might look like. Of course, I probably won't be working on it for awhile so in the meantime I wonder if you think it is a good idea or if you have any comments. Here is the sample of the tag structure, however, I want to note that I am not at all happy with the field and field expression syntax:
Code:

{report recordset=$data record=rec fields="lastyear:year-1,sum.sales:sum(sales)"}
   {report_header}
   {report_header}
   {report_group on="region" sort="asc"}
      {report_group on="year" sort="desc"}
         {report_group on="quarter" sort="asc" interval=2 fields="nextyear:year+1,sales:0" sum="nextyear,year"}
            {report_header}
            {/report_header}

            {report_detail}
            {/report_detail}

            {report_footer}
            {/report_footer}
         {/report_group}
      {/report_group}
   {/report_group}
   {report_footer}
   {report_footer}
{/report}

As you can see, header grouping levels are implied by the group tags which specify the sorts of things that the calculation engine will need. Further, group nesting is specified as a consequence of the layout of the {group} tags themselves instead of as a predetermined attribute in the {report} tag (which would be a default "ALL" group). It is assumed that if a parent defines a field, all siblings can see it unless they override it. Then I suppose there might be a syntax to access parent values...but I haven't thought about all those details to any degree. Its one trick to figure out a way to calculate everything, it is another, I think, to have a nice syntax to allow the display to access them. One idea was to introduce some sort of {report_field} tag but I think I need to step back and/or get some input on all of this.

Sheesh, have I ever been long winded in this thread. Moreso than usual, even.
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Mon Jun 27, 2005 5:39 pm    Post subject: coupla thoughts on the experiment... Reply with quote

IMO, the main idea behind this plugin (besides the pedagogical) is to push some level of database querying to the mythical "smarty template designer." Also, it can lessen some of the repetetive things a PHP coder has to do by allowing them to focus on providing a simple recordset array that the plugin organizes.

It's an interesting area because the interface between reporting and presentation is not always clear cut. Sometimes the designer needs control of the presentation of the end data based on some other data in the set. For instance, I want to present a table of numbers and show the max for each row in bold. Here's a snippet:
[php:1:038e6bbab0]
{assign var="max" value=$myarray|@max}
// then inside the foreach loop
{if $max eq $currentforeachvalue}
//bold it
{/if}
[/php:1:038e6bbab0]
This is a presentation function that properly exists under the designer's control; you wouldn't want to send formatted HTML to the template, right?

Here's where I think the current banded report plugin set fits into the smarty universe: it lets template designers create summary reports with ad hoc sorting, grouping, and access to simple aggregate (meta) data.

PHP Coders use:
SQL

    server side, no presentation
    get data, sort it, summarize it
    complete language with standardised (core) syntax


Array functions

    prepare array for use in smarty templates - foreach, section
    sort, summarize data
    well-known functionality


Custom functions

    any type of pre-processing before sending to template
    requires specification of array structure being sent to template so that template designer may manipulate presentation


Template Designers use:
foreach

    easy presentation of flat or nested array
    loop over single assoc array
    access key, value
    access to meta data first, last, iteration, total

section

    easy presentation of flat or nested arrays
    loop over multiple (parallel) arrays
    no access to key in assoc array ( true Question)
    access to meta data index_prev, index_next, first, last, iteration, total
    flexible array traversal (start, step, max parameters, also reverse traversal)

report

    easy presentation of flat/recordset array
    allows flat listing (report_detail) or culled/summarised listing (report_header/footer)
    allows in template sort definition
    allows in template grouping/summarizing (report_header/footer)
    access to meta data count, sum, avg per recordset or per group


It's not a complete list, but it gives an idea of why this plugin is needed and useful - it fills a gap.

The problem I see is you don't want to recreate SQL in a smarty block plugin unless there is some really clear subset of functionality that is needed. What exactly is the "template designer" going to do with a custom query syntax embedded in the plugin? I think the PHP coder in the equation should be able to handle most of the queries and prepare the arrays for use.

Also, I think the "meta-count" issue really wants a SQL JOIN solution. I realized that what my example with the facial expressions defined a single table that is properly 3 tables: person, expression, event. Getting to the count of events per person is then a simple JOIN. Supplying that functionality in the report_plugin for a flat recordset means you have to generate a primary/foreign key based on the plugin parameters. Again, more SQL in the template - which I think is a bad direction.

Yes, long-winded, but hopefully, precise! Very Happy
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Mon Jun 27, 2005 6:26 pm    Post subject: Reply with quote

I think you hit the nail on the head -- your summary is very succinct and I tend to agree with pretty much all of it. The only thing I would clarify is that everything that we do in the report plugin can be done better in a SQL query (partiuclarly providing summary information at any level) -- with the caveat that it would require either that the designer issue the SQL (unacceptable) or that the backend code and the design be tightly coupled which somewhat negates the abstraction and in both cases would require more template logic to support the group handling. So I agree that allowing the designer to regroup, sort and summarize an existing dataset has merit. I would also add that if implemented fully, {report} could also allow for generic looping that is more obtuse to implement in Smarty loops; for example, specifiying an interval value on a grouping expression is far simpler IMO than manually doing a modulous or tracking iterations.

As to upgrading {report} to better support these features, I can say that I have done some more work despite the fact that I should be doing other things Wink. First, I implemented additional state within the plugins (as previously discussed) which allows some setup communication to occur allowing me to do a precalulcation cycle. In tandem with this, I've been restructuring the plugins along the lines I posted last time using explicit group tags. Finally, I have begun the work of reimplementing the main recordset handling in a new object class (GroupedRecordset) thereby separating the calculation phase from the presentation phase. One reason for this is to allow for some code reuse and further, to provide a means for developers to override the GroupRecordset to provide alternate means of producing the summary data (eg: submitting an actual SQL query).

I'd say that I am about 1/2 way there. In the meantime, your comments are very helpful and appreciated -- thank-you!
Back to top
View user's profile Send private message
slacker775
Smarty n00b


Joined: 30 Jun 2005
Posts: 1

PostPosted: Thu Jun 30, 2005 3:16 pm    Post subject: Re: bug? with nested sort levels... Reply with quote

Quote:

So, I have two records that say:

[a]=>1
[b]=>something

[a]=>2
[b]=>something

if i send this data in with sort level a,b then the 'something' data tells this code that the group *hasn't* changed, but because the group above it *did* change, i need that to be recognized. it seems as if this code is breaking. I have some more tests to do, but thought I'd describe the problem to see if you had a quick answer.


I am seeing this problem as well. I am working on a Time & Expense system and I have reports that group by the Project, Type (Hours or Expense) and Category and I have cases where a project only has people charging hours, not expenses. Since the Hours is my sub-group and does not change, I don't get my subtotal until I hit a project that has expenses and that of course throws off all my values. Any quick fix for this?

In following the thread a little closer, I see that there was a fix posted but it seems that it only determines if the 'parent' group has changed. What about the case where there are 3 groups and it's the 1st one that changed while 2 & 3 stayed the same? Does that logic need to be abstracted out a bit further to allow an essentially unlimited depth?
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Thu Jun 30, 2005 5:42 pm    Post subject: Reply with quote

Hey slacker775, thanks for posting.

Are you using the latest version and still seeing that behaviour? Can you post a more explicit test case?

I am working on a completely different version at the moment. In the meantime, if someone wants to patch the existing version to address issues there, feel free. The new version is planned to have more robust grouping and summary capabilities but I can't say when it will be ready.
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Thu Jun 30, 2005 8:24 pm    Post subject: i think i've got this one... needed chain link checks Reply with quote

a little shorthand description of a recordset... note this is not an assoc array:

[project]=>x,y,z
[person]=>joe,joe,joe
[hours]=>4,4,4
[category]=>T,T,T

so, there are three records.

if you group by "project, person" you will see correct data summarized in report_header. each project has total 1 person on it - report_detail there shows the hours worked. great!

group by "project, person, hours" and you will see that the two levels of exact same data (in person and hours) confounds the report block. it only expects to get guidance on the end of a group from the group itself or from the group immediately above it.

so, i believe that you are right about the need to extend this check up to the very top group level.

perhaps another variable is needed in the loop where prev_group is set that keeps track of the exact record number where the group change occurred.

here's two commented lines in the report block code... replace them with the new ones. essentially, i've changed the "level above" checking code to check whether a variable was set in the group above. this extends the check to behave more like a chain:

OOPS! Embarassed Don't use the code if you saw it here before I edited the post. It's broken.

let me know how that goes!
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Thu Feb 02, 2006 5:35 pm    Post subject: ideas for using banded report in left outer join style Reply with quote

Long time no squeak! I'm at it again... I am trying to use banded report generator (BRG) on a slightly different problem. It boils down to the issue that I can't seem to get a left outer join behavior out of it.

Here's my vexing issue:

I have a set of records that span a certain time period, say 2004-2005. I want to report on the values in a field *broken down by quarter*. I call this cross-tabbing or bucketizing.

I can easily see the values (wth counts, sum, and avg handily provided by the BRG), but when there are no records in a particular bucket (quarter), I can't do anything with that - BRG just deals with the data that there is. It doesn't seem to have the capability to operate on a dataset through a "left outer join."

That is, I want to take the absolute time span for the report I need and prepare another table with those values (bucketized by quarter ID) and then do a left inner join from this new time frame table to the recordset. Then, even in the time frame where no records exist, the recordset calculations for counts, sum and avg would still be emitted in the report group sub tags; they would be zero.

And that's really the issue - I can't emit zeros where no records occur in the recordset.

Any ideas? Am I missing something easy or is this a change to be made in the plugin? I can try that, but thought I'd ask first since it was pretty painful last time! Razz
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Tue Feb 07, 2006 4:47 am    Post subject: Reply with quote

Hey Soph.

The rewrite of this code got a little dusty but I've been meaning to pick it up again and deal with some of the buggy behaviour in terms of the grouping issues in the old code. As a side-effect, it may make dealing with your situation a little easier. Give another ping here if I don't update in the next couple of weeks.

In terms of your specific issue, if possible, it would be nice to see some concise sample and the order you are trying to process it.

Greetings!
Back to top
View user's profile Send private message
sophistry
Smarty Rookie


Joined: 31 Jan 2005
Posts: 33

PostPosted: Tue Feb 07, 2006 3:45 pm    Post subject: Time Frame Buckets Reply with quote

Hi Boots... thanks for the reply. Glad to hear things have progressed with this plugin.

Here's a stab at a simple sample:

A recordset with results that span 1 year.
Fields quarter and type.

If the recordset contains one record from each quarter of the year, I have no problem. Each quarter shows in the listing created by the BRG. And I can use BRG to show my 4 quarters.

But, if my recordset has no records from a particular quarter, BRG knows nothing about that missing quarter. It only shows 3 quarters. But, I need to show that missing quarter or else there is a break in the natural flow of time. I would need to show that quarter and show 0 for that quarter.

So, I need some way to specify an array that will serve as the absolute "framework array" for the recordset. It would look something like this:

PHP fragment:
Code:

// create the bucket array to be the framework for quarters
$quarters_array=array('1Q05','2Q05','3Q05','4Q05');

TPL fragment:
Code:

{report recordset=$records record=rec buckets_1=$quartersarray groups="quarter, type" resort=true}


Notice the buckets_1 since I see a need to have a framework at arbitrary levels (i.e., buckets_2, buckets_3...).

In psuedocode description (a possible implementation):
1) the report plugin chomps through the main recordset array detecting group switches.
2) each time a group switch is detected the plugin should check the "framework array" and make sure that it matches the next group value.
3) if the framework/bucket value matches the new group, continue
4) if the framework/bucket value does not match, emit a group with 0 as the value for count,sum,avg.
5) resume normal processing

Hope that's clearer!
Any thoughts on this?
Smile
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Tue Feb 07, 2006 4:29 pm    Post subject: Re: Time Frame Buckets Reply with quote

sophistry wrote:
If the recordset contains one record from each quarter of the year, I have no problem. Each quarter shows in the listing created by the BRG. And I can use BRG to show my 4 quarters.


I see. Shouldn't you deal with this in your dataset construction? I would like to keep things such that a single, fully denormalized dataset is provided as input. It seems to me that distinguishing groups from buckets might lead to confusion. I'm not completely against adding support for this as it is a fair concern, but I would rather have a flag that said not to skip empty buckets if they exist in the dataset rather than having to specify the canonical list. Food for thought.
Back to top
View user's profile Send private message
Ross
Smarty Rookie


Joined: 18 Apr 2006
Posts: 6

PostPosted: Tue Apr 18, 2006 7:27 pm    Post subject: Reply with quote

Hi boots,
Has there been any word on your updates of this code? I'm on the verge of starting some PHP accouting reports and this would be a nifty piece of code to have.
Back to top
View user's profile Send private message
boots
Administrator


Joined: 16 Apr 2003
Posts: 5613
Location: Toronto, Canada

PostPosted: Tue Apr 18, 2006 7:34 pm    Post subject: Reply with quote

Hi Ross.

Thanks for the ping. I'm sorry to say I haven't had time to update this since my last post but it is still on my to-do list and it is moving closer to the top Smile
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Smarty Forum Index -> Plugins All times are GMT
Goto page Previous  1, 2, 3, 4, 5, 6, 7  Next
Page 4 of 7

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP