Hi
I'm having an issue with conditional rendering. My client has a report with around 10 measures and 10 attributes. They want to be able to select which measures/attributes appear on the report and to have summarise the measures against the attributes they have chosen. I've set up a load of prompts and variables to use as render variables on the list columns which works OK but the measures are being summarised against all the attributes. Also all the attributes appear in the SQL even when they haven't been selected (but not on the report).
Any suggestions on how I can get the measures to be aggregated just against the attributes in the report?
Thanks for your help.
Not sure I understand where your problem lies but my approach would be:
- use conditional formatting on the report to hide/show each data item as appropriate
- make all 10 attribute data items calculations which are set to the corresponding query subject item when used, or some arbitrary constant when unused
The report would always group on all 10 attributes but by virtue of the constant values in the unused ones would have the apparent logic you want. I'm not sure of the implications for local vs. database processing (this might require local so could affect performance) or if there's anything you can do about all attributes in the SQL.
Thanks for that.
I was under the impression that thr difference between conditional rendering and just hiding a data item was that if you used conditional rendering and said 'no' to the prompt the item wouldn't be included in the report so the measures would roll up differently
For Example
if I have brand, product and sales if both items are rendered the sales would be affgregated for products within brands but if products wasn't rendered the sales would be aggregated to the brand level.
Is there anyway of doing that?
Aggregate
Way facts are summarized/maximaized etc within the SQL. Independant on what is used to display in the report.
Roll-up
Way a fact behaves against the non-facts displayed in the report. Exclude non-facts from the report that are included in the query and the facts may be rolled up further that the aggregate value..
I can only tell you my understanding of Reportnet, in which conditional formatting (the approach I was suggesting) is strictly cosmetic and will not affect the SQL generated. Conditional layouts, on the other hand, will affect what is sent to the database: queries associated with false-condition layouts do not run. I'm comfortable that my suggested approach will work for you in C8 but there may be additional functionality that provides a more efficient alternative. I think blom0344's response (and your impression regarding prompting) may imply that you're working with a DMR data source, and if that's the case I really need to shut up now...
Quoteif I have brand, product and sales if both items are rendered the sales would be affgregated for products within brands but if products wasn't rendered the sales would be aggregated to the brand level.
aggregation <> rollup
example:
| brand product sales |
| skol lager 400 |
| skol ale 250 |
| bavaria malt 1000 |
| bavaria lager 600 |
| grolsch ale 1200 |
| heineken lager 50 |
| skol ale 300 |
| bavaria malt 700 |
| grolsch malt 500 |
| skol lager 70 |
Now, that's about my consumption a year :)
A typical SQL would be:
SELECT BRAND,PRODUCT ,SUM(SALES) FROM
BLOM0344_CONSUMPTION
WHERE ............
GROUP BY BRAND, PRODUCT
and the result:
| skol lager 470 |
| skol ale 550 |
| bavaria malt 1700 |
| bavaria lager 600 |
| grolsch ale 1200 |
| heineken lager 50 |
| grolsch malt 500 |
This is due to the aggregate behavior of sales.
Now , without changing the query remove (not delete) product from the list:
Result:
| skol 1020 |
| bavaria 2300 |
| grolsch 1700 |
| heineken 50 |
if sales is allowed to roll-up against the remaining dimension (brand)
However, the last effect has nothing to do with SQL aggregation
bloom344: maybe you should rollup some Becks Bier once a while! :)
Quote
bloom344
What's this bloom business?
blom0344
You gotta love a guy who keeps that kind of example statistics handy...
Everybody...
Thanks for your help and making not feel like too much of an alcoholic.
;D
Just to finsh this off in case anyone comes across this issue. The requirement is to provide users with a list if attributes and measures they can select to display on the report. The selected measures should then be rolled up to the selected attributes (in my opinion this is over pandering to the users as they could easily get the same functionality just by using query studio).
To get this to work I had to have conditional rendering on all the possible columns in the report and then add a case statement to the attributes to say that rendering parameter = yes then show the column else null.
Like I said not really a valid requirement but I'm a contractor and will do what the client wants/pays me to (isn't there a word for people who do that?)
Anyway thanks for your help.