Jump to content
Changes to the Jaspersoft community edition download ×

Generating an advanced excel report

achurchill

By achurchill
in Products

 Share

Recommended Posts

achurchill Rookie

achurchill

Posted
Posted

Hello,

I am using Jaspersoft Studio Professional 5.6.0 and I want to generate a report like the one below in excel. The query I have looks nice IBm DB2  SQL but I am not sure how to do this in Jaspersoft Studio. Any help is appreciated.

Full Name       Age

Joe Anderson   34

Account Number    Account Type

234433                      Personal

Share Name             Share ID

Savings                       0001

Account Number      Account Type

3333333                        Personal

Share Name             Share ID

Savings                       0001

Full Name       Age

Joe Anderson   34

Account Number    Account Type

234433                      Personal

Share Name             Share ID

Savings                       0001

Account Number      Account Type

3333333                        Personal

Share Name             Share ID

Savings                       0001

 

 

 

 

Link to comment
Share on other sites
  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

achurchill Rookie

achurchill

Posted
  • Author
Posted

Sorry I might have misled you on the data I actually have. In my SQL query I have unions to get the data output below. The problem is in Jaspersoft I want to have the ACCOUNT_NUMBER1 flush underneath the FIRST_NAME column with the column headers ACCOUNT_NUMBER1 to PRIMARY_PHONE underneath the FIRST_NAME until the TELE_LAST_LOGIN_TIME. I am not sure if I can achieve this by Groups or sub reports but the query is broken up in a CTE in different queries and are unioned.

 

CURERNT SQL OUT PUT:

 

 FIRST_NAME MIDDLE_NAME LAST_NAME  SUFFIX AGE  HAS_ONLINE_BANKING OB_LAST_LOGIN_TIME    HAS_TELE_VOICE TELE_LAST_LOGIN_TIME  ACCOUNT_NUMBER1 ACCOUNT_TYPE ACCOUNT_ELIGIBILITY ACCOUNT_OPEN_DATE ACCOUNT_CLOSE_DATE CORRESPONDENCE_DATE E_NOTICE_OPTION  E_STMT_OPTION    E_STMT_NOTIFICATION HAS_DIRECT_DEPOSIT TOTAL_AMOUNT_ACH HAS_MOBILE_DESPOIT MONTHLY_AVG_AMOUNT ADDRESS_ONE      ADDRESS_TWO CITY           STATE ZIP   EMAIL                    PRIMARY_PHONE SHARE_ID SH_TYPE          SHARE_OPEN_DATE APYE_POSTING_DATE APYE_AVERAGE_BALANCE CURRENT_SHARE_BALANCE PERSON_CATEGORY COURTESY_PAY_LIMIT COURTESY_PAY_ATM_DEBIT COURTESY_PAY_ATM_DEBIT_CH_DATE COURTESY_PAY_OTHER COURTESY_PAY_OTHER_CH_DATE LOAN_ID LN_PERSON_LINK_CATEGORY LOAN_OPEN_DATE LOAN_CLOSE_DATE ORIGINAL_LOAN_DATE ORIGINAL_FUNDING_DATE CURRENT_LOAN_BALANCE LN_TYPE_DESCRIPTION       CREDIT_SCORE_CATEGORY   CREDIT_SCORE CREDIT_LIMIT INTEREST_APR LOAN_LAST_ACTIVITY_DATE MATURITY_DATE CALCULATED_GRADE LOAN_TRANSFER_AMOUNT LOAN_TRANSFER_PERCENTAGE LOAN_TRANSFER_EFFECTIVE_DATE LOAN_TRANSFER_EXPIRATION_DATE DEBIT_CARD_NUMBER CARD_STATUS DEBIT_TRANS_COUNT CARD_ACTIVATION_TIME  CARD_EXPIRATION_DATE CARD_ISSUE_DATE LAST_TRANS_DATE       CREDIT_CARD_NUMBER CREDIT_CARD_OPEN_DATE CARD_TYPE                             CREDIT_CARD_LAST_ACTIVITY_DATE
JayTTestNULL  21Y                 6/13/2015Y             6/13/2015NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 55555555TestTest6/13/2015NULL              6/13/2015Mail notice onlyE-notice only   YesNo11111N                               NULL567 TestNULL       Tets 1PA   11111tttttest@gmail.com657 777 77771Savings         6/13/2015NULL             NULL                3Primary owner  0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         4Checking        6/13/20156/13/201533333333333333Primary owner  100N                     6/13/2015Y                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         6Traditional IRA 6/13/2015NULL             NULL                3333333Primary owner  0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         8Money Market    6/13/2015NULL             NULL                3333333Primary owner  0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         6000TRAD IRA CD 12 M6/13/2015NULL             NULL                3333333Primary owner  0N                     NULL                          N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      2Primary                6/13/2015NULL           6/13/2015NULL                 0Personal Line of Credit  TransUnion FICO 04     22255506/14/20156/14/2015D                NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      3Primary                6/13/2015NULL           6/13/2015NULL                 666666Indirect Used Vehicle    TransUnion FICO Auto 08333366606/14/20156/14/2015D                NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      7010Primary                6/13/2015NULL           6/13/2015NULL                 0Visa Platinum Credit CardNone                   0777706/14/2015NULL         None                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         1.11111E+15Issued     106/12/20186/12/20186/12/20186/12/2018NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         1.11111E+14Issued     106/12/20186/12/20186/12/20186/12/2018NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         NULL    NULL            NULL           NULL             NULL                                 NULLNULL                         NULLNULL                  NULL                          NULL              NULL                      NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 1.11111E+116/12/2018Visa 6/11/2018
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 6666666TestTest6/13/2015NULL              6/13/2015Mail notice onlyE-notice only   No                 NULL                          NULLN                               NULL300 TestNULL       TestCA11111tttttest@gmail.com657 777 77771Savings         6/13/2015NULL             NULL                3333333Joint owner    0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         2Vacation Club   6/13/2015NULL             NULL                3333333Joint owner    0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         3Holiday Club    6/13/2015NULL             NULL                3333333Joint owner    0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         4Checking        6/13/2015NULL             NULL                0Joint owner    100N                     6/13/2015Y                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 NULL           NULL        NULL               NULL             NULL              NULL               NULL            NULL            NULL               NULL                          NULLNULL                            NULLNULL            NULL       NULL          NULL NULL NULL                    NULL         8Money Market    6/13/2015NULL             NULL                3333333Joint owner    0N                     6/13/2015N                 6/13/2015NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL
 NULL      NULL       NULL      NULL  NULLNULL              NULL                 NULL          NULL                 7777777TestTest6/13/2015NULL              6/13/2015Mail notice onlyMail notice onlyNo                 NULL                          NULLN                               NULL300 TestNULL       Test 544PA   11111tttttest@gmail.com657 777 77771Savings         6/13/2015NULL             NULL                3333333Joint owner    0N                     6/13/2015N                 NULL                      NULL   NULL                   NULL          NULL           NULL              NULL                                 NULLNULL                     NULL                           NULL        NULL        NULLNULL                   NULL         NULL                            NULL                    NULLNULL                        NULL                         NULL             NULL                    NULLNULL                 NULL                NULL           NULL                 NULL              NULL                 NULL                                 NULL

 

EXPECTED OUTPUT IN Jaspersoft:

 

 FIRST_NAME MIDDLE_NAME LAST_NAME  SUFFIX AGE  HAS_ONLINE_BANKING OB_LAST_LOGIN_TIME    HAS_TELE_VOICE TELE_LAST_LOGIN_TIME             
JayTTest  21Y                 6/13/2015Y             6/13/2015           
ACCOUNT_NUMBER1 ACCOUNT_TYPE ACCOUNT_ELIGIBILITY ACCOUNT_OPEN_DATE ACCOUNT_CLOSE_DATE CORRESPONDENCE_DATE E_NOTICE_OPTION  E_STMT_OPTION    E_STMT_NOTIFICATION HAS_DIRECT_DEPOSIT TOTAL_AMOUNT_ACH HAS_MOBILE_DESPOIT MONTHLY_AVG_AMOUNT ADDRESS_ONE      ADDRESS_TWO CITY           STATE ZIP   EMAIL                    PRIMARY_PHONE 
                                                                                                                                                                                                                                     
55555555TestTest6/13/2015              6/13/2015Mail notice onlyE-notice only   YesNo11111N                               567 Test       Tets 1PA   11111tttttest@gmail.com657 777 7777
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
6666666TestTest6/13/2015              6/13/2015Mail notice onlyE-notice only   No                                           N                               300 Test       TestCA11111tttttest@gmail.com657 777 7777
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
                                                                                                                                                                                                                                     
7777777TestTest6/13/2015              6/13/2015Mail notice onlyMail notice onlyNo                                           N                               300 Test       Test 544PA   11111tttttest@gmail.com657 777 7777
SHARE_ID SH_TYPE          SHARE_OPEN_DATE APYE_POSTING_DATE APYE_AVERAGE_BALANCE CURRENT_SHARE_BALANCE PERSON_CATEGORY COURTESY_PAY_LIMIT COURTESY_PAY_ATM_DEBIT COURTESY_PAY_ATM_DEBIT_CH_DATE COURTESY_PAY_OTHER COURTESY_PAY_OTHER_CH_DATE         
                                                                                                                                                                                          
1Savings         6/13/2015                             3Primary owner  0N                     6/13/2015N                 6/13/2015        
4Checking        6/13/20156/13/201533333333333333Primary owner  100N                     6/13/2015Y                 6/13/2015        
6Traditional IRA 6/13/2015                             3333333Primary owner  0N                     6/13/2015N                 6/13/2015        
8Money Market    6/13/2015                             3333333Primary owner  0N                     6/13/2015N                 6/13/2015        
6000TRAD IRA CD 12 M6/13/2015                             3333333Primary owner  0N                                               N                 6/13/2015        
                                                                                                                                                                                          
                                                                                                                                                                                          
                                                                                                                                                                                          
                                                                                                                                                                                          
                                                                                                                                                                                          
                                                                                                                                                                                          
1Savings         6/13/2015                             3333333Joint owner    0N                     6/13/2015N                 6/13/2015        
2Vacation Club   6/13/2015                             3333333Joint owner    0N                     6/13/2015N                 6/13/2015        
3Holiday Club    6/13/2015                             3333333Joint owner    0N                     6/13/2015N                 6/13/2015        
4Checking        6/13/2015                             0Joint owner    100N                     6/13/2015Y                 6/13/2015        
8Money Market    6/13/2015                             3333333Joint owner    0N                     6/13/2015N                 6/13/2015        
1Savings         6/13/2015                             3333333Joint owner    0N                     6/13/2015N                                               

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...

© 2024 Cloud Software Group,
Inc. All rights reserved.

Products

Explore