Conditionally suppress rows in a crosstab 3

[jenschmidt]

(Using Crystal Report v9)

The crosstab layout looks like the following:
200402 200403
Prov id: 110606 $1000 $1500
Prov id: 210606 $500 $550
Prov id: 200116 $800 $880

I want to suppress the rows when the prov id starts with a 2. I can't find the appropriate place to conditionally suppress rows in a cross tab - any ideas?

Thanks in advance!

jennifer.giemza@uwmf.wisc.edu

 

[synapsevampire]

If you don't mind just suppressing them from the entire report, try adding something like the following to the record selection formula:

left({table.provid},1) <> "2"

This probably won't pass to the database.

I'd create a SQL Expression which containing something like:

substring(table.field,1,1)

Then I'd reference the SQL Expression in the record selection and it would pass SQL:

%SQLExp <> "2"

You might have to convert either formula if your data type is numeric.

Another approach might be to drop th cross-tab into a subreport and then you could filter as described.

-k

 

[jenschmidt]

Unfortunately, I can't suppress the row completely because some of the dollars coming from the providers beginning with a 2 are actually being reported under another provider, so they have to stay in order to get all that data.

I've tried to pull in a subreport, but the main report and the subreport are based on a stored procedure which has 25-30 parameters. When I try to run the report, it asks for the end user to enter data for the parameters once for the main report and again for the subreport. I've tried to get the subreport to retrieve it's answers from the main report (see thread 149-816521) but nothing seems to work.

Does it seem to you that I'm at a dead end or is that just the way it feels?!

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

Go to format crosstab-> customize style tab->highlight the row->note the name in the "Alias for formulas" section or change it to a short name, and then go to preview mode and select a summary cell in the row->right click->format field->common->suppress->x+2 and enter:

GridRowColumnValue("shortname") startswith "2"

If the row field is a number, change this formula to:

left(totext(GridRowColumnValue("shortname"),0,""),1) = "2"

Repeat for the total column. For the row label, go to the field suppression format formula area and enter:

currentfieldvalue startswith "2"

However, even though these rows will be suppressed, I don't think there is a way to eliminate the suppressed row cells. If you don't want those values to appear in the crosstab, you could instead use specified order, omitting those starting with "2", and then choose the "Others" tab and choose "discard all others". This would eliminate the blank rows.

-LB

 

[jenschmidt]

Thanks for the idea lbass! When I get to the x+2 button next to suppress and first I entered the first formula you showed - I get an error that a string is required. The formula is using two strings, so it should be resulting in a string - I must be doing something wrong.

The formula is (both fields are strings) -
if {SP_TABLE.PERFORMING_PROVIDER_ID} StartsWith ["2"]
then {SP_TABLE.PERFORMING_PROVIDER_ID} else
{SP_TABLE.BILLING_PROVIDER_ID}

jennifer.giemza@uwmf.wisc.edu

 

[lbass]

Please paste the formula you tried that resulted in the error into this thread.

-LB

 

[lbass]

The x+2 suppression formula should look like:

GridRowColumnValue("@yourformula") startswith "2"

If you didn't change the alias, then the alias to be put between the "" should be "@"+your formula name, without the curlicue brackets. (I'm assuming that the formula you are displaying is your row field.)

-LB

 

[blindguy]

I am trying to do the same thing but for numbers. I want to supress all data on a row if the count is less than X. I have the supress checked and have CurrentFieldValue < 2 as the forumla, but the row still appears, it's just blank. Even if I tell it to supress black rows.
any suggestions?
TIA

 

[lbass]

As I mentioned in an earlier post, I don't think there is a way to eliminate blank rows resulting from suppression. You might consider whether using topN would meet your needs--you could eliminate lower values that way. To do that in a crosstab, while in preview mode, select the crosstab by clicking in the upper left corner. Then go to report->topN/group sort and select topN and enter the value for N, etc.

-LB

 

[blindguy]

The topN sort helped much, thanks!