Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Report 11 -- Cross Tab format and SQL connect by function

Posted on 2010-11-16
2
Medium Priority
?
794 Views
Last Modified: 2012-05-10
I have a cross-tab with 24 months of data. (Please see the attached file). But sometimes the SQL doesn't return 24 months of data, because there is no data for those months. In our example the Sep 2010 and Oct 2010 (201009 & 201010) doesnt have data. But I want those columns in the report with just 0s as shown in the attachment. i tried using the following SQL, but still not able to acheive the one I want.

The ?DATE_ENTERED is a parameter. If for example today's date is given (11-16-2010), I have a formula[maximum(LastFullMonth)] which makes the date to previous months last date (10-31-2010). So the date range would be from oct 2010 and goes back 24 months nov 2008.
I tried playing with the connect by level number sometimes it goes backward or forward not the right one.
When I run just the second part of the union all, I get the desired result ie., from 11/01/2008 thru 10/01/2010, but when I use it together its not giving the desired result, maybe I dont understand that function fully.

select 
trunc(s_date, 'MM') as SDATE,
trunc(s_date, 'MM') as SDATE,
SALARY

FROM TABLE_A,
TABLE_B
.....
WHERE
.....
s_date BETWEEN TRUNC(ADD_MONTHS(TRUNC({?DATE_ENTERED},'MM'),-23))  AND {?DATE_ENTERED}
p_date BETWEEN TRUNC(ADD_MONTHS(TRUNC({?DATE_ENTERED},'MM'),-23))  AND {?DATE_ENTERED}

UNION ALL

-- This is to get those months which aren't returned
SELECT 
     trunc({?DATE_ENTERED}, 'mm') + INTERVAL '-1' MONTH * ROWNUM SDATE,
     trunc({?DATE_ENTERED}, 'mm') + INTERVAL '-1' MONTH * ROWNUM PDATE,
    0 AS salary

FROM dual 

CONNECT BY LEVEL <=24

Open in new window


My next question is, can we change the label of the summary row, that is, GRAND TOTAL instead of just TOTAL. I have highlighted in red in the attachment.

Thoughts?

Thanks!
CROSS-TAB-FORMAT.xls
0
Comment
Question by:D-pk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 14

Accepted Solution

by:
LinInDenver earned 2000 total points
ID: 34150582
I have done something similar in Crystal - using connect by level, to return all possible dates...

This is basically what I did, and replaced the start (3/1/2008) and end (3/31/2010) with 2 crystal date parameters... I'm sure you can accomplish something similar by using just a single date param.

      select
        trunc(to_date('01-MAR-2008'))-1 + level as cal_date
        from dual
        where (to_date('01-MAR-2008')-1+level) <= to_date('31-mar-2010')
        connect by level<=100000


      select distinct
      {?beginofrange}-1 + level
      from dual
      where ({?beginofrange}-1+level) <= trunc({?endofrange})
      connect by level<=1000


Regarding changing labels, Yes - just select to highlight the field in the cross tab, and his F2 to edit the text.
0
 

Author Closing Comment

by:D-pk
ID: 34168716
Thanks LinInDenver!
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question