Results 1 to 5 of 5

Thread: Sugar PRO Reporter - SELECT DISTINCT

  1. #1
    beschaeftigt.de's Avatar
    beschaeftigt.de is offline Sugar Community Member
    Join Date
    Mar 2009
    Location
    Europe
    Posts
    28

    Default Sugar PRO Reporter - SELECT DISTINCT

    Hi,
    I have some trouble with duplicate entries in reports.
    I generate a rows report with a filter, lets say, account type = Customer. Then when I want to return the accounts contact name I get duplicate entries if a contact has two or more accounts. When I add DISTINCT to the modules/Reports/Reports.php file the problem is resolved. But the other report types are also changed because they also use the same line (1472) with the SELECT and now SELECT DISTINCT in it.
    Is there a easy way to reduce the duplicate entries from my report, or do I really have to change the code and add a new report type with makes a DISTINCT query?
    Kind regards,
    paul

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

    Default Re: Sugar PRO Reporter - SELECT DISTINCT

    There is no such option in the Sugar Reporter I would know of. In our KINAMU Reporter we have added a feature that allows you to e.g. select the first contact or specifically built for that reason with the group concat feature have the various contact names concatenated into one field of the report.

    christian.

    Quote Originally Posted by beschaeftigt.de View Post
    Hi,
    I have some trouble with duplicate entries in reports.
    I generate a rows report with a filter, lets say, account type = Customer. Then when I want to return the accounts contact name I get duplicate entries if a contact has two or more accounts. When I add DISTINCT to the modules/Reports/Reports.php file the problem is resolved. But the other report types are also changed because they also use the same line (1472) with the SELECT and now SELECT DISTINCT in it.
    Is there a easy way to reduce the duplicate entries from my report, or do I really have to change the code and add a new report type with makes a DISTINCT query?
    Kind regards,
    paul

  3. #3
    beschaeftigt.de's Avatar
    beschaeftigt.de is offline Sugar Community Member
    Join Date
    Mar 2009
    Location
    Europe
    Posts
    28

    Default Re: Sugar PRO Reporter - SELECT DISTINCT

    Hi Christian, and thank you for your reply.
    To solve the problem I added a new report type to the reporter that does a SELECT DISTINCT and now the results are acceptable.
    Kind regards,
    Paul

  4. #4
    cent Guest

    Question Re: Sugar PRO Reporter - SELECT DISTINCT

    Dear Paul,
    I am using SugarCRM PRO v 6.2.4 . When I take Contact report i got result , but the data is repeated , means duplicate . How it can avoid ? please elaborate your solution please

    My problem view is
    Name:  duplicate Report.png
Views: 203
Size:  37.3 KB.

    There is no relation between Contact and User in relationship .


    Thankfully,
    Anes

  5. #5
    beschaeftigt.de's Avatar
    beschaeftigt.de is offline Sugar Community Member
    Join Date
    Mar 2009
    Location
    Europe
    Posts
    28

    Default Re: Sugar PRO Reporter - SELECT DISTINCT

    Hi Anes,
    I solved it for 6.0.0 but it should work nearly in the same way.

    In include/javascript/report.js add a new report type:

    Code:
    showWizardStep:function(isPrev,stepName){
        if(!SUGAR.reports.saveCurrentStep()&&!stepName)
            return false;
        if(report_type=='tabular')
            SUGAR.reports.showWizardStepTabular(isPrev,stepName);
        // DISTINCT QUERY BEGIN
        else if (report_type == 'distinct')
            SUGAR.reports.showWizardStepDistinct(isPrev, stepName);
        // DISTINCT QUERY END
        else if(report_type=='summation')
            SUGAR.reports.showWizardStepSummation(isPrev,stepName);
        else if(report_type=='summation_with_details')
            SUGAR.reports.showWizardStepSummationWithDetails(isPrev,stepName);
    },
    in _reportCriteriaWithResult add the type too:
    Code:
    $reportType = ($reporter->report_def['report_type'] == 'distinct' ? 'Zeilen und Spalten Bericht (DISTINCT)' : $mod_strings['LBL_SUMMATION_REPORT']);
    in templatesRecord.php add in function getReportDetailViewString
    Code:
            $reportType = ($reporter->report_def['report_type'] == 'distinct' ? 'Zeilen und Spalten Bericht (DISTINCT)' : $mod_strings['LBL_SUMMATION_REPORT']);
    in Report.php add in function run_query:
    Code:
    if($this->report_type == 'distinct'){
                        $this->create_query_distinct();
                    }
    and then create the function for the DISTINCT query analogue to create_query:
    Code:
    function create_query_distinct($query_name='query',$field_list_name='select_fields'){
            $query = "SELECT DISTINCT ";
    I hope I didn't miss something.
    Kind regards,

    Paul

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Bug in kinamu reporter
    By rakeshray in forum Developer Help
    Replies: 0
    Last Post: 2011-05-16, 02:07 PM
  2. Distinct items using SOAP
    By algorithme in forum Help
    Replies: 2
    Last Post: 2011-01-31, 05:23 PM
  3. Problem in KINAMU reporter
    By MahsaN in forum Developer Help
    Replies: 2
    Last Post: 2010-08-12, 06:23 AM
  4. Combinational distinct name.
    By yuri.gelman in forum Developer Help
    Replies: 5
    Last Post: 2010-06-24, 10:00 AM
  5. Trying to use Kinamu Reporter
    By anicolais in forum Help
    Replies: 3
    Last Post: 2010-04-25, 05:15 PM

Tags for this Thread

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
  •