Joined: 22 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
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.
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.
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
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....
Joined: 22 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
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
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
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.
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
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>
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
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.
Joined: 22 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 3
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..
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
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
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.
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
IP Logged
