October 2, 2015 at 5:31 am
Hi all,
J helped me with a report to combine logins and rooms in use.
Problem is the two Select statements within my Query reference two separate Objects on different servers.
In Query Designer, I assign the Logins Dataset and have the code in it, but when it gets to the part where the select statement refers to the Rooms in Use, it fails and says "object not found". This is understandable as the Rooms data is on a different Server and I can't access it because we don't use linked servers.
So i'm stuck, can anyone help?
Here is the full query:
DECLARE @StartDate AS VARCHAR(100), @EndDate AS VARCHAR(100), @Room AS VARCHAR(100)
SET @STARTDate = '24/09/2015'
SET @EndDate = '25/09/2015'
SET @Room = 'CL1.03
'; WITH roomhours(hour_number, hour_description) AS
(SELECT 0, '12 am'
UNION ALL
SELECT 1, '1 am'
UNION ALL
SELECT 2, '2 am'
UNION ALL
SELECT 3, '3 am'
UNION ALL
SELECT 4, '4 am'
UNION ALL
SELECT 5, '5 am'
UNION ALL
SELECT 6, '6 am'
UNION ALL
SELECT 7, '7 am'
UNION ALL
SELECT 8, '8 am'
UNION ALL
SELECT 9, '9 am'
UNION ALL
SELECT 10, '10 am'
UNION ALL
SELECT 11, '11 am'
UNION ALL
SELECT 12, '12 pm'
UNION ALL
SELECT 13, '1 pm'
UNION ALL
SELECT 14, '2 pm'
UNION ALL
SELECT 15, '3 pm'
UNION ALL
SELECT 16, '4 pm'
UNION ALL
SELECT 17, '5 pm'
UNION ALL
SELECT 18, '6 pm'
UNION ALL
SELECT 19, '7 pm'
UNION ALL
SELECT 20, '8 pm'
UNION ALL
SELECT 21, '9 pm'
UNION ALL
SELECT 22, '10 pm'
UNION ALL
SELECT 23, '11 pm'), roombookdata AS
(SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime,
LogonTimes.LogoffTime, LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
WHERE (Room = @Room)
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName), pclogdata AS
(SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT rdowner.V_ACTIVITY.Description, rdowner.V_ACTIVITY_DATETIME.StartDateTime, rdowner.V_ACTIVITY_DATETIME.EndDateTime,
rdowner.V_LOCATION.Name
FROM rdowner.V_ACTIVITY INNER JOIN
rdowner.V_ACTIVITY_DATETIME ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
rdowner.V_ACTIVITY_LOCATION ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
rdowner.V_LOCATION ON rdowner.V_ACTIVITY_LOCATION.LocationId = rdowner.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)), pcsummary AS
(SELECT r.hour_number, r.hour_description, p.machine, p.logontime, p.logofftime
FROM roomhours AS r LEFT OUTER JOIN
pclogdata p ON r.hour_number >= p.logonhour AND r.hour_number < p.logoffhour), pctotal AS
(SELECT hour_description, COUNT(DISTINCT machine) AS pclogin
FROM pcsummary
GROUP BY hour_number, hour_description), roomsummary AS
(SELECT r.hour_number, r.hour_description, i.description, i.StartDateTime, i.EndDateTime, i.Name
FROM roomhours AS r LEFT OUTER JOIN
roombookdata AS i ON r.hour_number >= DATEPART(hh, i.StartDateTime) AND r.hour_number < DATEPART(hh, i.EndDateTime))
SELECT rs.hour_description, rs.description, rs.StartDateTime, rs.EndDateTime, rs.Name, pt.pclogin
FROM roomsummary AS rs INNER JOIN
pctotal AS pt ON rs.hour_description = pt.hour_description
ORDER BY rs.hour_number;
Using reporting services, can anyone help me to somehow run this query without linked servers?
Is there a way this can be broken into 2 queries? One for Logins, one for Rooms and then bring them both together in my Report...
Pulling me hair out, would appreciate any help.
Thanks
October 2, 2015 at 7:48 am
The problem is, you can only reference one dataset in an object.
In other words, you can only link a chart or tablix to one single dataset.
The straightforward solution would be to put the data into one common location.
Maybe use Power Query to combine the data sets and dump the results in an Excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2015 at 9:03 am
Koen Verbeeck (10/2/2015)
The problem is, you can only reference one dataset in an object.In other words, you can only link a chart or tablix to one single dataset.
The straightforward solution would be to put the data into one common location.
Maybe use Power Query to combine the data sets and dump the results in an Excel file?
Hi koen,
The data is live and gets changed throughout the day, hence me wanting to query both server DBs where they are.
I can't do Openrow or opensource queries because I do not have permissions.
I can individually query both Servers so have the credentials, but can't use one query to do this....
Obviously we don't allow linked servers, so how else can I do this? I don't want to use text files etc, but be in tables etc.
Thanks
October 6, 2015 at 12:08 am
This topic goes through most of the options that you have in SSRS. Which aren't that many.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 6, 2015 at 9:07 am
Managed to get the linked server created and it appears as a linked sever in SSMS.
So can anyone help (using my code) show me how to connect to the linked server for my query to run?
October 9, 2015 at 9:53 am
If you create VIEW with the Query you mentioned (That will be your Dataset).
Now you should be able to reference one DATASET from your Report.
October 12, 2015 at 9:34 am
The Query now works in Query Designer, but getting lots of errors in Reporting Services on Design tab when doing report:
Warning1[rsMissingFieldInDataSet] The dataset ‘Logon_Duration_and_Bookings’ contains a definition for the Field ‘Building’. This field is missing from the returned result set from the data source.
00
Warning2[rsErrorReadingDataSetField] The dataset ‘Logon_Duration_and_Bookings’ contains a definition for the Field ‘Building’. The data extension returned an error during reading the field. There is no data for the field at position 7.
Any ideas?
October 12, 2015 at 11:53 am
What if you drop the data set and try again?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 13, 2015 at 4:52 am
I tried creating a New Dataset and pasting the query in, same issue.
Its complaining about those fields...
October 13, 2015 at 5:49 am
I usually use SSMS to create a VIEW where I write my query and make sure there are no empty values. It seems you are reading from live data which theoretically all fields should have a value but in the real world that is not always true. Redo the query by filtering empty or Null values (depends) so that you don't get those errors. Normally, I look at my data in T-Sql so that I know how many records I'm expecting, how many I will remove because of missing data or if possible alter the table and fill the missing data. You are making the assumption that everything is there and it should work. RS is telling you that some fields are missing.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply