Matrix report, date: 1900-01-01 wrong!! Suppose to be empty, how?

Hi

I am making a report in Visual Studio. I’m making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields’ aliases at least. So what I did is that in the second table is:

SELECT ‘ ‘ AS ‘Date’

Right?

Now, in the report the first table gives me the dates in a format:

=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesn’t have a date in that field when I run the report it gives me:

1900-01-01

Something I don’t want.

So, how do I make it understand that the second tables date field is suppose to be empty?

Try something like this:

SELECT myDate AS DATE FROM MyTable

UNION ALL

SELECT CASE WHEN myBlankField = ' ' THEN ' ' ELSE ' ' END FROM OtherTable

And choose a field that is NOT a date for myBlankField. This will put blanks in the result set, but you can always just filter those out later.

|||

Thanks for your quick answer, sounds interesting, but I don't know quite where to put your example. Here's the code I have, maybe you know.

SELECT estimatedclosedate AS 'Date', blablabla...

INTO TempTable1

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT ' ' AS 'Date', blablabla... -- This is the date that is troubling me

INTO TempTable2

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT * FROM TempTable1

UNION

SELECT * FROM TempTable2

The TempTable1 gives me Opportunities from a CRM system, the TempTable2 gives me Ongoing Business in the CRM system. Of course, Opportunities have an estimated close date and Ongoing Business has not … so I just want it to show an empty cell in the matrix report.

So, where exactly could I put your suggestion?

|||

try -- select null as 'Date'., xyz........into temptable

Priyank

|||

Worked perfectly!!! Thanks mate!!!|||

can you please mark it as answer...|||

By the way ... do you know anything about my other thread I have here? About putting a tooltip window thing on one of my filters? One of my filters is done that the user can write what ever he wishes to filter on, either exactly or even with a % ... the thing is that I don't want to write on the Prompt: "Tradelane (STO-LAX, or STO%, or %STO) ... it's just to long ... do you happen to know how?

|||Mark it as answer? DONE!!