Ad-hoc reporting, set analysis

[aisolomatin]

Hi, there!

Need your help with issue: I need to make expression, that will calc a Sum(Value) for max DateReport.

But if DateReport has added to table as Dimension, then I need to get Sum(Value) for current DateReport.

Here example. I try to use expression

=If(Dimensionality()=0,

  Sum({<DateReport={"$(=Max(Date(DateReport)))"}>}Value),

  Sum(Value)).

But problem is that it works fine for first table, but doesn't work for second.

Second table should look like this:

I need to use it in Ad-hoc reporting, so I have one table with all Dimensions (users choose Dimensions for table by themselves), and I need one expression.

[Anil_Babu_Samineni]

Is it, Can you attach updated QVW, Please

[aisolomatin]

It will be Ad-hoc reporting tool: 1 table. All users will be create they own reports (they can add to Dimensions all necessary fields: Branch, Subbranch, DateReport and other fields). Do you get it?

[aisolomatin]

Check it out.

[vishsaggi]

Ok so why do you even need that column then? Sum(Aggr(Sum()) I mean, you are already getting the values for each datereport using Sum(value) right? Correct me, sorry quite not getting what exactly your are looking for, can you explain a little more with some expected output you want to see.

[aisolomatin]

Yes, in first table expression works good. But, if user decide remove Dimension [DateReport] from table, values become incorrect, like in my example (2nd table).

In final solution (output) user will be decide which Dimensions should be in table.

[Anil_Babu_Samineni]

I am not sure, Whether which values you are expecting while Zero

Like this, May be?

Or this?

[vishsaggi]

Check this?

[aisolomatin]

Yes, work. But you use different expressions in two tables.

Is it possible to make one expression for 2 tables? I mean Expressions with same sintax.

Because in my output solution (Ad-hoc) i will get only one table, and user will be decide which Dimensions should be include to it.

[aisolomatin]

I need one UNIVERSAL expression, which will give me result in first table like this : Sum(Value) for each row and Sum(Value) with DateReport=Max(DateReport) for Total:

and 2nd table like this: Sum(Value) with DateReport=Max(DateReport).

But it shoul be 1 Expression (same syntax).

[vishsaggi]

This ?