Conditional suppressing of a row in a crosstab

[Former Member]

Hello All

Please point me out How to conditionally suppress a row in a crosstab report based on the content in the crosstab.

i.e

i have such a crosstab

                                  01:00     02:00     03:00   04:00   05:00   06:00    Total
------------------------------------------------------------------------------------------------------------------------
Event X                      1            0             0           1          0           1         3
Event Y                      1            0             1           1          0           1         4
NOEVENT                   1            0             0           1          0           1         3 
------------------------------------------------------------------------------------------------------------------------
Total                           3            0             1           3          0           3         10

When the row entry is of "NOEVENT" , then i need to suppress the whole row in the crosstab, including the one reflecting for the Total.

                                  01:00     02:00     03:00   04:00   05:00   06:00    Total
------------------------------------------------------------------------------------------------------------------------
Event X                      1            0             0           1          0           1         3
Event Y                      1            0             1           1          0           1         4
------------------------------------------------------------------------------------------------------------------------
Total                           2            0             1          2          0          2         7

Is such a thing possible ??

If yes ... Please help me to achieve it

Regards

Srivatsa

[Former Member · Accepted solution]

I think you can try using record selection formula like

{field}<>"NOEVENT"

Regards,

Raghavendra.G

[Former Member · Accepted solution]

Hello Raghavendra

Thanks for your reply.

Using record fields will completely remove the row entry and the columns associated uniquely to it.

I want the column values to be retained, and also the Total fields should calculate the values only that are visible.

Is there any way to change the row data of the crosstab total field in the runtime..

Srivatsa

[Former Member · Accepted solution]

Hello

To be specific i need something like this to be done:

Current output format

01/01/2009  01:00    02:00    03:00    04:00    Total

Event A      1          2       0       1        4
Event B      1          1       0       0        2
Event C      1          0       1       0        2
Total         3         3       1       1        8

Required output format

01/01/2009  01:00    02:00    03:00    04:00    Total

Event A      1          2       0       1        4
Event B      1          1       0       0        2
Total         2         3       0       1        6

Note that the even though the entries for the Event A and Event B is '0', It still should appear in the Summation field

Regards

Srivatsa

[Former Member · Accepted solution]

This looks strangely familar...

When you pull your data from the database, try to replace the NO EVENT value with an event that you know will be on the report (like SELECT MIN(event) from... where...).

I can't seem to put my finger on it right now, but I answered a similar question quite recently. Look for "cross tab" in the post titles from the past few days or so... I don't recall if it was about adding columns for missing data, or something similar, but that would be a good guess.

HTH,

Carl

[former_member260594 · Accepted solution]

Srivatsa,

Create 2 formulas

1) to output the row field when it is not equal to No event

If <> 'No event' then

2) to output the summary field when the row field is not equal to No Event

If <> 'No event' then

Replace the row and summary fields in the crosstab with the formulae.

You should now see an unnaemd row with zero values.

In the corsstab expert enable the option to suppress empty rows.

[Former Member · Accepted solution]

Hello Graham

Thank you for your reply

Could you please clarify what do you mean when you say

to output the summary field when the row field is not equal to No Event

The summary fields are based on the count of rowfields at a particular time.And how can i get it inside a formula ?

Could you please give me in the form of a snippet

Thanks again

Srivatsa

[former_member260594 · Accepted solution]

Srivatsa,

If the field used as the Summarized field in the crosstab is the same as the field used for rows then the formula would be same as the first one. Please note that the does not equal symbol is read by the forum as HTML and therefore does not appear.

Here is the formula written without using the not equal symbol;

if NOT ( = 'No Event') then

Substitute the row and summarized fields in the crosstab expert with this formula.

[Former Member · Accepted solution]

You can make "not equal" show by typing andpersand (&) followed by "lt;" ot "gt;". I.e., & l t ; (without spaces) gives &lt;, and & g t ; (without spaces) gives &gt;.

HTH,

Carl

&lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt; &lt;&gt;

[Former Member]

My solution:

Open Cross-Tab Expert. In Cross-Tab under Rows select your row which should be conditional suppressed. Then select Group Options. This will open Cross-Tab Group Options dialog. Go to Specified Order and add "Event X" and "Event Y". In this case, this rows will be displayed in your cross-table all other rows will be placed in one row "Others". Then select tab Others and check Discard all others.

[Former Member]

It's probably late but as I found this blog looking for solution and Graham gave me a clue I did found the solution .

You need to create 2 formulas:

Formula1 :

if NOT ({eventfield}) = "NoEvent" THEN {eventfield} ELSE "0" (it can be anthing)

Formula2:

if NOT ({Formula1}="0" ) then 1 ELSE 0

Then in your cross tab expert , in rows area place the Formula1 and in your Summarized Fields area place your Formula2 and change summary to SUM.

[Former Member]

Hi I have a similar problem,

I have an clock in solution, where i have some dates with data such as, various entries for a date eg, 01/11/2010 1hr, 01/11/2010 3 hrs etc, 03/11/2010 2hrs , 05/11/2010 4.5hrs, 05/11/2010 4 hrs so i need total for each day and highlight only those days, where total is less than 4.5, including days which donu2019t have records eg 02/11/2010 & 04/11/2010, I summarise in totals using a cross tab, to get summarised output for each day as,

Totals

01/11/2010 4

03/11/2010 2

05/11/2010 8.5

in order to get the dates which didnu2019t have records, i added a dataset from Excel spreadsheet where i just have a sequential dates for the year , and use record selection to select only those dates in range which i need to display, so the result i get

01/11/2010 4

02/11/2010 0

03/11/2010 2

04/11/2010 0

05/11/2010 8.5

so far so good, all using cross tab, now i want to suppress rows which have total > 4.5 so the result should be

01/11/2010 4

02/11/2010 0

03/11/2010 2

04/11/2010 0

How can i do that?

[Former Member]

This threat is closed and if you want answers I recommend you start a new thread. But first you should seach for recent crosstab issues as I have seen several in the last few of days.