Crosstab ?? Projects (rows) dont show if no date

[nb4884]

I have a cross tab where :
project attributes display as rows, Dates at particular stages as columns and max(date) as summary

Report displays only those projects that have dates at those stages, how ever it does not show all those projects that do not have dates so far. How do i get to display all projects even if there is no date and it is blank.

In queries- I have 2 queries one for project attributes and the other for Stages and have left outer join in links tab.

Pls suggest if i need to check/uncheck something.

Thanks

 

[lbass]

You should have a left join FROM the projects table TO the dates table, with no selection criteria on the dates table.

-LB

 

[nb4884]

Thanks LB, I infact have the same settings but still the cross tab report shows only those projects that have dates.

As a test I created a standard report with same 2 queries and same join linking, and in a standard report it shows me all projects with a blank space where there are no dates.

So my concern is I have to change some settings in cross tab but dint know what to change..

Suggestions??

 

[nb4884]

I am using CR XI version

 

[lbass]

Well, did you further test to see if a crosstab placed in the report footer of the new report would show all projects?

Are you using a formula for your column field? If so, what is it? Please identify your actual row, column and summary fields, using actual field names and/or show formula contents if a formula.

-LB

 

[lbass]

I tested a mockup report, and it returned zeros for cells with a null column field, but included all rows. Make sure that on the customize style tab you have NOT checked "suppress empty rows" and "suppress empty columns".

-LB

 

[nb4884]

Thanks LB, I checked my cross tab and it doesnot have supress empty rows/columns as checked. While I was checking this I also noticed that for my columns which is dates I have those dates in specific order.

For ex column 1 is where stage =1
column 2 is where stage =2
likewise 5 columns.

Now I have not chosen 6-100 stages to display so i checked discard all others on the options.

As a test I unchecked "discard all others" and checked " leave in their own group". With this I am able to see all projects but I also see all other stages as columns.

This is what my cross tab has
Rows- Project Name, Manager Name, Location
Columns Stage (specific order where stage =1 , stage =2...stage =5)
Summary - Date (Maximum)

Hope I am able to explain the scenario

 

[lbass]

You need to check "Put all others together, with the name: "

-LB

 

[nb4884]

Thanks LB, this did show the projects that I wasn't seeing before BUT I now have an additional column named 'Others' , which I don't want, Is there a way to take that off?

 

[lbass]

Use this formula as your summary field and then insert a maximum on it:

if {table.stage} in 1 to 5 then
{table.date}

-LB

 

[nb4884]

I tried the above, It simply took off the dates (data) in 'Others' but did not take off the column.

Others column is still there , however now its empty

 

[lbass]

You have to use this in conjunction with checking "suppress empty column" only. This worked when I tried it here.

-LB

 

[nb4884]

Did not work for me LB, however as per the logic I too was expecting it to work....

 

[lbass]

I tested this, and it did work, so the question is what you have done differently. Please explain your current set up exactly in the crosstab expert tabs and show the contents of your summary formula. Please always also explain what you mean when you say something doesn't work. What DID happen?

-LB

 

[nb4884]

Ok , Here is my set up

Cross-Tab : Tab
Rows : project attribute

column: Stage
Group options for column: Specified Order (the order i wanted by choosing stage = 1 and so on)

Summary: max(@formula)
where @formula= if {stage} in ["1","3","4","6","9"] THEN {Date}

Now on Customize tab here are the boxes that are checked:

Horizontal
Keep Columns Together
Show cell margin
Suppress empty columns
Suppress Row Grand Totals
Suppress column Grand Totals

 

[lbass]

Are either row or column fields formulas? Contents of those formulas?

You didn't answer my question about what happened.

-LB

 

[nb4884]

No , there is no formula in my rows or in columns(I only have specific stages in my column group options, where stage =1, stage = 3 and so on), the only formula that is there is in summary.

About the results: There is no change in report even if I click suppress empty columns. The report still remains same and display "Others" column as an empty column

 

[lbass]

I recreated this again, and still it works for me. I'm wondering whether you have a Date default that <> date(0,0,0). Try changing the summary formula to this:

if {stage} in ["1","3","4","6","9"] THEN
{Date} else
date(0,0,0)

-LB

 

[nb4884]

LB,

Taking off "Others" is stilla nightmare for me.

I tried the above formula too , I did not get any errors in formula but the column others is still there..

Thanks

 

[lbass]

I don't know why this isn't working for you. Another approach would be to insert a subreport to hold the crosstab where you limit the stages in the record selection formula.

-LB