Remove Sat/Sun Columns from running. 1

[Jackmetickler]

CR11 SQL2005

I am reporting daily qty to be despatched on each part in a tabular format. It shows 3 days previous to cover late orders and 14 days forward from todays date.

I now have to extend this report to cover a month and attempt to fit it onto one sheet of either a4/a3.

We do not despatch on Sat/Sun, so we have no need to see these columns. I need the space that these columns take up in order to fit it onto to one sheet of paper.

I have been attempting numerous formulae to xmt Sat/Sun on a running daily basis.. i.e I need to see a running 30 days or as near as I can exempting Sat/Sun.

My qty columns are calculated as follows (yesterday for example)
if {table.CURDUE}= CurrentDate -1 then {table.DUEQTY} else 0.

I need to see the nil qty for the weekdays hence I cannot hide nil qty.... also I do not want blank columns for Sat/Sun but to jump to mon/fri depending on whether I go forwards or backwards from todays date.

Any pointers as to how to jump Sat/Sun would be gratefully received.

Yours despairingly
Jack

 

[dgillz]

What does "xmt" mean?

If you don't want to see Sat/Sun on your report, exclude these days with a record selection formula. Have you tried that?

If so and this did not work, please post sample data, current output and desired output.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[poujor]

Try modifying your expression:

if {table.CURDUE}= CurrentDate -1 then {table.DUEQTY} else 0

to:

if ({table.CURDUE}= CurrentDate -1) and ({table.CURDUE} in [2 to 6] then {table.DUEQTY} else 0

Additionally use the "Suppress empty Columns" on the Customize Style tab of the Cross-Tab Expert


Poul

 

[Jackmetickler]

Many thanks gentelmen for your advice.

dqiltz - cannot use exclude records as curdue is a date field and I would have to input all dates in year that are
sat or sun unless you can tell me how to accomplish it... however it may be one option.... sorry for using term xmt which means exempt in my current job... but I did not even register I had used it, once more apologies.
I will post some sample data on Monday if it will help.


poujor - same problem as curdue is not giving me working day 1-7. I think I need to give you further details in order not to waste your valuable time.

Many thanks for all your excellent suggestions
Jack

 

[synapsevampire]

If "curdue is not giving me working day 1-7" then you've no need to filter out weekends, right?

I think that the language barrier is the biggest hurdle here, post example data of what is returned by the database, and then show what you need as output.

-k

 

[lbass]

Sounds like you're developing a manual crosstab. I think you would have to develop your formulas like this:

//{@curr-1):
datevar daybef;
if dayofweek(currentdate-1) = 1 then
daybef := currentdate-3 else
if dayofweek(currentdate-1) in 2 to 5 then
daybef := currendate-1;
if {table.CURDUE}= daybef then
{table.DUEQTY} else 0

//{@curr-2}:
datevar twodaysbef;
if dayofweek(currentdate-2) in [1,7] then
twodaysbef := currentdate-4 else
if dayofweek(currentdate-2) in 2 to 4 then
twodaysbef := currendate-2;
if {table.CURDUE}= twodaybef then
{table.DUEQTY} else 0

//{@curr-3}:
datevar threedaysbef;
if dayofweek(currentdate-3) in [1,6,7] then
trheedaysbef := currentdate-5 else
if dayofweek(currentdate-3) in [2,3] then
threedaysbef := currendate-3;
if {table.CURDUE}= threedaysbef then
{table.DUEQTY} else 0

//{@curr+1}:
datevar nextday:
if dayofweek(currentdate+1) = 7 then
nextday := currentdate+3 else
if dayofweek(currentdate+1) in 3 to 6 then
nextday:= currendate+1;
if {table.CURDUE}= nextday then
{table.DUEQTY} else 0

//{@curr+2}:
datevar in2days:
if dayofweek(currentdate+2) in [1,7] then
in2days := currentdate + 4 else
in2days := currentdate + 2;
if {table.CURDUE}= in2days then
{table.DUEQTY} else 0

//{@curr+3}:
datevar in3days:
if dayofweek(currentdate+3) in [1,2,7] then
in3days := currentdate + 5 else
in3days := currentdate + 3;
if {table.CURDUE}= in3days then
{table.DUEQTY} else 0

//etc.

This then becomes endlessly complicated by holidays, but otherwise I think the above should work.

-LB

 

[Jackmetickler]

Many thanks all

Sorry my plain language explanation was confusing.

I am just in the process of adapting LB'S formualae and testing out and I will get back once complete with data if required.

Once again many thanks for all your assistance.

Jack

 

[Jackmetickler]

LB

Please could you just elucidate a little for me.
Formula working great, until I got to curr+7
//
datevar in7days;
if dayofweek(currentdate+7) in [1,2,7] then
in7days := currentdate + 9 else
in7days := currentdate + 7;
if {SO_Detail.CURDUE_28}= in7days then
{SO_Detail.DUEQTY_28} else 0
//

I am trying to follow the logic but I am a little puzzled by second line
if dayofweek(currentdate+3) in [1,2,7]then

presume this is day of week number 1,2,7
?why day 2 ?

I know there is a good reason but I cant see it.

Many thanks
Jack

 

[Jackmetickler]

LB

Further to my last

curr+7 and curr+8 are returning same data as curr+5 and curr+6?????

Jack

 

[lbass]

You have to work out the logic for each formula individually. For curr+7 days the seven days crosses only one weekend while for some it crosses two:

//{@curr+7}:
datevar in7days;
if dayofweek(currentdate+7) in 2 to 4 then
in7days := currentdate + 9 else
if dayofweek(currentdate+7) in [5,6] then
in7days := currentdate+ 11;
if {SO_Detail.CURDUE_28}= in7days then
{SO_Detail.DUEQTY_28} else 0

However, let me backtrack. I think you would be better off using Ken Hamady's formulas:

http://kenhamady.com/form17.shtml

At the end of each add or subtract formula, you would add the clause that sets the date equal to the variable "target".

-LB

 

[Jackmetickler]

LB
Many thanks
I am going to persevere with your formula until I get there, its all in the learning process for me..
if I get stumped I will certainly try Ken's formula next.

All your assistance is greatly appreciated.

Best regards
Jack