Issue with hiding null totals in subtotal & grantotal crosstab

[krishdw85]

Hi Experts,

Pl guide me!
How to handle null records in totaling.

I have a two statements in two dimensions like:

case
     when [XYZ]. [Line of Business].[Line of Business Desc]='Retail'
    then [XYZ].[New/Used].[New/Used Desc]
end

case
    when[XYZ]..[Line of Business].[Line of Business Desc]='SmartLease'   
  then  [XYZ]..[Line of Business].[Line of Business Desc]
end

Data is looks like this in cross tab output:::

Applications#  20100813    20100814
new                 171             200
used                  59             310
                       110                10
subtotal           340            420
smartlease        20              30
                        100             40
subtotal           120             70
grandtotal       460            490

I want to ignore   **                     110                10**  &                         100             40 these columns and and want to ignore them in totalling also.

Your help is really appreciated.
Thank you so much!!

[krishdw85]

your advices are really appreciated

[MFGF]

If you want the null values to be removed, add a detail filter to your report:

[XYZ].[Line of Business].[Line of Business Desc] is not null

Regards,

MF.

[krishdw85]

I did this filter , and tested also.No result. This will hide in LOB , not in new used.
If we keep two filters like LOB is not null and Newused is not null then report will display blank page.

Why it is not working is::

Null records are coming in two dimensions i..e, New , and line of business.
And hence both the condition satisfies and the report coming is blank.

And i tried with Boolean variable also same result.

[krishdw85]

Help needed!!!

[blom0344]

This can be easily solved by splitting the original query into a  2-set and applying a union :

Query1:

Fetch  [XYZ].[New/Used].[New/Used Desc]
with detail filter  [XYZ]. [Line of Business].[Line of Business Desc]='Retail'

Query2:

Fetch  [XYZ]. [Line of Business].[Line of Business Desc]
with detail filter  [XYZ]. [Line of Business].[Line of Business Desc]='Smartlease'

This allows for creating the proper filter for each set

The resulting Union query can then be used as input for the list/crosstab

[krishdw85]

Hi ,
Thanks for your help.

I ll make a try .
In the meanwhile may i know u r mail address so that i cans end u some screenshots with an example.

krish.dw85@gmail.com is my id.

[krishdw85]

Hi ,

I done with your solution which u provided....but not getting exact data.

am unable to paste the screenshot, may i know u r mail id so that i ll send XML.

Thanks for the reply.

[blom0344]

The cognoise forum has an option to send pm's (private messages) through the board. This is a better option than posting email addresses out into the open.

The xml has not much value since I do not have your package definition as well. Perhaps you better describe what does not work for you..

[krishdw85]

After unioning...am seeing both the data for line of business i.e., retail & smatlease. And all the subtotals , grandtotals data is same.

[krishdw85]

Am doing report in crosstab.

[blom0344]

Just  a tip : You can modify your own post, so you do not need to create another entry to add comment to an earlier post.

Your last 2 posts really give no clue what has improved and what still needs to be fixed

[krishdw85]

Issue was not yet resolved with union also........still it is unable to ignore null values data in totalling.

[blom0344]

Issue was not yet resolved with union also........still it is unable to ignore null values data in totalling.

What do you mean by ignore?  Each set within the union needs it own filter definition to make sure relevant data is fetched. Did you apply any filters?

[krishdw85]

Thanks blom for your response.


finally i did it using nvl function..ie., nvl(lob,0) ...and filtered on nvl-retail & smartlease.

Thanks to cognoise & all.

[krishdw85]

Using a single query in relational db , its impossible to hide nulls in totaling.
I did it finally through UNION.

[krishdw85]

Simple way to handle null values in crosstab is::::


filter ([LOB],[LOB]<>'null')

so that while totaling  , we cant see null value in totalling.

[blom0344]

 :o  :o
This is confusing. You mention that you sorted the problem through the defined queries?  In what context should we read your last post?