Matrix and Columns nightmare.

Question

Post reply

Matrix and Columns nightmare.

Kerrie Sorrell

SSC Eights!

Points: 921
More actions
September 26, 2005 at 7:16 am

#187824

Hi all,
I hope I explain this right...
I have a report, designed with a matrix. I want it to have two columns. Open cases and closed cases, the field i have to work off of is CaseStatus(opened, closed, suspend, resolved, unassign, assigned, waitcust, and waitother). I want is set up so the columns will have this data in it,
OPENED CASES
=
opened
resolved
assign
unassigned
suspend
waitcust
waitother
CLOSED CASES
=
closed
Is there an IIF statments or do I set it up in the datasets? I have tried different ways and coming up with garabage.
I keep getting 8 columns when i do an IIF all saying OPENED CASES. And when I set up the datasets I get no data at all.
Someone please help. I hope I explained this well enough, if not let me mknow and i will try to do it better.
Thanks in advanced.
Kerrie

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select

CASE WHEN CaseStatus = 'closed' THEN 'Closed' ELSE NULL END AS Closed,

CASE WHEN CaseStatus 'closed' THEN CaseStatus ELSE NULL END AS Opened

from ...

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 2

Thank you, that is the closes that I have gotten as of yet.

Is there a way to make the OPENED column include the resolved, opened, waitcust, etc? The result that I get is....

CLOSED CASES (column)

closed data

OPENED CASES (column)

opened data

RESOLVED CASES (column)

resolved data

WAITCUST CASES (column)

waitcust data

And so on.

Do I set up the 'case when' differently?

I am still new at this. I know this is probably a simple problem but I just don't see it.

Thanks again!!!!!!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Post the table definition, some sample data and the required results... I'm sure we'll figure it out.

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 4

The report is of our product cases. (I work for a software company)

Here is my select statement

SELECT ProductName, ProductVersion, CaseSeverity, ServiceCenter, CaseStatus, CaseEnteredDate,

CASE WHEN CaseStatus = 'closed' THEN 'Closed' ELSE NULL END AS Closed, CASE WHEN CaseStatus 'closed' THEN CaseStatus ELSE NULL

END AS Opened

FROM dbo.CS_REPORTING_CASE_VW

WHERE (ServiceCenter = @SC_Prompt)

The results are

...................CLOSED..OPENED..RESOLVED.WAITCUST.etc

Product...Version...1-2-3-4.1-2-3-4.1-2-3-4..1-2-3-4..(Severity)

CONTROL..1501.....2 5 6 8.1 5 6 7.1 5 5 5..1 5 8 7

So for the control 1501 product there are closed case- 2 cases with 1 severity, 5 cases with 2 severity and so on

Opened cases - 1 case with 1 severity, 5 cases with 2 severity and so on

What management wants is just two columns; opened and closed.

I hope I explained it well enough.

Thank you!!!

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Assuming you're using dynamic columns in the matrix, modify RGR'us's () code slightly to produce only single column of output for the status e.g.

SELECT ProductName, ProductVersion, CaseSeverity, ServiceCenter, CaseStatus, CaseEnteredDate,

CASE CaseStatus WHEN 'closed' THEN 'Closed' ELSE 'Open' END AS BasicCaseStatus

FROM dbo.CS_REPORTING_CASE_VW

WHERE (ServiceCenter = @SC_Prompt)

and then just drop the new field/column "BasicCaseStatus" into the Column section of the matrix object in your report. If you now add a field for the rows and another for the 'Data' section, you should have two colmns, dynamically generated and based on the BasicCaseStatus field values. note named the column 'BasicCaseStatus' rather than CaseStatus to avoid duplication of col names.

Steve.

Kerrie Sorrell SSC Eights! Points: 921 More actions · Answer 6

HAPPY DAYS ARE HERE AGAIN!!!!!

YOU GUYS ARE THE GREATEST!!!!!!!!!

Thank you both so much!!!!!!!

It worked!!!!