Measures Not Working in Web Intelligence

[#1]

I have a very simple universe I am having trouble with in Web Intelligence. We are on BOE 4.1 SP06 Patch 4. The database is SQL Server 2014.

My universe has two tables that are not connected. When I create a simple query to count the number of transaction

This is the generated query...

SELECT CONVERT(DATE, Database.dbo.Table1.ReportDate),
COUNT(Database.dbo.Table1.ID)
FROM Database.dbo.Table1
WHERE Database.dbo.Table1.ID = 11781149
GROUP BY CONVERT(DATE, Database.dbo.Table1.ReportDate)

Now when I add the Transaction Amount measure I get an error sometimes...

However, if I refresh the query a few times eventually it will work...

This is the generated query that sometime does not work and sometimes does...

SELECT CONVERT(DATE, Database.dbo.Table1.ReportDate),
COUNT(Database.dbo.Table1.ID),
SUM(Database.dbo.Table1.TranAmt)
FROM Database.dbo.Table1
WHERE Database.dbo.Table1.ID = 11781149
GROUP BY CONVERT(DATE, Database.dbo.Table1.ReportDate)

If I can get the query to run and return to the report designer I do not have the Transaction Amount available to me even though it is showing in the query results...

How is this even possible?

I have one other measure, Settlement Amount, that results in the same behavior when I remove Transaction Amount and replace it with Settlement Amount. However, when add both of them I always get the error shown above. The Projection Function in IDT for both measures is "Sum".

When I specify "Use custom query script" the query consistently runs without error in the query panel. However, when I run the query and return to the report designer I the Transaction Amount is not available to drag into a table despite being shown in the data results.

When I put the generated query with the Transaction Amount measure into a free-hand SQL query it runs, but I get #COMPUTATION for the Report Date column...

The same query works flawlessly in IDT, directly on SQL Server, and even Crystal for Enterprise...

It seems to me that either their is a bug in Web Intelligence or there is a universe setting that WebI needs to be set a certain way. How do I get past this? I know I can return all of the data and do my aggregation within WebI. The issue with that is I am dealing with about 500,000 rows per day and we want to be able to get some monthly totals. This is the exact sort of thing I should be able to do in WebI and it is frustrating to not be able to figure it out.

Thanks for reading this lengthy question.

Noel

[nscheaffer · Accepted solution]

Previously I had both dimensions and measures named Transaction Amount and Settlement Amount in the same folder. IDT allowed it and it even worked in a test query in IDT. A few days ago I thought even though IDT allowed it maybe WebI just didn’t like having a dimensions and measures from the same folder with the same name. So I created a Measures folder and moved my measure there and it worked.

Lesson learned.

Noel

[jyothirmayee_s]

Hi,

I see Transaction Amount from Query 1 as dimension.

How is this object is defined in Universe?.

Thanks,

Jothi

.

[nscheaffer]

I have two objects called Transaction Amount; one is a dimension and the other is a measure. The one that is not showing up on the left is the measure.

Noel

[jyothirmayee_s]

Hi,

yes, it appears as Master data but Is that measure part of Business Layer - Daily Settlement - Test folder?.

drag and drop to the folder if it is not.

Thanks,

Jothi