Report Services Help with a filter

Question

Post reply

Report Services Help with a filter

Paul Morris-1011726

SSCommitted

Points: 1879
More actions
June 1, 2009 at 9:25 am

#138184

Hello All,
I am looking to create a filter to look for a status in a history and if it is there to exclude that row from the report unless it also has another status listed also.....
This is for a case management system that we maintain a status history (i.e. Submitted, Released, Filed, etc...). If a case has been 'Filed' but later is changed to s different status, I need to exclude that case from the report unless the case has a 'Non-Suited' status which means it should be included.
I have a query that displays all the statuses but have no idea how to step through the rows to compare and find the statuses I want to include/exclude. It is a text field with 50 different status names but the names are standard so could search for a specific name....
Any help would be greatly appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

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

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 1

Can you provide table layout to give an idea how it looks like?

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 2

There are 2 tables that are required:

Here is the query to pull the data:

SELECT case_pick.case_sk, case_pick.group_code, group_items.description AS Status

FROM case_pick AS cpick LEFT OUTER JOIN

group_items AS gi ON case_pick.item_sk = group_items.item_sk

WHERE (case_pick.group_code = 'cstatu')

case_pick.case_sk (int, not null) => unique key also used to link to case number

case_pick.group_code (varchar(6), not null) => cstatu has to be specified to get the statuses list

group_items.description (varchar(30), null) => returns status description or null

When the above query runs it returns (varies pending case):

CSTATUSettled Before Suit Filed

CSTATUInvestigation Pending

CSTATUNULL

CSTATUReleased

CSTATUCase Tried

CSTATUArbitration

CSTATUMediation

CSTATUNew Case

CSTATUSettled After Suit Filed

CSTATUSet for Trial

CSTATUService on ALL Defendants

CSTATUSubmitted

CSTATUFiled

Does this provide you with what you need?

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 3

I won't say this explains the entire situation to me, but lot better than the first post. 😉

Ok, here is what I understood, you have a "status code" table and a "case-status" x-mapping table. And you want to fetch only those case ids that do not have a case status description containing "suit filed" {my interpretation for non-suited}. From what I have understood, I would have written the query below:create table #gi (statusCode varchar(6), statusDescription varchar(100), groupCode varchar(6))

create table #case (caseId int, statusCode varchar(6))

insert into #gi

select 'F', 'Filed','CSTATU'

union all

select 'S', 'Submitted','CSTATU'

union all

select 'SB', 'Settled Before Suit Filed','CSTATU'

union all

select 'SA', 'Settled After Suit Filed','CSTATU'

insert into #case

select 1,'SA'

union all

select 2, 'F'

union all

select 3, 'S'

union all

select 4, null

union all

select 5, 'SB'

select c.statusCode, c.caseId

from #case c

left join #gi g on c.statusCode = g.statusCode

where case when CHARINDEX('Suit Filed',g.statusDescription) >0 then 0 else 1 end = 1

drop table #gi

drop table #case If this is way-off what you expect then elaborate more and it would help others to take look if you build a sample table/data like the one I've used.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 4

Kind of but not way off base.... First I apologize... I am not a DBA but a hardware guy that got suckered into writing SSRS reports.... with that said... let me try to explain...

I am looking to do something like this:

If "case status histroy" does not have 'Filed'

DISPLAY case

but if "case status histroy" has 'Filed' and 'Non-Suit'

Do not Display

My problem is I do not know how to traverse rows to do the comparison of values (i.e. if the status history has several rows returned, how do I do the comparison on each row to verify that it is not have a value of Filed and/or Non-Suit). I can do it if it is the first row...but if it falls below that, I have no idea.

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 5

Paul Morris (6/1/2009)
I am not a DBA but a hardware guy that got suckered into writing SSRS reports.... with that said... let me try to explain...
I am looking to do something like this:
If "case status histroy" does not have 'Filed'
DISPLAY case
but if "case status histroy" has 'Filed' and 'Non-Suit'
Do not Display

With what you have mentioned now; things should be seen in a different way. By that I mean, there are certain things that would be best done in SQL and certain things can be easily & best done in SSRS.

What you are trying to achieve can be done like the SQL statement I've given above (get your hands dirty that's the best way to figure out). The only thing you'll have to figure out is the relationships between the table (I'd taken a guess). Also, in your list of status values there is no status "Non-Suit". How do you infer that? I though any status that does not contain a "Suit Filed" will be a non-suit. Another question that would come up is, when you say history table, does it imply that a single case id may have multiple entries in that table (Submitted to Filed to Resolved etc)? If yes, how do you find out which one is the latest status -- based on a flag or date stamp?

My problem is I do not know how to traverse rows to do the comparison of values (i.e. if the status history has several rows returned, how do I do the comparison on each row to verify that it is not have a value of Filed and/or Non-Suit). I can do it if it is the first row...but if it falls below that, I have no idea.

I guess you are thinking in terms of loop.. forget it and stay away from it. I would say you have a basic thing covered when you stated in sentence what exactly you want and what you don't -- that is your WHERE clause. Just figure out the table relationships and you will almost be done.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 6

First let me say, I appreciate the help.... I know it is difficult to work with someone that doesn't explain very well his needs due to lack of experience....

The unique key case_sk is the JOIN to almost all the tables and I can JOIN it for simple queries... but now management wants some more complex (at least for my skill set) reports. I am using BIDS to generate the reports (SQL 2005 which will be upgraded this weekend to 2008 by me).

There is a current_status flag in the table so it is very easy to pick up what is the current status. For any given case, I can get a list of the history of the statuses if I do not filter by the flag in the query.

The list I provided above doesn't contain a 'Non-Suit' status. For us, it basically means that a case has to be refiled in court. Few cases actually have to be refiled but I have to write it into the report. If I can get the query or expression filter for 'Filed', I should be able to figure out how to include 'Non-Suit' cases.

I guess my issue is I can get the TOP 1 or the current status but do not know how to get any other status. That is the root of my problem. I just don't know to move through rows... I thought an array but that might be over thinking the solution.... When I try to compare values, it only does it for the TOP 1 nothing else...

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 7

Paul Morris (6/2/2009)
I know it is difficult to work with someone that doesn't explain very well his needs due to lack of experience....

I'm pretty new to this forum as well. I've seen the veterans on this forum be more patient than what you would presume me to be. 🙂

From your post above I understand, You want to look at status history of all cases and not only the recent status. Let me see if I have been able to capture what you've stated:

a.) For a case (say Case1) you will fetch all the rows in case history table.

b.) Lets say we got 10 rows for Case1. Now look through all 10 records and see if any of the status is "Non-suit". If there is a "Non-suit" status ignore Case1 else show Case1.

Is the sequence of steps right? I'm trying to identify the steps you are thinking of and then build upon it.

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 8

YES!!! Initially it would actually 'Filed' but that is exactly what I need... 🙂

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 9

I hope then that the code below is what you are looking for

create table #gi (statusCode varchar(6), statusDescription varchar(100), groupCode varchar(6))

create table #case (caseId int, statusCode varchar(6))

insert into #gi

select 'F', 'Filed','CSTATU'

union all

select 'S', 'Submitted','CSTATU'

union all

select 'SB', 'Settled Before Suit Filed','CSTATU'

union all

select 'SA', 'Settled After Suit Filed','CSTATU'

union all

select 'NS', 'Non-Suit','CSTATU'

insert into #case

select 1,'SA'

union all

select 1, 'F'

union all

select 1, 'S'

union all

select 1, null

union all

select 2, 'SB'

union all

select 2, 'S'

union all

select 2, 'NS'

union all

select 3, 'SB'

union all

select 3, 'S'

union all

select 3, 'NS'

union all

select 4, 'SB'

union all

select 4, 'S'

union all

select 4, 'SA'

select c.statuscode, c.caseId

from #case c

left join #gi g on c.statusCode = g.statusCode

where c.caseid not in

(select distinct caseid from #case where statuscode ='Ns')

drop table #gi

drop table #caseAs you can see, case ids 2 & 3 are not listed because they have a status "Non-Suit" at one point of time (may not be the latest status). Also, one thing you may want is to just return a single row for each case.

IMP: I've used NOT IN (which is inefficient vs NOT EXISTS, I gather). So if somebody can tweak this query for NOT EXISTS and post back that would be great. I tried a bit but didn't succeed. :angry: