Home page access

We are running the June CTP.
When some users go to htttp://theserver/Reports, all they see is the
word 'Home' and a line across the page.
This happens even though I have added these users as both
Administrators and Users.
I imagine that this has something to do with the domain, but I don't
see what arrangement would allow them to get to a partially completed
page.
Any ideas?
JimMake sure the RS services are running under a domain log in and that the
login has permissions on the Tempfiles directory under ths MSSQL.3
directory.
--
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
<jhcorey@.yahoo.com> wrote in message
news:1125062702.187423.141040@.g14g2000cwa.googlegroups.com...
> We are running the June CTP.
> When some users go to htttp://theserver/Reports, all they see is the
> word 'Home' and a line across the page.
> This happens even though I have added these users as both
> Administrators and Users.
> I imagine that this has something to do with the domain, but I don't
> see what arrangement would allow them to get to a partially completed
> page.
> Any ideas?
> Jim
>|||The services are running under a login that is an admin in the domain,
as well as an admin on the machine.
We notice that there is a user group on the machine called.
SQLServer2005ReportServerUser$MSSQLServer. Users that we've added to
this can use RS normally. But as I noted, users that we've added from
the web site cannot. These are all users in the same domain.|||My guess here is that they have not been setup in a role. The windows groups
are for validation of who they are, not what they can do. You need to assign
users or groups to a role (in report manager). Read up in books online about
roles. What I do is have a local group to that local group I add domain
groups and invidual users. I then assign the local group to the browser
role.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jhcorey@.yahoo.com> wrote in message
news:1125330865.896229.33860@.f14g2000cwb.googlegroups.com...
> The services are running under a login that is an admin in the domain,
> as well as an admin on the machine.
> We notice that there is a user group on the machine called.
> SQLServer2005ReportServerUser$MSSQLServer. Users that we've added to
> this can use RS normally. But as I noted, users that we've added from
> the web site cannot. These are all users in the same domain.
>|||Ah yes, it was simple of course once one knew where to look -- in the
properties for the home page.
I was thinking that System security would take care of everything.

Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

High Availability across different sites

Can someone give me a list of outside vendors that help support High
Availability of SQL across geographically dispersed locations. I know
Veritas, NSI, EMC have some solutions that eliminate the shared disk storage
limitation of clustering.. Thanks.Using SQL 2000Posting to groups again..
Can someone give me a list of outside vendors that help support High
Availability of SQL across geographically dispersed locations. I know
Veritas, NSI, EMC have some solutions that eliminate the shared disk storage
limitation of clustering.. Thanks.Using SQL 2000
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>|||I think Hitachi does as well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> storage
>|||if you go with hardware (EMC or Hitachi), you are usually going with
"synchronous" which will raise the ongoing cost of the solution in bandwidth
and limit the maximum distance apart that the nodes can be.
if you go with NSI, you can go any distance since its asynchronous and as a
software-only soluton, you wont have to buy to expensive Symetrix boxes and
SRDF.
jason
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> storage
>|||...though you would risk lose Microsoft support for your cluster by doing
so.
Regards,
John
"Jason Buffington" <jasonbuffington@.hotmail.com> wrote in message
news:%23$I4sjGdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> if you go with hardware (EMC or Hitachi), you are usually going with
> "synchronous" which will raise the ongoing cost of the solution in
bandwidth
> and limit the maximum distance apart that the nodes can be.
> if you go with NSI, you can go any distance since its asynchronous and as
a
> software-only soluton, you wont have to buy to expensive Symetrix boxes
and
> SRDF.
> jason
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> storage
>

High Availability across different sites

Posting to groups again..
Can someone give me a list of outside vendors that help support High
Availability of SQL across geographically dispersed locations. I know
Veritas, NSI, EMC have some solutions that eliminate the shared disk storage
limitation of clustering.. Thanks.Using SQL 2000
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
>I think Hitachi does as well.
--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> > Can someone give me a list of outside vendors that help support High
> > Availability of SQL across geographically dispersed locations. I know
> > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> storage
> > limitation of clustering.. Thanks.Using SQL 2000
> >
> >
>|||if you go with hardware (EMC or Hitachi), you are usually going with
"synchronous" which will raise the ongoing cost of the solution in bandwidth
and limit the maximum distance apart that the nodes can be.
if you go with NSI, you can go any distance since its asynchronous and as a
software-only soluton, you wont have to buy to expensive Symetrix boxes and
SRDF.
jason
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> > Can someone give me a list of outside vendors that help support High
> > Availability of SQL across geographically dispersed locations. I know
> > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> storage
> > limitation of clustering.. Thanks.Using SQL 2000
> >
> >
>|||...though you would risk lose Microsoft support for your cluster by doing
so.
Regards,
John
"Jason Buffington" <jasonbuffington@.hotmail.com> wrote in message
news:%23$I4sjGdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> if you go with hardware (EMC or Hitachi), you are usually going with
> "synchronous" which will raise the ongoing cost of the solution in
bandwidth
> and limit the maximum distance apart that the nodes can be.
> if you go with NSI, you can go any distance since its asynchronous and as
a
> software-only soluton, you wont have to buy to expensive Symetrix boxes
and
> SRDF.
> jason
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> > Posting to groups again..
> >
> > Can someone give me a list of outside vendors that help support High
> > Availability of SQL across geographically dispersed locations. I know
> > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> storage
> > limitation of clustering.. Thanks.Using SQL 2000
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> > > Can someone give me a list of outside vendors that help support High
> > > Availability of SQL across geographically dispersed locations. I know
> > > Veritas, NSI, EMC have some solutions that eliminate the shared disk
> > storage
> > > limitation of clustering.. Thanks.Using SQL 2000
> > >
> > >
> >
> >
>

High Availability across different sites

Can someone give me a list of outside vendors that help support High
Availability of SQL across geographically dispersed locations. I know
Veritas, NSI, EMC have some solutions that eliminate the shared disk storage
limitation of clustering.. Thanks.Using SQL 2000
Posting to groups again..
Can someone give me a list of outside vendors that help support High
Availability of SQL across geographically dispersed locations. I know
Veritas, NSI, EMC have some solutions that eliminate the shared disk storage
limitation of clustering.. Thanks.Using SQL 2000
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
|||I think Hitachi does as well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> storage
>
|||if you go with hardware (EMC or Hitachi), you are usually going with
"synchronous" which will raise the ongoing cost of the solution in bandwidth
and limit the maximum distance apart that the nodes can be.
if you go with NSI, you can go any distance since its asynchronous and as a
software-only soluton, you wont have to buy to expensive Symetrix boxes and
SRDF.
jason
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Posting to groups again..
> Can someone give me a list of outside vendors that help support High
> Availability of SQL across geographically dispersed locations. I know
> Veritas, NSI, EMC have some solutions that eliminate the shared disk
storage
> limitation of clustering.. Thanks.Using SQL 2000
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message news:...
> storage
>
|||...though you would risk lose Microsoft support for your cluster by doing
so.
Regards,
John
"Jason Buffington" <jasonbuffington@.hotmail.com> wrote in message
news:%23$I4sjGdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> if you go with hardware (EMC or Hitachi), you are usually going with
> "synchronous" which will raise the ongoing cost of the solution in
bandwidth
> and limit the maximum distance apart that the nodes can be.
> if you go with NSI, you can go any distance since its asynchronous and as
a
> software-only soluton, you wont have to buy to expensive Symetrix boxes
and
> SRDF.
> jason
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23XZ9R6faEHA.2516@.TK2MSFTNGP10.phx.gbl...
> storage
>

hideous join statement

just came across this JOIN stmt

wtf?!

LEFT OUTER JOIN (SELECT DealID, MAX(PersonIDInitiator) PersonIDInitiator, MAX(PersonIDExecutor) PersonIDExecutor, MAX(PersonIDSalesPerson) PersonIDSalesPerson FROM (SELECT DealID, CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator, CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor, CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson FROM dbo.DealRole WHERE RoleID BETWEEN 1 AND 3) dr GROUP BY DealID) Roles ON Roles.DealID = D.DealID

ugh!

can someone translate? why are there selects in the join?

You are dealing with what are called "derived tables". Please take a look at the way I have restructured your query:

LEFT OUTER JOIN
( SELECT DealID,
MAX(PersonIDInitiator) PersonIDInitiator,
MAX(PersonIDExecutor) PersonIDExecutor,
MAX(PersonIDSalesPerson) PersonIDSalesPerson
FROM ( SELECT DealID,
CASE WHEN RoleID = 1 THEN PersonID END PersonIDInitiator,
CASE WHEN RoleID = 2 THEN PersonID END PersonIDExecutor,
CASE WHEN RoleID = 3 THEN PersonID END PersonIDSalesPerson
FROM dbo.DealRole
WHERE RoleID BETWEEN 1 AND 3
) dr
GROUP BY DealID
) Roles
ON Roles.DealID = D.DealID

There are two different derived tables in your join. Derived tables are select expressions that can be substituted for and used for tables similar to the way that views can be substituted for and used in the place of tables. I colored the inner derived table in green. Note the inner derived table has "dr" for an alias that I colored brown.

The outer derived table includes the inner derived table so even though I have colored the inner derived table in green, it is still part of the outer derived table. Notice that the outer derived table has "Roles" for an alias and is colored blue.

I notieced by your "ugh" response to the derived table that you are initial repulsed by the use. This is not uncommon; however, derived tables are an integral part of many advanced queries and I would certainly recommend that you take time to learn the use.

Also, SQL Server 2005 introduced the use of "Common Table Expressions" -- CTEs. I would suggest that you might also want to give the CTE beasties a look

|||ok, so bearing in mind that the [deal role].[deal role id] appears to be a number incremented each time the person id on a deal is changed, then it would appear the code is wrong

i.e. it is returning the highest personid, rather than the person id of the highest role id

so if person A was id = 5 was the original initiator, but then person B with person id of 2 is then the originator, it would wrongly show A instead of B as 'current/latest initiator'|||Yes, it sounds like that logic problem exists. Are you using SQL 2005 or SQL Server 2000?|||sql2k5 sp2|||This looks like a job for the ROW_NUMBER function; please give a look to ROW_NUMBER() and OVER in books online while I cook up an example with what you have given.|||

Adolf:

Here is an example of code using (1) a derived table and (2) the ROW_NUMBER() function with the OVER, PARTITION BY and ORDER BY syntax:

Code Snippet

declare @.dealRole table
( drId integer,
DealID integer,
roleId integer,
PersonId integer,
eventDt datetime
)
insert into @.dealRole
select 1, 11, 1, 51, '3/4/7' union all
select 2, 11, 2, 57, '3/5/7' union all
select 3, 11, 3, 54, '3/8/7' union all
select 4, 11, 1, 47, '4/3/7' union all
select 5, 11, 2, 51, '4/3/7' union all
select 6, 11, 1, 44, '4/5/7' union all
select 7, 12, 1, 52, '4/8/7' union all
select 8, 11, 3, 55, '4/8/7' union all
select 9, 11, 4, 14, '4/15/7'

select dealId,
RoleId,
PersonId,
eventDt
from ( select dealId,
roleId,
row_number() over
( partition by dealId, roleId
order by eventDt desc, drId desc
) as seQ,
PersonId,
eventDt
from @.dealRole
where roleId between 1 and 3
) a
where seq = 1

/*
dealId RoleId PersonId eventDt
-- -- -- --
11 1 44 2007-04-05 00:00:00.000
11 2 51 2007-04-03 00:00:00.000
11 3 55 2007-04-08 00:00:00.000
12 1 52 2007-04-08 00:00:00.000
*/