KReporter 3.0.3 - Filter Results by parameter

[kir]

hi

I want to filter results by paramater as follow

I want to list only Accounts with 2 related Contacts, another case - i want to list only Contacts with 3 planned Calls

Is it possible and how i can do it?

Thanks

[christianknoll]

count would mean making a having statement in the selection ... not easy to do programatic ... what you can do is to create a kreporter field that does a subselect and returns the numer of assigned contacts. Then use that in the select clause. Also no big deal and easy to do.

christian.

hi

I want to filter results by paramater as follow

I want to list only Accounts with 2 related Contacts, another case - i want to list only Contacts with 3 planned Calls

Is it possible and how i can do it?

Thanks

[kir]

count would mean making a having statement in the selection ... not easy to do programatic ... what you can do is to create a kreporter field that does a subselect and returns the numer of assigned contacts. Then use that in the select clause. Also no big deal and easy to do.

christian.

what do you mean Create KReporter field? Where? In DB accounts table with storing there updated value of related contacts?
Could you provide some code sample of how to use created Kreporter field in select ection?

Thanks in advance!

kir

[christianknoll]

Look at this Document on Page 20. The concept is still from the old KINAMU Reporter but stayed the same with the migration to KReporter.

The Purpose of the Field of type "kreporter" is that you do not have to store any data (bad practice) but can evaulate at report runtime on the database.

christian.

what do you mean Create KReporter field? Where? In DB accounts table with storing there updated value of related contacts?
Could you provide some code sample of how to use created Kreporter field in select ection?

Thanks in advance!

kir

[kir]

Look at this Document on Page 20. The concept is still from the old KINAMU Reporter but stayed the same with the migration to KReporter.

The Purpose of the Field of type "kreporter" is that you do not have to store any data (bad practice) but can evaulate at report runtime on the database.

christian.

Thanks!
got it partly, exept the syntax in 'eval'. Can I use regular SQL expression? Could you you give some example. I little bit confused on using $ with non module's tables

and one more question: i have non-db field (which is calculating by logic hook) and want to include it to the KReport. Is it possible to include that non-db fields to the report? May be i just need to add type=kreporter?

Thanks in advance!

kir

[kir]

Thanks!
got it partly, exept the syntax in 'eval'. Can I use regular SQL expression? Could you you give some example. I little bit confused on using $ with non module's tables

and one more question: i have non-db field (which is calculating by logic hook) and want to include it to the KReport. Is it possible to include that non-db fields to the report? May be i just need to add type=kreporter?

Thanks in advance!

kir

Looks like i dig it by myself.
I can use SQL query.
For module's fields i used $.fieldname
For other tables used tablename.fieldname

It is very cool option!

Interesting - is there some syntax to be able to use same fake field with calculation for kreporter and for DetailView and ListView? May be through function?

Any idea?

Thanks!

kir

[christianknoll]

Also look at the releasenotes for 3.0.2 since there have been more changes to the kreport type fields. Especially when you want to use them in the selection. Also the '$' has been deprecated and will sooner or later be phased out.

regarding your question of reuse - this is not possible this way since the kreport type fields are evaluated on the database by MySQL. Hooks are fired in php.

What you could do is to wrap the code in your hook, make sure you include the class needed there globally and make the function static available. Then you could use the function on the ID field like I did it in this Video to get the image urls. This is also just calling a static function in a global available class. Not too difficult but will require an understanding of architecture and PHP development.

christian.

Looks like i dig it by myself.
I can use SQL query.
For module's fields i used $.fieldname
For other tables used tablename.fieldname

It is very cool option!

Interesting - is there some syntax to be able to use same fake field with calculation for kreporter and for DetailView and ListView? May be through function?

Any idea?

Thanks!

kir

[kir]

Also look at the releasenotes for 3.0.2 since there have been more changes to the kreport type fields. Especially when you want to use them in the selection. Also the '$' has been deprecated and will sooner or later be phased out.

regarding your question of reuse - this is not possible this way since the kreport type fields are evaluated on the database by MySQL. Hooks are fired in php.

What you could do is to wrap the code in your hook, make sure you include the class needed there globally and make the function static available. Then you could use the function on the ID field like I did it in this Video to get the image urls. This is also just calling a static function in a global available class. Not too difficult but will require an understanding of architecture and PHP development.

christian.

Thanks, Christian!

I read in pointed release notes that Option Dialog is fixed, but not in my case. I still have problem with it. - I can't change options in dynamic_options section - they are always collapsed and result always expanded. I mean i can change but changes not saving.

will it be fixed in new release?

btw: created fake field with type of kreporter can be used to populate value by logic-hook (i tested it with after_retrieve for DetailView and process_record for ListView). KReporter use 'eval' and logic-hook just fill by it function value.

[tomas.berger@imper.cz]

Hi guys, I'm Kreports rookie, and I have very similar problem: we need filter Accounts with Opportunities which have Amount 1000 and 2000 (Every Account must have both Opportunities with that Amounts). Is there some simple solution to get this result?

[kir]

Hi guys, I'm Kreports rookie, and I have very similar problem: we need filter Accounts with Opportunities which have Amount 1000 and 2000 (Every Account must have both Opportunities with that Amounts). Is there some simple solution to get this result?

create file in custom/Extension/modules/Accounts/Ext/Vardefs/kreporterfields.php
define kreporter field by next code

 <?php
$dictionary['Account']['fields']['vip_candidate'] = 
      array (
        'name' => 'vip_candidate',
        'vname' => 'Need Follow Up',
        'type' => 'kreporter',
        'source'=>'non-db',
        'comment' => 'Flag field for KReporter - if Customer has $1000 and/or $2000 amount in related opportunities',
        'eval' => "SELECT COUNT(DISTINCT ROUND(a.amount,0)) 
                           FROM opportunities a INNER JOIN accounts_opportunities b ON b.opportunity_id = a.id AND b.deleted = 0
                   WHERE b.account_id = $.id AND a.deleted = 0 AND (ROUND(a.amount,0) = 1000 OR ROUND(a.amount,0) = 2000)",
      ); //used function ROUND because "amount" field has type=double with not exact value in DB  

Do repair and rebuild

Create Report with main module Accounts. Now you will be able to see our new field in the field list of the module. U can display this field and can use this field for select clause.
Add created field "Need Follow Up" to the report.
This field will may display 1 of 3 values:
0 - for "No opportunities with amount 1000 or 2000",
1 - for "At least 1 Opportunity with amount 1000 or 2000" and
2 - for "Account has opportunities with both amount 1000 and 2000"

So if you will add this field to Select tab and set filter to value=2 - your Account report will show you only Accounts you wanted to

Hope it will help you or others who straggling with reports

kir

Best wishes from Russia