For the first record ProductionMonth = 2009-03, but no amount is received for 2009-06, 2009-08 and 2009-09 months. So that means the Account Charge is missing for these months and I want to display “Missing” in those cells instead of blank cells.
Now for the second record, ProductionMonth = 2009-07, and we haven’t received amount for 2009-8, so I want to display “Missing” for that month. But I don’t want to display any thing for the month of 2009-06 as at that time, the account was not in existence.
Could some one please help me with the way to achieve the same at the report level.
Please let me know if my requirement is not clear, I will try my best to clarify it.
You can modify your query as NVL(value,0). So if the account exists for an year and there is no value 0 will be returned by the query.
Next in Infoview u can use if(value=0;‘Missing’;value+’’). Sisnce ‘Missing’ is a string so I have converted value to string as well.
If this doesnt work can you ask your costomer for displaying 0 instead of ‘Missing’? Sinc there is a datatype difference so I am not sure how crosstab will behave.
Here my Account Number = 456. And Production date is 2009-07. Now for 2009-08, the charge is null, so I want to display missing at that place.
Also the charge for 2009-06 is null, but this account was created in 2009-07(ProductionMonth), so it is but obvious that for 2009-06, the charge will come null, so I don’t want to change anything there.
But as the charge for 2009-08 is null and this account was created in 2009-07, that means I haven’t recevied any amount for this. so that means it is missing for that particular month.
Hope this time i am clear, if not please let me know…
ProductionMonth is the one which says when the account was created and Invoice Date year month is the one which says the charges per month received. And the formula I am using is -
=If([Invoice Date Year Month] >= "2009-07" And [ProductionMonthYear] = "2009-07" And IsNull([ChargeAmount]);"Missing";[ChargeAmount])
But it is showing Missing everywhere, but i want to see only if the invoice date year month is greated than production month. And the formula for productionmonth is:
I think I am confusing you, let me try to put it again:
Formula for ProductionMonth:
=FormatDate([Production Date];"yyyy-MM")
InvoiceMonth is directly coming from datawarehouse in the format yyyy-MM.
Now I created a variable with the following code:
=If([InvoiceMonth] >= [ProductionMonth] And IsNull([ChargeAmount]);"Missing";[ChargeAmount])
Now this formula is giving write output. But it is giving “Missing” everywhere which is wrong. I need to show “Missing” word only if InvoiceMonth is greater or equal to ProductionMonth.
Example -
Account----2009-06-----2009-07-----2009-08----2009-09-----ProductionMonth
123---------- Null---------45.32--------Null----------Null-----------2009-03
456-----------Null---------35.02--------Null----------38.56---------2009-07
12345--------85.00-------45.10--------745---------21.01----------2009-03
Now here for Account = 456, 2009-06 is null and it should be null, as this account was created in 2009-07. So if after 2009-07 the charge is null, I need to show “Missing”, before that if there is null, I don’t want to touch that value.
Mak this is what I am using till now. But I am getting “Missing” word everywhere. Even If the Invoice Month is less than Production Month , I am getting Missing word where I should not get.
And I guess this is happening because InvoiceMonth is not comparing with ProductionDate.
Here in report , InvoiceMonth = 2009-08(for an example) is coming directly from the datawarehouse.
Wherein ProductionMonth = FormatDate([Production Date];“yyyy-MM”) is a report level variable.
Please let me know if you have understood the concern.
InvoiceMonth is of characted data type. And for ProductionMonth i am using FormatDate(). So I guess it is also in Character(I am not sure FormatDate() output is in character or date format ).
=If(([NumberInvoice] < [NumberProduction]) And IsNull([ChargeAmount]);[ChargeAmount];If(([NumberInvoice] >= [NumberProduction]) And IsNull([ChargeAmount]);"Missing";[ChargeAmount]))
But still whereever ChargeAmount is null, it is displaying “Missing” which is the major problem.
Please help.
Even I tried below formula, but no success:
=If(([NumberInvoice] >= [NumberProduction]) And IsNull([ChargeAmount]);"Missing";[ChargeAmount])
I’m pretty sure you should compare numeric, so the way so far has been good.
One other thing to check, I see invoice month is a dimension or detail object (as it is in the across, but how about production-month?
For this to work both should be dimension (or detail).
Just a guess since if would have built the production month in the universe, I would have found the min() and then the object defaults to measure.
If its measure, but coming straight from the query, no problem, you can declare a dimension variable using the measure object.
You do see ‘missing’ everywhere right, so the combination of dimension objects are not really ‘not there’, only the measure is NULL.
Then you can also try the alerter approach, and create an alerter to replace the real value with the text ‘missing’…
Also you can try a two text if formula in the crosstab, just to see that it actually went trough the if and evaluated it to this side of the if.
Another step is put the actual test objects in the same crosstab,
so you can visually see what the test is comparing.
Just some debugging thoughts,
Hope you get it to work now,
Marianne
Mak1 I appreciate your responses and your help. And I do understand you can’t replicate this problem at your end. Once again I appreciate your thoughts on this issue.
I tried your code, but it is displaying nothing , means the values and nulls are displayed as it is:
(BOB member since 2007-10-05)
(BOB member since 2005-01-06)
(BOB member since 2002-08-20)