Share
Report
28Dear Community,
I developed this data Entity to use with Power Bi, but when I selected the data Entity for ETL it gave me a DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request, though I checked it in SQL Server before loading it to Power Bi and the Results were as expected. Upon executing the URL provided by Power Bi in the Browser I saw this error
<ExceptionMessage>Cannot select a record in Sub Query (Subquery). The SQL database has issued an error.</ExceptionMessage>
I referred to the community forum and did all the suggestions, Rebuilt the Model, Synchronized the Database, Dropped and Regenerated the Staging Table, Dropped the View Generated and the Staging Table Generated in SQL Server and Recompiled and did all the mentioned steps but still the same result. Can Any One Help me where I went Wrong.
This is the View Created after compiling
SELECT T1.ITEMID AS ITEMID, T1.MODIFIEDDATETIME AS MODIFIEDDATETIME, T1.RECVERSION AS RECVERSION, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION,
1010 AS RECID,
T2.RECVERSION AS RECVERSION#2,T2.DATAAREAID AS DATAAREAID#2, T2.PARTITION AS PARTITION#2,
T3.RECVERSION AS RECVERSION#3, T3.DATAAREAID AS DATAAREAID#3, T3.PARTITION AS PARTITION#3,
T4.DATAAREAID AS DATAAREAID#4,T4.PARTITION AS PARTITION#4,
T5.RECVERSION AS RECVERSION#5, T5.DATAAREAID AS DATAAREAID#5, T5.PARTITION AS PARTITION#5,
(CAST((IIF(DATEDIFF(dd, T3.proddate, GETDATE()) < 0, 0, DATEDIFF(dd, T3.proddate,GETDATE())))
AS NVARCHAR(20))) AS AGESTR,
(CAST((sum(AVAILPHYSICAL)) AS INT)) AS QTY,
(CAST((sum(T1.AvailPhysical) * T5.AverageCost) AS INT)) AS AMOUNT
FROM INVENTSUM T1
CROSS JOIN
INVENTDIM T2
CROSS JOIN
INVENTBATCH T3
LEFT OUTER JOIN
INVENTITEMPURCHSETUPSTOPPED T4 ON ((((T1.ITEMID = T4.ITEMID) AND (T1.DATAAREAID = T4.DATAAREAID)) AND (T1.PARTITION = T4.PARTITION)) AND (T4.DATAAREAID = N'CBL')) LEFT OUTER JOIN
TXLITEMMOVINGAVERAGE T5 ON ((((T1.ITEMID = T5.ITEMID) AND (T1.DATAAREAID = T5.DATAAREAID)) AND (T1.PARTITION = T5.PARTITION)) AND (T5.DATAAREAID = N'CBL'))
WHERE ((((T1.AVAILPHYSICAL > 0) AND (T1.DATAAREAID = N'CBL'))
AND ((((((T1.INVENTDIMID = T2.INVENTDIMID) AND (T1.DATAAREAID = T2.DATAAREAID))
AND (T1.PARTITION = T2.PARTITION))
AND (((T1.DATAAREAID = T2.DATAAREAID)
AND (T1.DATAAREAID = T2.DATAAREAID))
AND (T1.PARTITION = T2.PARTITION)))
AND (((T1.PARTITION = T2.PARTITION)
AND (T1.DATAAREAID = T2.DATAAREAID))
AND (T1.PARTITION = T2.PARTITION)))
AND (((T3.PARTITION = T2.PARTITION)
AND (T3.DATAAREAID = T2.DATAAREAID))
AND (T3.PARTITION = T2.PARTITION))))
AND ((T3.DATAAREAID = N'CBL')
AND ((((T1.ITEMID = T3.ITEMID)
AND (T1.DATAAREAID = T3.DATAAREAID))
AND (T1.PARTITION = T3.PARTITION))
AND (((T1.INVENTBATCHID = T3.INVENTBATCHID)
AND (T1.DATAAREAID = T3.DATAAREAID))
AND (T1.PARTITION = T3.PARTITION)))))
GROUP BY
T1.ITEMID,
T1.MODIFIEDDATETIME,
T1.RECVERSION,
T1.DATAAREAID,
T1.PARTITION,
T2.RECVERSION,
T2.DATAAREAID,
T2.PARTITION,
T3.PRODDATE,
T3.RECVERSION,
T3.DATAAREAID,
T3.PARTITION,
T4.ITEMID,
T4.DATAAREAID,
T4.PARTITION,
T5.AVERAGECOST,
T5.RECVERSION,
T5.DATAAREAID,
T5.PARTITION
All responses (
Answers (
