Suppress rows of Cross Tab 1

[deedar]

I have a Cross-Tab object in my report. Version of Crystal Reports is 10. Cross-Tab is as follows:

Rows:
MgmtReport.SortLevel1
MgmtReport.Level1
MgmtReport.SortLevel2
MgmtReport.Level2
MgmtReport.SortLevel3
MgmtReport.Level3
MgmtReport.SortLevel4
MgmtReport.Level4

Columns:
@Quarter

Summarized Fields:
Sum of MgmtReport.Amount

Subtotals and Labels are suppressed for SortLevel1, SortLevel2, SortLevel3 and SortLevel4. Row Grand Totals are suppressed.

There are some occurrences in this Cross-Tab, in which hierarchy is present only up to MgmtReport.Level2, and MgmtReport.Level3 as well as MgmtReport.Level4 are missing. In such cases, Cross-Tab is repeating values of MgmtReport.Level2 in Level3 and Level4 and the Row Description is blank.

I need to suppress those rows of Cross-Tab where MgmtReport.Level3 or MgmtReport.Level4 is blank.

 

[deedar]

To add to the above information; Indent Row Labels is checked with 0.20 inch and Column Totals on Top is checked

 

[lbass]

You can use a formula like this to suppress the unwanted summaries:

gridrowcolumnvalue("MgmtReport.Level3") = "" or
gridrowcolumnvalue("MgmtReport.Level4") = ""

Right click on the cell->format field->suppress->x+2 and enter it there. However, you will still have a blank row--even if you check "suppress blank row"--this feature doesn't work consistently.

-LB

 

[deedar]

Thank you.

I proceeded as follows:

1. Right-Click MgmtReport.Level3, Format Field, Common, checked Suppress, x+2 and entered the formula:
gridrowcolumnvalue("MgmtReport.Level3") = ""

2. Repeated Step 1 for MgmtReport.Level4 with formula as gridrowcolumnvalue("MgmtReport.Level4") = ""

3. Right-Click Sum of MgmtReport.Amount in MgmtReport.Level3 row, Format Field, Common, checked Suppress, x+2 and entered the formula:
gridrowcolumnvalue("MgmtReport.Level3") = ""

4. Right-Click Sum of MgmtReport.Amount in MgmtReport.Level4 row, Format Field, Common, checked Suppress, x+2 and entered the formula:
gridrowcolumnvalue("MgmtReport.Level4") = ""

5. In Customize Style, I checked Suppress Empty Rows

Now Cross-Tab is not repeating values of MgmtReport.Level2 in Level3 and Level4 but showing blank for these occurrences. However, it is still showing blank rows for these occurrences.

I have suppressed Subtotals and Labels for SortLevel1, SortLevel2, SortLevel3 and SortLevel4 and these rows are suppressed.

If I could conditionally suppress Subtotals and Labels for MgmtReport.Level3, then empty rows of Level3 may be suppressed ?? For Level4 (since it is last in the Rows List), Suppress Subtotals and Labels options are not enabled

 

[lbass]

As I said in the last line of my post, the crosstab expert does not successfully suppress blank rows at all times.

-LB

 

[deedar]

I eventually had to create a manual cross tab. This is working fine for row fields but I am stuck as how to add column fields.

I proceeded as follows:

1. Insert -> Group -> Level1 -> in original order
2. Similarly, inserted groups for Level2, Level3 and Level4
3. In Section Expert, checked Suppress Blank Section for all Group Headers
4. Suppressed Detail Section
5. Suppressed All Group Footers
6. Created the following Formula fields:

SumGrp1:
if {MgmtReport.Level1} <> "" then {MgmtReport.Amount} else 0

SumGrp2:
if {MgmtReport.Level2} <> "" then {MgmtReport.Amount} else 0

SumGrp3:
if {MgmtReport.Level3} <> "" then {MgmtReport.Amount} else 0

SumGrp4:
if {MgmtReport.Level4} <> "" then {MgmtReport.Amount} else 0

7. Placed these formula fields in Detail Section
8. Inserted Summary for SumGrp1, SumGrp2, SumGrp3 and SumGrp4
9. By default, these summaries were placed in Group Footer. I dragged them to Group Headers
10. This gave me the report layout in the desired format. But Group summary is showing total for all Post Entry Dates.

I need to create columns so that the report shows quarterly totals, i.e Qtr1, Qtr2, Qtr3, Qtr4.

 

[lbass]

You need to have a separate conditonal formula for each quarter, so change your summary formulas like this:

//{@Qtr1}:
if {MgmtReport.Level1} <> "" and
datepart("q",{table.date}) = 1 then //or 2,3,or4
{MgmtReport.Amount} else
0

Then insert summaries on these formulas.

-LB

 

[deedar]

Thanks a lot. It worked perfectly.

I created 16 formulas:

//{@Grp1Qtr1},{@Grp1Qtr2},{@Grp1Qtr3},{@Grp1Qtr4}:
if {MgmtReport.Level1} <> "" and
datepart("q",{MgmtReport.Post Entry Date}) = 1 then //or 2,3,or4
{MgmtReport.Amount}
else 0

//{@Grp2Qtr1},{@Grp2Qtr2},{@Grp2Qtr3},{@Grp2Qtr4}:
if {MgmtReport.Level2} <> "" and
datepart("q",{MgmtReport.Post Entry Date}) = 1 then //or 2,3,or4
{MgmtReport.Amount}
else 0

//{@Grp3Qtr1},{@Grp3Qtr2},{@Grp3Qtr3},{@Grp3Qtr4}:
if {MgmtReport.Level3} <> "" and
datepart("q",{MgmtReport.Post Entry Date}) = 1 then //or 2,3,or4
{MgmtReport.Amount}
else 0

//{@Grp4Qtr1},{@Grp4Qtr2},{@Grp4Qtr3},{@Grp4Qtr4}:
if {MgmtReport.Level4} <> "" and
datepart("q",{MgmtReport.Post Entry Date}) = 1 then //or 2,3,or4
{MgmtReport.Amount}
else 0

Then I placed these formulas in Detail Section and then inserted summaries on these formulas.

This fulfilled my requirement.

One thing which I could not accomplish as yet is that in the built-in cross-tab feature, Quarter columns are automatically suppressed to show only up to the quarter for which data is available; i.e., if data is available up to June, then it is grouped into Qtr1, Qtr2 and Qtr3, Qtr4 are not shown. In the manual cross tab, all 4 columns of Quarter are always shown.

Can the quarter columns be conditionally suppressed in manual cross tab?

There are other columns in this report after Quarter columns. So, let’s say, if Qtr3 and Qtr4 are to be suppressed, then I need to show the next column of report after Qtr2.

 

[lbass]

You can suppress columns by using a formula something like this:

datepart("q",maximum({table.date})) < 3 //to suppress qtr3 and qtr4

datepart("q",maximum({table.date})) < 4 //to suppress qtr4

However, to shift the columns to the left, you'd have to have multiple group sections a-d, to account for the four possible scenarios, with the appropriate fields in each. Then conditionally suppress the group sections using formulas like the above.

-LB

 

[deedar]

Thank you. Worked perfectly.

I proceeded as follows:

1. Created multiple group sections a-d from GH1a, GH1b, GH1c, GH1d to GH4c, GH4d
2. Placed 4 quarter column fields in GH1a, 3 quarter GH1b, 2 quarter GH1c, 1 quarter GH1d
3. Placed the next column field in GH1a to GH1d
4. Then entered the following formulas in GH1a to GH1d

// GH1a
datepart("q",maximum({MgmtReport.Post Entry Date})) <> 4

// GH1b
datepart("q",maximum({MgmtReport.Post Entry Date})) <> 3

// GH1c
datepart("q",maximum({MgmtReport.Post Entry Date})) <> 2

// GH1d
datepart("q",maximum({MgmtReport.Post Entry Date})) <> 1

5. Similarly, used the above steps for GH2, GH3 and GH4.

Thanks once again.

 

[Kunning]

I ran into the same situation now. But instead of using a table field directly in the row, I am using a formula in the row. This row formual named as @FAS Label has three values -
# Resolved Tier 1
# Resolved Tier 2
# Resolved Tier 3

Here is the formula for the @FAS Label:

if {PROBSUMMARYM1.OPEN_GROUP} IN ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] AND {PROBSUMMARYM1.CLOSED_GROUP} in ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] then '# Resolved Tier 1'
else if {PROBSUMMARYM1.OPEN_GROUP} IN ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] AND {PROBSUMMARYM1.CLOSED_GROUP} in ['ADV SUP-CLIENT/SVR','ADV SUP-CLIENT/SVR IE','TSC-CORP RUNNER'] then '# Resolved Tier 2'
else if {PROBSUMMARYM1.OPEN_GROUP} IN ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] AND not ({PROBSUMMARYM1.CLOSED_GROUP} in ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR','ADV SUP-CLIENT/SVR','ADV SUP-CLIENT/SVR IE','TSC-CORP RUNNER']) then '# Resolved Other'

The result in the cross-tab is in each cell there are three values with only one value has the correct numbers and the other 2 values have 0. How can I get rid of the 0 values in the cell.

 

[lbass]

You could change this formula to:

if {PROBSUMMARYM1.OPEN_GROUP} IN ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] AND
{PROBSUMMARYM1.CLOSED_GROUP} in ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] then
'# Resolved Tier 1' else
if {PROBSUMMARYM1.OPEN_GROUP} IN ['TSC-FLEET ADMN','TSC-FLEET ADMN-LD/SR'] AND
{PROBSUMMARYM1.CLOSED_GROUP} in ['ADV SUP-CLIENT/SVR','ADV SUP-CLIENT/SVR IE','TSC-CORP RUNNER'] then
'# Resolved Tier 2' else
'# Resolved Other'

Please explain how you have the crosstab set up. What are the rows, columns, and summary fields?

-LB