A lil bit unorthodox solution!!
I faced this error (Report
Server Model generation error: Table has a non-primary data source) yesterday
while working on SSRS. I was trying to create a Report Model (SMDL) on SQL
Server Buisness Development Studio and was getting this error repeatedly in
final step of “Report Model Wizard”.
Query which I was using in Data Source View was somewhat like below:
SELECT Table1.Col1, Table1.Col2, Table2.Col2,
Table2.Col3
FROM Table1
JOIN Table2
ON Table1.Col1 = Table2.col1
I googled this error a bit but found no solution and hence I
adopted this lil bit unorthodox option.
My solution was to add an artificial column to my resultset
and then using it as logical primary key. So I changed my query as below:
SELECT Table1.Col1, Table1.Col2, Table2.Col2,
Table2.Col3 ', NEWID()AS Id
FROM Table1
JOIN Table2
ON Table1.Col1 = Table2.col1
And then I used this new column as logical primary key, as
shown in below image.
This is working like charm for me but I am yet to test this fully so use this solution for you after through testing. Hope this
helps.
