Print Page | Close Window

Chart by months and week together

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19793
Printed Date: 07 May 2024 at 2:25am


Topic: Chart by months and week together
Posted By: wibni
Subject: Chart by months and week together
Date Posted: 22 Jul 2013 at 12:10am
Hi, I'm using Crystal 2010.
Is it possible to have grouping on teh x-axis done by month and week?

On my report, I'm showing the rolling purchase order values for the last 12 months, but I would like to show the current months in weeks.

So if the report was run today the x axis would start with the PO total for October up to June and for July it shows every weeks PO total.
is that possible at all?


Replies:
Posted By: DBlank
Date Posted: 22 Jul 2013 at 3:55am
you could use a totext to convert it to a string and group on that.
The trick will be to make sure you make the string is in an alpha order that matches your date order.

Posted By: wibni
Date Posted: 22 Jul 2013 at 4:03am
Not sure I understand.

Currently my 'on change of' field is set to this formula: ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyy-MM")

This formula I can't use if I want to show it weekly.
But how do I show the first 11 months as month and only the current months as weeks?

Posted By: DBlank
Date Posted: 22 Jul 2013 at 4:21am

how are you defining the current months weeks? days 1-7 of the month as week 1? or by monday-sunday? or some other fashion?

Posted By: DBlank
Date Posted: 22 Jul 2013 at 4:33am
basically you just need to add an if statement to your formula but I do not know how you are defining your "week"...
 
 
if pwformatdate(ToNumber({POOpenOrders.CurrentDate})) in monthtodate then ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyy-MM") + week condition here else ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyy-MM")

Posted By: wibni
Date Posted: 22 Jul 2013 at 4:41am
So far I've only been able to create a table in the report with the info I require.
Getting a chart will be the tricky thing because ideally it should all be in the same series.

In the report table I define my weeks for the current months with 5 fields - one for each week in the month.
I look for the last day in the current month and find out the week number for that day.
Then I go backwards 5 weeks to get all weeks in that month.
Later in the report I sum up the individual fields and get the weekly total.
My database table contains the weeknumbers so I look for the highest week number of the year and use that as a reference (this is DB field {POOpenOrders.MaxWeekNo})


//field 1 = week 1
IF  ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")     
    AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 4 = {POOpenOrders.MaxWeekNo} //get weeknumber of 1st week of the month
THEN
    {POOpenOrders.ExtendedCost}
ELSE
    0

//field 2 = week 2
IF  ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")     
    AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 3 = {POOpenOrders.MaxWeekNo} //get weeknumber of 2nd week of the month
THEN
    {POOpenOrders.ExtendedCost}
ELSE
    0

//field 3 = week 3
IF  ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")     
    AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 2 = {POOpenOrders.MaxWeekNo} //get weeknumber of 3rd week of the month
THEN
    {POOpenOrders.ExtendedCost}
ELSE
    0

//field 4 = week 4
IF  ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")     
    AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday) - 1 = {POOpenOrders.MaxWeekNo} //get weeknumber of 4th week of the month
THEN
    {POOpenOrders.ExtendedCost}
ELSE
    0

//field 5 = week 5
IF  ToText(pwformatdate(ToNumber({POOpenOrders.CurrentDate})),"yyyyMM") = ToText(DateAdd("m",0,currentdate),"yyyyMM")     
    AND DATEPART("ww",dateadd("m",1,currentdate-day(currentdate)) , crMonday)  = {POOpenOrders.MaxWeekNo} //get weeknumber of 5th week of the month
THEN
    {POOpenOrders.ExtendedCost}
ELSE
    0

Posted By: DBlank
Date Posted: 22 Jul 2013 at 5:15am
so I understand,
if a week (monday to sunday) straddles two months (current and last), you want to exlcude the counts from the the prior month and include them in the current month for that one week.
Is that correct?

Posted By: wibni
Date Posted: 22 Jul 2013 at 5:16am
yes.

Posted By: DBlank
Date Posted: 22 Jul 2013 at 5:19am
are you using a stored proc or can you for your source?

Posted By: wibni
Date Posted: 22 Jul 2013 at 5:24am
Not using a stored procedure but can do.
Just using a view currently.

Posted By: DBlank
Date Posted: 22 Jul 2013 at 5:31am
you might consider using a stored proc but you can also alter your view
you could much more esialy include in the column for 'current month' as defined by your week straddling in that then use that column as your way to break out the data in the report.
you can use an inner query to get the one value for each week number filtering on the currentmonth and join that back to your current data set on the week.

Posted By: wibni
Date Posted: 25 Jul 2013 at 2:15am
Thanks for your help!
Much appreciated.

I can also use this formula as my 'on change of' field to get my desired result - or at least parts of it.

If      Month({Data.Date}) <> Month(CurrentDate)
Then    DateSerial(Year({Data.Date}), Month({Data.Date}) + 1, 1) - 1
Else    {Data.Date}

What this does is, for every month prior to the current month, amend the date to the last day of its respective month, but leave the dates for the current month unchanged.


Print Page | Close Window