Report Builder: Filter drop down list unspecified

I have a report model which functioned correctly until I copied the project
over to another server and changed the data source for it. I can still build
a report that returns the correct data, but when I add a filter to the report
data, the ddl for the filter never loads the data and says 'unspecified'.
I'm not sure what the problem could be. Any ideas? ThanksAfter doing some hunting around I found this:
One of the most commonly used features in ad-hoc reporting is filtering. To
add filters to a report, select a field, and then click the Filter button. If
the InstanceSelection attribute enables drop-down, filter values will be
automatically populated. In article
http://msdn2.microsoft.com/en-us/library/aa964121.aspx#adhocrptsol_topic5
This is not the issue asthe InstanceSelection property is properly configured.
"Jason" wrote:
> I have a report model which functioned correctly until I copied the project
> over to another server and changed the data source for it. I can still build
> a report that returns the correct data, but when I add a filter to the report
> data, the ddl for the filter never loads the data and says 'unspecified'.
> I'm not sure what the problem could be. Any ideas? Thanks

Report Builder Filter drop down list - when the list is more than 500 values

Hi All,

I build a model (either from Cube or from tables) and open it in report builder. When I select a FIELTER on field which has few distinct values like state code, I get a drop down list. But when I select a filter on 'Dept Name', the drop down list on filter disapears. I have to manually key the filter value.

Can any one help me in getting a drop down list on filter, when distinct values in that column are more than few hundred.

Regards

Bobba

This is controlled by a property InstanceSelection, that can be set on each attribute of a dimension (within the dimension Editor).

The cube/dimension wizard initially sets this property, but then it can be changed as required.

Report

This is general scanario and I want some feedback from you guys.
I am planning to add new indexes, drop some indexes, and change other
indexes to composite and covering indexes. I want to create report that
shows the indexes used by queries before and after I make changes. What
should I do?
Can anyone tell me that which one would be the best solution.
1) SHOWPLAN_TEXT option. Use the output for the report.
2) Show Execution Plan option. Use output for the report.
3) Tuning Wizard against a Workload file. Use the output for the report.
4) DBCC SHOW_STATISTICS statement. Use the output for the report.
> 1) SHOWPLAN_TEXT option. Use the output for the report.
Gives you an text ouput of the execution plan. Easy for such documentation

> 2) Show Execution Plan option. Use output for the report.
Gives a pictorial output. Tough for documentation purpose hence.

> 3) Tuning Wizard against a Workload file. Use the output for the report.
Great for all recommendations. Needs a very good workload to arrive at a
decent solution and suggestions.

> 4) DBCC SHOW_STATISTICS statement. Use the output for the report.
Gives the histogram output of the Indexes. Good to know on the selectivity
activity.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Noor" <noor@.ngsol.com> wrote in message
news:uhgj1N%23jEHA.1996@.TK2MSFTNGP09.phx.gbl...
> This is general scanario and I want some feedback from you guys.
> I am planning to add new indexes, drop some indexes, and change other
> indexes to composite and covering indexes. I want to create report that
> shows the indexes used by queries before and after I make changes. What
> should I do?
> Can anyone tell me that which one would be the best solution.
> 1) SHOWPLAN_TEXT option. Use the output for the report.
> 2) Show Execution Plan option. Use output for the report.
> 3) Tuning Wizard against a Workload file. Use the output for the report.
> 4) DBCC SHOW_STATISTICS statement. Use the output for the report.
>

Replication-Replication Distribution Subsystem: agent (null) failed.

After migrate SQL 2000 replication to SQL 2005, by script and have maked some
changes, like drop some publications and have added many articles in one,
this message has appered so often:
Replication-Replication Distribution Subsystem: agent (null) failed. The
publication '<name of publication>'does not exist.
The application log of distributor machine is always with this message like
this.
What is the real problem, because some articles don't exist any more and
there aren't any errors or mistakes of any agents, I mean , the replication
are ok.
Unfortely, I didn't see any articles or foruns about the solution and these
problems impact with Replication alerts.
I have one machine with three sql instances of distribution functions. All of
them are administering many publications with many articles.
Sds!
Krisnamourt
Message posted via http://www.droptable.com
This could be a pull subscriber. Run profiler to see if you can see it logon
to the distributor and then capture it's hostname and visit the host and
drop the subscription there.
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7b995dc2c337c@.uwe...
> After migrate SQL 2000 replication to SQL 2005, by script and have maked
> some
> changes, like drop some publications and have added many articles in one,
> this message has appered so often:
> Replication-Replication Distribution Subsystem: agent (null) failed. The
> publication '<name of publication>'does not exist.
> The application log of distributor machine is always with this message
> like
> this.
> What is the real problem, because some articles don't exist any more and
> there aren't any errors or mistakes of any agents, I mean , the
> replication
> are ok.
> Unfortely, I didn't see any articles or foruns about the solution and
> these
> problems impact with Replication alerts.
> I have one machine with three sql instances of distribution functions. All
> of
> them are administering many publications with many articles.
> Sds!
> Krisnamourt
> --
> Message posted via http://www.droptable.com
>
|||Thanks Hilary,
I just dropped these agents and the pull jobs at subscriber and distributor,
but these message still appear at Distributor.
I thought these message would be some register from oldest row of
distribution table, but I couldn't identify any of them.
At moment, it's so difficult to "redo" all publication , because all of them
are at production replication, without any "windows time" to do quickly and
we just have done three weeks ago.
Any suggest to help me to find out where were all of these row register at
distribution system tables?
Sds!
Krisnamourt
Hilary Cotter wrote:[vbcol=seagreen]
>This could be a pull subscriber. Run profiler to see if you can see it logon
>to the distributor and then capture it's hostname and visit the host and
>drop the subscription there.
>[quoted text clipped - 24 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
|||I'd look to see if I can figure out when these agents are running or
logging. For example suppose you find they log these messages every 10
minutes. Then I would run profiler when you next expect it to run and see if
you can track down the culprit.
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bcba7853b95b@.uwe...
> Thanks Hilary,
> I just dropped these agents and the pull jobs at subscriber and
> distributor,
> but these message still appear at Distributor.
> I thought these message would be some register from oldest row of
> distribution table, but I couldn't identify any of them.
> At moment, it's so difficult to "redo" all publication , because all of
> them
> are at production replication, without any "windows time" to do quickly
> and
> we just have done three weeks ago.
> Any suggest to help me to find out where were all of these row register at
> distribution system tables?
> Sds!
> Krisnamourt
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>
|||Hilary,
I just followed your advice and see that application "Replication
Distribution History" send these message.
How can I delete history informatiom without damage any replication?
See the sequence:
if @.publication is not null and
exists (select * from dbo.MSpublications
where publication = @.publication and
publisher_id = @.publisher_id and
publisher_db = @.publisher_db and
independent_agent = 0)
The subscription to publication 'Pub_T400RECL' has expired or does not exist.
RAISERROR (21056, 16, -1, @.publication)
return (1)
Hilary Cotter wrote:[vbcol=seagreen]
>I'd look to see if I can figure out when these agents are running or
>logging. For example suppose you find they log these messages every 10
>minutes. Then I would run profiler when you next expect it to run and see if
>you can track down the culprit.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
|||I am sorry look for the entries in mspublications, then drop it in that
database.
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bcd3a3b13b7c@.uwe...
> Hilary,
> I just followed your advice and see that application "Replication
> Distribution History" send these message.
> How can I delete history informatiom without damage any replication?
> See the sequence:
> if @.publication is not null and
> exists (select * from dbo.MSpublications
> where publication = @.publication and
> publisher_id = @.publisher_id and
> publisher_db = @.publisher_db and
> independent_agent = 0)
> --
> The subscription to publication 'Pub_T400RECL' has expired or does not
> exist.
> --
> RAISERROR (21056, 16, -1, @.publication)
> --
> return (1)
> --
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>
|||look for an entry in the distribution database where the syspublications
table has entries for this publication.
Note the publisher and the publication database. Then go to it and drop the
publication
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bcd3a3b13b7c@.uwe...
> Hilary,
> I just followed your advice and see that application "Replication
> Distribution History" send these message.
> How can I delete history informatiom without damage any replication?
> See the sequence:
> if @.publication is not null and
> exists (select * from dbo.MSpublications
> where publication = @.publication and
> publisher_id = @.publisher_id and
> publisher_db = @.publisher_db and
> independent_agent = 0)
> --
> The subscription to publication 'Pub_T400RECL' has expired or does not
> exist.
> --
> RAISERROR (21056, 16, -1, @.publication)
> --
> return (1)
> --
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>
|||Hilary,
I did what you say to do, but I wasn't finding any register at distributor
tables that refer to any of these publication that no exists any more.
are there any kind of multiple register on Monitor replication? I mean, now I
have many Publication to see in only one SQL 2005 Monitor. When I add some
new publication, the Monitor connect to your distribution instance. Speaking
in numbers, It's about 6 distribution connect together in one Monitor.
What do you think? I can't imagine any other suggest to these problem!
Sds!
Hilary Cotter wrote:[vbcol=seagreen]
>I am sorry look for the entries in mspublications, then drop it in that
>database.
>[quoted text clipped - 29 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
|||Can you check each database on the publisher for these entries?
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bda14a7e07c4@.uwe...
> Hilary,
> I did what you say to do, but I wasn't finding any register at distributor
> tables that refer to any of these publication that no exists any more.
> are there any kind of multiple register on Monitor replication? I mean,
> now I
> have many Publication to see in only one SQL 2005 Monitor. When I add some
> new publication, the Monitor connect to your distribution instance.
> Speaking
> in numbers, It's about 6 distribution connect together in one Monitor.
> What do you think? I can't imagine any other suggest to these problem!
> Sds!
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>