December 19, 2014 at 8:19 am
Hi,
I have the following dataset in a report I'm writing. It is built in the "Edit in Text" query builder.
SELECT DISTINCT trans.tncy_sys_ref AS 'TncySysRef'
,ten.tenancy_ref AS 'TenancyRef'
,trans.trans_date AS 'TransactionDate'
,dat.Month AS 'StartMonth'
,dat.MonthEnd AS 'EndMonth'
,trans.created_date AS 'CreatedDate'
,trans.comment_ AS 'TransactionComment'
,trans.posting_week AS 'PostingWeek'
,trans.posting_year AS 'PostingYear'
,trans.trans_week AS 'TransactionWeek'
,trans.trans_year AS 'TransactionYear'
,trans.trans_amt AS 'TransactionAmount'
,trans.account_type AS 'AccountType'
,trans.account_code AS 'AccountCode'
,loc.scheme AS 'Scheme'
,loc.mgt_area AS 'Management Area'
,loc.location_type AS 'Location Type'
FROM[dbo].[re_tncy_trans] trans
INNER JOIN
re_tenancy ten
ON trans.tncy_sys_ref = ten.tncy_sys_ref
INNER JOIN
re_tncy_place tenpl
ONTEN.tncy_sys_ref = tenpl.tncy_sys_ref
INNER JOIN
ih_location loc
ONtenpl.place_ref = loc.place_ref
INNER JOIN
[InSightOpenHousingDataWarehouse].[dbo].[DimWhen] dat
ON trans.trans_date = dat.Date
WHERE trans.account_type IN ('IN','HB')
AND trans.trans_date >= CONVERT(DATETIME, '2014-04-01 00:00:00', 102)
--and tenancy_ref = '211506045028'
When I paste that in the query editor and execute it - the query runs successfully. When I run the report with this dataset the report errors with a generic error -
An error has occured during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1' (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local machine, or enable remote errors.
I can trace the issue to the join to another database -
INNER JOIN
[InSightOpenHousingDataWarehouse].[dbo].[DimWhen] dat
ON trans.trans_date = dat.Date
When I remove this part and the bit in the SELECT statement that looks at this table the report runs.
Does this mean that in report builder I can't have a query across databases?
I've tried adding a datasource to that database to the report and it still doesn't work.
Please advise as I have key dates in this database that I need to look at.
Thanks
December 19, 2014 at 8:23 am
maybe try creating a view in the local database that gets the data, then selecting from that in the report
December 19, 2014 at 8:34 am
Worked it out again guys - not long after posting on here.
I enabled remote errors. - see here on how to do this -
https://www.youtube.com/watch?v=PLIDL0FLkqI
Then this changed the error to basically say that the service id that connects to the datasources didn't have access to the database that I was trying to connect to. Doh!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply