Good day, I have searched everywhere and seems like the lookup function might be the solution but I need some advise. I have a report with more than one dataset linked to it. Tablix 1 returns a count based on a name field and groups(counts) it per date on specified date range. Tablix 2 returns details on tablix 1's count with more fields including the date per row.
I need to add a "click_link" on Tablix 1 so that when you click on any of the counted values it must return the detailed data from tablix 2 but only if the date matches.
Image of Tablix 1:
Image of Tablix 2:
Image of Tablix 1's data returned without where clause
This is the query for dataset 1(Tablix 1)
DECLARE @Actual int;
DECLARE @Date varchar;
SELECT Actual.Actual,Actual.DateActual,Original.Original,Original.DateOriginal FROM
(
Select count (payhist.AC_CODEID) as Actual,PAYHIST.PH_DATE as DateActual from PAYHIST
join Paymonth on PAYHIST.PH_DATE >= Paymonth.ph_datesd and PAYHIST.PH_DATE <= Paymonth.ph_dateed
Join EMPLOYEE E on PAYHIST.MST_SQ = e.MST_SQ
Join Worklocation wl on e.wl_codeid = wl.wl_codeid
Join Paymonth pm on PAYHIST.PH_DATE >= pm.ph_datesd and PAYHIST.ph_date <= pm.ph_dateed
where Paymonth.CurrentPD = 1
and PH_EXCEPTION = 1
and e.EMP_DISCHARGE is null
and e.EMP_CONTRACTOR = 0
and wl.WL_CODEID = 1
GROUP BY PAYHIST.PH_DATE
) AS Actual
FULL JOIN
(
Select count (PAYHISTTEMP.MST_SQ) as Original,PAYHISTTEMP.PH_DATE as DateOriginal from PAYHISTTEMP
join Paymonth on PAYHISTTEMP.PH_DATE >= Paymonth.ph_datesd and PAYHISTTEMP.PH_DATE <= Paymonth.ph_dateed
Join EMPLOYEE E on PAYHISTTEMP.MST_SQ = e.MST_SQ
Join Worklocation wl on e.wl_codeid = wl.wl_codeid
Join Paymonth pm on PAYHISTTEMP.PH_DATE >= pm.ph_datesd and PAYHISTTEMP.ph_date <= pm.ph_dateed
where Paymonth.CurrentPD = 1
and PH_EXCEPTION = 1
and e.EMP_DISCHARGE is null
and e.EMP_CONTRACTOR = 0
and wl.WL_CODEID = 1
group by PAYHISTTEMP.PH_DATE
) AS Original ON Original.DateOriginal = Actual.DateActual
where (@Actual = '-1' or @Actual = Actual.Actual)
and (@date = '-1' or @date = Original.DateOriginal)
order by DateOriginal Asc
And this is the query for dataset 2
Select e.EMP_EMPNO,e.emp_Firstname,e.emp_surname,d.DPT_NAME,a.AC_NAME,ph.PH_DATE as PAYHIST_DATE,d.DPT_RESPEMP,
ph.PH_FIRSTCLOCK,ph.PH_LASTCLOCK,WL_NAME
into EXCEPREPORT
from PAYHIST PH
Join EMPLOYEE E on ph.MST_SQ = e.MST_SQ
Join DEPARTMENT D on ph.DPT_CODEID = d.DPT_CODEID
join ATTEND A on ph.AC_CODEID = a.AC_CODEID
Join Worklocation wl on e.wl_codeid = wl.wl_codeid
Join Paymonth pm on ph.PH_DATE >= pm.ph_datesd and ph.ph_date <= pm.ph_dateed
where pm.CurrentPD = 1
and PH_EXCEPTION = 1
and e.EMP_DISCHARGE is null
and e.EMP_CONTRACTOR = 0
and e.wl_codeid = 1
select pht.EMP_EMPNO,pht.EMP_FIRSTNAME,pht.EMP_SURNAME, DPT_NAME,e.emp_firstname as RESP_FIRSTNAME,e.emp_surname as RESP_SURNAME,
AC_NAME,PAYHIST_DATE,PH_FIRSTCLOCK,PH_LASTCLOCK,WL_NAME as WORKLOCATION
from EXCEPREPORT pht
join employee e on pht.dpt_respemp = e.mst_sq
drop table EXCEPREPORT
@Harry, I am completely confused now...Should the parameters be added on the summary or details tablix? I am only interested in the details of the outstanding transactions. Which I get from the summary tablix's data set. The query returns a count of one of the columns namely(AC_CODE) as well as date and the same query (using full join) to run two select queries each to different tables does a count on a similar column and date(Results as link of picture above). The details are then returned with Tablix 2's data set which is a different query and there are no identical column names except the date which is the same on both queries with different column names. Ultimately I want to send this report out as a Excel attachment and if you click on one of the dates on summary in outstanding row it must sort of bookmark to the detail sheet but only show details for relevant date above outstanding number selected. I hope this makes sense?
Please let me know if you require any additional info?
