Skip to Content
0
Former Member
Jun 04, 2009 at 10:54 PM

How to get XLR to show BPs with no transaction data for a given date range

28 Views

Hi -

I am building an XLR report that does a comparison of net sales data across two periods for a given sales employee's BPs.

The report has the row expansion:

FACT BPA(*) SLP(SlpName = "ASalesPersonNameHere") ARDT(Code = "ARCreditMemo", "Invoice") Group by BPA.CardName

and column expansions:

FIG(SO_TaxDate = @StartDate:@EndDate)

and

FIG(SO_TaxDate = @StartDate2:@EndDate2)

where @StartDate, @EndDate, @StartDate2, @EndDate2 are parameters that define the two ranges of dates.

The column formulas are, from left to right:

=ixDimGet("BPA", "CardName")

=ixGet("SO_DocTotal") <-- filtered by column expansion for first date range

=ixGet("SO_DocTotal") <-- filtered by column expansion for second date range

The report works fine except for one problem, I would like it to include BPs for which no transaction occurred in either date range as well.

Any help is greatly appreciated!

Thanks,

Lang Riley