Basic MDX question

I understand the very basics of MDX. I know how to, for example, get
some set of measures as columns with some dimension on the Rows.
However, I need to do something more complex and I just can't get my
head around it.
What I'm trying to do is compare some given measure for various periods.
For example, say you have a measure [Sales] and a dimenion called
[Company Regions], the query to get the sales numbers for the company
regions basically looks like this:
with member [Date Dim].[Date Range] as
'Aggregate( {[Date Dim].[2004].[M05]:[Date Dim].[2004].[M06]}'
select [Measures].[Sales] ON COLUMNS,
Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE)
from myCube
where ([Date Dim].[Date Range])
That gives me the sales numbers for the months May and June.
What I need to do is that same basic query but I want to compare the
Date Range with last year. I just can't grasp how to make it show up in
the columns. I've read about cousin() and parallelperiods() but all the
examples return the period itself, not some measure in that period.
Can someone point me in the right direction?
Zach
Try it with a calculated member:
For example:
WITH MEMBER [Measures].[Sales_LastYear] as 'Sum([Date Dim].[2003],
[Measures].[Sales])'
MEMBER [Measures].[Sales_ThisYear] as 'Sum( {[Date Dim].[2004].[M05],[Date
Dim].[2004].[M06]}, [Measures].[Sales])'
MEMBER [Measures].[Sales_Difference] as ' [Measures].[Sales_ThisYear] -
[Measures].[Sales_LastYear]'
SELECT
{[Measures].[Sales_LastYear],[Measures].[Sales_ThisYear],[Measures].[Sales_D
ifference]} on COLUMNS,
Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE) on ROWS
from myCube
Michael
"Zach Wells" <no_zwells_spam@.ain1.com> schrieb im Newsbeitrag
news:%2366CkJyUEHA.3336@.TK2MSFTNGP11.phx.gbl...
> I understand the very basics of MDX. I know how to, for example, get
> some set of measures as columns with some dimension on the Rows.
> However, I need to do something more complex and I just can't get my
> head around it.
> What I'm trying to do is compare some given measure for various periods.
> For example, say you have a measure [Sales] and a dimenion called
> [Company Regions], the query to get the sales numbers for the company
> regions basically looks like this:
> with member [Date Dim].[Date Range] as
> 'Aggregate( {[Date Dim].[2004].[M05]:[Date Dim].[2004].[M06]}'
> select [Measures].[Sales] ON COLUMNS,
> Descendants([Company Regions], [Lowest Level], SELF_AND_BEFORE)
> from myCube
> where ([Date Dim].[Date Range])
> That gives me the sales numbers for the months May and June.
> What I need to do is that same basic query but I want to compare the
> Date Range with last year. I just can't grasp how to make it show up in
> the columns. I've read about cousin() and parallelperiods() but all the
> examples return the period itself, not some measure in that period.
> Can someone point me in the right direction?
> Zach

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
Buster
On Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoiceno

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
BusterOn Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoicenosql

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
BusterOn Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoiceno

Basic evaluations in SELECT statement

Is there a way to determine several fields under one CASE or IF in a SELECT statement? For example, I don't think I can do this (though I'd like to):

@.Type INT

AS

SELECT

CASE @.Type

WHEN 1 THEN

Field1 = <some calculation>

Field2 = <Some calculation>

WHEN 2 THEN

Field1 = <some calculation>

Field2 = <some calculation>

END

The alternative, of course, is to evaluate @.Type twice, once for Field1 and again for Field2. But this seems like such a waste. Is there a better way?

Thanks,

One alternative might be to use an inline table -- maybe something like this:

declare @.type integer
set @.type = 2

select type,
field1,
field2
from ( select 1 as type, 1+3+5 as field1, 1*3*5 as field2 union all
select 2, 2+4+6, 2*4*6
) a
where type = @.type

/*
type field1 field2
-- -- --
2 12 48
*/

|||

Jararaca,

You are correct that a CASE can only return a single value.

So in YOUR case, you must use two CASE statements: one for Field1, and then another for Field2.

I think the speed of evaluation of the CASE usually exceeds the speed of retrieving the rows of data and/or formatting the result set.

Dan

|||

If only one condition value, How about this.

Code Snippet

If @.Type = 1

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

if @.Type = 2

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

|||

Another method of doing this if you are wanting to apply it to an entire table is something like this:

declare @.tString char(20)
set @.tString = convert(char(10), 2*4*6)
+ convert(char(10), 1*3*5)

declare @.demo table
( rid integer,
type tinyint
)
insert into @.demo
select 1, 1 union all
select 2, 2 union all
select 3, 1 union all
select 4, 1 union all
select 5, 2

select rid,
rtrim(substring (@.tString, 10*type-9, 10))
computedField
from @.demo

/*
rid computedField
-- -
1 48
2 15
3 48
4 48
5 15
*/

Bar chart with row data

Hi,
I have a dataset with values for each month.
For example:
Jan Feb Mar Apr May Jun Jul
1200 1500 1420 1540 1450 1470 1467
I would like to create a Bar chart from this data. Is there a way to supply
the row as a source data? Since my data is in 12 columns, i have to add each
column as a data field to the chart and all bars show up in different colors
because of this.
Is there a way to provide the row a single series?
Thanks,If you have RS 2000 SP1 or SP2 installed, you could set the color to the
same identical value for all values (under appearance - series styles).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prash" <Prash@.discussions.microsoft.com> wrote in message
news:06121C94-6D66-4BD6-9AFC-CA3E787723C7@.microsoft.com...
> Hi,
> I have a dataset with values for each month.
> For example:
> Jan Feb Mar Apr May Jun Jul
> 1200 1500 1420 1540 1450 1470 1467
> I would like to create a Bar chart from this data. Is there a way to
> supply
> the row as a source data? Since my data is in 12 columns, i have to add
> each
> column as a data field to the chart and all bars show up in different
> colors
> because of this.
> Is there a way to provide the row a single series?
> Thanks,

Backwards in a foreach for ADO?

I need to simulate cursor-type (groan) behavior in a dataset and am wondering if this is possible in the foreach task. Example - The user has the need to go through the data row-by-row, and if a certain value is missing in row 10 then go back to row 8, grab a value from that row, and plug it in the missing column in row 10. Then move on to row 11.

Is there a way to make the foreach ADO enumerator travel backwards? Is there a way to do this that will not be awfully inefficient? Any suggestions out there would be welcome.

I've advocated for set-based updates, and these simply aren't an option, as each successive row depends on the updates that may have happened above it in the sequence. Unless I hear of any other ideas out there I have to move forward with this sequential type operation.

This sounds like a script task to me... I don't believe that there is an effecient way to do this (if at all) with a for each loop...

|||

That's the headache I'm having - I don't see a good way to do this period, from a SQL/SSIS/relational perspective.

|||Not sure if this would meet your needs, but you can use a script transform to buffer rows. You'd have to create some structure to store the data in (an array or dataset would work) and then you can access the rows in any order you wish.|||

Sounds intriguing - do you happen to have an example handy?

backward compatibility (2000->2005)

Hi!
I have heard that it's not possible to use (for example) *= as a join in SQL
Server 2005 and now I have tried to find other things that I can't use. Does
anyone know where I can find this?
Thanks =)
//MalinHi
"Breaking Changes to Database Engine Features in SQL Server 2005" in Books
Online.
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Check out Backward Compatibility in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm).
This documents discontinued, deprecated and breaking changes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Micheal and Dan,
this was exact what I was looking for =)
Thank you!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Om3dtTn0GHA.2636@.TK2MSFTNGP06.phx.gbl...
> Check out Backward Compatibility in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm).
> This documents discontinued, deprecated and breaking changes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>> Hi!
>> I have heard that it's not possible to use (for example) *= as a join in
>> SQL Server 2005 and now I have tried to find other things that I can't
>> use. Does anyone know where I can find this?
>> Thanks =)
>> //Malin
>

backward compatibility (2000->2005)

Hi!
I have heard that it's not possible to use (for example) *= as a join in SQL
Server 2005 and now I have tried to find other things that I can't use. Does
anyone know where I can find this?
Thanks =)
//MalinHi
"Breaking Changes to Database Engine Features in SQL Server 2005" in Books
Online.
http://msdn2.microsoft.com/en-us/library/ms143532.aspx
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Check out Backward Compatibility in the Books Online
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec
-ba920476dd69.htm).
This documents discontinued, deprecated and breaking changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hi!
> I have heard that it's not possible to use (for example) *= as a join in
> SQL Server 2005 and now I have tried to find other things that I can't
> use. Does anyone know where I can find this?
> Thanks =)
> //Malin
>|||Micheal and Dan,
this was exact what I was looking for =)
Thank you!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Om3dtTn0GHA.2636@.TK2MSFTNGP06.phx.gbl...
> Check out Backward Compatibility in the Books Online
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96
ec-ba920476dd69.htm).
> This documents discontinued, deprecated and breaking changes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23wWlrJn0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>