on 08-01-2018 6:10 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.