Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Crosstab With Totals

  1. #1

    Default Crosstab With Totals

    HI there,

    have devoloped a crosstab report which is working fine, but i want to add some more fileds as TOTAL, AVG for my crosstab report so how can i accomplish that for eg if my report is like this

    -------------------------------------------------------------------------
    RollNumber---Subject1---Subject2---Subject3---Total
    -------------------------------------------------------------------------
    -------1-------------10-------------20-----------30-----
    -------2-------------20-------------60-----------30-----
    -------3-------------40-------------50-----------30-----


    if i have this in cross tab it works fine but how will i add the TOTAL Column

    awaiting for your replies

    regards
    Nayeem
    Regards
    Nayeem

  2. #2
    Join Date
    Oct 2008
    Posts
    124

    Default

    Wouldn't these aggregates normally be pulled from your cube or calculated in your query?

  3. #3
    Join Date
    Oct 2007
    Posts
    235

    Default

    If you have done the cross tab the old way this might be of help: http://forums.pentaho.org/showthread.php?t=64279

    Good luck

    Wil
    SQL: as much of a standard as the English language

  4. #4

    Default Got Solution Here

    Hey there here am with the awesome solution for the crosstab with totals under (MS-Server), u need to do little tricky query have a look at this which works perfectly


    HTML Code:
    declare @TableTest table(RowNo int,OrderNumber int null,RollNumber nvarchar(1000),Subjects nvarchar(1000),Marks int)
    DECLARE @NoOfRows INT; 
    
    insert into @TableTest(RowNo,RollNumber,Subjects,Marks)
    select ROW_NUMBER() OVER(ORDER BY b.SSAY_RollNumber, b.SUB_Name),b.SSAY_RollNumber, b.SUB_Name, a.STM_Marks
    from
    (select SUB_ID, SUB_Name, SUB_StandardID,SSAY_RollNumber,SSAY_UserID from tblSubject,tblSchoolStandard,tblSection,tblStudentSectionAcademicYear where 
    SUB_StandardID = SCLSTD_StandardID and SCLSTD_ID=SEC_SchoolStandardID and SSAY_SectionID=SEC_ID and SEC_ID=1
    and SUB_Deleted=0 and SUB_Active=1 and SUB_Type=0
    )b
    left join
    (select STM_Marks,STM_StudentID,SSAY_RollNumber,STM_SubjectID from tblStudentTestMarks,tblStudentSectionAcademicYear
    where SSAY_UserID=STM_StudentID and SSAY_SectionID=1 and STM_TestID=29)a
    on b.SUB_ID=a.STM_SubjectID and b.SSAY_UserID=a.STM_StudentID
    order by b.SSAY_RollNumber, b.SUB_Name
    SET @NoOfRows=@@ROWCOUNT
    
    insert into @TableTest(RowNo,RollNumber,Subjects,Marks)
    select ROW_NUMBER() OVER(ORDER BY GETDATE()),RollNumber,'Total',SUM(Marks) from @TableTest group by RollNumber
    
    insert into @TableTest(RowNo,RollNumber,Subjects,Marks)
    select ROW_NUMBER() OVER(ORDER BY GETDATE()),RollNumber,'AVG',AVG(Marks) from @TableTest group by RollNumber
    
    declare @NoSubjectsIncrement int
    set @NoSubjectsIncrement=(select COUNT(*)-2 from (select distinct Subjects from @TableTest) a)
    
    declare @SubjectStop int;
    set @SubjectStop=@NoSubjectsIncrement;
    declare @CurrentRow int;
    set @CurrentRow=1;
    declare @OrderNo int;
    set @OrderNo=1;
    
    while @CurrentRow<=@NoOfRows
    begin
        update @TableTest set OrderNumber=@OrderNo where RowNo=@CurrentRow    
        set @OrderNo = @OrderNo + 1
        --Check If One Count is Done
        if(@CurrentRow = @SubjectStop)
            begin            
                update @TableTest set OrderNumber=@OrderNo where Subjects='Total' and RollNumber IN (select RollNumber from @TableTest where RowNo=@CurrentRow)    
                set @OrderNo = @OrderNo + 1
                update @TableTest set OrderNumber=@OrderNo where Subjects='AVG' and RollNumber IN (select RollNumber from @TableTest where RowNo=@CurrentRow)                
                set @OrderNo = @OrderNo + 1
                set @SubjectStop = @SubjectStop + @NoSubjectsIncrement;
            end
            
        --Increament Row Pointer
        set @CurrentRow =  @CurrentRow + 1        
    end
    
    select RollNumber,Subjects,Marks from @TableTest order by OrderNumber


    now u can have any type of columns like (TOTAL, AVG, MIN, MAX) cheers
    Regards
    Nayeem

  5. #5

    Default

    One more issue here is when i execute this query using Native connection it works fine but when publish the report and set the report connection to JNDI the report displays "Report validation failed.".........

    is there some thing difference in executing MS-Sql Server Quries in Native AND JNDI mode... if yes then whts the difference..... please let me know
    Regards
    Nayeem

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

    Default

    JNDI is not magic, it is just a different way of aquireing a connection.

    If your report fails on the server, check your log, check that you use the same JDBC driver in the server and PRD and finally check your JNDI/Pentaho Datasource connection definition in both PRD and server and see that they are the same.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7

    Default

    Thanks for the reply...

    the thing is in the query what i have stated works fine with JNDI when i remove all those staff and only left with the select query particularly the if i remove the Temporary table which i have used "select * from @TableTest"............

    if i remove this Temporary table and use select query works fine with JNDI

    but when i include it that will displays the error as "Report Validation Failed" wht may be the suspect

    is this functionality not supported with JNDI does the features are limited which i have used inside the query, i mean to say is there limitation in writing query when we use JNDI
    Regards
    Nayeem

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

    Default

    The server-side JNDI uses connection pooling, so you may not get a new connection when the report runs. Again, check your log, see what the exact JDBC-error is. If the error is something like "table already exists" then maybe try to drop/undeclare the table before starting to put values in.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  9. #9

    Default

    the table which i have declared is a variable it doesn't need to be undeclared or anything once the operation is finished its done...........
    and am not getting any errors in logs the report just displays "Report Validation Failed"
    Regards
    Nayeem

  10. #10

    Default

    Hi there, am still waiting for your replies.,,,,,, please brief me the cause for my problem
    Regards
    Nayeem

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.