Issue with filter function and aggregate command

(9 posts) (2 voices)

Tags:

No tags yet.

  1. spyhunter88, Member

    Hi Team,

    We found another issue when use dbr.filter, we have test in build 3049.

    I. The first one is The count by dbr.count is wrong after take filter, also we have dbr.hdr, maybe that cause another countable line. We have latest build (3049).

    select 'dbr.hdr', 'Company'; select 'dbr.sum', 'Sales'; select 'dbr.summary.text', 'Company', 'Total:'; select 'dbr.count', 'BU'; select 'dbr.column.filter', 'Company'; select 'dbr.column.filter', 'BU';

    SELECT 'A' as Company, 'F1' as BU, 1500 as Sales UNION ALL

    SELECT 'A' as Company, 'F1' as BU, 2500 as Sales UNION ALL

    SELECT 'A' as Company, 'F2' as BU, 1300 as Sales UNION ALL

    SELECT 'A' as Company, 'F1' as BU, 11300 as Sales UNION ALL

    SELECT 'A' as Company, 'F2' as BU, 1240 as Sales;

    II. The second one is column aggregate by dbr.calc does not update after filter is taken.

    select 'dbr.sum', 'Sales', 'Cost'; select 'dbr.summary.text', 'Company', 'Total:'; select 'dbr.count', 'BU'; select 'dbr.column.filter', 'Company'; select 'dbr.column.filter', 'BU'; select 'dbr.calc', 'GP', '([Sales]-[Cost])';

    SELECT 'A' as Company, 'F1' as BU, 1500 as Sales, 3000 as Cost, null as `GP` UNION ALL

    SELECT 'A' as Company, 'F1' as BU, 2500 as Sales, 3000 as Cost, null as `GP` UNION ALL

    SELECT 'A' as Company, 'F2' as BU, 1300 as Sales, 3000 as Cost, null as `GP` UNION ALL

    SELECT 'A' as Company, 'F1' as BU, 11300 as Sales, 3000 as Cost, null as `GP` UNION ALL

    SELECT 'A' as Company, 'F2' as BU, 1240 as Sales, 3000 as Cost, null as `GP`;

    III. Another issue was found but we now unable to re-procedure this in a simplest report. The aggregate function is wrong in crosstab columns, the total is right. And also, after the filter is taken.

    Thanks,

  2. myDBR Team, Key Master

    The issue with dbr.count and columnfilter has been fixed. The dbr.calc is not supported in columnfilter as it is server side component (can include PHP code).

    If the problem persists with the crosstab and subtotals, just export the report as SQL and send the output to the support email as forum sw is not best suited for long code posts.

    --
    myDBR Team

  3. spyhunter88, Member

    Dear Team, thanks for your update.

    I have another new style error with build 3049. I test with this code:
    select 'dbr.calc', 'target_p1', '([P1_Sales]/[P1_Target]*100)'; select 'dbr.calc', 'target_p2', '([P2_Sales]/[P2_Target]*100)';

    select 'Service', 159.1091 as 'P1_Sales', null as 'P1_Target', null as 'Diff[p1_diff]', null as 'target_p1', null as 'P2_Target', null as 'target_p2', 902.1709 as 'P2_Sales', null as 'P3_Sales'

    union all select 'Service', 50, 104, null, null, null, null, 1236.7267, 1990.4086 ;

    And compare the output with another one in previous version (3039).

    3039:

    3049:

    I don't want to create new topic because it's just a bug and absolutely fixed and no one will face it again.

  4. myDBR Team, Key Master

    MySQL has some inconsistencies what the datatype of a column containing just NULLs is. The new build improves the datatype detection of the calc result.

    --
    myDBR Team

  5. spyhunter88, Member

    Many thanks for that. I will update soon.

    For crosstab issue, I will make a simplest code for an easy test, and for our security also.

    Regards,

  6. myDBR Team, Key Master

    You can use the support email for sending in sample data.

    --
    myDBR Team

  7. spyhunter88, Member

    Hi team,

    We have updated and the above issues are fixed, but another found. Relate to filter function.

    I don't know this is error or an feature to show all summary line but it takes too much space.

  8. myDBR Team, Key Master

    It is a feature of showing the summary lines. Your report has header levels set and you filter out rows and since your filter filters out most of the rows, you are left with the summary lines of each header level.

    --
    myDBR Team

  9. spyhunter88, Member

    Sorry, but I have only one header level. Only first summary line relate to line 1, others summary lines belong to other groups.
    When I filter 'LNV' as above, the only group 1 have this line and the 1st summary line appears is ok. But others group without 'LNV' does not hide their summary line while it's zero.
    You can see in the dbr.count for the second column. The first summary line is #1, other are #0 and the final summary (for all) is #1.

    Thanks,


Reply

You must log in to post.