Crosstab --2 date ranges 1

[ind123p]

CP(03/01/05 to 03/31/2005) YTD (01/01/05 to 05/31/2005)
Hours Amount Hours Amount
Client1 StaffA 10 $50 150 $750
StaffB 5 $60 50 $1300
Total 15 $110 200 $2050

Client2 StaffA 10 $50 150 $750
StaffB 5 $60 50 $1300
Total 15 $110 200 $2050


CP stands for Current Period
YTD---Year to Date

I get CP and YTD data. But I am also getting data that is difference between YTD data and CP data.

CP(03/01/05 to 03/31/2005) YTD (01/01/05 to 05/31/2005)
Hours Amount Hours Amount Hours Amount
Client1 StaffA 10 $50 140 $700 150 $750
StaffB 5 $60 45 $1240 50 $1300
Total 15 $110 185 $1940 200 $2050

Client2 StaffA 10 $50 140 $700 150 $750
StaffB 5 $60 45 $1240 50 $1300
Total 15 $110 185 $1940 200 $2050


I do not understand why I am getting the difference data (shown in bold.) I was just expecting to see CP and YTD data

Following are the formula I am using in my report

@CPdtrng
if {@trandt} in date(2005,03,01) to date(2005,03,31) then
"CP(03/01/05 to 03/31/2005)"

@YTDdtrng
if {@trandt} in date(2005,01,01) to date(2005,05,01) then
"YTD (01/01/05 to 05/31/2005)"


Can somebody please provide me a direction..?

 

[ind123p]

Hmm.. Nobody has replied to my post yet....

Please help!!!

 

[lbass]

In the crosstab expert, highlight your column field {@CPdtrng}->group options->specified order->add "CP(03/01/05 to 03/31/2005)" to the list->others tab->check "discard all others".

I'm assuming you have the YTD formula in a separate crosstab.

-LB

 

[ind123p]

Thanks LB for responding..

I have the YTD formula in the same crosstab .

CP formula and YTD formula are two different columns in the same crosstab.

Would your approach discard YTD ?

Let me try and I will tell you...

 

[lbass]

I think you should set these up as separate crosstabs where you suppress the row label and grid over the row labels in the second crosstab. Then use the zoom feature to align the crosstab grids.

Follow my instructions above to eliminate the "others" column.

-LB

 

[ind123p]

LB,

I tried your approach and I do get just CP and YTD but the data is messed up now..

I get the CP data in YTD data

CP(03/01/05 to 03/31/2005) YTD (01/01/05 to 05/31/2005)
Hours Amount Hours Amount
Client1 StaffA 10 $50 10 $50
StaffB 5 $60 5 $60
Total 15 $110 12 $110

Client2 StaffA 10 $50 10 $50
StaffB 5 $60 5 $60
Total 15 $110 12 $110


You were talking about having YTD in a different cross tab. I am assuming in a different section below the CP tab and underlaying the sections.

But how do I remove the row names.. I do not want Client and Staff to appear again.

Hope I am able to explain my problem correctly...Can you or somebody please suggest how I go about solving this issue.

 

[lbass]

Yes, you cannot discard others if you are trying to do this within the same crosstab--and you can't eliminate the extra column without doing that, so you need two crosstabs with the same rows, but with only one formula as the column. I mean you should place these in the same section. For the crosstab on the right, go to the customize style tab in the crosstab expert->format grid lines->select the grid lines around the row labels->uncheck "draw". Then in preview mode, suppress the labels. Use the zoom feature to align the crosstabs exactly.

-LB

 

[ind123p]

Hmm.. I did not think of the grid lines.. I will try it and let you know .. I think this should work....

 

[ind123p]

LB, Your approach worked perfect...STAR for you!!!!

For the current period, I get rows that match the criteria.
For YTD, I get all the rows.

But there is still a small problem...

So I get say 2 rows for CP and say 8 rows for YTD..I need to show the 6 rows with 0 hours and $0 amount under the CP section. Hope I am clear in explaing my problem

This is what I am getting

CP YTD
Hrs Amt Hrs Amt
C1 S1 X $X X $X
S2 Y $Y Y $Y
X $Y
X $Y
X $Y
X $Y

This is what I would like to have

CP YTD
Hrs Amt Hrs Amt
C1 S1 X $X X $X
S2 Y $Y Y $Y
S3 0 $0 X $Y
S4 0 $0 X $Y
S5 0 $0 X $Y
S6 0 $0 X $Y

Is there a way of achieving this..?

 

[ind123p]

Since nobody has responded to my question, can I assume that it is not achievable in a inserted crosstab ?

 

[ind123p]

Experts !!!! Help

Need your advice on this one.

 

[lbass]

You are being a little impatient here--note that not everyone is able to be on Tek-Tips at all times of the day!

On your current period crosstab, instead of discarding others, suppress the column and the label and then eliminate the grid over these, by doing the following:

For the inner cells and total, go to format field->common->suppress->x+2 and entering:

gridrowcolumnvalue("@CPdtrng") = ""

This method will give you the same rows as in your YTD crosstab, but it will not, however, show a value if there is no value in the database. If you need to show all rows regardless of whether the data appears, you will have to do a manual crosstab.

-LB

 

[ind123p]

LB,

Sorry for being little impatient...

Your solution worked perfect. I learnt something new today..

STAR for you!!!