Max Function - But between a fixed value and a column

Dear Sirs / Ladies
In HQL their was a MAX Function that would compare a fixed value
( 1.95 ) with a Column.
An example is MAX( 1.95, RegularBasePrice )
If the RegularBasePrice was 0.95 it would return 1.95 or
If the RegularBasePrice was 2.49 it would return 2.49
Is their anyway to duplicate that in TSQL
Below is an example of the code under HQL
from Item;
where CurrentUnitCost > 0
and CurrentPrice > 0;
update CurrentMargin : ( ( ( CurrentPrice /
Max( 1, CurrentSplit ) ) - CurrentUnitCost ) /
Max( .01, ( CurrentPrice /
Max( 1, CurrentSplit ) ) ) ) * 100;
MarkThis is also the GREATEST( <exp list> ) function in Oracle.In Standard
SQL, you will need a CASE expression like this:
CASE WHEN x > 1.95 THEN x ELSE 1.95 END

Max () query problem

Say I have a table like this (just for example)

ID Date Name
__ ____ _____
1 1/2/2004 Store A
2 1/1/2004 Store A
3 1/3/2004 Store B
4 1/2/2004 Store B

Say I wanted to get the rows for the maximum dates for stores A and B
aka this record set..

ID Date Name
__ ____ _____
1 1/2/2004 Store A
3 1/3/2004 Store B

How would I got about doing this in sql?

thanks

-JimSELECT id, date, name
FROM SomeTable AS T
WHERE date =
(SELECT MAX(date)
FROM SomeTable
WHERE name = T.name)

--
David Portas
SQL Server MVP
--

Matrix subtotal row question

I have created a matrix that looks like the following example:
APRIL MAY JUNE
PRODUCT X 10 20 30
PRODUCT Y 20 30 40
where the data is profit per unit sold (=PROFIT/# of UNITS)
I need to add a row that is the average profit for each month. I know
how to get the subtotal row to show up, but this justs adds the rows
(which is meaningless for me). Even the simple average (e.g. (10 +
20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
for APRIL/ALL UNITS sold for APRIL). A data example is:
for April I sold 10 units of X for a $100 profit and I sold 20 units
of Y for a $400 profit
As in the table above, the profit per unit is (100/10) $10 for X and
(400/20) $20 for Y
But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
profit / 30 units = ~$17
So two questions:
How do I add a average row to the matrix?
How do I make this a weighted average?
Sorry if my explanation is confusing or the answer is basic... new to
this and can't find an answer anywhere!It sounds like your current expression is something like this:
=Sum(Fields!ProfitPerUnit.Value)
What you really need is something more like this:
=Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
If you are only returning ProfitPerUnit and Units in your query, you could
do this instead:
=Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"coldfact" <bryan@.coldfact.com> wrote in message
news:249185cd.0407151323.79162246@.posting.google.com...
> I have created a matrix that looks like the following example:
> APRIL MAY JUNE
> PRODUCT X 10 20 30
> PRODUCT Y 20 30 40
> where the data is profit per unit sold (=PROFIT/# of UNITS)
> I need to add a row that is the average profit for each month. I know
> how to get the subtotal row to show up, but this justs adds the rows
> (which is meaningless for me). Even the simple average (e.g. (10 +
> 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> for APRIL/ALL UNITS sold for APRIL). A data example is:
> for April I sold 10 units of X for a $100 profit and I sold 20 units
> of Y for a $400 profit
> As in the table above, the profit per unit is (100/10) $10 for X and
> (400/20) $20 for Y
> But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> profit / 30 units = ~$17
> So two questions:
> How do I add a average row to the matrix?
> How do I make this a weighted average?
> Sorry if my explanation is confusing or the answer is basic... new to
> this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!|||Very nice - works now - thanks for your help!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<uONOoHsaEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> It sounds like your current expression is something like this:
> =Sum(Fields!ProfitPerUnit.Value)
> What you really need is something more like this:
> =Sum(Fields!TotalProfit.Value)/Sum(Fields!Units.Value)
> If you are only returning ProfitPerUnit and Units in your query, you could
> do this instead:
> =Sum(Fields!ProfitPerUnit.Value*Fields!Units.Value)/Sum(Fields!Units.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "coldfact" <bryan@.coldfact.com> wrote in message
> news:249185cd.0407151323.79162246@.posting.google.com...
> > I have created a matrix that looks like the following example:
> >
> > APRIL MAY JUNE
> > PRODUCT X 10 20 30
> > PRODUCT Y 20 30 40
> >
> > where the data is profit per unit sold (=PROFIT/# of UNITS)
> > I need to add a row that is the average profit for each month. I know
> > how to get the subtotal row to show up, but this justs adds the rows
> > (which is meaningless for me). Even the simple average (e.g. (10 +
> > 20)/2) won't do - I need a weighted average per month (e.g. ALL PROFIT
> > for APRIL/ALL UNITS sold for APRIL). A data example is:
> > for April I sold 10 units of X for a $100 profit and I sold 20 units
> > of Y for a $400 profit
> > As in the table above, the profit per unit is (100/10) $10 for X and
> > (400/20) $20 for Y
> > But the average profit I want is not ($10 + $20) / 2 ($15). It is $500
> > profit / 30 units = ~$17
> >
> > So two questions:
> > How do I add a average row to the matrix?
> > How do I make this a weighted average?
> >
> > Sorry if my explanation is confusing or the answer is basic... new to
> > this and can't find an answer anywhere!

Matrix sorting

Hi. I need to implement a matrix that supports column sorting just like a table.
For example, given a matrix that contains...

Col1 Col2
Row1 1 8
Row2 2 6
Row3 3 7

...if the user clicks on "Col2", I need the matrix to look like this:

Col1 Col2
Row2 2 6
Row3 3 7
Row1 1 8

I cannot use the interactive sort feature since the matrix must be WYSIWYG with the printed copy and the built-in interactive sort arrows do appear in printed output.

Here's my partial solution; my hope is that someone can point me in the right direction from here:

When the user clicks on a column heading I will jump to a URL such as:
http://myserver/myreport.aspx?SortField=Col2&SortDirection=Ascending

SortField and SortDirection are parsed by the web app and passed as parameters to my report.

So, after the user clicks on Col2 the report knows the following:
Parameters!SortField.Value = "Col2"
and
Parameters!SortDirection.Value = "Ascending"

Given this information, is there a way to tell the report's matrix to actually display the data in the order shown in the example above.

Thanks for any help you can provide.
Try right click on matrix and select matrix properties, select groups, select edit matrix group, select the sorting tab, change the expression value should be col 12 and direction descending. It worked for me.

matrix issues

example rpt..

| Last year | this year |
Resolution | Resolved | Closed | Cost | Resolved | Closed | Cost |
| (qty) | (qty) | (total)| (qty) | (qty) | total) |
Res 1 | 1 | 0 | 500 | 5 | 2 | 350 |
Res 2 | 6 | 3 | 475 | 3 | 4 | 600 |
. . . |
Res 10 | 0 | 0 | 0 | 0 | 0 | 0 |
Res 11 | 3 | 4 | 640 | 4 | 2 | 450 |

Data info: a case can have a status of 'Open', 'Resolved', or 'Closed', I am only interested in counts of 'Resolved' and 'Closed', with a sum of the costs for each resolution type (1 - 11). And split by 'Last Year' and 'This Year'

I'm having difficulty setting up the groupings, plus I want all 11 rows to show up even if they did not happen to be in the recordset (like Res 10, above) with 0 totals. and I want the Resolutions to be listed in a specific order (not alphabetically like the example).

I've tried creating a new row group for each Resolution, and filtering buy that specific value, but after the second row is added I get an array out of bounds error...

any assistance would be great
SamWell, I've given up on trying to make this through a matrix. I re-wrote the stored procedure to calculate all of the needed fields, then used a standard table...

Thank you to anyone that took the time to read the question and try to come up with a solution..

SC|||I done it using Microstrategy , it's very easy.
if u want to use SQL Server , use anaysis services ( MDX)

matrix issues

example rpt..

| Last year | this year |
Resolution | Resolved | Closed | Cost | Resolved | Closed | Cost |
| (qty) | (qty) | (total)| (qty) | (qty) | total) |
Res 1 | 1 | 0 | 500 | 5 | 2 | 350 |
Res 2 | 6 | 3 | 475 | 3 | 4 | 600 |
. . . |
Res 10 | 0 | 0 | 0 | 0 | 0 | 0 |
Res 11 | 3 | 4 | 640 | 4 | 2 | 450 |

Data info: a case can have a status of 'Open', 'Resolved', or 'Closed', I am only interested in counts of 'Resolved' and 'Closed', with a sum of the costs for each resolution type (1 - 11). And split by 'Last Year' and 'This Year'

I'm having difficulty setting up the groupings, plus I want all 11 rows to show up even if they did not happen to be in the recordset (like Res 10, above) with 0 totals. and I want the Resolutions to be listed in a specific order (not alphabetically like the example).

I've tried creating a new row group for each Resolution, and filtering buy that specific value, but after the second row is added I get an array out of bounds error...

any assistance would be great
SamWell, I've given up on trying to make this through a matrix. I re-wrote the stored procedure to calculate all of the needed fields, then used a standard table...

Thank you to anyone that took the time to read the question and try to come up with a solution..

SC|||I done it using Microstrategy , it's very easy.
if u want to use SQL Server , use anaysis services ( MDX)

Matrix Column Trouble

I am trying to add an ungrouped columns to the end of a matrix. in RS SP2 2000

For example how do I add the three columns in red to a matrix in a report?

Q1

Q2

Q3

Total

Percentage

Turn Over Ratio

Budget Variance

Sales

100.00

100.00

100.00

300.00

100%

1.23

4.22

Cost

100.00

100.00

100.00

300.00

100%

1.23

4.22

Expense

100.00

100.00

100.00

300.00

100%

1.23

4.22

I had an instance where I wanted a total and then an average of the data in a matrix, however I was not able to figure it out. I hope someone answers, but I was unsuccessful in adding anything other than 1 subtotal.

I ended up using a table next to the matrix to display the avg. It took a bit to line it up properly but it works fine. Just a suggestion if you dont get a positive response.

|||

Thanks for the response... That is what I was afraid of, that I wouldn't be able to do this, it's just not one column that I will need to add but many. I guess I'll have to try and get something like you are describing thanks for you help. I hope somone else will respond that has had this type of issue as well.

|||but how to do it if use a table?|||

I was finally able to do it without a table. but one matrix. I did this by adding a fake groups, Expression=1, Expression=2, Expression=3.

Then using the InScope function to determine where I was at on the different subtotals.

If anyone wants to know send me an email at jwisener@.gmail.com

I just have one more problem and that is to figure out how to get rid of the white space above the matrix. Because I have to add a padding to the subtotal top to get the header to move down to the same line as the rest of the headers.

matrix column grouping collapse

Hi Folks
Going a little crazy with this one...
Have a SSRS 2K SP1 matrix report which is highly similar to the company
sales report example. As a matter of act, the column headings are identical.
Have no issues with the row groupings expanding and showing initially as
collapsed, but for the life of me, I can not get the columns (YEAR/QUARTER)
to rendering as collapsed or have any of this functionality.
Having groupings working fine for both row/column, but the collapsed for
columns just doesn't seem to be functional?
thanks
robgot it...
solution was found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_0zvx.asp
rob
"Rob" wrote:
> Hi Folks
> Going a little crazy with this one...
> Have a SSRS 2K SP1 matrix report which is highly similar to the company
> sales report example. As a matter of act, the column headings are identical.
> Have no issues with the row groupings expanding and showing initially as
> collapsed, but for the life of me, I can not get the columns (YEAR/QUARTER)
> to rendering as collapsed or have any of this functionality.
> Having groupings working fine for both row/column, but the collapsed for
> columns just doesn't seem to be functional?
> thanks
> rob

Matrix - Divide one column by another

Hello,
Is there a way to divide a "dynamic column" by another "dynamic column"
?
Example:
Assume matrix has 2 columns column1 and column2. I want a third dynamic
column called column3 based on an expression column2/column1
Matrix will look like
colmn1 | column2 | column3 (value is column2/column1)
----
10 | 5 | 2
----
15 | 5 | 3
----
20 | 10 | 2
----
10 | 100 | 0.1
----
Is this possible? Any help wlll be greatly appreciated.
Thanks,
BRThere are a couple alternatives for tables:
If you have Col1 and Col2 coming in from your data set, you can create a
virtual field by right clicking in dataset field list and adding your own
calculated field. Then your table would only need to drop the new field into
Col3.
The alternative is to create a function in Col3 as
= Fields!clm_fin_curr_loss_reserve.Value/ Fields!clm_no.Value
If I am making the wrong assumption on what you are doing, please explain
further.
"Ray" wrote:
> Hello,
> Is there a way to divide a "dynamic column" by another "dynamic column"
> ?
> Example:
> Assume matrix has 2 columns column1 and column2. I want a third dynamic
> column called column3 based on an expression column2/column1
> Matrix will look like
> colmn1 | column2 | column3 (value is column2/column1)
> ----
> 10 | 5 | 2
> ----
> 15 | 5 | 3
> ----
> 20 | 10 | 2
> ----
> 10 | 100 | 0.1
> ----
> Is this possible? Any help wlll be greatly appreciated.
> Thanks,
> BR
>|||First, Thanks for the reply.
I cannot do a calculated field as the col1 and col2 are coming from the
same Table column. It is the column grouping in the Matrix (not a
table) .
In the new column I have to do
col2.value in scope of the current row / col1.value in scope of the
current row
Does this make sense?
William wrote:
> There are a couple alternatives for tables:
> If you have Col1 and Col2 coming in from your data set, you can create a
> virtual field by right clicking in dataset field list and adding your own
> calculated field. Then your table would only need to drop the new field into
> Col3.
> The alternative is to create a function in Col3 as
> = Fields!clm_fin_curr_loss_reserve.Value/ Fields!clm_no.Value
> If I am making the wrong assumption on what you are doing, please explain
> further.
> "Ray" wrote:
> > Hello,
> >
> > Is there a way to divide a "dynamic column" by another "dynamic column"
> > ?
> >
> > Example:
> >
> > Assume matrix has 2 columns column1 and column2. I want a third dynamic
> > column called column3 based on an expression column2/column1
> >
> > Matrix will look like
> >
> > colmn1 | column2 | column3 (value is column2/column1)
> > ----
> > 10 | 5 | 2
> > ----
> > 15 | 5 | 3
> > ----
> > 20 | 10 | 2
> > ----
> > 10 | 100 | 0.1
> > ----
> > Is this possible? Any help wlll be greatly appreciated.
> >
> > Thanks,
> > BR
> >
> >|||I have the same exact problem. Actually I needed to add several
columns.
To clarify, I have a matrix, and I have a subtotal. But in addition to
the subtotal I need to have "percentage of subtotal" columns for some
of my "important" data columns. And the lame matrix object does not let
me do it.
I ended up doing the following. Added N=no. of required columns to my
matrix "data region". Put the formulae for each of N of these columns
(since 1 column is for the subtotal). In my case N=3 (not including the
subtotal column) So I ended up with "garbage" of 3 extra data columns
for EACH of my original data columns. I then went and used "Inscope" to
"hide" the values in these 3 junk columns.
So now I have my report EXCEPT I have 3 blank columns for each of my
data columns followed by my subtotal and 3 computed percentage columns.
"CanShrink" blah blah properties will not get rid of the empty columns.
And "Hide" does not get rid of the columns only of the textboxes within
leaving the blank space.
Told my business user to deal with it or find someone else to do his
report. But if someone has a solution let me know. I'm not the only
person having issues with the matrix columns. In their infinite wisdom,
microsoftees have left critical practical considerations out of the
table and matrix implementations. It's either ALL static or ALL
dynamic. Which is okay for "hello world" reports. Welcome to reality
folks.
Ray wrote:
> First, Thanks for the reply.
> I cannot do a calculated field as the col1 and col2 are coming from the
> same Table column. It is the column grouping in the Matrix (not a
> table) .
> In the new column I have to do
> col2.value in scope of the current row / col1.value in scope of the
> current row
> Does this make sense?
>
> William wrote:
> > There are a couple alternatives for tables:
> >
> > If you have Col1 and Col2 coming in from your data set, you can create a
> > virtual field by right clicking in dataset field list and adding your own
> > calculated field. Then your table would only need to drop the new field into
> > Col3.
> >
> > The alternative is to create a function in Col3 as
> > = Fields!clm_fin_curr_loss_reserve.Value/ Fields!clm_no.Value
> >
> > If I am making the wrong assumption on what you are doing, please explain
> > further.
> >
> > "Ray" wrote:
> >
> > > Hello,
> > >
> > > Is there a way to divide a "dynamic column" by another "dynamic column"
> > > ?
> > >
> > > Example:
> > >
> > > Assume matrix has 2 columns column1 and column2. I want a third dynamic
> > > column called column3 based on an expression column2/column1
> > >
> > > Matrix will look like
> > >
> > > colmn1 | column2 | column3 (value is column2/column1)
> > > ----
> > > 10 | 5 | 2
> > > ----
> > > 15 | 5 | 3
> > > ----
> > > 20 | 10 | 2
> > > ----
> > > 10 | 100 | 0.1
> > > ----
> > > Is this possible? Any help wlll be greatly appreciated.
> > >
> > > Thanks,
> > > BR
> > >
> > >

Matrix - Calculate percentage column

How do I write expression to calculate a value to show percentage of the
subtotal value in a Matrix? example:
Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
like to show the percentage of the subtotal for Matrix_Row in a column next
to Matrix Col.
Matrix_Col Percent
Matrix_Row
value 1 %25
value 2 %50
value 1 %25
Total 4I would try this in the third column
Field!Name.Value/ SUM(Field!Name.Value)
I'd also give this third column a percentage format
Med bouchenafa
"Curtis" <gilbertson.curtis@.leg.wa.gov> a écrit dans le message de news:
%23tJnBVXFGHA.2320@.TK2MSFTNGP11.phx.gbl...
> How do I write expression to calculate a value to show percentage of the
> subtotal value in a Matrix? example:
> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I would
> like to show the percentage of the subtotal for Matrix_Row in a column
> next to Matrix Col.
>
> Matrix_Col Percent
> Matrix_Row
> value 1 %25
> value 2 %50
> value 1 %25
>
> Total 4
>|||Med, Thanks for your reply. I have tried this already and it does not work.
I tried it in the third column as well and it still does not work. Any
other ideas?
"Med Bouchenafa" <com.hotmail@.bouchenafa> wrote in message
news:uuhpEWfFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I would try this in the third column
> Field!Name.Value/ SUM(Field!Name.Value)
> I'd also give this third column a percentage format
> Med bouchenafa
>
> "Curtis" <gilbertson.curtis@.leg.wa.gov> a écrit dans le message de news:
> %23tJnBVXFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>> How do I write expression to calculate a value to show percentage of the
>> subtotal value in a Matrix? example:
>> Matrix has Coulmn group Matrix_col and a Row group Matrix_Row and I
>> would like to show the percentage of the subtotal for Matrix_Row in a
>> column next to Matrix Col.
>>
>> Matrix_Col Percent
>> Matrix_Row
>> value 1 %25
>> value 2 %50
>> value 1 %25
>>
>> Total 4
>>
>

Materializing a reference dimension in SSAS

Hi,

I think there is an issue with the Materialize option when we have a referenced dimension in SSAS. For example if there is a dimension B which is linked to a measure group F through dimension A and materialize option is checked (which is default and in best practices to be followed) I have an issue. If there are any dimension keys of dimension A in measure group F that are not in dimension A then it doesn’t give an error saying dimension key not found when processing. The processing query ignores the rows that do not have a corresponding dimension key and the processing succeeds. Only when the materialize option is unchecked I get the error.

It seems that there is a problem since there is a regular relationship between dimension A and measure group F and if there are dim key errors then it should fail and not ignore those rows and process.

Can you please confirm if this is a bug in SSAS and if we should always avoid using this option (Materialize option checked) when use referenced dimensions?

I am using SQL 2005 SP2a.

Thanks

Arun

This is not a bug. When you click materialize, the fact table associated with the measure group is joined to the intermediate dimension using an inner join. If you have a missing reference, the effect of the inner join is that fact records are dropped.

What we always recommend to folks is that they do NOT use the Unknown Member and associated error handling features in their cubes. Instead, we recommend they resolve all references in the data warehouse. For example, if you have a fact table that represents an order that has not shipped, the order fact record has a NULL ship date reference. In the Date dimension table, you would have an entry for the NULL date and your fact record would reference that Date record's surrogate key (which we usually set to -1).

B.