SSRS Manual Drill Down

[prachin1]

Hi,

I need to know the steps to manually create a ssrs report with a drill down for the query from adventure works database usgin matrix. Can someone guide me through this.

The query is as follows:-

select PPC.name as cat,PPSC.name as subcat,year(SSOH.orderdate) as FinYear
,'Q'+convert(char(2),datepart(qq,SSOH.orderdate)) as FinQuarter,
sum(SSOD.UnitPrice * SSOD.OrderQty) as QuarterSum
from Sales.SalesOrderHeader SSOH
inner join Sales.SalesOrderDetail SSOD on SSOH.salesorderid = SSOD.salesorderid
inner join production.product PP on SSOD.productid = PP.productid
inner join Production.ProductSubcategory PPSC on PP.productsubcategoryid = PPSC.productsubcategoryid
inner join Production.ProductCategory PPC on PPSC.productcategoryid = PPC.productcategoryid
where year(SSOH.orderdate) between 2002 and 2003
group by PPC.name,PPSC.name,year(SSOH.orderdate),
'Q'+convert(char(2),datepart(qq,SSOH.orderdate))
order by PPC.name,PPSC.name,finyear,finquarter

and the output should be like a drill down for components showing sub categories and year showing the quarters

Regards
Prachin

[Island1]

Check out the "Company Sales" report within the samples that ship with Reporting Services - the report reflects the Adventure Works Cycles sales by quarter and product category. Moreover, it illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.

In short, everyting you want and more - all in a working example. The tutorial that accompanies this and the other sample reports would be another great place to learn the basics.

Good Luck ...

Bill

[prachin1]

Hi,

Thanks Bill. Actually I had looked at the same report and was trying to create it manualy by dragging the matrix control from the toolbox and adding the columns in it. It works fine without drill down, but when i want to assign drill down to the columns I am not able to, I read on net that it is possible by using the "Visibility" and "toggle" options. But no clear material is available as to how the link is to be made between the columns.

Regards
Prachin

[Island1]

I think it would be harder to figure out how to accomplish everything one wanted to do with "drag and drop" than to simply look at things like properties in the sample. Properties abound in RS, several levels deep in some cases, and you're not going to be able to get far with real world reports without investing a little time in learning what they are, and how they interrelate.

As you might guess from my articles, the matrix data region is my clear favorite to date - especially for OLAP. I spend pages describing its nuances and features, and the power you can obtain through expressions, etc., via the properties / other settings. I spend significant time on the things you mention, which are really basic "need to knows" for anyone who is going to be doing this more than once.

Good Luck.

Bill