Duplicate entry in Matrix - not able to resolve

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)

Duplicate entry in Matrix - not able to resolve

Author

Topic

lavvu
Starting Member

13 Posts

Posted - 2008-12-03 : 01:32:57

Hi all,

I am having an issue with data displayed in a matrix.

I have a data set that has below data:

Mrg.ID.....Mrg.Name....UID.....U.Name.....Proj.....Dept.....Role

U1234......Dan..........U1523...John.......ABC......Dev......Pgmmer
U1234......Dan..........U1523...John.......ABC......Dev......Tester
U1234......Dan..........U1573...Ann........ABC......Dev......Pgmmer
U1234......Dan..........U1573...Ann........ABC......Dev......Tester
U1234......Dan..........U1793...Mat........ABC......Dev......SSE
U1234......Dan..........U1793...Mat........ABC......Dev......Pgmmer
U1234......Dan..........U1793...Mat........ABC......Dev......Tester

In the report I have a matrix in which the data should be displayed as:

Mrg.ID.....Mrg.Name....UID.....U.Name.....Proj.....Dept.....Role

U1234......Dan.........+U1523...John.......ABC......Dev......Pgmmer
...............................................................Tester
.......................+U1573...Ann........ABC......Dev......Pgmmer
...............................................................Tester
.......................+U1793...Mat........ABC......Dev......SSE
...............................................................Pgmmer
...............................................................Tester

But it is displaying as

Mrg.ID.....Mrg.Name....UID.....U.Name.....Proj.....Dept.....Role

U1234......Dan.........+U1523...John.......ABC......Dev......Pgmmer
...............................................................Tester
.......................+U1573...Ann........ABC......Dev......Pgmmer
...............................................................Tester
U1234......Dan.........+U1793...Mat........ABC......Dev......SSE
...............................................................Pgmmer
...............................................................Tester

The Manager ID and name is repeating. It should not.
I have checked my query but i cant seem to find any issues with that. There are other fields that are used also. I am using sub total also in the reprot but i dont think it is because of that the manager id and manager name is repeating. The manager id and name is repeating only for two managers. For all the other 37 managers the entry is coming correct. Under some managers there are about 15 employees and the manager id is not repeating.

Can you please help me find out why the manager id and name is repeating? Could it be an issue with the query we are using?

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 01:41:10

there's a property called hide duplicates for each cell. enable it for textboxes inside matrix displaying name and id.

lavvu
Starting Member

13 Posts

Posted - 2008-12-03 : 01:51:00

Nope. It is not working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 02:52:18

quote:
Originally posted by lavvu

Nope. It is not working.

it will work provided you select correct data region inside dropdown

lavvu
Starting Member

13 Posts

Posted - 2008-12-03 : 03:01:48

The drop down list contains both the row group name and the dataset name in it. if i am selecting the dataset name then no error is coming but the duplicate ids are there.

if i am selecting the matrix row group name then it is showing error:

"The value is the name of the dataset of group over which to apply the hiding. it should either be the name of a group containing the current group or to hide duplicates over the entire dataset the name of the current dataset."

no idea wht to do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 03:03:09

quote:
Originally posted by lavvu

The drop down list contains both the row group name and the dataset name in it. if i am selecting the dataset name then no error is coming but the duplicate ids are there.

if i am selecting the matrix row group name then it is showing error:

"The value is the name of the dataset of group over which to apply the hiding. it should either be the name of a group containing the current group or to hide duplicates over the entire dataset the name of the current dataset."

no idea wht to do.

then give the current groups name

lavvu
Starting Member

13 Posts

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

the same error is coming.

WabbitSlayer
Starting Member

6 Posts

Posted - 2010-01-12 : 11:38:09

Hi,

I'm having the same problem. Did you manage to find a work-around for this? Please advise.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 12:52:30

quote:
Originally posted by WabbitSlayer

Hi,

I'm having the same problem. Did you manage to find a work-around for this? Please advise.

Thanks

what are your row groups? can you post screenshot of report designer as well as preview here?

WabbitSlayer
Starting Member

6 Posts

Posted - 2010-01-13 : 01:14:48

quote:
Originally posted by visakh16

quote:
Originally posted by WabbitSlayer

Hi,

I'm having the same problem. Did you manage to find a work-around for this? Please advise.

Thanks

what are your row groups? can you post screenshot of report designer as well as preview here?

Nope - my bad - this is not my problem.
My issue is that I get data in a matrix, where there's not supposed to be data. Looks like a grouping issue, but I cannot see where I've gone wrong.

I've got data in the following order

Date, Person, WorkedFor, Area, CostCode, Hours1, Hours2
01/10/2010, A, Foreman, Yard, 123, 8.5, 0.00
02/10/2010, A, Foreman, Yard, 123, 8.5, 0.00
03/10/2010, A, Foreman, Yard, 123, 8.5, 0.00
04/10/2010, A, Foreman2, Yard, 123, 8.5, 0.00
05/10/2010, A, Foreman2, Yard, 123, 8.5, 0.00
06/10/2010, A, Foreman2, Yard, 123, 8.5, 0.00

In the matrix, the row group consists of:
Person, WorkedFor, Area and CostCode

and the column group is the date.

so I expect the following

01/10/2010 02/10/2010 03/10/2010 04/10/2010 05/10/2010 06/10/2010
PersonA Foreman Yard 123 8.5, 0.00 8.5, 0.00 8.5, 0.00 [null] [null] [null]
PersonA Foreman2 Yard 123 [null] [null] [null] 8.5, 0.00 8.5, 0.00 8.5, 0.00

What I get is:

01/10/2010 02/10/2010 03/10/2010 04/10/2010 05/10/2010 06/10/2010
PersonA Foreman Yard 123 8.5, 0.00 8.5, 0.00 8.5, 0.00 RandomData RandomData RandomData
PersonA Foreman2 Yard 123 RandomData RandomData RandomData 8.5, 0.00 8.5, 0.00 8.5, 0.00

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:53:07

whats the expression given in data part?

WabbitSlayer
Starting Member

6 Posts

Posted - 2010-01-13 : 04:04:08

quote:
Originally posted by visakh16

whats the expression given in data part?

Thanks for the quick response.

I must admit, I feel like such a *dumbass* now. Don't know how I didn't see the sum in there, but it's all good now. Thanks for the answer!!!!

Keep well!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 04:07:34

welcome

Glad that you spotted and sorted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources