Forum Discussion
Cannot see the drill through option
Hi I have two reports (graphs). One graph report is (weekly counts) the other report is daily , both have same filters (year and month). I added the countsweekly field from the weekly counts report to the " drill through field" . I turned on the cross report toggle on both the reports , but when i right click i cannot see the drill through option.
Is there some kind of special requirements for the Direct Query
The two datasets are from the same underlying tables, only difference is one is summarized at the week level and the other is detailed daily. please advice.
PLEASE NOTE : I am using Direct Query
3 Replies
- jdbuchanan71
Super User
hello Anonymous
What you want to add to the drillthrough well is the category fields that match between to two so add year and month to the drillthrough fields.
I added month to the drillthrough on my page 2 so on page 1, because I have the month field in that report, I can drillthrough to page 2:Thanks jdbuchanan71 it stll not working , please see the below sql's
first sql is the report by week, the second one is by day . I named the fields the same . My main page has multiple reports (from different datasets) , weekly count is a graph and i have slicers named year and weekof year (littlechange from last time). In the daily count report i have the same slicers with the same names , still i cannot see the drill through option when i right click on the graph.
select distinct year(orderdate) as year,datepart(mm,orderdate) as monthofyear,datepart(week,orderdate) as weekofyear , count(k.salesorderno) over(partition by year(orderdate),datepart(week,orderdate) ) NewFabricOrderCounts from (SELECT distinct t1.[SalesOrderNo] ,t1.[ItemCode]
,convert(date,t2.OrderDate) as orderdate,t2.OrderStatus ,ROW_NUMBER() OVER(PARTITION BY T1.SALESORDERNO ORDER BY T1.SALESORDERNO) AS RN FROM [dbo].[SO_SalesOrderHistoryDetail] t1 inner join [dbo].[SO_SalesOrderHistoryHeader] t2 on t1.SalesOrderNo=t2.SalesOrderNo where itemcode ='*00_cover_len' ----and convert(Date,t2.OrderDate)=convert(Date,getdate()) ----- AND DATEDIFF(DD,CONVERT(dATE,ORDERDATE),convert(Date,getdate()))=1 and t2.OrderStatus in ('A','C') ) k where k.rn=1 and year(orderdate)>=2016select distinct k.OrderYear as year,K.OrderMonth as monthofyear,weekofyear,rtrim(k.orderdate) as orderdate,count(k.salesorderno) over(partition by k.orderyear,orderdate) as dailycounts from ( SELECT distinct t1.[SalesOrderNo] ,t1.[ItemCode] ,convert(date,t2.OrderDate) as orderdate,year(convert(date,t2.OrderDate)) as OrderYear ,MONTH(convert(date,t2.OrderDate)) as OrderMonth,datepart(week,convert(date,t2.OrderDate)) as weekofyear ,t2.OrderStatus ,ROW_NUMBER() OVER(PARTITION BY T1.SALESORDERNO ORDER BY T1.SALESORDERNO) AS RN FROM [dbo].[SO_SalesOrderHistoryDetail] t1 inner join [dbo].[SO_SalesOrderHistoryHeader] t2 on t1.SalesOrderNo=t2.SalesOrderNo where itemcode ='*00_cover_len' and t2.OrderStatus in ('A','C') ) k where k.rn=1
