Matrix Data not displayed properly - Urgent

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Matrix Data not displayed properly - Urgent

Author

Topic

lavvu
Starting Member

13 Posts

Posted - 2008-11-28 : 02:04:06

Hi,

In my report I am using a matrix to display data. I am facing a problem in the display of data.

The query I am retrieving gives me the followint output:

Employee BU ACCOUNT PROJECT SKILL SET PLACE BILLEDSTATUS Start Date End Date

Badrish S A B Well-123 .net India Billed jan 8 2008 dec 31 2008

Badrish S A B Well-123 .net India Billed jan 22 2008 dec 31 2008

Badrish S A B Well-123 BI India Non-Billed jan 8 2008 dec 31 2008

Badrish S A B Well-123 BI India Non-Billed jan 22 2008 dec 31 2008

Lavanya A B Well-123 .net India Billed jan 10 2008 dec 31 2008

Lavanya A B Well-123 .net India Billed jan 25 2008 dec 31 2008

Lavanya A B Well-123 BI India Non-Billed jan 10 2008 dec 31 2008

Lavanya A B Well-123 BI India Non-Billed jan 25 2008 dec 31 2008

The issue is with SSRS.

I have to calculate four things in my report

1. No of onsite resource = Resource whose location is not India

2. No of oniste billed resource = Resource whose location is not India and whose billstatus is billable

3. No of offshore resource = Resource whose location is India

4. No of offshore billed resource = Resource whose location is India and whose billstatus is billable

5. No of shadow resources = Resources whose billstatus is non-billable.

When i am trying it out on the reports i am getting the data correct if there are only billed resoruces or non-billed resources are there in a project.

if there is a mixture of billed and non billed resoruces in the project then the displayed data is not correct.

If i am expanding the drillable fields to the lowest drill level then the data is correct. But I want to get the data correct even if the data is in the expanded form. The issue is there only for the billed resoruces and shadow resources.

Can any one help me in this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 05:55:06

Show a data example of discrepancy you're facing.Its hard to make out problem from explanation. Also what were coulmn & row groupings you applied in matrix?

lavvu
Starting Member

13 Posts

Posted - 2008-11-28 : 13:04:56

I have dataset where the data is like:

EmpID-----------Technology--------------------------BillStatus----------------------Year

1234---------------C----------------------------------------billed----------------------------2008

1233---------------C----------------------------------------non-billed---------------------2008

1234---------------SQL------------------------------------billed----------------------------2008

1233---------------SQL----------------------------------- non-billed---------------------2008

When I display this in the matrix the aggregates for column groups is coming all wrong.

The aggregate is shown as

-------------------------------------2008
Technology---------------------------# Resources------#Billed-------# Non Billed
C------------------------------------2----------------0-------------2
SQL----------------------------------2----------------0-------------2

Where it should have been

-------------------------------------2008
Technology---------------------------# Resources------#Billed-------# Non Billed
C------------------------------------2----------------1-------------1
SQL----------------------------------2----------------1-------------1

I cannot use SUM(IIF) as I need to take count of distinct resources.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 13:14:02

Why take sum? it seems like what you want is count. so just use

=CountDistinct(IIF(Fields!BillStatus.value="billed",Fields!EmpID.Value,Nothing)

for billed and

=CountDistinct(IIF(Fields!BillStatus.value="non-billed",Fields!EmpID.Value,Nothing)

for Non Billed

lavvu
Starting Member

13 Posts

Posted - 2008-12-03 : 01:07:06

Thank you.
It worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 01:20:59

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources