Need Help with SSRS Report Builder Date Parameters (Paginated Report)

olsen9 — Tue, 18 Jun 2024 15:12:45 GMT

I'm not having any issues using dates between my start date and end date.

However I have a request to only show items outside of my start and end date.

Can't figure out how I can accomplish this. My Parameters are FromIssuesCreated & ToIssuesCreated

DEFINE VAR vFromIssuesCreated1 = IF(PATHLENGTH(@FromIssuesCreated) = 1, IF(@FromIssuesCreated <> "", @FromIssuesCreated, BLANK()), IF(PATHITEM(@FromIssuesCreated, 2) <> "", PATHITEM(@FromIssuesCreated, 2), BLANK()))
VAR vFromIssuesCreated1ALL = PATHLENGTH(@FromIssuesCreated) > 1 && PATHITEM(@FromIssuesCreated, 1, 1) < 1
VAR vToIssuesCreated1 = IF(PATHLENGTH(@ToIssuesCreated) = 1, IF(@ToIssuesCreated <> "", @ToIssuesCreated, BLANK()), IF(PATHITEM(@ToIssuesCreated, 2) <> "", PATHITEM(@ToIssuesCreated, 2), BLANK()))
VAR vToIssuesCreated1ALL = PATHLENGTH(@ToIssuesCreated) > 1 && PATHITEM(@ToIssuesCreated, 1, 1) < 1
EVALUATE SUMMARIZECOLUMNS('Issues'[Created], 'Issues'[DateIssueIdentified], 'Issues'[ISMS], 'Issues'[IssueStatement], 'Issues'[IssueType], 'Issues'[ActivityID], 'Issues'[RelatedToContractorPerformance], 'Issues'[RepeatOccurrence], 'Issues'[using_related], 'Issues'[TitleActivity], FILTER(VALUES('Issues'[Created]), (vFromIssuesCreated1ALL || 'Issues'[Created] >= DATEVALUE(vFromIssuesCreated1) + TIMEVALUE(vFromIssuesCreated1)) && (vToIssuesCreated1ALL || 'Issues'[Created] <= DATEVALUE(vToIssuesCreated1) + TIMEVALUE(vToIssuesCreated1))))

Re: Need Help with SSRS Report Builder Date Parameters (Paginated Report)

olsen9 — Tue, 18 Jun 2024 16:21:53 GMT

Update:

Updated code to reflect the below. It now will allow for only values outside my 2 parameters

DEFINE
// Declare the parameters
VAR FromIssuesCreated = DATE(2023, 1, 1) // Example date, replace with your actual parameter
VAR ToIssuesCreated = DATE(2023, 12, 31) // Example date, replace with your actual parameter

// Variables to parse the date parameters
VAR vFromIssuesCreated1 =
IF(
PATHLENGTH(FORMAT(FromIssuesCreated, "yyyy-MM-dd")) = 1,
IF(FORMAT(FromIssuesCreated, "yyyy-MM-dd") <> "", FORMAT(FromIssuesCreated, "yyyy-MM-dd"), BLANK()),
IF(PATHITEM(FORMAT(FromIssuesCreated, "yyyy-MM-dd"), 2) <> "", PATHITEM(FORMAT(FromIssuesCreated, "yyyy-MM-dd"), 2), BLANK())
)

VAR vFromIssuesCreated1ALL =
PATHLENGTH(FORMAT(FromIssuesCreated, "yyyy-MM-dd")) > 1 && PATHITEM(FORMAT(FromIssuesCreated, "yyyy-MM-dd"), 1, 1) < 1

VAR vToIssuesCreated1 =
IF(
PATHLENGTH(FORMAT(ToIssuesCreated, "yyyy-MM-dd")) = 1,
IF(FORMAT(ToIssuesCreated, "yyyy-MM-dd") <> "", FORMAT(ToIssuesCreated, "yyyy-MM-dd"), BLANK()),
IF(PATHITEM(FORMAT(ToIssuesCreated, "yyyy-MM-dd"), 2) <> "", PATHITEM(FORMAT(ToIssuesCreated, "yyyy-MM-dd"), 2), BLANK())
)

VAR vToIssuesCreated1ALL =
PATHLENGTH(FORMAT(ToIssuesCreated, "yyyy-MM-dd")) > 1 && PATHITEM(FORMAT(ToIssuesCreated, "yyyy-MM-dd"), 1, 1) < 1

EVALUATE
SUMMARIZECOLUMNS(
'Issues'[Created],
'Issues'[DateIssueIdentified],
'Issues'[ISMS],
'Issues'[IssueStatement],
'Issues'[IssueType],
'Issues'[ActivityID],
'Issues'[RelatedToContractorPerformance],
'Issues'[RepeatOccurrence],
'Issues'[using_related],
'Issues'[TitleActivity],
FILTER(
VALUES('Issues'[Created]),
(
(vFromIssuesCreated1ALL || 'Issues'[Created] < DATEVALUE(vFromIssuesCreated1)) ||
(vToIssuesCreated1ALL || 'Issues'[Created] > DATEVALUE(vToIssuesCreated1))
)
)
)

topic Re: Need Help with SSRS Report Builder Date Parameters (Paginated Report) in DAX Commands and Tips

Need Help with SSRS Report Builder Date Parameters (Paginated Report)

Re: Need Help with SSRS Report Builder Date Parameters (Paginated Report)