Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: multiple row for row axis in crosstab report

  1. #1

    Default multiple row for row axis in crosstab report

    Hello all,
    I am facing a problem in crosstab report designed in PRD 5.0. In my cross tab for each itemname I want to show income by each item in a month but for every month I am getting a different row which is not desired. i.e for ItemName 'Registration of ALC' I am getting 3 different row with the value of one month at a time and value 0 for other month.

    Itemtype- Income
    CLIENT NAME- DPS
    Head Name- Student Registration
    January-2014 February-2014 March-2014
    ITEMNAME ITEMCOUNT ITEM_RATE AMOUNT ITEMCOUNT ITEM_RATE AMOUNT ITEMCOUNT ITEM_RATE AMOUNT
    Registration of Student 11.00 10.00 110.00 0.00 0.00 0.00 0.00 0.00 0.00
    Registration of Student 0.00 0.00 0.00 6.00 10.00 60.00 0.00 0.00 0.00
    Registration of Student 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    and this is my query for report

    SELECT
    IT.ITEMTYPENAME AS ITEMTYPE,
    C.CLIENTNAME,
    H.HEADNAME,I.ITEMNAME,
    DATE_FORMAT(FROMDATE,'%M-%Y') AS DATELABEL,
    ITR.ITEMCOUNT ITEMCOUNT,
    ITR.PERITEMRATE AS ITEM_RATE,
    ITR.AMOUNT, ITR.FROMDATE
    FROM ITEMTRANS ITR,ITEM I,HEAD H,
    `CLIENT` C,
    PROGRAM P,
    ITEMTYPE IT
    WHERE ITR.ITEMID = I.ID
    AND I.HEADID = H.ID
    AND H.CLIENTID = C.ID
    AND C.PROGRAMID = P.ID
    AND I.ITEMTYPEID=IT.ID
    AND P.ID=1
    GROUP BY I.ITEMTYPEID,C.CLIENTNAME, H.HEADNAME, I.ID,DATELABEL

    is there any problem with my query or report I have designed.
    Please reply me as soon as possible.
    Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Your sorting is wrong. The old rule of "only give sorted data and sort by your groups" still holds true.

    Make sure your sorting matches the report's groups, starting with any relational groups, then crosstab-other-groups, then crosstab-row-groups and finally crosstab-column-groups.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.