Conditional Rendering

[wyconian]

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.

[almeids]

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.

[wyconian]

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?

[blom0344]

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..

[almeids]

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...

[blom0344]

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.

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:

Code Select Expand


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

[raro]

bloom344: maybe you should rollup some Becks Bier once a while!  :)

[blom0344]

bloom344

What's this bloom business?

blom0344

[almeids]

You gotta love a guy who keeps that kind of example statistics handy...

[wyconian]

Everybody...

Thanks for your help and making not feel like too much of an alcoholic.

;D

[wyconian]

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.