Referencing another table throws off totals
-
Using Microsoft SQL Server Report Builder 3.0, This SQL query code:
SELECT
tblDataPermit.Type
,tblDataPermit.Status
,tblDataPermit.ApplicationType
,tblDataPermit.ApplicationDate
,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo1"])[1]', 'nvarchar(max)') AS UniformCode
,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo2"])[1]', 'nvarchar(max)') AS EnergyCode
,tblDataPermit.XmlDataField.value('(//*[local-name()="BuildingType"])[1]', 'nvarchar(max)') AS BuildingType
FROM
tblDataPermit
WHERE
tblDataPermit.ApplicationDate >= @ApplicationDate
AND tblDataPermit.ApplicationDate <= @ApplicationDate2
AND tblDataPermit.Status IN (@AppStatus)
Produces this, which is correct:
In order to fill in the missing value at the bottom I have to pull in tblDataActions.Actions.
This is the modified coding:
SELECT
tblDataPermit.Type
,tblDataPermit.Status
,tblDataPermit.ApplicationType
,tblDataPermit.ApplicationDate
,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo1"])[1]', 'nvarchar(max)') AS UniformCode
,tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo2"])[1]', 'nvarchar(max)') AS EnergyCode
,tblDataPermit.XmlDataField.value('(//*[local-name()="BuildingType"])[1]', 'nvarchar(max)') AS BuildingType
,tblDataActions.Actions
FROM
tblDataPermit
INNER JOIN tblDataActions
ON tblDataPermit.ApplicationNumber = tblDataActions.ApplicationNumber
WHERE
tblDataPermit.ApplicationDate >= @ApplicationDate
AND tblDataPermit.ApplicationDate <= @ApplicationDate2
AND tblDataPermit.Status IN (@AppStatus)
But, it results in this:
Why does the additional coding change the previous correct totals, while at the same time giving me the correct total for the last value at the bottom? Thanks much for any help. -
I would assume that it is because there is more than one match in tblDataActions for each Application number, and that is causing your result set after joining to contain more rows than it did before.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ah yes, you have steered me in the right direction. The Actions value needs this filter: WHERE tblDataActions.Actions IN (@Actions)
Where and how would I place this filter so that it only effects the last result in the column, the expression for which is: =Count(Microsoft.VisualBasic.Interaction.IIF(Fields!Actions.Value = "Certificate of Occupancy/Compliance", 1, Nothing))
-
Unfortunately, I'm not a user of Report Builder, so I can't help you with that bit.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - bzoom100 - Friday, March 3, 2017 8:39 AM
What do you mean by the filter would only effect the last column? If you filter data, you filter it all, so I'm not exactly sure what you're getting at. Especially as your images of your report don't show any columns, but your expression is for SSRS. Could you elaborate please?
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - bzoom100 - Friday, March 3, 2017 8:39 AM
Try it in your SQL Query to limit the number of rows returned from the right table
WHERE tblDataPermit tblDataPermit.ApplicationDateApplicationDate >= @ApplicationDate@ApplicationDate
AND tblDataPermit AND tblDataPermit.ApplicationDateApplicationDate <= @ApplicationDate2@ApplicationDate2
AND tblDataPermit AND tblDataPermit.StatusStatus IN ((@AppStatus@AppStatus))
AND tblDataActions.Actions IN (@Actions)Wes
(A solid design is always preferable to a creative workaround)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply