HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Help with Projected Plan Amounts

Project Plan Tables

I have an office yealy plan table, that is mostly static. Will I need to make this table a fact table and break this yearly plan number down by day using the time dimension to use it to calculate against all the facts in my fact table?

I have a fact table with all my daily submitted $ amounts per day, per office. I have a static "yearly office plan" table with my $$ per office per year. The thing is this "yearly office plan amount" could change based on business or sales and marketing decisions. I use this plan amount along with my fact table to base many mdx calculations against actuals vs plan, per day, per month etc.

FactPolicyTable hasolicyNumber(FK), DistributionChannelCode(FK), OfficeCode(FK), TimeKey(FK) and Face_amt, current_day_amt, lost_amt, prior_submitted_amt, pending_amt

DimOfficeTable has OfficeCode(key) and additional office information

PlanOffice table has keys of Plan_Office_Code(FK from OfficeCode), Plan_Office_Year, Plan Office Annual_Amt

The PlanOffice Annual Amt is what I need help with, how could I best implement this? all indications are I would have to break it down by day and move it into the FactPolicyTable or make it a fact table with a FK TimeKey in it. By the way this amount does not really relate to the PolicyNumber key in the fact table either.

I hope this gives you more to work with to help with my issue.

Or

Can I make this yearly plan amount a KPI? would the KPI still need to be broken down by day? can I use the KPI in to do calculations against other values in FactPolicy table?

What would be the best way to implement this Plan Value?

I hope I explained enough for this to make sense?

Well, the PlanOffice table could be a fact table for another measure group, which relates to the date dimension at a different granularity (ie. year) than does the FactPolicyTable (day). For example, in Adventure Works, the Sales Targets measure group relates to the Date dimension at the Calendar Quarter granularity, but [Measures].[Sales Amount Quota] gets allocated down to the month level in the cube MDX script.

Help with Projected Plan Amounts

Project Plan Tables

I have an office yealy plan table, that is mostly static. Will I need to make this table a fact table and break this yearly plan number down by day using the time dimension to use it to calculate against all the facts in my fact table?

I have a fact table with all my daily submitted $ amounts per day, per office. I have a static "yearly office plan" table with my $$ per office per year. The thing is this "yearly office plan amount" could change based on business or sales and marketing decisions. I use this plan amount along with my fact table to base many mdx calculations against actuals vs plan, per day, per month etc.

FactPolicyTable hasolicyNumber(FK), DistributionChannelCode(FK), OfficeCode(FK), TimeKey(FK) and Face_amt, current_day_amt, lost_amt, prior_submitted_amt, pending_amt

DimOfficeTable has OfficeCode(key) and additional office information

PlanOffice table has keys of Plan_Office_Code(FK from OfficeCode), Plan_Office_Year, Plan Office Annual_Amt

The PlanOffice Annual Amt is what I need help with, how could I best implement this? all indications are I would have to break it down by day and move it into the FactPolicyTable or make it a fact table with a FK TimeKey in it. By the way this amount does not really relate to the PolicyNumber key in the fact table either.

I hope this gives you more to work with to help with my issue.

Or

Can I make this yearly plan amount a KPI? would the KPI still need to be broken down by day? can I use the KPI in to do calculations against other values in FactPolicy table?

What would be the best way to implement this Plan Value?

I hope I explained enough for this to make sense?

Well, the PlanOffice table could be a fact table for another measure group, which relates to the date dimension at a different granularity (ie. year) than does the FactPolicyTable (day). For example, in Adventure Works, the Sales Targets measure group relates to the Date dimension at the Calendar Quarter granularity, but [Measures].[Sales Amount Quota] gets allocated down to the month level in the cube MDX script.