Hello,
Within a Report Studio nested crosstab against a PowerCube, how would
you suppress a summary row when there is only one detail row for the
level? In this case, the summary contains the exact same values as the
detail rows, making the summary unnecessary.
Example:
Country A | Fax order method |1
Country A Total 1
Country B | Fax 2
| Email 3
Country B Total 5
In the example above, I want to suppress the Country A total line.
I have attempted a solution by adding a rowcount and trying to use
conditional formatting when the rowcount at the Summary level =1, but
in a crosstab there does not appear to be a way to add the rowcount
data item to the properties of the Query, when the rowcount item does
not appear in the layout. I know this is possible in a list report,
but best practices say to use crosstabs against OLAP sources.
Thanks in advance.
I'm assuming you have two data items nested in the country node, Order Method and Total.
Instead of using normal suppression, you'll have to do this in the data item for the total. Try this:
head(member(aggregate(currentMeasure within set [Order Method])),
if(count(1 within set [Order Method])= 1) then (0) else (1)
)
This will count the number of members in Order Method, in the context of country. If there's 1, it will return the total. If not, it will return an empty set.
CognosPaul,
Thanks for your reply. I should have been a bit more specific, in that I have the standard QTD-Grouped members as my columns. What I am then attempting to do is to use conditional formatting to suppress the summary row when there is only one detail line (i.e. one order-method). I can determine the row count using your example, but when I try to use conditional formatting, I receive:
The following expression is not valid: [Query1].[ROW_COUNT]=0. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'. The variable named '[Query1].[ROW_COUNT]' is invalid.
I am trying to set the summary row to "not visible" and box-type = "none" using condition "ROW_COUNT=0", based upon the data item . But there does not appear to be a way to add the ROW_COUNT data item into the query using the "properties" of the query, like you are able within a list report.
I have attached a PDF example. In the PDF, the only summary rows I would keep would be for "TrailChef Double Flame" and "TrailChef Kettle".
Perhaps there is another way, other than conditional formatting?
Don't use conditional formatting for this. Even if you got it working, you'd start running into layout problems.
The method I wrote handles the suppression as well. Use that instead of the normal summary node. Simply replace "Order method" with QTD-grouped.
Each individual node in the crosstab has a properties option.