Forum Discussion
Paginated Reports Query
DEFINE
VAR vFromDateTableStartofMonth1 =
IF (
PATHLENGTH ( @FromDateTableStartofMonth ) = 1,
IF (
@FromDateTableStartofMonth <> "",
@FromDateTableStartofMonth,
BLANK ()
),
IF (
PATHITEM (
@FromDateTableStartofMonth,
2
) <> "",
PATHITEM (
@FromDateTableStartofMonth,
2
),
BLANK ()
)
)
VAR vFromDateTableStartofMonth1ALL =
PATHLENGTH ( @FromDateTableStartofMonth ) > 1
&& PATHITEM (
@FromDateTableStartofMonth,
1,
1
) < 1
VAR vToDateTableStartofMonth1 =
IF (
PATHLENGTH ( @ToDateTableStartofMonth ) = 1,
IF (
@ToDateTableStartofMonth <> "",
@ToDateTableStartofMonth,
BLANK ()
),
IF (
PATHITEM (
@ToDateTableStartofMonth,
2
) <> "",
PATHITEM (
@ToDateTableStartofMonth,
2
),
BLANK ()
)
)
VAR vToDateTableStartofMonth1ALL =
PATHLENGTH ( @ToDateTableStartofMonth ) > 1
&& PATHITEM (
@ToDateTableStartofMonth,
1,
1
) < 1
EVALUATE
SUMMARIZECOLUMNS (
'ResultData'[Sample],
'ResultData'[Date Sampled],
'ResultData'[Sampling Point],
'Analyte'[Analyte],
'ResultData'[Class],
'ResultData'[Result],
'ResultData'[Status],
'PRData'[PR],
FILTER (
VALUES ( 'DateTable'[Start of Month] ),
(
vFromDateTableStartofMonth1ALL
|| 'DateTable'[Start of Month]
>= DATEVALUE ( vFromDateTableStartofMonth1 )
+ TIMEVALUE ( vFromDateTableStartofMonth1 )
)
&& (
vToDateTableStartofMonth1ALL
|| 'DateTable'[Start of Month]
<= DATEVALUE ( vToDateTableStartofMonth1 )
+ TIMEVALUE ( vToDateTableStartofMonth1 )
)
),
RSCUSTOMDAXFILTER (
@SiteDEPT,
EqualToCondition,
[Site].[DEPT],
String
),
"TotalSamples", [TotalSamples]
)