How do I add a single column heading row in the detail section?

Could somebody please tell me how to do this?
I've created a report with the wizard that lists Managers. You toggle
a Manager and a detail section of employees opens for that Manager. I
want to put a row of column names at the top of the detail section when
it's opened (not showing when the detail is closed). I've succeeded in
adding a row that shows above each detail record, but that's not what I
want. I just want a single row of column headings.
I've tried looking everywhere, including the Help system, and I can't
find how to do this. Probably simple, right?
Thanks, JulieHi Julie,
Highlight the Managers group, right click and select Insert Row Below.
That will add another row for that group, but since the group only gets
repeated once for each manager this is what you want. Toggle this new
row on the Manager and you're good to go.
take care,
Michelle|||Michelle, thank you, that's what I've been trying to do. That just
leaves me to figure out how to hide this new detail header when the
Manager group is not toggled. Setting the visibility of the detail
header to False makes it invisible all the time. I want it to become
visible when the Manager is toggled, and only for any Manager(s)
toggled.
Michelle wrote:
> Hi Julie,
> Highlight the Managers group, right click and select Insert Row
Below.
> That will add another row for that group, but since the group only
gets
> repeated once for each manager this is what you want. Toggle this
new
> row on the Manager and you're good to go.
> take care,
> Michellesql

How concatenate members from 3 virtual dimensions using MDX

Hello,
There is a big problem - my client made 3 virtual dimensions (Year, Quarter,
Month) from single time dimension and wants to use them, because the
graphical interface in Excel is more friendly for end-users to have 3
dimensions (i.e. 3 separate tabs).
But in the same time we need to make MDX queries with time series with MDX
formulas like YTD, LastPeriod and so on. These formulas need full time
dimension.
I wonder there is possibility to concatenate all 3 members from virtual
dimension and use that aggregate as member for real Time dimension. For
example if user selects 2005 year, I Quarter and February from virtual
dimensions, we can assume, that member from real dimension could be
[2005].[I].[February], but how make that member and submit it to
formula for
later use.
In SQL we have possibility to construct SQL queries dynamically, and what
about MDX.
ErnestasJust an idea -
StrToTuple(
IIF( < original tuple >.Item(0).Dimension IS [VYear] AND < tuple
>.Item(0).Dimension IS [VQuarter] AND < tuple >.Item(0).Dimension IS
[VMonth], < Parse the unique names to create your real unique name using
VB
functions>, TupleToStr(<original tuple> ) )
)
"Ernestas" <sysojevas@.delfi.lt> wrote in message
news:efUjpNHNFHA.244@.tk2msftngp13.phx.gbl...
> Hello,
>
> There is a big problem - my client made 3 virtual dimensions (Year,
> Quarter, Month) from single time dimension and wants to use them, because
> the graphical interface in Excel is more friendly for end-users to have 3
> dimensions (i.e. 3 separate tabs).
>
> But in the same time we need to make MDX queries with time series with MDX
> formulas like YTD, LastPeriod and so on. These formulas need full time
> dimension.
>
> I wonder there is possibility to concatenate all 3 members from virtual
> dimension and use that aggregate as member for real Time dimension. For
> example if user selects 2005 year, I Quarter and February from virtual
> dimensions, we can assume, that member from real dimension could be
> [2005].[I].[February], but how make that member and submit it
to formula
> for later use.
>
> In SQL we have possibility to construct SQL queries dynamically, and what
> about MDX.
>
> Ernestas
>|||Correction:
StrToTuple(
IIF( < original tuple >.Item(0).Dimension IS [VYear] AND <
tuple.Item(1).Dimension IS [VQuarter] AND < tuple >.Item(2).Dimension IS
[VMonth], Parse the unique names to create your real unique name using
VB
functions>, TupleToStr(<original tuple> ) )
)
"Elad" <elad> wrote in message news:OZh2t9HNFHA.2372@.TK2MSFTNGP10.phx.gbl...
> Just an idea -
> StrToTuple(
> IIF( < original tuple >.Item(0).Dimension IS [VYear] AND < tuple
> [VMonth], < Parse the unique names to create your real unique name usi
ng
> VB functions>, TupleToStr(<original tuple> ) )
> )
>
> "Ernestas" <sysojevas@.delfi.lt> wrote in message
> news:efUjpNHNFHA.244@.tk2msftngp13.phx.gbl...
>

How can we format data in single MDX expression rather than MDX statement?

Hi, experts,

Thanks for your kind attention.

How can we format data by a single MDX expression rather than MDX statement? (e.g. I want to format measure member A with 2 decimal places. How can we achieve this in a single MDX expression?, not in complex MDX statement).

Hope it is clear for your help.

Thanks and I am looking forward to hearing from you shortly.

With kind regards,

Yours sincerely,

Hi,

Not to sure what you mean by expression rather than MDX Statement.

In a query it might look like:

with member [measures].[Percentage Of Region Total] as

[Measures].[Charge Out]/

([Measures].[Charge Out],[Campaign].[Campaign Region Hierarchy].[All Campaign Regions]),

format_string ='#.##%'

In the calculation script another might look like

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales Rev Variance]

AS ([Measures].[Sales Revenue])-([Measures].[Sales Revenue],[Revenue Recognition Date].[Financial Year].currentmember.prevmember),

FORMAT_STRING = "￡#,#",

NON_EMPTY_BEHAVIOR = { [Sales Revenue] },

VISIBLE = 1;

Hope that helps

Matt

|||

Or in the MDX Script of the cube with

Code Snippet

format_string([Measures].[Discount])="#,#.00";

HANNES

|||

Hi, Thanks very much, Matt and HANNES.

With kindest regards,

Yours sincerely,

How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:
>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT

How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:

>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT

How can I turn off parallelism for a single query?

I changed servers from a 2 xeon system to a 4 dual core system.
With the exact same database on each server, an update statement takes
3 minutes on the new server...and 2 seconds on the old server.
I already set the MAXDOP on the new system to 4 to mimic the old
system...I've also updated stats, rebuilt indexes, etc.
So I want to try running the update statement without parallelism to
see if it still takes 3 minutes.
Does anyone know the exact syntax to turn off parallelism each time
this update statement is run?
Here's the update statement. If I run just the select statements
separately that are used in the udpate statement...they all return in 1
second...so it looks like SQL server is finding the data that is needs
to update quickly.
But the update statement is taking minutes. It should only take 3
seconds. I've already dropped and recreated the indexes as well.
Anyone ever seen this before?
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
I would check the query plan of both versions and see if there are any
difference. Since the select is running fast and the update is
running slow, I would check for blocking (select needs a shared lock.
Update needs an exclusive lock. That makes a big difference). I would
also check for triggers in the updated table. In any case if you want
to check if the problem is due to parallelism, you can add at the end
of the query the option maxdop:
Update ClientContacts
SET StatusID = (select statusID from status where module = 'Contact'
AND
status.description = 'Current' and status.active = 1) where ContactID
IN
( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
Orders O ON CC.ContactID = O.contactID OR
CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
Inner join OrderCandidates OC ON
OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
OrderCandidates
where OrderCandidates.stageid = (select stageID from stages
where stages.stagename = 'Assigned/Placed' AND
stages.active = 1) ) )
option (maxdop 1)
Adi
mchi55@.hotmail.com wrote:
> I changed servers from a 2 xeon system to a 4 dual core system.
> With the exact same database on each server, an update statement takes
> 3 minutes on the new server...and 2 seconds on the old server.
> I already set the MAXDOP on the new system to 4 to mimic the old
> system...I've also updated stats, rebuilt indexes, etc.
> So I want to try running the update statement without parallelism to
> see if it still takes 3 minutes.
>
> Does anyone know the exact syntax to turn off parallelism each time
> this update statement is run?
> Here's the update statement. If I run just the select statements
> separately that are used in the udpate statement...they all return in 1
> second...so it looks like SQL server is finding the data that is needs
> to update quickly.
> But the update statement is taking minutes. It should only take 3
> seconds. I've already dropped and recreated the indexes as well.
> Anyone ever seen this before?
> Update ClientContacts
> SET StatusID = (select statusID from status where module = 'Contact'
> AND
> status.description = 'Current' and status.active = 1) where ContactID
> IN
> ( Select DISTINCT CC.ContactID From ClientContacts CC Inner join
> Orders O ON CC.ContactID = O.contactID OR
> CC.ContactID = O.SecondContactID OR CC.ContactID = O.ThirdContactID
> Inner join OrderCandidates OC ON
> OC.OrderID = O.OrderID WHERE O.OrderID IN (Select OrderID from
> OrderCandidates
> where OrderCandidates.stageid = (select stageID from stages
> where stages.stagename = 'Assigned/Placed' AND
> stages.active = 1) ) )
|||On 22 Jan 2007 00:32:00 -0800, mchi55@.hotmail.com wrote:

>Does anyone know the exact syntax to turn off parallelism each time
>this update statement is run?
Add this to the end of the UPDATE command itself:
OPTION ( MAXDOP 1 )
Roy Harvey
Beacon Falls, CT