"Translating" a SQL GROUP BY HAVING into Report Studio

[JBischoff]

We are in the process of implementing Cognos BI 10; I'm still quite new to it.  I have a list report that was previously written purely as a SQL query. It had a subselect with a GROUP BY and a HAVING clause, as well as some user-entered WHERE parameters.

Simplified, it went like this:

SELECT field1, field2, field3 FROM mytable WHERE field1 IN (SELECT field1 FROM mytable WHERE (user-entered filter criteria apply) GROUP BY field1 HAVING MIN(field2) <> MAX(field2>) WHERE (same user-entered filter criteria apply)

The purpose was to pick up only records where field1 matched but field2 did not.  The user-entered filter criteria are pretty standard (records between start date and end date, username equals, etc), and I have created applicable filters in Framework Manager.

I'm having a hard time otherwise figuring out how to even start with this subquery, however, particularly since it requires these same user-entered params.  Should I attempt to create some sort of filter in FW Manager?  This particular requirement applies only to this report, so I'd prefer to do the work in Report Studio if possible.

I tried creating a new query encompassing the subselect in Report Studio and joining it to my primary data query, but it seems terribly clunky in terms of the number of times I have to add fields, and I would seem to have to recreate all my package source filters.

I'm having difficulty even figuring out where to start with this one.  Any advice would be much appreciated.

[the6campbells]

there are two classes of filters - detail (if it helps think where-clause) and summary (think having). the engine actually allows aggregate expressions in a detail filter such as when detail rows are removed based on an an expression which computes an aggregate. queries by default normally have one or more columns they are grouping by in the layout which defines the 'grouping' of the query.

[JBischoff]

Ah!  Thank you, that makes sense, and got me significantly further than I was before.

I tried a couple of approaches today:  in the first, I created a separate query2 in Report Studio based on a custom SQL statement.  (This is most of my original subselect, with the exception of my user-entered WHERE clauses.)  I then defined a detail filter in my main query with the syntax "myfield IN (query2.myfield)".  It appeared to run without error.

Where I got stumped on that approach was trying to include the user-entered filter fields.  They need to be applied to query2 before the GROUP BY, so that I'm only including rows that meet the user-entered criteria.  But the custom SQL query2 is based on doesn't parse if I include ?parameters? in a WHERE clause.  I'm only including a single field (the field I want to match and filter my main query on) in the GROUP BY, so I can't see how to add appropriate detail filters to query2 after the fact, and I'm not positive it would return the correct results even if I could figure out how.

So, I backed up and tried another approach.  Now I have query2 selecting from my package datasource items.  The detail filters are easy to add from my package filters.  I'm still using query2 as a detail row filter for my main query.  Now I'm stumped on exactly how to add the GROUP BY/HAVING part. 

I'm not entirely clear whether I want to have an aggregate expression in the detail filter, and have not yet succeeded in the syntax is to make that work.  If it should be a summary filter, I can do the "having" equivalent but can't figure out how to tell it to group on my chosen field.

Thank you again for your input!

[ykud]

"Where I got stumped on that approach was trying to include the user-entered filter fields.  They need to be applied to query2 before the GROUP BY, so that I'm only including rows that meet the user-entered criteria.  But the custom SQL query2 is based on doesn't parse if I include ?parameters? in a WHERE clause.  I'm only including a single field (the field I want to match and filter my main query on) in the GROUP BY, so I can't see how to add appropriate detail filters to query2 after the fact, and I'm not positive it would return the correct results even if I could figure out how."

You should write #prompt('prompt_name',string,default_value)# in your custom sql instead of ?prompt_name? and then just hook the appropriate prompt variables to prompt_names.

[JBischoff]

Thank you for the assist with the custom SQL and prompts!  I'm filing that one away for future reference.

I did finally manage to convince the Report Studio GUI to generate my desired SQL.  For posterity, here are the steps I took:

Created a query, set Auto Group & Summarize to Yes.  Added data items (from the data source) field1, field2, field2, field3, field3.  I set the properties for data item field1 to None for the Aggregate and Rollup Aggregate Functions.  (This was my GROUP BY field).

I renamed the first field2 to Min Field 2, and set the Aggregate and Rollup Aggregate Functions to Minimum.  Renamed the second field2 to Max Field 2, set aggregate functions to Maximum.  Same for field3.

I added my user prompt filters to the detail filter section, with the Application property set to Before Auto Aggregation.
I added another filter to the detail filter section, with the definition [Min Field 2] (from the current query) <> [Max Field2] OR [Min Field 3] <> [Max Field 3].  I set the Application property for this filter to After Auto Aggregation.  This got me the HAVING clause.

Finally, in my main report query, I added a detail filter with the definition [source].[field1] in [query2].[field1].  (Plus my other user prompt filters, again.)

Whew!  It looks simple now that it's working, but it was a nightmare of non-helpful error messages to get there.  Thank you so much for the assistance along the way!