March 3, 2017 at 12:15 pm
In Microsoft SQL Server Report Builder 3.0, I have one report that, in part, supplies the total to this:
=Count(IIF(Fields!Type.Value = "1", 1, Nothing))
and one report that supplies the total to this:
=Count(IIF(Fields!Actions.Value = "Certificate of Occupancy/Compliance", 1, Nothing))
How do I combine the two codes below to get the same results in just one combined report?
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)
SELECT
tblDataActions.Actions
,tblDataActions.CompletionDate
FROM
tblDataActions
WHERE
tblDataActions.CompletionDate >= @CompletionDate
AND tblDataActions.CompletionDate <= @CompletionDate2
AND tblDataActions.Actions = (@Actions)
March 3, 2017 at 1:37 pm
Based on what you have posted, I don't see how. The queries are not even related to each other. They are from different tables, with different parameters, and do not return results that even resemble each other.
March 3, 2017 at 3:02 pm
If I am understanding the issue correctly, you have 2 reports that you want to merge into 1?
Why not just use multiple data sources in a single report? I created an SSRS report not that long ago that had 4 data sources. It was messy and clunky and felt very hacky, but it did do what I needed it to.
There is no way that I can see to merge those 2 queries into 1 without more information on the tables. If you have some way to join the tables you can likely get all the information you require in 1 data source, but otherwise I agree with Lynn, you cannot put these into 1 data source.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply