Separate page for each selected value

[Eva]

I have created a report which looks like the below:
(attached ReportLook.jpg)

I have a prompt, where the single country and single shop, the 1st and 2nd Date range is selected.
The report has query1 with filter for Date1, query2 with filter for Date2, and the difference is calculated with Layout Calculation, at 4th (splitted) column.
The report page itself has the query1 assigned, then for query2 I use a Singleton, which has the table in it with the data. For the difference its also a Singleton used.
--- this is how I found I can make the page look like a spreadsheet. If there is an easier way to achieve this, I am interested, but this is not my question now.  ---

The report works, and creates what is the expectation.

But, now, the requirement is to have the same report, but at prompt page it should allow to select multiple shops and a report page should  be created for each selected shop separately, as in the setup before.
I found that Page Set could be used for this, and it works for query1, as I can do the Master Detail relationship linking. But since query2 is used on the page with Singleton, this does not work with Page Set. Is there any other way to achieve the expected results?

[tjohnson3050]

Instead of using a singleton, try using a single query with the following query items:

Country,Shop,Date,AVG,MAX,Data item

Filter -> date in (?Date1?,?Date2?)

Build the crosstab, then create a calculation for the difference using running difference:

https://www.ibm.com/support/knowledgecenter/SSRL5J_1.0.1/com.ibm.swg.ba.cognos.ug_fm.10.1.1.doc/c_ces_runningdifference.html

Then a page set on Shop should do what you want.

[Eva]

Thank you tjohnson3050 for looking into this.

I started to try out what you suggested, but at filter you wrote "in" and I should use "in_range" so I have a date range. Unfortunately, I cannot use in_range in this case as I get parser error. Is there another way to get date range?

[tjohnson3050]

I misunderstood your example.  It looked like you had one column to show data on a specific date, and another column for data on a second date, then a third column that showed a comparison (difference) between the values on two specific days.  In_range isn't appropriate for that query, using in would filter the data for just those two days. 

If you really want a range of dates, I don't understand your report requirement.

[Eva]

I think your understanding was correct except that I need the data not only for a specific day, but within a date range. I did not emphasize that well enough.

I would like to have one column to show average data value within a date range, one column to show the maximum data value within the same date range. Then have the same for a second date range. Finally show the difference between these two date ranges.

Thank you again for looking into this.

[tjohnson3050]

Ahh, that makes sense.  I see now that you originally said date range:)

So instead of filtering the query, you could use case statements for the max and average for Date 1 and Date 2.  For example, assume 'measure' is what you want to aggregate (min,avg):

Max:
maximum(case when [date] in_range ?Date1? then measure else null end)

Avg:
total(case when [date] in_range ?Date1? then measure else null end)/
total(case when [date] in_range ?Date1? then 1 else 0 end)

[Eva]

Thank you so much, with this I can build the report page with only one query and that can be used for the Page Set.