Crystal Report Backcolor Change by week

[ms901Boss]

I have a Crystal Report and I need to have the backcolor changed for every week within the month.
Week one Aqua
Week two Orange etc.

I have a formula field that have the date in mm/dd/yyyy format but I'm not sure how to find the week starting at Monday ending at Friday and putting a color to it.

 

[pmax9999]

Hi ms901Boss

The function DatePart can return the week number, so a formula like this will do the initial part:

DatePart('ww',{table.your_field})

Crystal only has 16 'STANDARD' colors however you can create many different colors using the Color(R,G,B) function where R, G & B are integers between 0 and 255.

You could play around with the colour picker and manually get the R, B & G colors to use in a formula, or you could have a play around with a formula that uses a calculation based on the week number from the above formula to create the 52 different colors. It might take a bit of playing around to make sure all of the colours are suitable, perehaps something along the following lines as a starting point:

Local NumberVar x = DatePart('ww', {table.your_field});
Color(x * 4, x + 200, 2 * x + 50)

You will definitely need to play around to get suitable colours, but hopefully it will get you started.

Cheers
Pete

 

[ms901Boss]

That worked for getting the week number of each row, but how do I get it to give me a week count for each row in the month? I need the first week of every month to be a color and so on. I'm not sure how this needs to be done but this is what I was thinking. I have a shipping date, if I could take the month and find what week it is and have the back color a different color for each week.

 

[pmax9999]

What's your definition of 1st week? Is it the 1st 7 days or the week starting Monday (or perhaps Sunday) that has the 1st of the month in it?

 

[pmax9999]

OK, this struck me as an interesting challenge, so I thought I would have a bit of a play. It assumes that the first week of a month is the one first full week starting on a Monday. It is likely there is a more elegant way of doing this but below is how I did it.

It does rely on the Detail section being sorted in date order.

The following series of formula should be placed in the details section, but they can be suppressed in need.

[Code {@Week_Number}]
DatePart('ww', {Table.Field}, crMonday)
[/Code]

[Code {@First_Monday}]
If Not OnFirstRecord and
{@Week_Number} <> Previous({@Week_Number}) and
Day({Table.Field}) < 8
Then 'Y'
[/Code]

[Code {@First_Week}]
WhilePrintingRecords;
Global DateVar DTE;

If {@First_Monday} = 'Y'
Then DTE := Date({Table.Field});

If {Table.Field} < DTE + 7
Then 'Y'[/Code]

The Rows where where {@First_Week} returns a 'Y' are those dates in the first week of each month.

Hpe this helps.

Cheers, Pete

 

[ms901Boss]

The first week will be the first Monday of the month.

For the First_Monday I only get one 'Y' and its for a shipped_day that is blank. (I put a 'N' where it

If Not OnFirstRecord and
{@Week_Number} <> Previous({@Week_Number}) and
Day({@Shipped_day}) < 8
Then 'Y'
else 'N'



Here is what I currently have:

 

[pmax9999]

OK, I see the problem. I hadn't allowed for null values in the date field which is easy enough to test for, but the bigger issue will be that not every date will necessarily appear in the data. If the first Monday of a month does not appear in the shipped date data, the formula will fail.

As I see it, the only foolproof way to deal with this is if the database contains a calendar table on which to determine the relevant dates. Alternative work-arounds are available but realistically none are going to be particularly efficient and will require ongoing maintenance to ensure the report continues to work.

Hopefully someone else here can see something I've missed.

Cheers.

 

[ms901Boss]

Thanks pmax9999 I have it working now. Here is what I did:

if {@dateDiff} = 0
then "Week 0" else
" Week "+ totext(Datepart("ww",{@Shipped_day})+ 1- Datepart("ww",({@Shipped_day} - Day({@Shipped_day})+1)),0,"")

It display: week 1, week 2, week 3, week 4

 

[pmax9999]

Glad it does what you want.

I just ran your solution against my test data because I didn't understand the logic, and it certainly doesn't give the result I was expecting to see.

For example, your solution returns Week 2 for Monday May 3, whereas it is actually week 1 by my calculation. Similarly, Sunday Feb 7 returns Week 2 where I understood it should be week 1.

Anyway, I guess I just misunderstood your requirements.

Regards.