[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregat

I put this into a textbox, why isn't this possible?

=SUM(ReportItems!GrossGoal1.Value)

I need to do this!!!!

[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox6' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsMissingAggregateScope] The Value expression for the textbox ‘textbox6’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.

Build complete -- 2 errors, 0 warnings

I also need to be able to accomplish the same thing. Is there any way to get this done? I have been searching for an answer for a while now, and I can't seem to find anything.

Thanks

|||

Anyone find a solution to this problem?

thanks..

dd

|||There isn't one, Microsoft said they are working on a fix for it in future editions of SSRS, rather an enhancement. We'll see if they ever get to it.|||

Is there another way to get real time row totals in excel without ReportItems? So, if the user changed a value in an excel cell it updates to the total in the spreadsheet (not in the database of course). I got it working for column totals but it's not working for rows. Partly because of the aggregation issue; but, also due to the fact this would have to be more dynamic than the columns one. Anyways, here is an example of what I am talking about:

t < got this working

1 1 1 | 3

1 2 3 | 5

2 3 4 | 8

^^isnt working

[newbie] SQL-query with SUM() doesnt work. :(

Hi NG,

I have two tables:

TABLE1 (id1 INTEGER NOT NULL, id2 INTEGER NOT NULL, value INTEGER, PRIMARY KEY (id1, id2))

TABLE2 (id1 INTEGER NOT NULL, text CHAR(4), PRIMARY KEY (id1))

And this is the query, that I'm trying to run on this tables:

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

Well - it doesn't work at all! :(
That is the reason?!

Appreciate everyone's help!!!

S.B.well when doing aggregates you must have everything in the select statement that is not being summed in the group by clause.

Example:

select t1.id1, t2.text, sum(t1.value)
from table1 t1, table2 t2
where t1.id1 = t2.id1
group by t1.id1, t2.text
order by t1.id1 asc|||Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com|||Thanks, mkkmg and alligatorsql.com !!! :)

"group by TABLE1.id1, TABLE2.text" works perfectly!

S.B.

Originally posted by alligatorsql.com
Hello,

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1
order by TABLE1.id1 asc

DROP the TABLE2.text field cause this is not a group field or
use

select TABLE1.id1, TABLE2.text, sum(TABLE.value)
from table1, table2
where TABLE1.id1=TABLE2.id1
group by TABLE1.id1, TABLE2.text
order by TABLE1.id1 asc

Hope that helps ?

Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

[Cummulative] does not work

-- Take all year but current year

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[Year],

Ancestor([Time].[FiscalYear].CurrentMember, [Time].[FiscalYear].Year).PrevMember

)

, [Measures].[PTD Actual])

+

-- Take current YTD

SUM(Ytd([Time].[FiscalYear].CurrentMember), [Measures].[PTD Actual])

-

Above MDX is trying get a Cummulative number up to current time period. The time.FiscalYear was defined with Y, Q and M levels. I tried with an (All) level and w/o (All) level. But PeriodsToDate just does not go beyond pervious year to get all the Periods (here all the periods are all the years up to last year). So when I look at Y2007 cummulative number, I only see it adds up all the number in Y2006 and whatever periods up current in Y2007. Anything before 2006 was not included.

Is there a correct way to do cummulative actual (add up all the previous years plus all the period up to CurrentMember in current year)?

Try this:

SUM({NULL:[Time].[FiscalYear].CurrentMember}, [Measures].[PTD Actual])

|||

Hi, Deepak:

Could you explain a bit what the purpose of NULL in the function?

Thanks!

Julius

|||

Sorry, Deepak.

I am on AS 2000. It gives me syntax error on NULL.

Could you help?

Thanks again!

Julius

|||

The "NULL:" won't work in AS 2000, so (assuming that there is an "All" level) you could try:

Sum

(

PeriodsToDate

(

[Time].[FiscalYear].[All]

)

, [Measures].[PTD Actual])

|||

Deepak:

Interesting that AS2000 took this MDX.

But it seems the set is not the calc member is meant to be. By using [All] w/o defining a CurrentMember, it seems return the ENTIRE time dimension which includes the future period.

If we have 2003, 2004, 2005, 2006, 2007, 2008 as Yearl level members, how do I ask MDX to give the "Cumulative up to date" figure up to 2006 (including 2003, 2004, 2005, 2006) w/o anything from 2007 and 2008? I think (All) will just return entire dimension member set, right? Or in other words, how do I tell MDX that I only want to PeriodsToDate up to my current Year level member (or Previous Year level member, so I can add YTD accurately for current year) when using (All)?

I am confused.

Thanks much!

Julius

|||Depends on how you define your current date - if it's based on the latest date with data in the cube, then previous year (2006) could be computed like Tail(NonEmptyCrossJoin([Time].[Year].Members)).Item(0).PrevMember. Without explicitly selecting a CurrentMember, the DefaultMember will be used instead. So you could set the Time dimension's DefaultMember to the above expression, so that the year 2006 is selected by default.