Same report query, multiple outputs by filters

[gpollock]

All,

I have a report built on a single query and a single page, and I want to find the best way to run multiple pages of that report where they only difference is a filter.  This is a list report built on a relational package in 10.2.

For this report, the user wants to see data in the following formats.  Note that all other filters are the same, the column layout is the same...the only thing that changes is a single filter:
Report A page 1 is the list filtered for the prior date (we have a flag in the date dimension).
Report A page 2 is the same list filtered for week-to-date (also have a flag).
Report A page 3 ...filtered for month-to-date.
Report A page 4 ...filtered for quarter-to-date.
Report A page 5 ...filtered for year-to-date.
..and Report B page 1 is the same, but on a custom date range.

Now a brute force way of building this would be to create the report, and keep making copies so I have two reports--Report A has 5 pages and 5 queries; while report B has 1 page and 1 query.  The problem with this comes from a maintenance standpoint.  When there's a modification request, we have to do the same change 6 times.

What I'm looking for is a way to have one report page and one query, and based on a prompt, either report A or B will output.  I've considered using page sets, but I'm not breaking a new page based on a new data value, so I don't see a way of doing it.  I'll also add that Report B will be run on-demand by a fluent user, and Report A will be automatically run and emailed to users; and let's just say I'd prefer to send a single five-page report than five one-page reports.

[dax]

Hi

I think the page sets could work.  I set up a similar report to what you mentioned but then added a "date bucket" data item,which for my example was defined as:


if (day([Journal Date])>0 and day([Journal Date])<8) then
('Week 1')
else
(
if (day([Journal Date])>8 and day([Journal Date])<15) then
('Week 2')
else
('Week 3')
)


I then added the data item to the list, highlighted the data item and chose Structure, Set Page break.  When I ran it I then got one page per date bucket.  You  can remove the data item from the list once it's working.

Is this any help?

Dax

[gpollock]

I like the way you think.  My problem is that these dates are not mutually-exclusive.  Yesterday is in week-to-date; week-to-date is in month-to-date, etc.

I wonder if I can have a "date level" field where yesterday is 1, wtd is 2, etc.  Then I could have a master-detail query with the date types, and join to the list on a "<=" join (If master type is week-to-date, then it pulls date levels 2 and 1).  I'll try it and report back.

My only concern with that is how to implement the date range.  I'll do some tinkering.

[gpollock]

Alright, I have a resolution, but it's not the one I wanted.

I didn't use a page set because the custom range threw out any date bucket system.  For prior date..YTD, each bucket was in the next one; so I could use a rank system to get the buckets, but then the custom range could be outside or overlap the other buckets.  To get that to work, I would need to use union queries to get all the dates and ranks, and an external data source to populate the bucket names what which ranks go in the buckets.  In short, it was more complicated than what I was trying to avoid.

I was going to use a query reference setup, so I would have 6 pages but only one root query.  When testing this, I found that the filters on the derived queries use having instead of where.  This was a major performance killer.

I ended up with what I wanted to avoid--6 pages and 6 queries.  I'm using a parameter and render variable to determine which pages to show.  It's not what I really wanted, but at least it's a single report.

Thanks for your help.

[cognostechie]

Seems like you initially ran into performance issues by having 6 queries so you tried to have one query instead. Since this seems to be an analytical report, have you though of making a cube for this? I have seen people making this kind of report from a relational package and have always wondered why don't they use cubes !

[gpollock]

I'm not as experienced with cube reporting, so please enlighten me with how this could be accomplished with a cube.  The original requirement was to have one page and one query, but run it on up to 6 different times with a different filter each time, without needing to call the report again.

Also, to give some background, there is no performance reason for this--only a maintenance reason.  My organization has a very large number of reports that use the same basic query, but with a different hard-coded filter.  It's common to see copies of the same report, but one for week-to-date and another with year-to-date; and also to see one hard-coded for organization A and another for organization B.  If you saw it, it would make you cry.

Ideally, I would have liked to see something like a page set, but instead of a data item change causing a new page, you could give a list of filters.  As before, I tried query references, but found the different filters triggered a HAVING clause instead of a WHERE clause, which was a performance killer.  As a small victory, I can consolidate this into a single report, and the user chooses which pages to run.  This report is simple enough that I can live with it, but I really hope I can find something for our other reports that are more complicated.

Thanks for the tips so far!

[Lynn]

What if you .....

1) Create the report with one query and one page and a prompt for the date type.
2) Create 5 report views that pre-set the date type to the various flavors of date ranges.
3) Use a report booklet to put Humpty Dumpty back together again into a single report with all five pages.

https://www-304.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/t_rpt_studio_create_rpt_booklet.html

[BigChris]

Or just prompt the user for the date range that they need...

http://cognostips.weebly.com/blog/date-prompts

[gpollock]

Thanks, Lynn.  I'm going to look at report booklets.  So long as it sends to the user as a single report, I think they'll be happy.  I'll try it and report back.

[cognostechie]

I'm not as experienced with cube reporting, so please enlighten me with how this could be accomplished with a cube.  The original requirement was to have one page and one query, but run it on up to 6 different times with a different filter each time, without needing to call the report again.

Also, to give some background, there is no performance reason for this--only a maintenance reason.  My organization has a very large number of reports that use the same basic query, but with a different hard-coded filter.  It's common to see copies of the same report, but one for week-to-date and another with year-to-date; and also to see one hard-coded for organization A and another for organization B.  If you saw it, it would make you cry.

Ideally, I would have liked to see something like a page set, but instead of a data item change causing a new page, you could give a list of filters.  As before, I tried query references, but found the different filters triggered a HAVING clause instead of a WHERE clause, which was a performance killer.  As a small victory, I can consolidate this into a single report, and the user chooses which pages to run.  This report is simple enough that I can live with it, but I really hope I can find something for our other reports that are more complicated.

Thanks for the tips so far!

I am happy to see that you have the 'will' to do things right. That's what I see lacking in lot of developers and the multiple copies of the same report you mentioned in your environment is no surprise for me. I once worked for a big retailer in Texas who had the same scenario. Thousands of report mainly because they were copies of the original. One report actually had more than 200 copies, the only difference being a filter for the store. Different packages too causing different versions of the truth.

My ideal way of making this kind of report would be to put the queries for WTD, MTD, YTD and the Date Range all on the same page and make it look like a Dashboard so that the users can see the entire picture on the same screen. Visualize having all this on the same page along with a prompt to select date Range to make the query for date Range work too. You could put each one of them vertically too . Having a scroll bar is ok because they still get to see all together.
Having a prompt to select which one they want just makes them run the same report 5/6 times.

To answer your question about what you can do with a Cube - First of all, the cube has pre-built categories for Relative time like WTD, MTD etc. which can be used against any item present in the cube whereas with a relational package, you will have to create calculated columns in FM for each measure for each time range. Secondly, you can use MDX against a cube so if you decide to let the user select whether they want WTD or MTD then you can make one query and using MDX, you can change the Rows and columns to show either WTD or MTD. Since it will be only one query, Render variables and blocks are not required. You can also change measures using MDX.   

[kdorian]

Please forgive the necro-posting, but I came across this yesterday when I was trying to figure out how to do the same thing. I came up with a simple fix for my situation that might work for others, and this is the most recent post I could locate, so I thought I'd describe my solution for others with the same problem.

My query was pulling information from our payroll database and making calculations. I needed two different versions of the final results, one on a page with all the records and the second on a page with only certain records.

I created a 'dummy' query that pulled all the employee IDs from the database, then ran an outer join with the first query (the one that showed all the results). I then filtered that query. I used the second query to create the second page I needed.

I hope this is helpful to someone.