Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

Conditionally format subtotal for matrix report

I have a matrix that will pull out the current quarters sales figures. I want to change the color of the subtotal font ONLY when we are in the current period. I have a boolean field in the matrix report that is true when it is the current month.

For example, at the end of last month it displays January, February and March figures. I want the sub total to display the totals for January and February in white, whilst the totals for March are Yellow.

Any ideas anyone?

You can use expression for the subtotal style. Just check the value of your bool field in the expression, and return the corresponding color.|||

Unfortunately, that doesn't work.

I am using a condition in the color field of the subtotal properties checking the boolean value - and it ALWAYS thinks that the value is true, whether it is or not. Problem is that I have a mixture in my matrix - the previous 2 months hold false, the current month true and I am displaying all 3 months.

I am using the following in the color field in the properties of the subtotal:

=IIf(Fields!CurrentPeriod.Value, "Yellow", "White")

And it is showing yellow for all months.

Any pointers greatfully received

|||Hi there,

Did you find an answer to this? I am stuck with the same problem.

-Thanks a lot|||

Hi Ragas, Just try like this add your expression in differen place.Click in textbox and go to properties window,go to back ground color in that drop down list add your expression.

|||

Hi folks,

I have tried various methods for conditional formating in subtotal. Everything is working in development environment, but when i deploy and see the report in the http://ReportServer/reports, it is not being implemented. But when i print from the report from report server, i can see the conditional format !!!!

Can anyone give solution for this?

Regards,

karthik

|||

Hi there,

This worked for me:

=iif(inscope("Rating_Group") and inscope("RowRating_Group"),"white","Gainsboro")

Use in any of the attributes (Font, Color, Borderstyl) of the Data cell, in other words the cells that will contain the values of the matrix.

"Rating_Group" and "RowRating_Group" simply represent the Group which the subtotal belongs to. I had two subtotals on my report that's why I used both.

This can be a column group or a row group.

You should be able to format the subtotals to your heart's content with this beaut.

sqlsql

Conditional suppression formula PLEASE HELP

I have Crystal reports 8.5 and Im trying to generate a report that will give all of my sales orders with the part numbers price and the address they shipped to but I keep getting alot of duplicate fields so I used a suppresion forumla below

{InvoiceDetail.PartNumber} = previous({InvoiceDetail.PartNumber})

That did a great job of eliminated the duplicates but I found out that I have some orders that are in succesion with identical part numbers and they were being suppressed so I tried this formula

{InvoiceDetail.PartNumber} and {InvoiceHeader.SONumber} = previous({InvoiceDetail.PartNumber}) and {InvoiceHeader.SONumber}

thinking that will suppress any field that has the same part number and so number but it errors our and tells me I need a boolean.

I've tried just about everything and Im sure im missing something very simple but I dont know what it is.

PLEASE HELP{InvoiceDetail.PartNumber} and {InvoiceHeader.SONumber} = previous({InvoiceDetail.PartNumber}) and {InvoiceHeader.SONumber}

The very first thing is try to find out why you are getting duplicate data. Something must be wrong with you linking.

To suppress try this:

{InvoiceDetail.PartNumber} = previous({InvoiceDetail.PartNumber}) AND {InvoiceHeader.SONumber} = previous({InvoiceHeader.SONumber})

Conditional sum in reporting services

I need to find a conditional sum of sales if fields!Example.value = 0
(And same if fields!Example.value = 1)
How do I do that in reporting services?
Thanks
CS=Sum(IIf(Fields!Example.value=0,0,Fields!Example.Value))
"chetanasamal@.gmail.com" wrote:
> I need to find a conditional sum of sales if fields!Example.value = 0
> (And same if fields!Example.value = 1)
> How do I do that in reporting services?
> Thanks
> CS
>

Conditional join?

I have a dropdown box in a .net app that I am populating from a couple
tables.
One is a salesrep table, with the sales rep code and a name in it.
The other table is a table with a sold to sales rep code and a ship to
sales rep code - which may not always be the same in both these columns.
One of those two columns will be in the salesrep table.
Is there a way to do a join on the first table from the second?
Basically, I want to pick out a statement like:
select repname from salesrep_table
inner join sourcetable on
sourcetable.soldtorepcode = salesrep_table.repcode or
sourcetable.shiptorepcode = salesrep_table.repcode
In other words, I want to show this repname in a box when the repcode
that is selected is in either the soldto repcode column, or the shipto
repcode table.
Wiil this work? If not, how would you go about doing this?
Any help would be appreciated.
BC>> I have a dropdown box in a .net app that I am populating from a couple ta
bles. <<
This is a database group and we do not care about the front end. The
basic principle of a tiered architecture is that display and input are
done in the front end and never in the back end. This a more basic
programming principle than just SQL and RDBMS.
This is a major screw up. There is no LOGICAL difference in these
guys. A sales rep is a sales rep; if you want to flag with a role,
then that goes into a column, not a separate table.
Look up "attribute splitting" as a design error.|||Hi Blasting Cap,
This help?
select repname
from salesrep_table as st
inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
t.shiptorepcode )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:OiYTzAWmGHA.1568@.TK2MSFTNGP05.phx.gbl...
>I have a dropdown box in a .net app that I am populating from a couple
>tables.
> One is a salesrep table, with the sales rep code and a name in it.
> The other table is a table with a sold to sales rep code and a ship to
> sales rep code - which may not always be the same in both these columns.
> One of those two columns will be in the salesrep table.
> Is there a way to do a join on the first table from the second?
> Basically, I want to pick out a statement like:
> select repname from salesrep_table
> inner join sourcetable on
> sourcetable.soldtorepcode = salesrep_table.repcode or
> sourcetable.shiptorepcode = salesrep_table.repcode
> In other words, I want to show this repname in a box when the repcode that
> is selected is in either the soldto repcode column, or the shipto repcode
> table.
> Wiil this work? If not, how would you go about doing this?
> Any help would be appreciated.
> BC|||> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
Check the group title AGAIN -> MICROSOFT.SQLSERVER.PROGRAMMING
SQL Server is not just a database rather RDBMS - its a data processing
engine with many different features other than just SQL and store/retrieve.
If you don't like the posts then keep your gob shut and don't answer them!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151366508.537061.16560@.m73g2000cwd.googlegroups.com...
> This is a database group and we do not care about the front end. The
> basic principle of a tiered architecture is that display and input are
> done in the front end and never in the back end. This a more basic
> programming principle than just SQL and RDBMS.
>
> This is a major screw up. There is no LOGICAL difference in these
> guys. A sales rep is a sales rep; if you want to flag with a role,
> then that goes into a column, not a separate table.
> Look up "attribute splitting" as a design error.
>|||On 26 Jun 2006 17:01:48 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

>This is a database group and we do not care about the front end. The
>basic principle of a tiered architecture is that display and input are
>done in the front end and never in the back end. This a more basic
>programming principle than just SQL and RDBMS.
The questions was not about how to program the front end, it was
simply about database retrieval. Mentioning the front end when
describing the query requirement and asking for help with the query is
not a problem.

>This is a major screw up. There is no LOGICAL difference in these
>guys. A sales rep is a sales rep; if you want to flag with a role,
>then that goes into a column, not a separate table.
>Look up "attribute splitting" as a design error.
Nonsense. This exact configuration occured where I worked, and was an
important BUSINESS REQUIREMENT. Surely you remember that the database
must model the business? The salesman who walked into the customer's
headquarters and made a sale that resulted in orders going to hundreds
of stores received credit for the sale. However, each store (shipto)
was also associated with a local salesman. Sales reporting required
tracking BOTH associations, the appropriate one chosen for any given
report.
Roy Harvey
Beacon Falls, CT|||Tony:
Thanks for the help..
It does what it is supposed to do - but not exactly what I want.
The table structure is sort of like this:
Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
Cust1 Rep1 Rep1 Grp1 Grp1
Cust2 Rep1 Rep2 Grp1 Grp2
Cust3 Rep1 Rep3 Grp1 Grp3
Cust4 Rep4 Rep4 Grp2 Grp2
Because Coalesce returns the first non-null value, it returns Rep1 in
the query when I actually want Reps 1, 2 & 3 to show up. I am going to
use this same thing when determining which groups I want to show up, too.
I believe if I reverse the columns (shipto first, soldto second), it may
do exactly what I wanted.
Many many thanks for your succinct help.
BC

> Hi Blasting Cap,
> This help?
> select repname
> from salesrep_table as st
> inner join sourcetable t on st.repcode = coalesce( t.soldtorepcode,
> t.shiptorepcode )
> Tony.
>|||Hi BC,
If you get stuck just post another thread - good luck.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Blasting Cap" <goober@.christian.net> wrote in message
news:O3esDoemGHA.4816@.TK2MSFTNGP03.phx.gbl...
> Tony:
> Thanks for the help..
> It does what it is supposed to do - but not exactly what I want.
> The table structure is sort of like this:
> Cust No SoldtoRep ShiptoRep SoldToGrp ShiptoGrp
> Cust1 Rep1 Rep1 Grp1 Grp1
> Cust2 Rep1 Rep2 Grp1 Grp2
> Cust3 Rep1 Rep3 Grp1 Grp3
> Cust4 Rep4 Rep4 Grp2 Grp2
> Because Coalesce returns the first non-null value, it returns Rep1 in the
> query when I actually want Reps 1, 2 & 3 to show up. I am going to use
> this same thing when determining which groups I want to show up, too.
> I believe if I reverse the columns (shipto first, soldto second), it may
> do exactly what I wanted.
> Many many thanks for your succinct help.
> BC
>
>

Conditional Formatting

I have a matrix report that has rows grouped by year, by period, and
columns grouped by sales office.
I would like to conditionally format the b/g colour of the data
values so that if for example period 1 sales in 2007 were less than
period 1 sales in 2006, the b/g would be red.
I have done this in othjer reports but how can it be done in a
matrix table?
--
-- -- -- -
Posted with NewsLeecher v3.7 Final
Web @. http://www.newsleecher.com/?usenet
-- -- -- -- -On May 7, 5:54 am, Jason (j...@.junkiesplace.net) wrote:
> I have a matrix report that has rows grouped by year, by period, and
> columns grouped by sales office.
> I would like to conditionally format the b/g colour of the data
> values so that if for example period 1 sales in 2007 were less than
> period 1 sales in 2006, the b/g would be red.
> I have done this in othjer reports but how can it be done in a
> matrix table?
> --
> -- -- -- -
> Posted with NewsLeecher v3.7 Final
> Web @.http://www.newsleecher.com/?usenet
> -- -- -- -- -
The best way to manage this would be to set a flag column in the query/
stored procedure that is sourcing the report. Then if the sales are
lower for a later year, the LowerFlag column = 1 in the query. Then in
the report, on the Layout tab, select the cells that will be affected
by the background color change and in the Properties window next to
background color, enter in an expression something like:
=iif(Fields!LowerFlag.Value = 1, "Red", "White")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional Cascading Parameters

Hi,
I am looking for some help with builing parameters in a report. A have a
report that displays sales persons. What I need to do is either allow entry
of the sales person number directly OR use a set of cascading parameters that
start at country, then region, then state, followed by office. The office
parameter/query would display all salespersons for that office.
Is there any way that when the salesperson number is entered directly to
disable all other parameters and render the report? On the flip side, if the
salesperson number is not entered, then allow all other parameters.
Thank you,
BobI am not aware of a way to do this, although I have messed with it a
little... It would be a nice feature to have, but right now I think you'd
have to do this in HTML in front of the report
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:DFACCE39-01A3-495F-BBD1-0013A6CA1D62@.microsoft.com...
> Hi,
> I am looking for some help with builing parameters in a report. A have a
> report that displays sales persons. What I need to do is either allow
> entry
> of the sales person number directly OR use a set of cascading parameters
> that
> start at country, then region, then state, followed by office. The office
> parameter/query would display all salespersons for that office.
> Is there any way that when the salesperson number is entered directly to
> disable all other parameters and render the report? On the flip side, if
> the
> salesperson number is not entered, then allow all other parameters.
> Thank you,
> Bob|||I thought I may need to put a front end on the report. Thank you for
comfirming this.
Bob
"Wayne Snyder" wrote:
> I am not aware of a way to do this, although I have messed with it a
> little... It would be a nice feature to have, but right now I think you'd
> have to do this in HTML in front of the report
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Bob" <Bob@.discussions.microsoft.com> wrote in message
> news:DFACCE39-01A3-495F-BBD1-0013A6CA1D62@.microsoft.com...
> > Hi,
> >
> > I am looking for some help with builing parameters in a report. A have a
> > report that displays sales persons. What I need to do is either allow
> > entry
> > of the sales person number directly OR use a set of cascading parameters
> > that
> > start at country, then region, then state, followed by office. The office
> > parameter/query would display all salespersons for that office.
> >
> > Is there any way that when the salesperson number is entered directly to
> > disable all other parameters and render the report? On the flip side, if
> > the
> > salesperson number is not entered, then allow all other parameters.
> >
> > Thank you,
> >
> > Bob
>
>

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Condensing/Modularizing several cumulative calculated measures

In my cube, I have a number of calculated measures - around 30 per date tree and sales type combination (each different sales type has a different fiscal year). I have a Product.Sales Type attribute that can flicker the results between the different sale types, but the issue of working with different hierarchies is still present

Is there a way to condense this statement or somehow hookup some pieces so that one doesn't have to keep coding the same logic over and over? Either reducing the # of calculated members, or simply using some supported functionality to only code the logic in 1 spot, passing in the 'sale type' and 'measure name' and the inner logic would calculate the correct date tree and year level all within itself.

The pieces that change are bolded

(Date Tree hierarchy name,

Date Tree year level,

Calculated measure name

)

CASE

WHEN [Date].[Date Tree].CurrentMember.Level IS

[Date].[Date Tree].[(All)]

THEN [Measures].[Calculated Sales]

ELSE

Sum

(

PeriodsToDate

( [Date].[Date Tree].[Fiscal Year],

[Date].[Date Tree].CurrentMember

),

[Measures].[Calculated Sales]

)

END

One of my thoughts is to do something like this, but can someone feed me some ideas and fill in the syntax holes?

public [return type?] GetCumulativeMeasure( ‘Group 1’, ‘Calculated Sales 1’)

public [return type?] GetCumulativeMeasure(SalesType as string, MeasureName as string)

{

string Hierarchy = “”;

string HierarchyYearLevel = “”;

switch (SalesType)

{

case ‘Group 1’:Hierarchy = “[Date].[Date Tree]”; HierarchyYearLevel = Hierarchy + “.[Fiscal Year]”;

case ‘Group 2’:Hierarchy = “[Date].[Date Tree B]”; HierarchyYearLevel = Hierarchy + “.[Year Period]”;

}

//build calculated measure string to get a calculated measure for the year period and MeasureName variables

}

One thing you can do is create what's known as a 'time utility' or 'shell' dimension. There's a good writeup of what this means here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

Incidentally, your use of the CASE statement to check what level you're at inside your calculation is not the most efficient way of writing the expression. Take a look at Mosha's blog entry on this subject for details on how you can use scoped assignments to do this instead:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

This might also reduce the amount of code you need to write.

HTH,

Chris

|||

Thank you for the links! It's always nice to know that there's a better way - I had a feeling about that as well, as things were too clunky.

|||

I've read over the material, but I can't picture how to put in mosha's suggestions into the mix.
Some date calculations I'd be doing are L30, L60, and Cumulative based on year.

The Last _ Days should only work with the Date level and always go back 30 days from the current membe,
while cumulative would be limited to the year that's being used.

I envision a shell dimension attribute with the following members:
L30 Days
L60 Days
MTD
LY MTD
YTD
LYTD
Yearly Cumulative

Could someone provide an example with a few of the calculations or let me know what other info would be helpful to post? Thank you for working with me through this process!!

|||

I think the logic you've already got in your calculations will stay pretty much the same; it's scoping the calculations that will be tricky. We'd need to know some details about your Date dimension structure.

Chris

|||

The date dimension hierarchy structure's right now are as follows. What more info is needed?

[Date].[Date Tree A]
Year Type A (spans from jan-dec)
Month
Day

[Date].[Date Tree B]
Year Type B (spans from march-july this year, then next year goes from Aug-July)
Month
Day

[Date].[Date Tree C]
Year Type C (spans from oct-sept)
Month
Day

Calculated measures include:

hierarchy based:
(Cumulative based on year and hard coded to a specific date tree)
Cumulative Type A Sales
Cumulative Type A Internet Sales
Cumulative Type B Sales
Cumulative Type B Internet Sales
Cumulative Type C Sales
Cumulative Type C Internet Sales

(hopefully obtainable in both hierarchy and non hierarchy form (from the Month or Day level):

LY MTD Type A Sales
LY MTD Type B Sales
LY MTD Type C Sales

MTD Type A Sales
MTD Type B Sales
MTD Type C Sales

Not hierarchy based:
L30 Days Sales
L30 Days Internet Sales
L60 Days Sales
L60 Days Internet Sales
(for these, I'm hoping to somehow just have a reusable 'L30' and 'L60' scoping, but that would allow you to see measures in both time periods in the same axis. If it's a good route to use a time shell dimension, then that's what I would do, but I'm looking for a little bit more instruction if possible.

Conception of my cube

Hello,
During my internship, I had to create a cube for sales based on only one dB (of an ERP).
So I've created a view grouping information I need.
So I've only one "table" (a view actually) which is my fact table and my only dimension table.
Actually, the dimension are the attributes of my sole dimesion.

The problem is that I've to write my report and I don't know if I've made the good choice. I can also create a dimension table with Customer, for countries ... but the result would be the same.

Thanks in advance for your advices.

Hey there,

If you are happy with the performance, the SQL is maintainable and you will only ever have one cube for your company, then your solution will be adequate.

However, you may find that you run into performance, scalability and maintenance issues as your data grows and if you need to start working with other business processes (e.g. order processing or invoicing).

By having a single view you are bypassing some of the automated work a cube / Analysis Services can give you. Also with the view, you may end up replicating some logic for bringing back dimension values (e.g. country). If you were to have another view for a new cube, you would have to replicate all the SQL for extracting the country information to return in your flattened view.

By using dedicated dimension tables, you will gain re-use of dimension content (conformed dimensions), requiring only a single extract from the source system.

There are numerous other benefits of having a more structured data warehouse/data extract process, such as change tracking, referential integrity checking, introduction of surrogate keys (non source system dependent keys if you are dealing with multiple data sources).

Hope that made sense,

Jonathon