I need a report that will produce NULL values when there is no activity for a given date period and category. I’ve created data provider for the date range and that works when adding the metric. Meaning if there was no activity for Jan-06, the result is showin zero on the report as I’ve used the if ISNULL function for the metric. I’ve created a third data provider for the distinct categories. However, when I drag the object into the report, only those categories with activity are showing. There are only two distinct categories, so I am expecting to see two rows for each reporting month, with some rows displaying zero for the NULL activity. There also is the issue that if there was no activity under either category for the month, then the category value is blank in the row for the month with no activity. I’m guessing I do not have something linked correctly between the three data providers.
Make sure that you use the correct objects. Even if you have linked, you need to use objects from all (3?) of your data providers to get the correct result.
Suppose that you have sales for 3 of the 4 weeks in a month. That’s data provider 1. Then you have DP2 that returns all weeks. When you put the “week name” (or number or whatever) on the report, be sure to use the one from DP2, not DP1.
Yes, DP1 contains all the activity detail, amount of sales by category by date. DP2 contains only a date range from the date table based on a prompt, so all months Jan-05 through Dec-05, and that is the date column in the report. This part works where all I have is the date and the amount of sales. If there is a month with no activity, the month still shows in the report and with zero, so no problem. DP3 contains only the category types, in this case just two values. So when I add the category type from DP3, the report only shows those categories with activity. Meaning if Jan-05 only had category 1 activity, the report does not show a row for category 2 with zero.
Dave - that is what I am doing. First DP is a listing of 13 months, second DP is a listing of 20 systems, third DP is the report detail which includes a month, system and metric. First column of the table is the 13 months from DP1. When I add the system listing from DP2, I would expect to see 20 rows for each month. I also want to set the month as a section, so for each section, I should see 20 records, but that is not happening. Only those systems with activity are showing on the table. There is no link between DP1 and DP2. The links are between the month in DP1 and month in DP3, and the system name between DP2 and DP3.
Attached is an example of what I mean from eFashions. You’ll see the report does not show all stores for each month. However, if you delete DP3, then all stores do show for each month.
Well, I was hoping that report would demonstrate my issue, but since all 13 stores had a sales amount for each of the 12 months, it isn’t the best example.
(BOB member since 2002-06-06)