Cross tabs with Drill downs

Hi Guys
I need to generate a report where I need to have drill downs in a matrix(cross tab query) in Business Intelligence.
When I try to build a report using the report wizard, I can either select matrix or Drill downs and subtotals.
Can I have both of these options?

Thanks
Mita

Moving to AS forum.

Cross tabs problem in sql server

I want a query which is used in sql server like access query

TRANSFORM Sum(Q_DayBook.Debit) AS SumOfDebit
SELECT Q_DayBook.Purticular, Sum(Q_DayBook.Debit) AS [Total Of Debit]
FROM Q_DayBook
GROUP BY Q_DayBook.Purticular
PIVOT Q_DayBook.CDate;Unfortunately, SQL Server does not have the same built-in functionality that Access does. The solution is to either do the pivot in the presentation layer, or to use some specialized Transact-SQL code in the database.

See this previous discussion for further details:view post 346479

Terri

Cross Tabs and Groupings

I m trying to struct a query that will give me the impression reports. I want a table that give the page name ,yesterday hits,todays hits, this weeks hits and this month hits.
My Table is like this ..
tblStat----
MID -- key id incrementing with no replication
MURL -- name of page
MIMp -- Counted impression at that day
MDate -- Smalldatetime value
------
returning rows are similar like:
1 index.asp 5 13/12/2003
2 main.asp 2 13/12/2003
3 index.asp 3 14/12/2003
4 main.asp 8 14/12/2003
--
I tried to do a query and it is :
SELECT DISTINCT dbo.tblStats.MURL, INSERT1.MToday, INSERT2.Mpre, INSERT3.MWeek, INSERT4.MMonth
FROM dbo.tblStats LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS Mpre
FROM tblStats
WHERE (Mdate = '3/18/2003')
GROUP BY MURL) INSERT2 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MToday
FROM tblStats
WHERE (Mdate = '3/19/2003')
GROUP BY MURL) INSERT1 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MWeek
FROM tblStats
WHERE (MDate >= '3/15/2003' AND MDate <= '3/21/2003')
GROUP BY MURL) INSERT3 ON dbo.tblStats.MURL = dbo.tblStats.MURL LEFT OUTER JOIN
(SELECT DISTINCT MURL, SUM(MIMP) AS MMonth
FROM tblStats
WHERE (MDate >= '3/01/2003' AND MDate <= '3/29/2003')
GROUP BY MURL) INSERT4 ON dbo.tblStats.MURL = dbo.tblStats.MURL
------------
but this query returning to me too much records. Because i only have to pages in table that must be two records to return me back. If anyone know how to solve this please response me. Thank you from now on.How About something using the [Case When] & DatePart

SELECT DISTINCT dbo.tblStats.MURL,
SUM(CASE WHEN MDate = Getdate() THEN MIMP ELSE 0 END) AS Today,
SUM(CASE WHEN MDate = (Getdate()-1) THEN MIMP ELSE 0 END) AS YesterDay,
SUM(CASE WHEN MDate = (Getdate()+1) THEN MIMP ELSE 0 END) AS Tommorow,
SUM(CASE WHEN DatePart(wk, MDate) = DatePart(wk, GetDate()) THEN MIMP ELSE 0 END) AS ThisWeek,
FROM tblStats

Or Something like that

PS. Joking about the Tommorow One - lol

GW|||GWilliy,
no distinct, but group by. Do not use abrevs for time parts, it is missguiding.
wk=week

SELECT dbo.tblStats.MURL
...
GROUP BY dbo.tblStats.MURL

hexcode,
be sure you don't store time. Also note that function DATEPART(week,@.D datetime) is nondeterministic (SET DATEFIRST/LANGUAGE dependent).

Good luck!|||The query really worked fine. Just i send the date values from program instead of getdate. Thank you for your help.