Crosstab Report Producing Rows With Blank Measures - How to Prevent This?

[pdaprem]

I have a crosstab report which is producing rows with blanks in the two measure data fields. When I export to excel and filter out the blanks I have the expected number of data rows in the report. The issue I need to resolve is what is causing the extra rows. 

If I pull data using the same report but removing the crosstab I don't get extraneous rows so feel it has to be something with the crosstab.

[kattaviz]

Hi,

Did you try using the supression for Crosstab (in Cognos 8.4.1). using this you can supress rows,columns that have zero values or missing values.

HTH

[pdaprem]

Thanks! Will look into that!

[pdaprem]

I tried usinng the Suppress option with Report Studio but this is causing the report to run excessively long, to teh point where it is completing before the system runtime settings expire.

Any suggestions??

[rockytopmark]

Replace the Data Item on the Row with anew Data item, incorporating the FILTER() function.  Try something like:

filter([your existing data item],tuple([your existing data item],[your measure]) > 0)

[pdaprem]

Want to make sure I'm clear on what you are recommending. Please see the attached doc file for a view of my Crosstab report.  I have 10 rows of data with two columns containing measures. Am I to replace all 10 data items on the row with this statement?  Since I have two measures can I get away with just including one in the new statement?  Reality is that both measures will mirror each other with the difference being that one would be in USD and the other in someother currency.

[pdaprem]

Any comment relative to my last post on this? Post in reply to suggestion fron rockytopmark.

[bdbits]

If your two measures are always either both going to be zero or both non-zero, then filtering on just one of them should be fine. Just pretend you are the Cognos server and apply the filter to some imaginary data and you will see this makes sense. :-)

I would put the filter on the innermost row item and see if you get the results you want. To me it is hard to say if you will need it on additional row items without knowing more about the data.

[pdaprem]

Set up a filter like this

Filter(Billing Ready Secure Query.Workforce Dim.Workforce.Workforce.Learner Country Currency at Proc Time, tuple(Billing Ready Secure Query.Dates.Proc Date.Proc Year.Proc Year, Item Total Cost Lc (Variable Cost)) > 0 )


Getting the error that the filter expression must evaluate to Boolean. OP-ERR-0061

It appears that my blank cells when I run the Crosstab Report without any filter can be attributed to the fact that some of my Row values come from Regular Dimension A and the others come form Regular Dimension B. If I delete the rows from either of the two Dimensions my report runs as expected. Unfortunately I need to have data from both Dimensions in this report.

[ammuk]

are u using relational package or dimensional

[pdaprem]

Dimensional package.