INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Alternate coloring of records based on 4 day intervals

Alternate coloring of records based on 4 day intervals

(OP)
Hello,

I am using CR 2013 on an SQL database.

I have a request to color records that are in 4 day bins starting from December 31, 2016 in alternate colors (2 colors)
so at the end my report should look like:

Dec 31, 2016 to Jan 3, 2017 - Color blue
Jan 4, 2017 to Jan 7, 2017 - Color green
Jan 8, 2017 to Jan 11, 2017 - Color blue
Jan 12, 2017 to Jan 15, 2017 - Color green
Jan 16, 2017 to Jan 19, 2017 - Color blue
Jan 20, 2017 to Jan 23, 2017 - Color green
..... etc.

is there a way to accomplish this?

Any help is appreciated.
Thanks
S.E.

RE: Alternate coloring of records based on 4 day intervals

I am assuming you are talking at the detail level. I would think it would be possible, but definitely would take some thought on how to do it. My first thought is a date diff from Dec 31, 2016, then do a modulo or remainder. But I do not have details yet.

RE: Alternate coloring of records based on 4 day intervals

(OP)
Yes,

it will be for coloring the details section based on the 4 day increments.

Thanks

RE: Alternate coloring of records based on 4 day intervals

I did it this way:

CODE

If      Remainder(Round(DateDiff('d', Date(2016, 12, 29), Date({Table.DATE}))/4,0),2) = 1
Then    crBlue
Else    crGreen 

Hope this helps.

RE: Alternate coloring of records based on 4 day intervals

First limit your records using a date parameter {?StartDate} in your selection formula:

{table.date}>={?StartDate}

Then go into the section expert->details->color tab->background color->x+2 and enter:

whileprintingrecords;
datevar start := {?StartDate};
numbervar cnt;
numbervar rng;
numbervar curr;

if onfirstrecord or
date({table.date})=previous({table.date}) + 1 then
cnt := cnt + 1 else
cnt := cnt;
if cnt = 9 then
cnt := 1;
rng := day(dateadd("d",cnt, start-day(start)));

if rng < 5 then
curr := crblue;
if rng > 4 then
curr := crgreen;
curr

You might want to replace crblue and crgreen with some lighter hues for readability. You can create custom colors using the color function: color(R,G,B).

-LB

RE: Alternate coloring of records based on 4 day intervals

(OP)
This is excellent,

Thank you very much.

smile

RE: Alternate coloring of records based on 4 day intervals

pmax9999,

Sorry--cross-post. Your solution is simpler than mine.

-LB

RE: Alternate coloring of records based on 4 day intervals

No stress LB. Obviously both working on the solution at the same time.

My initial thought was to take the approach you did - but pure laziness often drives me to find simpler solutions.

I don't often get the chance to beat you to a solution - I suspect I am at a timezone disadvantage - that's my excuse anyway, and I'm stickin' to it. bigsmile

Cheers
Pete.

RE: Alternate coloring of records based on 4 day intervals

(OP)
Thank you for both,

as always this site and users are the greatest.

You guys and gals have saved me countless hours spent trying to find a way to overcome this and from pulling the few hairs left on my head out,

Safa

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close