Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: dynamic date buckets across report Post Reply Post New Topic
Author Message
shae1
Newbie
Newbie
Avatar

Joined: 22 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote shae1 Replybullet Topic: dynamic date buckets across report
     Posted: 22 Jun 2007 at 11:27am

Hi
Im hoping someone can help me with a method of creating date 'buckets'
of totals across a report. I need a current month (determined from parameter) followed by subsequent months for the year. I guess for the headings (Jan07 Feb07 etc)
I can use formula fields and populate with a big if then else or case stmt depending on what the parameter mnth yr is, but i cant work out how to et the data across the page..
I have written an sql that selects data in the order below.
DATE1 will be a parameter runtime date converted to MMYYYY,so always the same, as will AMT1 for each type,sum of current month.
DATE2/AMT2 are the next 11 months from the runtime date with summed amounts from a different table. Other complication is that totals wont necessarily exist for all 11 subsequent months so need to put 0 in those buckets that dont exist.

current sql:
   DEPT       TYPE    DATE1  DATE2  AMT1  AMT2         

   ABCdept TYPE1 062007 072007 1111  5445  
   ABCdept TYPE1 062007 082007 1111  4444
   ABCdept TYPE1 062007 092007 1111  5532
   ABCdept TYPE1 062007 122007 1111  8755
   ABCdept TYPE2 062007 082007 2222  1212
   ABCdept TYPE2 062007 102007 2222  2424

and need it like this:

            DATE1   DATE2    DATE2(+1)  DATE2(+2) etc... 
              AMT1    AMT2     AMT2          AMT2 

ie:
           062007  072007  082007     092007    102007    112007     122007 
 TYPE1 1111       5445     4444         5532            0            0             8755    
 TYPE2 2222          0        1212            0            2424         0               0 etc
 TYPE3   etc
  
I started trying to outer join the first view to itself but was getting
in a mess..is there a neat way to do it with crystal formula?
Any help from all you experts greatly appreciated! Thanks.
  
 
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 24 Jun 2007 at 3:34pm

I have read your post several times, trying to sort out what it is you want.  Admittedly, I  have not done exactly what I think you are trying to do.

I am  offering some thoughts in hopes someone else in the forum might have better and simpler solution.
 
You could try creating 12 different formulas, calling them Month1, Month2, etc. These would go in the Page Header Section
 
You then could use the parameter date as the baseline and then using CR date functions add 1 month to the parameter , 2 months, etc.
 
That should give you the dates across the top (Page Header).
 
When you run your SQL Query based on the parameter I assume it will select all records from the parameter forward or between a range of dates and that there will always be 12 months.  This may not be a vaild assumption.
 
What I dont know is how your data is presented in the detail below the column headers.  It should be simple enough to suppress any given detail column based on a given date range so it does not display if it does not fall within the column headers date or date range.  Again you would create the appropriate formulas, using CR date functions, and your data field in them.
 
I guess what I telling you at this point is I see a report with 24 formulas. In reality it would be 2 formulas repeated 12 times each adjusting each one for the dates.
 
The larger problem is whether or not the detail information is really summary  data for a date range or is it individual records.  If it needs to be summary data that may mean creating a group or two to get the necessary level, putting in the details, suppressing the detail section, any unneeded group headers and footers and showing the output in the remaining group footer section.
 
Perhaps this will give you some new ideas to start with, I doubt it is a full or correct solution for you at this point. Again, it may spark some new thoughts from others in the forum.
 
Its clear as mud in my brain, but I hope it is of some help....
 
Regards,
 
John W.
IP IP Logged
shae1
Newbie
Newbie
Avatar

Joined: 22 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote shae1 Replybullet Posted: 25 Jun 2007 at 3:29am

Hi John, really appreciate your input on this and sorry if i havent explained too clearly. I dont know if its possible to attach the report layout here..? Anyhow, your assumption re the date range is correct (although lack of data in our system at the moment has made it difficult for me to test my sql with + 365 date calc, which is as follows (without joins to several other tables for additional info)

The amounts in the date buckets are based on summed values (E.FOREIGN_AMOUNT for current and F.RESOURCE_AMOUNT for subsequent 11 months)
The other thing i cant figure out is if the accounting date (used for 'current' date bucket) doesnt exist for the input parameter date, i'd like to display a blank date and still retrive the next 12 months payment data... for now im just selecting the Max date <= this parameter date but if one doesnt exist i wont return any rows..an outer join doesnt want to work with the to_char translation.
 
SELECT
G.ACTIVITY_TYPE,
TO_CHAR(TO_DATE(E.ACCOUNTING_DT),'MMYYYY'),
TO_CHAR(TO_DATE(F.PAYMENT_DT),'MMYYYY'),
SUM(E.FOREIGN_AMOUNT),
SUM(F.RESOURCE_AMOUNT)
FROM RESOURCE E,
          PAYMENT F,
          ACTIVITY G
WHERE E.PROJECT_ID = 'PROJECT1'            --- this is a parameter
  AND E.ANALYSIS_TYPE IN ('A','B')
  AND TO_CHAR(TO_DATE(E.ACCOUNTING_DT),'MMYYYY') =
        (SELECT MAX(TO_CHAR(TO_DATE(P.ACCOUNTING_DT),'MMYYYY'))
          FROM RESOURCE P
          WHERE P.BUSINESS_UNIT = E.BUSINESS_UNIT
          AND P.PROJECT_ID = E.PROJECT_ID
          AND P.ACTIVITY_ID = E.ACTIVITY_ID
          AND TO_CHAR(TO_DATE(P.ACCOUNTING_DT),'MMYYYY')
 <= TO_CHAR(TO_DATE('05-DEC-2007'),'MMYYYY')) -- this will be 'current' 
                                                                                -- date parameter
     AND TO_CHAR(TO_DATE(F.PAYMENT_DT),'MMYYYY') > TO_CHAR(TO_DATE(E.ACCOUNTING_DT),'MMYYYY')
     AND F.PAYMENT_DT <= E.ACCOUNTING_DT + 365
     AND E.BUSINESS_UNIT =  F.BUSINESS_UNIT (+)
     AND E.PROJECT_ID = F.PROJECT_ID (+)
     AND E.ACTIVITY_ID = F.ACTIVITY_ID (+)
     AND E.BUSINESS_UNIT = G.BUSINESS_UNIT (+)
     AND E.PROJECT_ID = G.PROJECT_ID (+)
     AND E.ACTIVITY_ID = G.ACTIVITY_ID (+)
    GROUP BY G.ACTIVITY_TYPE,TO_CHAR(TO_DATE(E.ACCOUNTING_DT),'MMYYYY'),TO_CHAR(TO_DATE(F.PAYMENT_DT),'MMYYYY')
 ORDER BY 1,2,3 

Anyhow, I'll start to build the report with your formula ideas and let you know how it goes. Thanks again, Shae

 
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 25 Jun 2007 at 7:39am
I was not familiar with the TO_Char function so I looked it up  and it apprears to be an Oracle SQL function.  I am a TSQL kind of person.... so here are a few ramblings.
 
Alot of ways to code the SQL, but you might consider using a declared variables of the correct data type and then assign it the value of the date fields. If that would work then perhaps that will allow you to get the type of join you want  on the data.
 
Also keep in mind the CR formulas coupled with the ability do determine when they run, using conditional logic and suppression logic will give added control over the data returned by the SQL Query. In theory you should be able to extract the pieces you data need for each of the report fields.  CR is a very mature product and has more functionality in it than most of us will ever use. 
 
I dont know how to post reports here either, I dont know if this allowed but I can always be contacted through my web site.  (If I am breaking some rule here Brian just let me know.)
 
The nice thing about these forums is we can all learn from each other and I have really been enjoying this one because of its singular focus on CR.
 
I am looking forward to hearing what the final solution is case I ever need to do something similar.
 
Regards,
 
John W.
 
 
 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 25 Jun 2007 at 10:45am
Hi John,

Thanks for all the help on these questions (I'm working day and night at my job and on the book so I appreciate all the help I can get). Re posting info, you can insert screen shots using the Reply button in the top right corner (not the quick reply button at the bottom). But there is no way to attach .rpt files. So yes, sharing email info (when both parties agree) is the best way. In fact, I saw someone ask Dell for her phone number one time. I don't know if she sent it or not, but I found it interesting to read the full thread.


Brian
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 25 Jun 2007 at 10:56am

Thanks Brian for responding,  I appreciate your hosting this forum, I just did not want to overstep myself here.   I know there are other forums such as the one at Business Objects, but I have to tell you I like what you are doing here.  I hope it grows and keeps its CR focus.

 
Regards,
 
John W.

 
IP IP Logged
shae1
Newbie
Newbie
Avatar

Joined: 22 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
Quote shae1 Replybullet Posted: 26 Jun 2007 at 7:08am

Hi John, Thanks for your further ideas.

Yes, im using Oracle DB, with Peoplesoft queries as input. Ive pretty much achieved my desired result using a cross-tab object, but i had to define two TO_CHAR... date columns in the sql, one MMYYYY that I use as the report column for the cross tab (required to sort properly) and a corresponding Mon-YY month field that i set as the display field for the columns.
I also did a UNION in the sql to get the current date and amount first from one table unioned with the rest from the other table, and ordered it by the dates.
The main problem with this is that if any months dont have any data then the month wont show...but for now im banking on at least one type having some data in each month, which sets the others to 0 nicely, or, as a last resort, hoping the users wont mind this scenario and accept that no month = no data for it..
I'd like to be able to achieve the result using formulas and got the heading done ok, but was stuck with the amounts. Started writing some   if previous(fld1) and previous(fld2) etc logic but the cross-tab seems a lot less hassle for now.
There must be a correct method out there to achieve it with formula fields..
Thanks again
 
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 26 Jun 2007 at 9:18am
I have only used  cross tab reports a few times.  I start out liking what they are doing, but them I find them too restricting. In part, this is because there seems be so little control you have over the final layouts.
 
A cross tab guru might disagree and I am willing to be corrected.
 
You have likely done this but I would try passing the parameter value to the DateAdd function.
 
============================
 
DateAdd ("m", 1, CDate({parameterFieldDate})
 
This adds one month to your parameter, chnging the 1 to a 2 would add two months and so on..
 
You can change this to adding days instead of months, by changing the 'm' to a 'd' and entering the number of days you want.
-----------------------
You could create a formula that would have an "if-then-else' statement that might look something like this
 
stringvar y:="No records";
 
If  Between({datefieldName} ,(DateAdd ("m", 1, CDate({parameterFieldDate})),(DateAdd ("m", 2, CDate({parameterFieldDate}))) THEN
whatever you want present
ELSE
y;
 
-----------------------------------------------------
 
I have not tested the formula, so any errors are my fault and I sure you can make it much better.  My thought was you  could the date parameter as the base, then use the DateAdd function to control what date range of information is displayed in a particular column of the report.
 
Another thing I have learned over the years is sometimes I can get more granular control over my SQL queries in a report, by splitting them into smaller more controlled pieces. I place then place each query in a different command object.  Then through linking the objects in the designer I can get data  I want where I want it.  to where I want them. The nice thing about CR is that is accepts multiple command objects in a single report.  I also find that sometimes it is faster than sorting out a complex SQL statement. 
 
It sounds like you are close to having that "ah ha" moment where it comes together. Keep us posted.
 
Regards,
 
John W.
 
 
 
 
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.