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!!
your advices are really appreciated
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.
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.
Help needed!!!
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
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.
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.
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..
After unioning...am seeing both the data for line of business i.e., retail & smatlease. And all the subtotals , grandtotals data is same.
Am doing report in crosstab.
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
Issue was not yet resolved with union also........still it is unable to ignore null values data in totalling.
Quote from: krishdw85 on 09 Jan 2012 02:40:03 AM
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?
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.
Using a single query in relational db , its impossible to hide nulls in totaling.
I did it finally through UNION.
Simple way to handle null values in crosstab is::::
filter ([LOB],[LOB]<>'null')
so that while totaling , we cant see null value in totalling.
: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?