Unable to create list of fields for query in Report Builder 3.0

1

I'm hoping someone can help! I'm trying to combine the total count of repairs with the total count of communications into one dataset. I tried doing this originally with a SUM of UNION ALL but I think because my values are already "Count(Distinct)..." of the values themselves, this isn't possible.
I've tried the code below instead, creating temporary tables and doing UNION ALL on those, and it runs just fine within the query designer window, but when I try to add the fields to my report I get the following error:

"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct"

I presume there is a problem with the way my columns are named but I can't figure out what it is. Any help would be much appreciated! Thanks

SELECT

[EM-ORIGINATOR].REFERENCE AS tenancyref ,COUNT(DISTINCT [EM-COMMUNICATION].[COMM-REFERENCE]) AS countcomms

INTO #CommCount

FROM
[EM-ORIGINATOR]
INNER JOIN [EM-COMMUNICATION]
ON [EM-ORIGINATOR].[ORG-CODE] = [EM-COMMUNICATION].[ORG-CODE] AND [EM-ORIGINATOR].[COMM-REFERENCE] = [EM-COMMUNICATION].[COMM-REFERENCE]
WHERE
[EM-ORIGINATOR].[Comm-Sequence] = 1
AND [EM-COMMUNICATION].[COMM-TYPE] NOT IN (N'OUTB', N'ASB', N'ASBH', N'ASBN', N'CONC', N'EM', N'ESTI', N'HVIS', N'ILLO', N'INSU', N'PROI', N'TENY', N'TNCE', N'TNCY', N'TSUP')
AND [EM-COMMUNICATION].[CRT-DATE] = N'03/31/2016 00:00:00'

GROUP BY
[EM-ORIGINATOR].REFERENCE

SELECT
[RE-TENANCY].[TENANCY-REF] AS tenancyref ,COUNT(DISTINCT [RM-JOB].[JOB-NO]) AS countcomms

INTO #JobCount

FROM
[RM-JOB]
INNER JOIN [CO-USER-DEFAULTS]
ON [RM-JOB].[ORG-CODE] = [CO-USER-DEFAULTS].[ORG-CODE] AND [RM-JOB].[RAISED-BY] = [CO-USER-DEFAULTS].[USER-NAME]
INNER JOIN IH_OFFICER
ON [CO-USER-DEFAULTS].[ORG-CODE] = IH_OFFICER.[ORG-CODE] AND [CO-USER-DEFAULTS].[OFFICER-CODE] = IH_OFFICER.[OFFICER-CODE]
INNER JOIN [RE-TNCY-PLACE]
ON [RM-JOB].[ORG-CODE] = [RE-TNCY-PLACE].[ORG-CODE] AND [RM-JOB].[PLACE-REF] = [RE-TNCY-PLACE].[PLACE-REF]
INNER JOIN [RE-TENANCY]
ON [RE-TNCY-PLACE].[ORG-CODE] = [RE-TENANCY].[ORG-CODE] AND [RE-TNCY-PLACE].[TNCY-SYS-REF] = [RE-TENANCY].[TNCY-SYS-REF]
INNER JOIN [IH-LOCATION]
ON [RE-TNCY-PLACE].[ORG-CODE] = [IH-LOCATION].[ORG-CODE] AND [RE-TNCY-PLACE].[PLACE-REF] = [IH-LOCATION].[PLACE-REF]
WHERE
[RM-JOB].[DATE-REPORTED] = N'03/31/2016 00:00:00'
AND [RM-JOB].[CL-ANALYSIS-CODE-ELEMENT] IN (N'35;10000;17000;;', N'36;10000;30006;;')
AND [RM-JOB].[CURRENT-STAGE-MAP] != N'CANC'
AND [RM-JOB].[SHORT-DESCRIPTION] NOT LIKE N'FOW%'
AND IH_OFFICER.DEPARTMENT = N'Southway Connect'
AND [RE-TENANCY].[TNCY-STATUS] != N'FOR'
AND [RE-TENANCY].[TNCY-TYPE] NOT IN (N'GAR', N'LEAS')
AND [IH-LOCATION].[BUILDING-TYPE] != N'COMREP'
AND [IH-LOCATION].[ACCESS-GROUP] IN (N'HSG', N'PRS')
GROUP BY
[RE-TENANCY].[TENANCY-REF]

SELECT tenancyref, SUM(countcomms) AS Total_Comms

FROM

(SELECT tenancyref, countcomms
FROM #CommCount

UNION ALL

SELECT tenancyref, countcomms
FROM #JobCount) joined

GROUP BY

tenancyref

3

Are you creating a stored procedure with the code you posted and invoking that stored proc from SSRS, or are you using the code you posted as ad-hoc code in the SSRS data set? I would recommend creating a stored proc and using that.

That may not be your issue though.

In your DataSet properties, Query tab, if you click Refresh fields, does that run correctly? If it does not, check your connection string. Also, you can run SQL profiler (which you can invoke from the Tools menu of SSMS) and click refresh to see the query coming through to the database server.

Your query returns two columns, tenancyref and Total_Comms. Are those the two columns that your tablix refers to?

1 Like
4

Hi James

Thanks for your reply.

I'm using Report Builder 3.0. I'm quite new to SQL so I'm not sure if this is different from SSRS?
I'm using the code as ad-hoc code in the data set.
I actually managed to solve this problem with fields (I just needed to make sure they were linked in the dataset properties) and had it working last week but now the code doesn't run at all - or just runs forever! It has changed slightly but not in the UNION ALL part, however this seems to be the problem now as I can run the two separate parts OK but can't run it all together...
Any suggestions?

Thanks

SELECT

[RE-TENANCY].[TENANCY-REF] AS tenancyref ,COUNT(DISTINCT [EM-COMMUNICATION].[COMM-REFERENCE]) AS countcomms

INTO #CommCount

FROM
[RE-TENANCY]
INNER JOIN [EM-ORIGINATOR]
ON [RE-TENANCY].[ORG-CODE] = [EM-ORIGINATOR].[ORG-CODE] AND [RE-TENANCY].[TENANCY-REF] = [EM-ORIGINATOR].[REFERENCE]
INNER JOIN [EM-COMMUNICATION]
ON [EM-ORIGINATOR].[ORG-CODE] = [EM-COMMUNICATION].[ORG-CODE] AND [EM-ORIGINATOR].[COMM-REFERENCE] = [EM-COMMUNICATION].[COMM-REFERENCE]
WHERE
[EM-ORIGINATOR].[Comm-Sequence] = 1
AND [RE-TENANCY].[TNCY-START] <= [EM-COMMUNICATION].[CRT-DATE]
AND ([RE-TENANCY].[TNCY-END] >= [EM-COMMUNICATION].[CRT-DATE] OR [RE-TENANCY].[TNCY-END] IS NULL)
AND [EM-COMMUNICATION].[COMM-TYPE] NOT IN (N'OUTB', N'ASB', N'ASBH', N'ASBN', N'CONC', N'EM', N'ESTI', N'HVIS', N'ILLO', N'INSU', N'PROI', N'TENY', N'TNCE', N'TNCY', N'TSUP')
AND [EM-COMMUNICATION].[CRT-DATE] >= @CRTDATE
AND [EM-COMMUNICATION].[CRT-DATE] <= @CRTDATE2

GROUP BY
[RE-TENANCY].[TENANCY-REF]

SELECT
[RE-TENANCY].[TENANCY-REF] AS tenancyref ,COUNT(DISTINCT [RM-JOB].[JOB-NO]) AS countcomms

INTO #JobCount

FROM

[RE-TENANCY]
INNER JOIN [RE-TNCY-PLACE]
ON [RE-TENANCY].[ORG-CODE] = [RE-TNCY-PLACE].[ORG-CODE] AND [RE-TENANCY].[TNCY-SYS-REF] = [RE-TNCY-PLACE].[TNCY-SYS-REF]
INNER JOIN [RM-JOB]
ON [RE-TNCY-PLACE].[ORG-CODE] = [RM-JOB].[ORG-CODE] AND [RE-TNCY-PLACE].[PLACE-REF] = [RM-JOB].[PLACE-REF]
INNER JOIN [CO-USER-DEFAULTS]
ON [RM-JOB].[ORG-CODE] = [CO-USER-DEFAULTS].[ORG-CODE] AND [RM-JOB].[RAISED-BY] = [CO-USER-DEFAULTS].[USER-NAME]
INNER JOIN IH_OFFICER
ON [CO-USER-DEFAULTS].[ORG-CODE] = IH_OFFICER.[ORG-CODE] AND [CO-USER-DEFAULTS].[OFFICER-CODE] = IH_OFFICER.[OFFICER-CODE]
INNER JOIN [IH-LOCATION]
ON [RE-TNCY-PLACE].[ORG-CODE] = [IH-LOCATION].[ORG-CODE] AND [RE-TNCY-PLACE].[PLACE-REF] = [IH-LOCATION].[PLACE-REF]

WHERE

[RE-TENANCY].[TNCY-START] <= [RM-JOB].[DATE-REPORTED]
AND ([RE-TENANCY].[TNCY-END] >= [RM-JOB].[DATE-REPORTED] OR [RE-TENANCY].[TNCY-END] IS NULL)
AND [RE-TENANCY].[TNCY-TYPE] != N'GAR'
AND [RM-JOB].[DATE-REPORTED] >= @CRTDATE
AND [RM-JOB].[DATE-REPORTED] <= @CRTDATE2
AND [RM-JOB].[CL-ANALYSIS-CODE-ELEMENT] IN (N'35;10000;17000;;', N'36;10000;30006;;')
AND [RM-JOB].[CURRENT-STAGE-MAP] != N'CANC'
AND [RM-JOB].[SHORT-DESCRIPTION] NOT LIKE N'FOW%'
AND IH_OFFICER.DEPARTMENT = N'Southway Connect'
AND [IH-LOCATION].[BUILDING-TYPE] != N'COMREP'
AND [IH-LOCATION].[ACCESS-GROUP] IN (N'HSG', N'PRS')
GROUP BY
[RE-TENANCY].[TENANCY-REF]

SELECT tenancyref, SUM(countcomms) AS Total_Comms

FROM

(SELECT tenancyref, countcomms
FROM #CommCount

UNION ALL

SELECT tenancyref, countcomms
FROM #JobCount) joined

GROUP BY

tenancyref

5

I would try to debug this in stages.

First, connect to the database server using SSMS and run the query from there. Look at each part of the query and see how many rows they return, and whether the union all part works as expected.

Once you get that working well, create a stored procedure to run the query. Creating a stored proc is as simple as wrapping your code as shown below:

USE YourDatabaseNameHere
GO

CREATE PROCEDURE dbo.GetTenantCommissions
	@CRTDATE DATE
AS
	SET NOCOUNT,XACT_ABORT ON;
	--- your code here
GO

Create that code in an SSMS window and click the execute button. That will create the stored procedure.

Then in SSRS, instead of choosing query text, select stored proc and provide the name of the stored proc.

I am not necessarily saying that not using a stored procedure is the problem; I am suggesting that it would be easier to debug and maintain the code if it is a stored proc, not to mention possible better execution efficiency.

1 Like
6

Thank you so much for the tip, we seem to be having problems with our server so I can't test this out but I certainly will when able to!