SOLVED: Cube Drill Through Report - How to Handle the Year '99999999'

[andy_mason_84]

Hi,

We have a Cube with Invoice Data and from the cube we have a Report Studio Report that you can drill through to (via a Drill Through Definition) from the cube that shows a breakdown of the figures.  The Report is pulling data from a Datamart.

The Date that is used is can be null. 

In Transformer we get the years that look like this

2010 | 2011 | 2012 | 99999999

'99999999' is what Transformer assigned the null date records too.

So in our Drill through report we have a filter which is :

cast([Invoice Date],date) in_range ?All Invoice Dates?

This works for everything except when you try and dill on something using the 99999999 year as this is not a range and Transformer passes the range through the drill through definition.

So what we need to do is change the filter to say is if the parameter value that comes through is 99999999 then show all invoices where the Invoice data is null else do what you were doing before i.e. cast([Invoice Date],date) in_range ?All Invoice Dates?

So I have tried this but it seems to get conflicted still ...

CASE WHEN (?All Invoice Dates?='99999999')
THEN
([Invoice Date] is null)
ELSE
(
cast([Invoice Date],date) in_range ?All Invoice Dates?
)
END


Any ideas on how to get around this?

Thanks.

AM

[Ammus1234]

What about this filter

(?All Invoice Dates? <>'99999999' and cast([Invoice Date],date) in_range ?All Invoice Dates?)
or
(?All Invoice Dates? ='99999999' and  [Invoice Date] is null)

[andy_mason_84]

It works for the first prompt when we drill on an actual year but downs work on the second (?All Invoice Dates? ='99999999' and  [Invoice Date] is null) when No Date is drilled on.

Error : RQP-DEF-0354 The query contains one or more unresolved prompts.

[aerick911]

Can you drag that date into your query?  [99999999] then do

(?All Invoice Dates? <>[99999999] and cast([Invoice Date],date) in_range ?All Invoice Dates?)
or
(?All Invoice Dates? =[99999999] and  [Invoice Date] is null)

[andy_mason_84]

Hi Guys,

I managed to crack it.

((?All Invoice Dates?='99999999' ) and
(cast([Invoice Date],varchar(10)) is null))

or (
(?All Invoice Dates?<>'99999999' ) and
cast([Invoice Date],varchar(10))

in_range?All Invoice Dates?)

This issue was the Drill through definition saw it as a date and then we were trying to pass a string.  By making it all strings then the drill through definition saw it as text and it works.

Thanks for your help.