Dropdown menu help

I have a report with 4 parameters
2 of them are dropdown menus, one contains groups that populate the other
dropdown box with computer names. The report works great but I would like to
have an ALL function in the computer name dropdown box.
From poking around here I was able to plug a UNION SELECT 'All' AS
ComputerName into the dataset that builds the computername list but I am no
sql wiz by any stretch of the imagination and it seems that is just plugging
in All to the dropdown without any meaning behind it.
Has anyone done this sort of thing before or have any examples they could
share?
Thanks!
Kevinhttp://blogs.msdn.com/chrishays/archive/2004/07/27/199157.aspx
--
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.
"Kevin Serafin" <kevin.serafinISHEREATecolabDOTcom> wrote in message
news:enxx48jeEHA.2852@.tk2msftngp13.phx.gbl...
> I have a report with 4 parameters
> 2 of them are dropdown menus, one contains groups that populate the other
> dropdown box with computer names. The report works great but I would like
to
> have an ALL function in the computer name dropdown box.
> From poking around here I was able to plug a UNION SELECT 'All' AS
> ComputerName into the dataset that builds the computername list but I am
no
> sql wiz by any stretch of the imagination and it seems that is just
plugging
> in All to the dropdown without any meaning behind it.
> Has anyone done this sort of thing before or have any examples they could
> share?
> Thanks!
> Kevin
>|||Thanks Chris, works great!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:us4dvpleEHA.140@.TK2MSFTNGP12.phx.gbl...
> http://blogs.msdn.com/chrishays/archive/2004/07/27/199157.aspx
> --
> 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.
> "Kevin Serafin" <kevin.serafinISHEREATecolabDOTcom> wrote in message
> news:enxx48jeEHA.2852@.tk2msftngp13.phx.gbl...
> > I have a report with 4 parameters
> >
> > 2 of them are dropdown menus, one contains groups that populate the
other
> > dropdown box with computer names. The report works great but I would
like
> to
> > have an ALL function in the computer name dropdown box.
> >
> > From poking around here I was able to plug a UNION SELECT 'All' AS
> > ComputerName into the dataset that builds the computername list but I am
> no
> > sql wiz by any stretch of the imagination and it seems that is just
> plugging
> > in All to the dropdown without any meaning behind it.
> >
> > Has anyone done this sort of thing before or have any examples they
could
> > share?
> >
> > Thanks!
> >
> > Kevin
> >
> >
>

Drop replication question

Using SQL2005 SP2 transactional replication. Whenever we have a deploy to
production that contains DDL, I drop the publication and then drop the
subscription on the replicated server. Once the update is complete, I
recreate the publication with any changes that are necessary and then
re-create the subscription, and then the snapshot agent runs. Is that what
most people do?
This works fine, but I feel it is overkill. Is there a simpler way to
temporarily disable the replication until the DDL changes are made without
dropping and recreating the publication and subscription?
Is there any negative impact by my dropping and recreating the publication
and subscription.
Thanks
Ron
Many schema changes will be automatically replicated is the replicate_ddl
parameter is set to true for sp_addpublication and sp_addmergepublication.
There are some which you will have to do the drop subscriptions and drop
publications, for example changes to pk, adding defaults, identities, etc.
The problem with your approach is that there is add overhead and your
subscriber tables have to be recreated each time.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:A7ED2B66-F49D-4FD3-AD22-54CD8A54DAB9@.microsoft.com...
> Using SQL2005 SP2 transactional replication. Whenever we have a deploy to
> production that contains DDL, I drop the publication and then drop the
> subscription on the replicated server. Once the update is complete, I
> recreate the publication with any changes that are necessary and then
> re-create the subscription, and then the snapshot agent runs. Is that
> what
> most people do?
> This works fine, but I feel it is overkill. Is there a simpler way to
> temporarily disable the replication until the DDL changes are made without
> dropping and recreating the publication and subscription?
> Is there any negative impact by my dropping and recreating the publication
> and subscription.
> Thanks
> Ron
|||Just so I'm clear, I think what you're saying is that there isn't any
intermediate step such as disable or turn off replication temporarily, you
would need to drop subscription and publication.
We do have "replicate Schema Changes" = TRUE, however one of our deploy code
failed due to replication being active. I guess since not knowing exactly
what can and can't be replicated, it wouldn't hurt to simply drop publication
and subscription, perform the changes and then add the publication and
subscription back again. Since a deploy is done after hours and replication
currently only takes about 15 minutes to add publication and subscription as
well as run the snapshot agent, it sounds like it's the safest way to go as
the time it takes to recreate the publication and subscription isn't an issue
to us.
Thanks.
"Hilary Cotter" wrote:

> Many schema changes will be automatically replicated is the replicate_ddl
> parameter is set to true for sp_addpublication and sp_addmergepublication.
> There are some which you will have to do the drop subscriptions and drop
> publications, for example changes to pk, adding defaults, identities, etc.
> The problem with your approach is that there is add overhead and your
> subscriber tables have to be recreated each time.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:A7ED2B66-F49D-4FD3-AD22-54CD8A54DAB9@.microsoft.com...
>
>
|||Exactly, there is no step that makes replication disabled.
There are some schema changes which are not replicated as you have found
out. I would probably try the deploy code out in a test environment to find
out what breaks and then remove those articles from the publication, make
the change and then add them back in.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:57CDE6A7-E4C1-45D7-8F1F-02BCD8A763C6@.microsoft.com...[vbcol=seagreen]
> Just so I'm clear, I think what you're saying is that there isn't any
> intermediate step such as disable or turn off replication temporarily, you
> would need to drop subscription and publication.
> We do have "replicate Schema Changes" = TRUE, however one of our deploy
> code
> failed due to replication being active. I guess since not knowing exactly
> what can and can't be replicated, it wouldn't hurt to simply drop
> publication
> and subscription, perform the changes and then add the publication and
> subscription back again. Since a deploy is done after hours and
> replication
> currently only takes about 15 minutes to add publication and subscription
> as
> well as run the snapshot agent, it sounds like it's the safest way to go
> as
> the time it takes to recreate the publication and subscription isn't an
> issue
> to us.
> Thanks.
> "Hilary Cotter" wrote:
|||Thanks for your help and clarification.
"Hilary Cotter" wrote:

> Exactly, there is no step that makes replication disabled.
> There are some schema changes which are not replicated as you have found
> out. I would probably try the deploy code out in a test environment to find
> out what breaks and then remove those articles from the publication, make
> the change and then add them back in.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:57CDE6A7-E4C1-45D7-8F1F-02BCD8A763C6@.microsoft.com...
>
>

Drop an article through EM

Hi is there any way to drop an article in EM which is part of a larger
publication which contains other articles, as the article I wish to drop
appears with the tick next to it in the publications under articles but it
does not allow me to untick this which seems strange. Is this by design and
is my only choice to drop the article through using T-Sql replication commands
thanks for any advice Sammy
You have to drop active subscribers to do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:C9282CFC-CC9E-448D-A1C5-27D4A9284BDA@.microsoft.com...
> Hi is there any way to drop an article in EM which is part of a larger
> publication which contains other articles, as the article I wish to drop
> appears with the tick next to it in the publications under articles but it
> does not allow me to untick this which seems strange. Is this by design
> and
> is my only choice to drop the article through using T-Sql replication
> commands
> thanks for any advice Sammy
|||Sammy,
it's possible but only through scripts eg:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Drillthrough Security Conundrum AS 2005

Hi,

I'm looking for some inspiration on how to solve the following issue.

I'm working on a cube that contains crime data. I have a number of reports built using SSRS that show summary totals for different types of crime. In addition, there are a number of linked reports where the user can drill through to the underlying details of the crimes in the fact table fact table. These reports use the DRILLTHROUGH statement via the OLE DB provider for OLAP. So far, so good.

The client now wants to restrict drillthrough on certain types of crime.

Essentially, if a user is a member of Role1, they should be able to see the summary total for Crime A in the main report but they should NOT be able to drillthrough to the details of that summary

If the user is a member of Role2, they should be able to drill through to the detail level.

So far, I cannot see a way to do this with role permissions. My understanding and experience of the role based security thus far is as follows (please correct me if I'm wrong)

1) You can enable or disable drill through at the cube level for a particular role via the Cubes > Drillthrough Access property. This is too restrictive for my needs in this case

2) You can hide certain dimension members via Dimension Data tab but this results in the particular crime types not being displayed in the main report (i.e. you can't see the summary total for that crime)

3) You can restrict access to certain cell values via Cell Data but again this results in a value like N/A being displayed in the main report.

Has anyone faced a similar problem and come up with a way to do this?

Any feedback appreciated

NJDUNNE

The right way to approach this is through dimension security (Dimension Data tab). Drillthrough respects all the dimension security settings. You can restrict access to details, but make sure that Visual Totals=false, therefore you will see the summaries but won't be able to go to details.|||

Hi ,

Do u know if we can create drill through reports in SSRS through SSAS & Sql Server 2005 as the Database . I want to know how this can be done ?

Regards

Rashmi

Drillthrough Security Conundrum AS 2005

Hi,

I'm looking for some inspiration on how to solve the following issue.

I'm working on a cube that contains crime data. I have a number of reports built using SSRS that show summary totals for different types of crime. In addition, there are a number of linked reports where the user can drill through to the underlying details of the crimes in the fact table fact table. These reports use the DRILLTHROUGH statement via the OLE DB provider for OLAP. So far, so good.

The client now wants to restrict drillthrough on certain types of crime.

Essentially, if a user is a member of Role1, they should be able to see the summary total for Crime A in the main report but they should NOT be able to drillthrough to the details of that summary

If the user is a member of Role2, they should be able to drill through to the detail level.

So far, I cannot see a way to do this with role permissions. My understanding and experience of the role based security thus far is as follows (please correct me if I'm wrong)

1) You can enable or disable drill through at the cube level for a particular role via the Cubes > Drillthrough Access property. This is too restrictive for my needs in this case

2) You can hide certain dimension members via Dimension Data tab but this results in the particular crime types not being displayed in the main report (i.e. you can't see the summary total for that crime)

3) You can restrict access to certain cell values via Cell Data but again this results in a value like N/A being displayed in the main report.

Has anyone faced a similar problem and come up with a way to do this?

Any feedback appreciated

NJDUNNE

The right way to approach this is through dimension security (Dimension Data tab). Drillthrough respects all the dimension security settings. You can restrict access to details, but make sure that Visual Totals=false, therefore you will see the summaries but won't be able to go to details.|||

Hi ,

Do u know if we can create drill through reports in SSRS through SSAS & Sql Server 2005 as the Database . I want to know how this can be done ?

Regards

Rashmi