Matrix report problem reporting services 2005

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 report problem reporting services 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-16 : 05:33:35

Hi,

Is it possible to create a report as in the image?
The subheadings Regular Inventory and Resold Inventory and also the grand total aren't in the select query. Should i add them also to the query?

[url]http://i844.photobucket.com/albums/ab6/collie_bucket/report-6.jpg[/url]


select 'invoiced' as invoiced, 'Regular' as Status, Businessunit_name, 'Sum Cartons' as Data,
count(carton_id) as Cartons  from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name
union
select 'invoiced' as invoiced, 'Regular' as Status, Businessunit_name, 'Sum Invoices' as Data,
count(invoice_id)+1 as cartons from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name

Thanks :)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 06:01:55

you need add them as row values of a new column and also to the row group of matrix.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-16 : 08:52:30

Thanks.

I modified my query and tried what you suggested
but it didn't work unless i misunderstood. I added a new row group SubHeading and new column group SubHeading

select 'invoiced' as invoiced, 'Regular' as Status, Businessunit_name, 'Sum Cartons' as Data,
count(carton_id) as Cartons ,'Regular Inventory' as SubHeading
 from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name
union
select 'invoiced' as invoiced, 'Regular' as Status, Businessunit_name, 'Sum Invoices' as Data,
count(invoice_id)+1 as cartons,'Regular Inventory' as SubHeading
 from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name
UNION
select 'invoiced' as invoiced, 'NPL' as Status, Businessunit_name, 'Sum Cartons' as Data,
count(carton_id)+20 as Cartons ,'Resold Inventory' as SubHeading
 from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name
union
select 'invoiced' as invoiced, 'NPL' as Status, Businessunit_name, 'Sum Invoices' as Data,
count(invoice_id)+21 as cartons,'Resold Inventory' as SubHeading
 from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name
union
select 'not invoiced' as invoiced, 'Regular' as Status, Businessunit_name, 'Sum Cartons' as Data,
count(carton_id)+3 as Cartons ,'Regular Inventory' as SubHeading
 from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name
union
select 'not invoiced' as invoiced, 'Regular' as Status, Businessunit_name, 'Sum Invoices' as Data,
count(invoice_id)+4 as cartons,'Regular Inventory' as SubHeading
 from invoice
inner join packlist on packlist.packlist_id=invoice.packlist_id
inner join businessunit on businessunit.businessunit_id=packlist.businessunit_id
inner join carton on carton.packlist_id=packlist.packlist_id
group by  Businessunit_name

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 12:15:47

the column looks fine. why didnt it work? what were the other groupings you applied?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-17 : 10:02:44

Hi

I also group by businessunit

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:24:02

i meant row & column groups. please specify. also which column contains DIVISION1,DIVISION2 etc values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-18 : 10:33:45

Column group: =Fields!Businessunit_name.Value
Row groups: =Fields!Invoiced.Value
=Fields!Status.Value
=Fields!Data.Value
Data: =Sum(Fields!Cartons.Value)

Is this what you meant?

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources