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!!

matrix

hey there

Can anyone direct me to explicit examples of a matrix.

eg I would like to see the layout view // then preview

I am using Visual Studio.net 2003 and Reporting Services

thanks

jewel

You can install the AdventureWorks sample reports during the setup installation of Reporting Services. The "Company Sales" sample report provides a matrix layout.

You can also take a look at this article: http://www.gotreportviewer.com/matrices/index.html
While that how-to article is targeted at the ReportViewer controls shipped in VS 2005, most of it still applies for Reporting Services 2000 / Report designer in VS 2003.

Also RS Books Online contains information about matrix reports, e.g.: http://msdn2.microsoft.com/en-us/library/ms157334.aspx

-- Robert

Materializing the cubes, absence of CREATE CUBE

Hi all,

(1) Does making a cube through Cube Wizard in Visual Studio imply materialiing the view
in a database?

I created a cube in AdventureWorksDW sample database. There is no error after I
deployed the project. Still the size of data files and transaction-log files of database
remain the same.

(2) Is the cube materialized? Can we materialize cubes?

(3) Shall Microsoft introduce operation CREATE CUBE in the MDX? I saw only
ALTER CUBE, CREATE SUBCUBE, and DROP SUBCUBE in online book.

Thank you,

Bernaridho

1) No

2) Yes, you can take the cubes offline

3) http://msdn2.microsoft.com/en-us/library/ms145581.aspx

Matching records using Business Intelligence Studio

Hello,

I have a database with thousands of records that contain personal details of customers. Some of these records pertain to the same customer - however, they have been submitted by different people, so they differ slightly in detail.

I've been looking to see if any of the data mining tools provided by Business Intelligence Studio in SQL Server 2005 will enable me to achieve a high degree of accuracy in matching records that pertain to the same customer. From what I can see, these tools seem more suited to making general predictions based on large groupings rather than the kind of precise prediction I am looking for.

So I'd appreciate it if anyone could tell me if there is any way I could use Business Intelligence Studio to match these 'duplicate' records together, or whether I will have to create a more SQL-based solution which attempts to match the customer records using SELECT statements and making assumptions about the data.

TIA,

Kweri

One solution is to start by creating an Integration Services project.

In the project, define a Data Flow task and add the following transforms:

- a Data Source transform, which reads from your database

- a Fuzzy Matching transform

- a Data Destination transform

The Fuzzy Matching Integration Services transform is intended to resolve the kind of problem you describe (matching records based on similarity)