Having a problem with List - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Having a problem with List

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-01 : 14:29:28

Hi,
I have a list that have two matrix inside of it. The list dataset is called list. The dataset for the 1st matrix is called mat1 and the 2nd is mat2. I can't seem to have it work. I will give an example:
list have 3 tribes: chehalis, colville and tulalip.
matrix1 have 4 type of service which are in the row: medical, dental, psychiatric and chemical. in the column is ethnic: native or non native and the data is $$.
matrix2 also have 2 type of service: pharmacy and pccm. in the column is ethnic: native or non native and the data is $$.
I would like list to show if matrix have that tribe and blank if not. Somehow it's not working. I give me both but the $$ is not correct.

Please help!!!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 00:54:47

does both the datasets return data for all tribes?

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-02 : 11:44:19

Yes. I only thing I can think of is the dataset. I have different dataset for each. I played around and use dataset list for list and matrix1 and it worked correctly, but when I use mat2 dataset, it just sum the $$. Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 11:48:51

quote:
Originally posted by huynhtl

Yes. I only thing I can think of is the dataset. I have different dataset for each. I played around and use dataset list for list and matrix1 and it worked correctly, but when I use mat2 dataset, it just sum the $$. Please help.

can you give a data sample to explain discrepancy? is the problem adding up $$ of tribes which are not in list? i'm not too clear with your explanation there

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-02 : 12:32:12

ok. below is what i want my report to be

CHEHALIS

matrix1
services $$
Medical $144,969
Dental $42,251
MH $167,233

matrix2---This should be blank because chehalis haven't billed.
services $$
Medical $354,453
Dental $354,453
MH $354,453

Hope this help....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 13:18:47

which field decides whether the tribes have been billed or not? also are two matrices repeated for each tribe?

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-02 : 14:16:02

to make it easier, here's my code for both matrix.

---matrix1---
select b.services,b.ethnic,a.tribe,sum(reimb) as $$
from dbo.tribalipacrollup a left join dbo.tribal_payment_type b
on a.types = b.type
where b.total_type in ('encounters')
and monthorder =@month
group by b.services,b.ethnic,a.tribe

----matrix2-----
select b.services,b.ethnic,a.tribe,sum(reimb) as $$
from dbo.tribalipacrollup a left join dbo.tribal_payment_type b
on a.types = b.type
where b.total_type in ('ffs')
and monthorder =@month
group by b.services,b.ethnic,a.tribe

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 12:41:43

does list dataset contain field total_type?

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-03 : 13:45:34

Yes. I do have total_type in the list dataset, but I don't have a way of separating it in the matrix. I don't want it to show up as one matrix. Am I makiing myself clear?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:54:51

quote:
Originally posted by huynhtl

Yes. I do have total_type in the list dataset, but I don't have a way of separating it in the matrix. I don't want it to show up as one matrix. Am I makiing myself clear?

then isnt it enough to set visibility property of matrix based on total_type field, something like

=IIF(Sum(IIF(Fields!Total_type.value="ffs",1,0),"listdataset")>0,False,True)

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-03 : 16:02:56

I tried that code in the matrix visibility property in the expression, it give me blank page. no data.

=IIF(Sum(IIF(Fields!total_type.value="ffs",1,0),"list")>0,False,True)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 22:34:38

so were you asking to remove some data alone rather than removing entire matrix?

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-04 : 11:22:23

No, what you showed me is correct, but the problem is there are no data at all. Nothing show up at all. I've been playing around with that code and used just the listdataset for both matrix and change the code in the visibility property around. matrix one should show up for the encounter.
=IIF(Sum(IIF(Fields!total_type.value="ffs",1,0),"listdataset")>0,False,True)
matrix two should show up for the ffs.
=IIF(Sum(IIF(Fields!total_type.value="encounter",1,0),"list")>0,False,True)
but it doesn't work at all. What it does is it gave me the exact data for both matrix.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-04 : 11:27:16

I was wondering, can I write the code in the visibility property like this: iif(fields!total_type.value='encounter',true,false)...
this should show up in matrix one if there any data in the medical or dental service for that tribe.
iif(fields!total_type.value='ffs',true,false)... this should show up in matrix tow if there any data in the pharmacy and pccm service for that tribe.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 11:45:06

quote:
Originally posted by huynhtl

I was wondering, can I write the code in the visibility property like this: iif(fields!total_type.value='encounter',true,false)...
this should show up in matrix one if there any data in the medical or dental service for that tribe.
iif(fields!total_type.value='ffs',true,false)... this should show up in matrix tow if there any data in the pharmacy and pccm service for that tribe.

shouldnt u be looking for presence of this value at all and decide on visibility? that was the reason why i used sum()>0 rather than individual value.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-04 : 13:32:16

I tried it again using your code, it still doesn't work. I'm not sure what I did wrong.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-04 : 18:26:54

I figure it out. I have to use filters in the matrix. Now i have a problem with the tribes. Not all of the tribe name show up. How do i fix this?

Thnx.

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-12-05 : 13:20:10

I figure out the problem too. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 23:37:32

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources