Repeat Page - how to use one page to produce many

[wcipolli]

I have a report that prints groups, services their performance and goals. The report takes in a parameter group and filters it such that only services that fall in that group show. How can I run the report and have it produce a report for each group that does not meet goal?

 

[blom0344]

Can you add an example? It is pretty hard to come up with an image of what you are trying to achieve..

Ties Blom

 

[wcipolli]

Group | service | Goal | Actual

1 phone 60sec 79sec
:
:

2 phone 65sec 70sec
:
:

3 phone 55sec 45sec
:
:

It's a table like this, now i have a filter parameter that asks what group you want. I'm trying to make it so I can run this report and print each group only if one of the services did not meet goal. I was told page sets would be helpful, but I haven't found any decent resources/tutorials.

 

[blom0344]

Page sets are usefull for dividing data over seperate pages, so you can define a pagebreak with a new value for service.
You can also hide/show complete pages based on a boolean expression.

It is another matter to extend control over the entire report and show all data for a given group if one services does not meet the goal.

I would create a logical expression like:

SELECT GROUP,
SUM(CASE WHEN GOAL <= ACTUAL THEN 1 ELSE 0 END) as INDICATOR
GROUP BY GROUP

and use this in an additional Query subject to control report behavior.
When Indicator value is 0 for a group then all GOALS are higher then ACTUALS, otherwise at least one GOAL scores less.



Ties Blom

 

[wcipolli]

So, I'm only a month or so into using cognos (Report Studio). So I'm not sure exactly what you're saying to do. I tried creating a data item like this and it shot a bunch of errors back. Now that I think of it, would I want to create that under Render variable?

Another thing I didnt completely make clear is that I would like the groups to print on different pages, which may be where the page set idea comes in.

 

[blom0344]

The code was meant to be added to the model, which may be inaccesible to you as a report writer.

It is perfectly well possible to use a page set to start each group on a new page.

The problem lies with your logic.

If I understand correct you only want to show pages (and grouped data) when at least one of the services within the group does not meet the goal.

I think this is doable by performing a sum against:

(CASE WHEN GOAL <= ACTUAL THEN 1 ELSE 0 END)

As dataitem expression this would be:

total((CASE WHEN [GOAL] <= [ACTUAL] THEN 1 ELSE 0 END) for [group])

you can then use this dataitem in a filter expression to limit the dataset to only those groups you want to show.
Make sure you set application of the filter to:
'After Auto Aggregation'

Ties Blom

 

[wcipolli]

So I've been playing with it for awhile. Thanks for all of your help.

Group | service | Goal | ActualApr ActualMay ActualJun

1 phone 60sec 79sec 50sec 61sec
:
:

2 phone 65sec 70sec 80sec 45sec
:
:

3 phone 55sec 45sec 64sec 50sec
:
:

My report is a crosstab, I have got your solution working to only show a group when it has a red in any month in any service. I would now like to set it up so that it only shows it when there's a red from last month. (in this case june - the previous won't matter)

I tried this and it's not quite doing the job. Am I going down the right path?


total((CASE WHEN ([As of Date] between _add_days (_add_months (current_date,-2), _days_to_end_of_month(_add_months (current_date,-2)+1)) and _add_days (_add_months (current_date,-1), _days_to_end_of_month (_add_months (current_date,-1)))) and [Goal] >= [ACTUAL] THEN 0 ELSE 1 END) for [Group])


One more step and I think I'll have it. Any additional help, like the previous, would be greatly appreciated.

 

[blom0344]

Did you try and check how the tabular data looks like with the new expression? In most cases this is the easier way to debug and see if the logic your are applying works..

Ties Blom

 

[wcipolli]

Yes, it provided the same effect, as if I never added the other requirement. When I added the value to the cross tab it returns the number of fails for the 3 month period. Is there a way to say for the previous month? I don't see why what I have wouldn't work. It seems logically correct at least.

 

[wcipolli]

I ended up getting it. I had swapped it to look at the green case and everything else was red. I switched it to look at the red and everything else was green and it worked just fine! Thanks for your help!