September 9, 2008 at 3:04 pm
Hey guys, I'm back with another issue. No SProcs this time though!
Alright, I've got a project that I'm working on that uses 6 different Matrix controls, all of them very simple in what they do and so forth. Five of those six work just fine, but one of them groups things together that it shouldn't.
The project pulls sales information from our different facilities (rows), and then breaks them down by product code (columns), and shows the Sum(Values) of the total sales in the grid.
There are only 5 Facilities, and 4 Product codes, so the matrix isn't very large at all. I'll throw some false numbers at you here to give you an idea of what the problem is.
---Facility A: "Fresno" ---
Prod Code 1: $20,000
Prod Code 2: $0
Prod Code 3: $0
Prod Code 4: $0
--Facility B: "Karson" ---
Prod Code 1: $0
Prod Code 2: $0
Prod Code 3: $0
Prod Code 4: $0
--Facility C: "Pampa Casper"---
Prod Code 1: $0
Prod Code 2: $0
Prod Code 3: $0
Prod Code 4: $20,000
--Facility D: "Pampa Old"---
Prod Code 1: $100,000
Prod Code 2: $11,000
Prod Code 3: $0
Prod Code 4: $0
--Facility E: "Edmond"---
Prod Code 1: $0
Prod Code 2: $0
Prod Code 3: $0
Prod Code 4: $0
So we've got 2 facilities that don't even have any sales (these are actually warehouses, and don't make direct sales more than maybe twice a year). The other three range in value from $20,000 to $120,000 total.
Facilities B & E both of no values to show, so they don't pop up in the Matrix. This isn't a big deal, though it would be cool if anybody could tell me how to make them pop up anyway.
The problem comes from two of the facilities being combined into one. Now, if I were reading my post here, I would assume that this guy was about to tell me that the two named "Pampa (whatever)" were the ones being combined. But, that's not the case. In this case, "Fresno" and "Pampa Old" are being combined and shown in a single row in the matrix.
I have no idea why they get combined in this Matrix. This one in particular shows the values for a single day. I have another Matrix that's exactly the same in its set-up, but it looks at values for the whole month. But, the monthly version of the matrix works without any problems at all.
I've tried everything I know how to do for the last week and ended up with nothing at all. Any help you can give me is appreciated.
Thank you,
Jason
September 15, 2008 at 12:34 pm
Jason, one way I've gotten around the need to have all of my offices show up in matrix reports is to hardcode a union that will display them all...
For example if I have 4 offices and I'm doing sales figures I'd do...
--Select for "real" Data"
Select sales, office
FROM salesData
WHERE invoice date between date1 and date2
UNION
SELECT 0, 'Office1'
UNION
SELECT 0, 'Office2'
UNION
SELECT 0, 'Office3'
UNION
SELECT 0, 'Office4'
That way you know you will always have your 4 offices show up in your matrixes...
As for combining them why couldn't you just use a case statement and run with it, or am I mis reading and you're trying to un-group 2 offices?
-Luke.
September 15, 2008 at 1:06 pm
Luke,
Thanks for the response. I've never used UNION before, so I'll have to do some studying up on that and then see what I can do.
As for the facilities getting combined - that was something that SSRS was doing on its own. I didn't want them combined and I could get it to stop combining them. I eventually got tired of not knowing what to do so I deleted the matrix and started over. Apparently I had kicked a setting somewhere, or had a glitch, or something, because the new one worked just fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply