Referencing another table throws off totals

Question

Referencing another table throws off totals

bzoom100

Ten Centuries

Points: 1092
More actions
March 3, 2017 at 8:03 am

#321052

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

bzoom100 Ten Centuries Points: 1092 More actions · Answer 2

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))

Phil Parkin SSC Guru Points: 246988 More actions · Answer 3

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 More actions · Answer 4

bzoom100 - Friday, March 3, 2017 8:39 AM
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))

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

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 5

bzoom100 - Friday, March 3, 2017 8:39 AM
Ah yes, you have steered me in the right direction. The Actions value needs this filter: WHERE tblDataActions.Actions IN (@Actions)

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)