Couldn't make Filter Option of Report to work

I am trying to figure out how to create report using dynamic parameter and filter from dropdown. I saw a webcast that allow to drag and drop the filter functionality in the dataset and generate report. I have the SQL Server Beta 2 and VS 2005 Beta installed in my machine.
But I didn't see any option in data design that allow me to do the filtering. I can add report parameter but was unable to bind the parameter value to filter expression. I am really confuse and any help will be appreciated.

Are you using a relational database as your data source or Analysis Services? Only Analysis Services has the drag and drop filter functionality. If you are using relational, you have to add the query parameters and then the designer will autogenerate report parameters.

Take a look at some of the AdventureWorks sample reports and walkthroughs it should help.

|||Thank you Brian for your reply. It cleared lots of thing for me. Yeah I was try to get the filter from relational database.

I saw a demo of AdventureWorks sample in one MSDN event. I thought I can do the same with all the database.

I hadn't work with Analysis service before. I tried to get the AdventureWorks sample in my computer and I am SQL Server Beta 2(full including analysis and reporting service) and VS 2005 Beta 2. But I couldn't figure out how to get the sample working. I couldn't get the analysis service to get the AdventureWorks database hooked up. May be I have find more about analysis service. I am building a reporting project and may be it is a better solution..

But currently my problem is creating dynamic report from parameter selection. I tried the MSDN solution but I have more complex query. I am kind of lost using Dataset filters property. I have a paramter declared using Report Paramter. In my sql I have group by clause. I want to filter the record depending on the parameter selection at run time. Therefore, I was trying to use filters tab from dataset. But I don't know how to set the expression and value. I know I have to get the value from form paramter and the expression should be the field name but it is not working.

But thanks for showing me the right direction.|||Just install the relational database samples for AdventureWorks. There are several examples of dynamic parameters.

For any filter with a dynamic value list, you need two queries. The first brings back the list of values. The second uses a single value and has a parameter that is bound to a report parameter populated from the first query.

There is a tutorial in the Books Online called "Using a Dynamic Query in a Report" that has an example of this.

There are also several really good books on Reporting Services that might help. There is a list at http://www.microsoft.com/sql/reporting/techinfo/books.mspx.

could not bulk copy out of table 'contE6E5BDF7F1C24EC588142A934ACF00D1'

Hi all,
Sorry to post this one again but I have added some more info....
I have a sql2000 SP3 merge replication set up using dynamic filters
to 29 sites/publications so each site only see's it's own data.
I have been adding new tables to the schema using sp_mergearticle
(but that is another story !!). I have been running the
sp_mergearticle , running the snapshot, merging, new tables published
...etc OK. I did this 10 times and then hit the error The process
could not bulk copy out of table
'contE6E5BDF7F1C24EC588142A934ACF00D1'. when running the snapshot
agent for one of the publications.
I added the -output (Outputverboselevel 2) and got the follow info at
the end (it failes when it starts to bulk copy)
Bulk copying snapshot data for system table 'MSmerge_contents'
select * from cont21D0C82D8E3E47B8A3C3BC024732C0AC where 1 = 2
[9/16/2004 2:56:22 PM]GODZILLA.MearsData: select * from
cont21D0C82D8E3E47B8A3C3BC024732C0AC where 1 = 2
SourceTypeId = 5
SourceName = GODZILLA
ErrorCode = 8624
ErrorText = Internal SQL Server error.
The process could not bulk copy out of table
'cont21D0C82D8E3E47B8A3C3BC024732C0AC'.
Disconnecting from Publisher 'GODZILLA'
I ran the profiler whilst the snapshot was generated, I could see the
view contE6E5BDF7F1C24EC588142A934ACF00D1 being queried, then when it
errored it sent a drop command on contE6E5BDF7F1C24EC588142A934ACF00D1
(any anything else it had temp created) Is there any way I can capture
the view before it is dropped or stop the drop from occuring. I am sure
if I can get hold of this view then I will be able to find the fault.
I am confused as to why this has been fine on 10 of the publications
then suddenly decided to error.
The file versions I have are:
SQLSRV32.DLL = 2000.81.9042.0
SQLSRV32.RLL = 2000.81.9001.0
odbcbcp.dll = 2000.81.9042.0
Thanks
Ian
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I've had my share of problems with dynamic filters, but not the one you are
encountering. Can you drop this subscription and filter and then try to
recreate it using another folder?
If you want to catch this transitory view you are best to query it in the
publication database.
IE repeatedly run
sp_helptext contE6E5BDF7F1C24EC588142A934ACF00D1
Or run profiler to try to capture its creation
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ian bentley" <ian@.ianpb.fsnet.co.uk> wrote in message
news:eesd36JnEHA.556@.tk2msftngp13.phx.gbl...
> Hi all,
> Sorry to post this one again but I have added some more info....
>
> I have a sql2000 SP3 merge replication set up using dynamic filters
> to 29 sites/publications so each site only see's it's own data.
> I have been adding new tables to the schema using sp_mergearticle
> (but that is another story !!). I have been running the
> sp_mergearticle , running the snapshot, merging, new tables published
> ...etc OK. I did this 10 times and then hit the error The process
> could not bulk copy out of table
> 'contE6E5BDF7F1C24EC588142A934ACF00D1'. when running the snapshot
> agent for one of the publications.
> I added the -output (Outputverboselevel 2) and got the follow info at
> the end (it failes when it starts to bulk copy)
> Bulk copying snapshot data for system table 'MSmerge_contents'
> select * from cont21D0C82D8E3E47B8A3C3BC024732C0AC where 1 = 2
> [9/16/2004 2:56:22 PM]GODZILLA.MearsData: select * from
> cont21D0C82D8E3E47B8A3C3BC024732C0AC where 1 = 2
> SourceTypeId = 5
> SourceName = GODZILLA
> ErrorCode = 8624
> ErrorText = Internal SQL Server error.
> The process could not bulk copy out of table
> 'cont21D0C82D8E3E47B8A3C3BC024732C0AC'.
> Disconnecting from Publisher 'GODZILLA'
>
> I ran the profiler whilst the snapshot was generated, I could see the
> view contE6E5BDF7F1C24EC588142A934ACF00D1 being queried, then when it
> errored it sent a drop command on contE6E5BDF7F1C24EC588142A934ACF00D1
> (any anything else it had temp created) Is there any way I can capture
> the view before it is dropped or stop the drop from occuring. I am sure
> if I can get hold of this view then I will be able to find the fault.
>
> I am confused as to why this has been fine on 10 of the publications
> then suddenly decided to error.
> The file versions I have are:
> SQLSRV32.DLL = 2000.81.9042.0
> SQLSRV32.RLL = 2000.81.9001.0
> odbcbcp.dll = 2000.81.9042.0
>
> Thanks
> Ian
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hilary,
Thanks for your responses.
The trouble is the temp view gets a new name each time I run the
snapshot so I do not know what to look for with sp_helptext.
I could drop the publication/subscription but it has over 100 tables
with un-merged data so I will have to manually put the data in(unless
there is an easier way that I do not know about ?)
Regards,
Ian
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The way I view it you have about 4 options
1) call PSS
2) figure out the proc which is used to generate this view and modify it to
write a perm view.
3) create a new publication which has this problem filter in and deploy it
to the subscriber
4) backup the publication database and restore it on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ian bentley" <ian@.ianpb.fsnet.co.uk> wrote in message
news:eq1LVMMnEHA.608@.TK2MSFTNGP09.phx.gbl...
> Hilary,
> Thanks for your responses.
> The trouble is the temp view gets a new name each time I run the
> snapshot so I do not know what to look for with sp_helptext.
> I could drop the publication/subscription but it has over 100 tables
> with un-merged data so I will have to manually put the data in(unless
> there is an easier way that I do not know about ?)
> Regards,
> Ian
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||I've decided to raise a call at Microsoft. If I still cannot resolve
then I will just re-create the publication etc so I will loose nothing
by doing so (except a few ).
I will let you know the outcome.Thanks again for your responses.
Regards,
Ian
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!