Matrix Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.

Thanks again for your help!

Matrix Subtotal but not all colums

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).

Please search this forum for threads related to the InScope function.

-- Robert

|||

Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?

Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:

= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")

|||

I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?

Basically all my columns are in the row group scope and I just need the subtotals to act differently.

Thanks again for your help!

Matrix SSAS dimension/hierarchy order problem

I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.

The report is somehow treating the Month like character data instead of numeric, how do I fix this?

It sounds like your MDX query only retrieves the Member_Caption (which is the string label representation), but not the Member_Value property (which is the value representation with the original data type).

For example:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Due Date].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS * [Due Date].[English Month Name].[English Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE ON ROWS FROM [Adventure Works DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Note that the .Value field property of RDL expressions always maps to the Member_Caption property value for dimensions (the member caption is always a string!). The additional Member_Value intrinsic dimension property is not mapped directly to any of the predefined RDL extended field property names. Therefore, in order to access the MEMBER_VALUE property in the report, you have to use the so-called field property collection syntax in the matrix group expression and sort expression to get the numeric value for the dimension property and specify the property name exactly as defined in the MDX query above (the property name is case-sensitive in this case):
=Fields!Month_Number_Of_Year("MEMBER_VALUE")

Additional information about MDX intrinsic member properties can be found here: http://msdn2.microsoft.com/en-us/library/ms145528.aspx

-- Robert

|||

Robert - that worked perfectly. Thanks a bunch!

To anyone else in the same situation, to get to the matrix group expression and sort expression right-click the matrix object in the report designer and select Properties. Goto the Groups tab and then select dimension member in question and click the Edit button. This brings up another window, goto the Sort tab and enter the expression.

Matrix question

I have a matrix issue with the colum ordering. What I want is for the
month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
cannot determine how to order them correctly. can someone point me in the
right direction?Brian,
In a matrix you probably have the month set up as a group. In the properties
of this group there is a sorting tap, I dont have a full picture of how you
set things up but I think choosing it to order by MonthNr should be the
thing to do.
regards
Perry
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
>I have a matrix issue with the colum ordering. What I want is for the
> month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> cannot determine how to order them correctly. can someone point me in the
> right direction?|||thanks perry, i noticed that too. Problem is I want to arrange the columns
horizontally, not the values vertically. I'm still searching technet and BOL
for an answer. Thanks again.
"Perry" wrote:
> Brian,
> In a matrix you probably have the month set up as a group. In the properties
> of this group there is a sorting tap, I dont have a full picture of how you
> set things up but I think choosing it to order by MonthNr should be the
> thing to do.
> regards
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >I have a matrix issue with the colum ordering. What I want is for the
> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> > cannot determine how to order them correctly. can someone point me in the
> > right direction?
>
>|||Perry - Ignore my last post. What I wrote didn't make sense. You were on the
money. Question is, how do you write the expression to hard code the sort?
Guess I'll keep reading. Thanks.
"Perry" wrote:
> Brian,
> In a matrix you probably have the month set up as a group. In the properties
> of this group there is a sorting tap, I dont have a full picture of how you
> set things up but I think choosing it to order by MonthNr should be the
> thing to do.
> regards
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >I have a matrix issue with the colum ordering. What I want is for the
> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> > cannot determine how to order them correctly. can someone point me in the
> > right direction?
>
>|||Brian,
in reply to -- how do you write the expression to hard code the sort?
(I have 7 months working experience with RS2000 and RS2005, I am guessing we
are/were both confronted with the same issues to solve one way or the other,
you can decide if this means enough about knowledge :-) )
To my knowledge you can not hardcode into the group expression itself how to
sort anything (i never to that there). This can only be done in the sorting
tab, for me this does the trick in all cases.
Sometimes I may use something like fiscalyear, left(fiscalmonthname,3) in
the columns. In my query I also select the fiscalmonthnr. In the matrix I
choose to show the fiscalyear, fiscalmonthname (first 3 characters, this you
can solve either in de query, or in the report). The fiscalmonthname I sort
on the fiscalmonthnr ascending, which is selected in the dataset but not
shown in the report (just for sorting purposes).
If this reply misses your point (then i misunderstood something), then which
expression you mean and where do you set it?
Perry
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:5ABD05FF-2F13-4F0B-A5EC-18C949D0A915@.microsoft.com...
> Perry - Ignore my last post. What I wrote didn't make sense. You were on
> the
> money. Question is, how do you write the expression to hard code the sort?
> Guess I'll keep reading. Thanks.
> "Perry" wrote:
>> Brian,
>> In a matrix you probably have the month set up as a group. In the
>> properties
>> of this group there is a sorting tap, I dont have a full picture of how
>> you
>> set things up but I think choosing it to order by MonthNr should be the
>> thing to do.
>> regards
>> Perry
>>
>> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
>> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
>> >I have a matrix issue with the colum ordering. What I want is for the
>> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
>> > cannot determine how to order them correctly. can someone point me in
>> > the
>> > right direction?
>>|||Thanks Perry.
"Perry" wrote:
> Brian,
> in reply to -- how do you write the expression to hard code the sort?
> (I have 7 months working experience with RS2000 and RS2005, I am guessing we
> are/were both confronted with the same issues to solve one way or the other,
> you can decide if this means enough about knowledge :-) )
> To my knowledge you can not hardcode into the group expression itself how to
> sort anything (i never to that there). This can only be done in the sorting
> tab, for me this does the trick in all cases.
> Sometimes I may use something like fiscalyear, left(fiscalmonthname,3) in
> the columns. In my query I also select the fiscalmonthnr. In the matrix I
> choose to show the fiscalyear, fiscalmonthname (first 3 characters, this you
> can solve either in de query, or in the report). The fiscalmonthname I sort
> on the fiscalmonthnr ascending, which is selected in the dataset but not
> shown in the report (just for sorting purposes).
> If this reply misses your point (then i misunderstood something), then which
> expression you mean and where do you set it?
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:5ABD05FF-2F13-4F0B-A5EC-18C949D0A915@.microsoft.com...
> > Perry - Ignore my last post. What I wrote didn't make sense. You were on
> > the
> > money. Question is, how do you write the expression to hard code the sort?
> > Guess I'll keep reading. Thanks.
> >
> > "Perry" wrote:
> >
> >> Brian,
> >>
> >> In a matrix you probably have the month set up as a group. In the
> >> properties
> >> of this group there is a sorting tap, I dont have a full picture of how
> >> you
> >> set things up but I think choosing it to order by MonthNr should be the
> >> thing to do.
> >>
> >> regards
> >>
> >> Perry
> >>
> >>
> >> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> >> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >> >I have a matrix issue with the colum ordering. What I want is for the
> >> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> >> > cannot determine how to order them correctly. can someone point me in
> >> > the
> >> > right direction?
> >>
> >>
> >>
>
>|||Seems like I made a simple problem into a difficult one. To fix my problem, I
added another criteria to my report. Month(Date) as MonthNum. Then used that
in the matrix and sorted ascending rather than using the month name ( Jan,
Feb. ) Worked like a charm.
"Perry" wrote:
> Brian,
> in reply to -- how do you write the expression to hard code the sort?
> (I have 7 months working experience with RS2000 and RS2005, I am guessing we
> are/were both confronted with the same issues to solve one way or the other,
> you can decide if this means enough about knowledge :-) )
> To my knowledge you can not hardcode into the group expression itself how to
> sort anything (i never to that there). This can only be done in the sorting
> tab, for me this does the trick in all cases.
> Sometimes I may use something like fiscalyear, left(fiscalmonthname,3) in
> the columns. In my query I also select the fiscalmonthnr. In the matrix I
> choose to show the fiscalyear, fiscalmonthname (first 3 characters, this you
> can solve either in de query, or in the report). The fiscalmonthname I sort
> on the fiscalmonthnr ascending, which is selected in the dataset but not
> shown in the report (just for sorting purposes).
> If this reply misses your point (then i misunderstood something), then which
> expression you mean and where do you set it?
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:5ABD05FF-2F13-4F0B-A5EC-18C949D0A915@.microsoft.com...
> > Perry - Ignore my last post. What I wrote didn't make sense. You were on
> > the
> > money. Question is, how do you write the expression to hard code the sort?
> > Guess I'll keep reading. Thanks.
> >
> > "Perry" wrote:
> >
> >> Brian,
> >>
> >> In a matrix you probably have the month set up as a group. In the
> >> properties
> >> of this group there is a sorting tap, I dont have a full picture of how
> >> you
> >> set things up but I think choosing it to order by MonthNr should be the
> >> thing to do.
> >>
> >> regards
> >>
> >> Perry
> >>
> >>
> >> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> >> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >> >I have a matrix issue with the colum ordering. What I want is for the
> >> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> >> > cannot determine how to order them correctly. can someone point me in
> >> > the
> >> > right direction?
> >>
> >>
> >>
>
>

Matrix Question

I have a view that gives me the following information from some tables.

Cust # Cust Name Date Order Type

001 John Doe 20070401 OR1

002 Miss Doe 20070401 OR2

001 John Doe 20070402 OR2

002 Miss Doe 20070402 OR2

What I would like to do is set up a matrix type report. The report is by the last 6 rolling order dates, with some % columns. So two row examples might be

Cust# Cust Name 20070401 20070402 % (of last x = OR1) %(of last x = OR2)

001 John Doe OR1 OR2 50% 50%

002 Miss Doe OR2 OR2 0% 100%

Another column just like the last two % based off a 3rd type, and finally a total % column that simply adds the 3 columns up (should always equal 100%, just an error check)

First, can this be done with a matrix? I tried a table but it lists cust# twice, but I can be doing it wrong. I am ok doing this within the query if need be, if someone gives me a hint how

Thanks,

When you tried the table, did you set a row group on the customer number?|||Yes, and it will not give me a column for each date in table form.

Matrix order of totals

I have a matrix that shows sums by month. The dataset returns both month and quarter. I want to have subtotals by Quarter. I have this working and the matrix returns columns as follows:
Jan Feb March Q1 April May June Q2 ... Total

What I would like to have is
Jan Feb March April May ... Q1 Q2 Q3 Q4 Total

Is there anyway to have subtotals displayed at the end of the row?

For dynamic groups (e.g. quarter), the subtotals are always part of that group. They currently cannot be moved out of that group instance.

Another way of doing this, is to use a table in the report with explicit columns for Jan, Feb, ..., Dec, Q1, ..., Q4, Total. You would then need to use expressions to calculate your month/quarter/year aggregations.

-- Robert

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.