March 6, 2012 at 7:35 pm
So we use SQL to associate databases with our clients facilities information and setting up reports for them to query that data is a pretty important aspect. Our old software was proprietary and did things its own way, but we have recently moved to a new software that uses SQL and SSRS to do this and there has been something of a learning curve in this.
The report that is giving me trouble is a simple facilities information report that is supposed to break down the square footage by building for the client. This is done easily as I just do a report that provides the building code in the first column and the square footage measurements (assignable, rentable, usable, etc) in the following. Each of these square footages has its own field in the table I'm querying from. The hard part is that they also want those square footages shown again, but this time accounting only for spaces that don't have an assigned group (and are therefore vacant), and this is where I'm running in to trouble.
As an example; let's say I have a very small building that has 3 rooms, each is 100 square feet and I want to simply show usable square feet. Obviously column 1 is simply the building code, and column 2 is the total usable square footage for the building, which will be 300 square feet. However, two of those rooms are assigned but one of them is not and so it is vacant. I want column 3 to show total usable square footage again, but only accounting for vacant rooms and therefore its total should only be 100 square feet. Columns 2 and 3 would pull from the same field, but one of them needs to filter the data based on another field while another does not, and I can't seem to get this to work.
I have tried it via the dataset with something like this:
SELECT A.BLDGCODE, B.BLDGDESC, A.USABLE
(SELECT USABLE
FROM A AS A_1
WHERE (GROUP_ = ' ')) AS VU
FROM A AS A INNER JOIN
B ON A.BLDGCODE = B.BLDGCODE
ORDER BY B.SITECODE, A.BLDGCODE
With this there would be a separate query that should pull usable square footage for each space but would only do so for spaces that have no group assigned. Unfortunately this doesn't seem to work and I just get an error that it returns more than 1 value.
I have also tried this via an IIF statement in the relevant textbox:
=IIF(Fields.GROUP_.Value = " ", " ", Fields.USABLE.Value)
I've tried that and a number of variations of it and nothing has worked. It either returns an error or it returns the same unfiltered number that the prior field returns that is simply calling the USABLE space field without filtering it.
Obviously I can't just put a filter on GROUP_ in the dataset because they will effect all data pulled from the tables and some of the columns still need the overall total, so I just need to find a way to filter some columns but not others.
I have a feeling that based on the simplicity of what I'm trying to do here that I'm just stupidly missing something, but I'm still new to this so I guess it is not surprising I've hit a roadblock like this.
March 9, 2012 at 2:45 pm
I am not totally clear on your table or column structure, but here is a stab at it
SELECT A.BLDGCODE, B.BLDGDESC, A.USABLE
,CASE WHEN GROUP_ = ' ' THEN USABLE ELSE 0 END AS VU
FROM A AS A INNER JOIN
B ON A.BLDGCODE = B.BLDGCODE
ORDER BY B.SITECODE, A.BLDGCODE
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply