Suppress group of rows in cross-tab

[Former Member]

Hi!

In my report I have 3 groups:

1.Status (Open, Closed)

     2. WorkGroup (WG1, WG2, ..., WGn)

          3. ID

and running total, which counts ID of each WG.

I get cross-tab in ReportHeader:

   Status  | WorkGroup | CountID.

Open	WG1	2
	WG2	1
	WG3	1
	WG4	2
	WG5	2
	Total	8
Closed	WG6	1
	WG5	2
	Total	3

And now I want to hide/suppress rows with status "Closed"..

I have read previous topics, but I can't implement them to my situation..

Help me, please! How can I do this?

Thanks in advance!

[abhilash_kumar · Accepted solution]

Hi Alena,

Try this please:

1) Create a formula called @Status with this code:

If {Status} <> 'Closed' then

     {Status}

else

     ""

2) Create a formula called @Workgroup with this code:

If {Status} <> 'Closed' then

     {Workgroup}

else

     ""

3) Modify the 'evaluation' formula of the Running Total to:

{Status} <> 'Closed'

4) Go to the Crosstab Expert > Customize Style tab > Check the 'Suppress blank rows' and 'Suppress blank columns' option.

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Thanks, Abhilash!

After making changes I get good cross-tab! Thanks a lot!

But.. now rows have wrong values because group ID was not used.

May be you can tell any solution for this problem?

Appreciate your help!

[abhilash_kumar · Accepted solution]

I hope you've replaced the 'Status' and 'Workgroup' fields on the Crosstab with the formula fields above.

Where was GroupID used on the original crosstab before the change?

-Abhilash

[Former Member · Accepted solution]

Yes, I have replaced fields with formulas.

I used GroupID in 'evaluation' formula of the Running Total.

and then modified it with the formula field {Status} <> 'Closed'.

[abhilash_kumar · Accepted solution]

Instead of using a Running Total for the 'count', create a formula with this code:

If {Status} <> 'Closed' then

     1

else

    0

Add this formula field as the 'Field to Summarize' and set the Summary Operation to be 'Sum'.

-Abhilash

[Former Member · Accepted solution]

Unfortunately, these code didn't help.

But thanks for all!

[abhilash_kumar · Accepted solution]

Do you have some screenshots of what you see now vs what is expected?

-Abhilash

[Former Member · Accepted solution]

If in the Running total 'evaluation' field is formula then I have values in red fraim.

In cross-tab I have the same values but using formulas instead of running total (yellow frame + pict.2).

In green fraim is expected values (running total where 'evaluating' field is GroupID - group №3).

[abhilash_kumar · Accepted solution]

Would you be able to attach the report with saved data please?

-Abhilash

[Former Member · Accepted solution]

I send my report to your e-mail.

Thanks!

[abhilash_kumar · Accepted solution]

It seems the report you sent to me is based off of a Business View - that won't save data unfortunately

-Abhilash

[Former Member · Accepted solution]

Yes, I use Business View.

I didn't know about impossibility to save data.. Sorry!

I just have a few records for each ID. So I groupped them. And now can't get right values in count formula.

Thank you for your efforts!!!

[former_member205840]

Hi Alena,

If you apply direct filter in record selection formula like

Status <> "Closed"

Then the closed status records will not appear in your report right ?

Thanks,

Sastry

[Former Member]

Hi, Sastry!

Yes, you are right. "Closed" records will not appear in the report (but they should be there).

Thanks for reply!