Suppressing columns in Cross-Tab

[LinPro]

Hi
CR 2008 w/Oracle 10g

I created a cross-tab report. It was completed until the user wanted to suppress columns they didnt want to show.

Year 2010
Month 5/2010 6/2010 7/2010
Days 5/1/2010 5/25/2010 Total 6/15/2010 total 7/1/2010 total

x 1 2 3 2 2 3 3
Y 2 3 5 3 3 5 5
Z 5 4 9 10 10 15 15

They wanted to see the months and days total for the current month only. And for the previous months, they only wanted to see months total.

My question is = how do I suppress the previous days date and total? I did a conditional suppress using month(xdate) < current month(date/time ) and year(xdate) < year(date/time). It suppressed everything.

Please advice.

Lin

 

[lbass]

You could just remove the month and day column and replace them with a formula that is printed on change of day:

if month({table.date}) = month(currentdate) and
year({table.date}) = year(currentdate) then
{table.date} else
date(year({table.date}),month({table.date}),1)

-LB

 

[LinPro]

thanks I will try it when I get back to work.

 

[LinPro]

thanks lbass - it worked.

 

[LinPro]

Hi lbass

I used your formula and it works great for the month, but now I need to suppress the days as well. I need the days to work the same way as the months.

thx

 

[lbass]

I don't know what you mean. This formula would have resulted in one column per month up to the cuurent month and then one per day for the current month. So you would have a year column and then this month/day column as your second column.

LB

 

[LinPro]

If you look at my example, there is a third row for the days(date).
The current date - should show all. Month/Year and Days/Year.

Any date prior to current date should show only Month/Year. Therefore third row should not show up. Client wants only level 1 and 2 for prior months.

Does this help?

 

[lbass]

Using my suggestion you would have only two rows: Year, and Day/Month, where the day appears only for the current month, and month for all previous months. Why don't you show what you would like it to look like if this doesn't meet your needs. My suggestion would result in (the example is just for the current year):

2010
Jan Feb Mar Apr May Jun Jul Aug Sep 10/1 10/2 10/3 10/4 //etc.

-LB

 

[LinPro]

Hi lbass

I attached my file. You will see the 3rd row showing the month day year. Plus the values below it. I need this to be suppressed.

thx

Lin

 

[lbass]

Your file is not attached. If you had tried my suggestion, there would only be two "rows" of column headings.

-LB

 

[LinPro]

hi I did try it

however I need 3 rows, but only 2 row showing when it is the current month

 

[lbass]

Please just show a mock sample of what you want the results to be by entering it in this thread.

-LB

 

[LinPro]

2010
Sept Oct Nov
total Total 11/5 11/15 11/16 11/20 Total Total
2 3 5 2 1 2 10 15
3 0 10 2 3 2 17 20
4 5 3 6 9 10 28 37


I hope this helps

thx
Lin

 

[lbass]

The problem is getting a month total for the current month, without getting duplicate columns for the other months.

One option would be to use two crosstabs that are set up to appear as one crosstab. You would have to be willing to have the row totals on the left, however. The first crosstab would contain three columns: date on change of year, date on change of month, and a formula {@spaceholder}:

whilereadingrecords;
"Total"

In the customize style tab, you would suppress subtotals for the total column since the spaceholder column will now hold these. You would also check "row totals on left".

Then insert the second crosstab to the right of the first in design mode. Add columns for date on change of year, date on change of month, and a formula:

//{@currmodts}:
if month({table.date}) = month(currentdate) and
year({table.date}) = year(currentdate) then
{table.date} else
date({@null})

...where {@null} is a new formula that you open and save without entering anything.

Then add the following formula as your summary field and insert a count on it:

if month({table.date}) = month(currentdate) and
year({table.date}) = year(currentdate) then
{table.yoursummaryfield} else
tonumber({@null}) //remove the tonumber() if your field is a string

In the customize style tab, suppress subtotals for the year and month and also check "suppress row totals", "suppress empty rows", "suppress empty columns", and "suppress row grand totals". Suppress the row labels, and then in the customize style tab->format grid lines->uncheck "draw" for each of the lines around the row labels and also the vertical line next on the left of the first column.

Then go into the section expert->select the section containing the crosstabs->check "relative positions". Place the second crosstab so that it slightly overlaps the first crosstab. You will have to play with this until they are aligned. It helps to snap them both to a horizontal gridline.

I created this and it looks like one crosstab, not two.

-LB

 

[LinPro]

lbass

Thanks I will try it today.

Lin

 

[LinPro]

lbass

Thanks a whole bunch. Most of it worked, I had to do some work to make it fit my requirement. But overall, it is working fine now.

Lin