Results 1 to 10 of 10

Thread: KReporter 3.0.3 - Filter Results by parameter

  1. #1
    kir
    kir is offline Sugar Community Member
    Join Date
    Nov 2011
    Posts
    702

    Default KReporter 3.0.3 - Filter Results by parameter

    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

  2. #2
    christianknoll's Avatar
    christianknoll is offline Sugar Community Member
    Join Date
    Nov 2008
    Location
    Vienna
    Posts
    1,382

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    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.

    Quote Originally Posted by kir View Post
    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

  3. #3
    kir
    kir is offline Sugar Community Member
    Join Date
    Nov 2011
    Posts
    702

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    Quote Originally Posted by christianknoll View Post
    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

  4. #4
    christianknoll's Avatar
    christianknoll is offline Sugar Community Member
    Join Date
    Nov 2008
    Location
    Vienna
    Posts
    1,382

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    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.

    Quote Originally Posted by kir View Post
    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

  5. #5
    kir
    kir is offline Sugar Community Member
    Join Date
    Nov 2011
    Posts
    702

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    Quote Originally Posted by christianknoll View Post
    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

  6. #6
    kir
    kir is offline Sugar Community Member
    Join Date
    Nov 2011
    Posts
    702

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    Quote Originally Posted by kir View Post
    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

  7. #7
    christianknoll's Avatar
    christianknoll is offline Sugar Community Member
    Join Date
    Nov 2008
    Location
    Vienna
    Posts
    1,382

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    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.

    Quote Originally Posted by kir View Post
    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

  8. #8
    kir
    kir is offline Sugar Community Member
    Join Date
    Nov 2011
    Posts
    702

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    Quote Originally Posted by christianknoll View Post
    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.

  9. #9
    tomas.berger@imper.cz Guest

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    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?

  10. #10
    kir
    kir is offline Sugar Community Member
    Join Date
    Nov 2011
    Posts
    702

    Default Re: KReporter 3.0.3 - Filter Results by parameter

    Quote Originally Posted by tomas.berger@imper.cz View Post
    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 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
    Last edited by kir; 2013-01-23 at 01:21 AM. Reason: comments added and typo fixed

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. KReporter 3.0.3 doesn't filter union modules
    By andy_vt in forum Developer Help
    Replies: 4
    Last Post: 2013-02-14, 11:33 AM
  2. KReporter 2.7.7 on CE 6.5.5 Problem
    By lmilkes in forum Installation and Upgrade Help
    Replies: 4
    Last Post: 2012-11-21, 03:01 PM
  3. Filter Autofill Results
    By robertbmirth in forum Developer Help
    Replies: 1
    Last Post: 2012-09-04, 02:30 PM
  4. Replies: 0
    Last Post: 2010-04-22, 08:22 AM
  5. filter select popup results based on other field
    By ravi15481 in forum Developer Help
    Replies: 0
    Last Post: 2008-09-11, 05:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •