Help on filtering dataset issue and do I mean HELP!.

I have the following filter expression on a dataset.
Under the Filter tab on the dataset
Expression:
=iif(CStr(Parameters!Bill_ID.Value) <> "All",
CStr(Fields!Bill_ID.Value) = CStr(Parameters!Bill_ID.Value),True)
Operator:
=
Value:
True
My report parameter Bill_ID is String datatype.
My field value Bill_ID from my dataset is a varchar(15).
Yet, when I preview the report with a legitmate parameter value I
receieve this error message.
"The processing of filter expression for "data set" cannot be
performed. The comparison failed. Please check the data type returned
by the filter expression.
How can I get this to work. I spent hours trying different
combinations. What am I doing wrong!! Please respond.Add "=" in front of the True to make it a boolean instead of a string
literal:
=True
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mossman" <tmosson1@.sbcglobal.net> wrote in message
news:1107572335.620117.23360@.o13g2000cwo.googlegroups.com...
> I have the following filter expression on a dataset.
> Under the Filter tab on the dataset
> Expression:
> =iif(CStr(Parameters!Bill_ID.Value) <> "All",
> CStr(Fields!Bill_ID.Value) = CStr(Parameters!Bill_ID.Value),True)
> Operator:
> => Value:
> True
> My report parameter Bill_ID is String datatype.
> My field value Bill_ID from my dataset is a varchar(15).
> Yet, when I preview the report with a legitmate parameter value I
> receieve this error message.
> "The processing of filter expression for "data set" cannot be
> performed. The comparison failed. Please check the data type returned
> by the filter expression.
> How can I get this to work. I spent hours trying different
> combinations. What am I doing wrong!! Please respond.
>|||Thank you Rob very much. I was going crazy trying to figure this out
on Friday.sql

Help on creating letter in RS

Hi,
I am trying to create report using Reporting Services 2000. My dataset returns 2 records. I want to generate two reports (one for each record) in a letter format. Can anybody tell me how do I design the report that to generate seperate letter for each record? Thanks.

Am I correct in assuming that you want a seperate page for each record?

Help Needed: DataSet

Should I create a new dataset in order to populate a multi select parameter
drop down box for users?
I am trying to capture all Companies stored in our backend database as a
drop down selection to users.
Thanks!Hi Terry,
You are correct. Thats how the guy explained it on the Reporting Services
webcasts.
The webcasts are quite informative. I would advise everyone to watch them.
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Should I create a new dataset in order to populate a multi select parameter
> drop down box for users?
> I am trying to capture all Companies stored in our backend database as a
> drop down selection to users.
> Thanks!|||Which webcast are you referring to?
Provide URL or link, if available?
Again, thanks!
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||I am getting an error when I create a new dataset and use it as a parameter.
Error:
[rsMissingDataSetName] The data set name is missing in the data region
â'DataSetNameâ'
CompanyLookup Dataset:
select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
from addresses
where len(rtrim(ADDR_Org_Name_1)) > 0
order by ADDR_Org_Name_1
"Stan" wrote:
> Hi Terry,
> You are correct. Thats how the guy explained it on the Reporting Services
> webcasts.
> The webcasts are quite informative. I would advise everyone to watch them.
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Should I create a new dataset in order to populate a multi select parameter
> > drop down box for users?
> >
> > I am trying to capture all Companies stored in our backend database as a
> > drop down selection to users.
> >
> > Thanks!|||The best thing to do is to go through the webcasts and see what you like.
But the ones I'm reffering to is a 5 day series. They touch on everything.
Follow the URL and scroll down till you see the webcast that starts on 07
March part 1
Very long URL :-)
http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
Hope this helps man...
R
S
--
http://dotnet.org.za/stanley
"Terry" wrote:
> Which webcast are you referring to?
> Provide URL or link, if available?
> Again, thanks!
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!|||Thank you for your assistance.
"Stan" wrote:
> The best thing to do is to go through the webcasts and see what you like.
> But the ones I'm reffering to is a 5 day series. They touch on everything.
> Follow the URL and scroll down till you see the webcast that starts on 07
> March part 1
> Very long URL :-)
> http://www.microsoft.com/events/AdvSearch.mspx?EventsAndWebcastsControlName=As1%3AAdvSrc&As1%3AAdvSrc%3AAudienceID=0&As1%3AAdvSrc%3AProductID=261ba873-f3ab-420e-96d6-e3004596a551&As1%3AAdvSrc%3AEventType=OnDemandWebcast&As1%3AAdvSrc%3ACountryRegionID=en%7CUS%7CUnited+States&StateProvinceID=0&As1%3AAdvSrc%3ATimeframeID=-1&As1%3AAdvSrc%3ASearchFilter=%C2%A0+Go+%C2%A0&PageNumber=3
> Hope this helps man...
> R
> S
> --
> http://dotnet.org.za/stanley
>
> "Terry" wrote:
> > Which webcast are you referring to?
> >
> > Provide URL or link, if available?
> >
> > Again, thanks!
> >
> > "Stan" wrote:
> >
> > > Hi Terry,
> > > You are correct. Thats how the guy explained it on the Reporting Services
> > > webcasts.
> > > The webcasts are quite informative. I would advise everyone to watch them.
> > >
> > > R
> > > S
> > > --
> > > http://dotnet.org.za/stanley
> > >
> > >
> > > "Terry" wrote:
> > >
> > > > Should I create a new dataset in order to populate a multi select parameter
> > > > drop down box for users?
> > > >
> > > > I am trying to capture all Companies stored in our backend database as a
> > > > drop down selection to users.
> > > >
> > > > Thanks!|||I hope I understand you correctly.
Just to clear things out.
You have a report that takes parameters, but you want the user to select the
parameter from a drop down box. Is this correct?
If that is the case, go to you Data Tab and click on new dataset. Write
your query that will bring back the company names. Now go to your layout
view and click on the report menu. Go to report parameters. Now select on
your company parameter and then click the radio button that says from query.
Select your new dataset in there. When you preview your report the dropdown
box should be populated.
Hope this helps man...
--
http://dotnet.org.za/stanley
"Terry" wrote:
> I am getting an error when I create a new dataset and use it as a parameter.
> Error:
> [rsMissingDataSetName] The data set name is missing in the data region
> â'DataSetNameâ'
> CompanyLookup Dataset:
> select DISTINCT ADDR_Org_Name_1, ADDR_Org_Name_2
> from addresses
> where len(rtrim(ADDR_Org_Name_1)) > 0
> order by ADDR_Org_Name_1
>
> "Stan" wrote:
> > Hi Terry,
> > You are correct. Thats how the guy explained it on the Reporting Services
> > webcasts.
> > The webcasts are quite informative. I would advise everyone to watch them.
> >
> > R
> > S
> > --
> > http://dotnet.org.za/stanley
> >
> >
> > "Terry" wrote:
> >
> > > Should I create a new dataset in order to populate a multi select parameter
> > > drop down box for users?
> > >
> > > I am trying to capture all Companies stored in our backend database as a
> > > drop down selection to users.
> > >
> > > Thanks!

help me with Paging problem sql server 2000

hello,
I need paging of records using stored procedure, so that i dont need to
fetch all the records in a dataset just to display a few (say 50 or 100)
in a datagrid web control (asp.net 1.1).
I am new to writing sql scripts but i 'ld come up with a solution using
ROWCOUNT. ( thanks to links given in newsgroups)
However, all the solutions provided 'ld give the next or previous pages.
I am providing page numbers in aspx page (not using any paging
mechanism of datagrid,rather depending only on sql server to give paged
records). I pass the last ID (The primary key, also Identity column)
from the datagrid page thats currently displayed, the Page difference (
current pageindex - requestedpage index) and the numof records per page
as parameters to the stored proc.
heres the stored proc..
CREATE PROCEDURE [dbo].[vitPageWiseProducts]
( @.PageDifference [int],
@.RecordsPerPage [int],
@.LowerID [int],
@.Direction [int],
)
AS
if @.PpageDifference <= 0 set @.PageDifference = 1
if @.RecordsPerPage = 0 set @.RecordsPerPage = 20
if @.LowerID = 0 set @.LowerID = 99
if @.Direction <=0 set @.Direction = 0
set NOCOUNT on
declare @.pages int,@.recs int,@.totrecs int
declare @.ttt table(
[ProductID] [int],
[ProductName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductDescription] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int],
[QOH] [int] NOT NULL ,
[ProductUnitPrice] [smallmoney] NOT NULL ,
[ProductImageURL] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductInsertDate] [smalldatetime] NULL ,
[SourceCountry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationCountry] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
)
declare @.restable table(
[ProductID] [int],
[ProductName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProductDescription] [nvarchar] (3000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int],
[QOH] [int] NOT NULL ,
[ProductUnitPrice] [smallmoney] NOT NULL ,
[ProductImageURL] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ProductInsertDate] [smalldatetime] NULL ,
[SourceCountry] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationCountry] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
)
set @.pages=@.PageDifference
set @.recs=@.RecordsPerPage
set @.totrecs = @.recs*@.pages
-- the count(*) is taking time when num of records r morethan 100,000
-- select @.@.NumOfPages=CEILING(count(*)/@.recs) from pp
-- if @.Direction = 0 means
if @.Direction = 0
begin
set ROWCOUNT @.totrecs
insert into @.ttt select * from pp where ProductID > @.LowerID --order by
ProductID DESC
set ROWCOUNT @.recs
insert into @.restable select * from @.ttt order by ProductID DESC
set ROWCOUNT 0
--set nocount on
select * from @.restable order by ProductID ASC
--set nocount off
end
if @.Direction >= 1
begin
set ROWCOUNT @.totrecs
insert into @.ttt select * from pp where ProductID < @.LowerID order by
ProductID DESC
set ROWCOUNT @.recs
--insert into @.restable select * from @.ttt order by ProductID ASC
--set ROWCOUNT 0
--set nocount on
select * from @.ttt order by ProductID ASC
end
set nocount off
GO
The table structure is the same as the declared table variable...
i dont know if this is the optimum way to solve the paging problem.
what r the divantages of the above stored proc.
please help me find a better solution as i am new to stored procs...
I do not know what does cumulative client processing time, and
cumulative wait time on server replies mean 'Can you post some sample data & expected results for other to test? In the
meantime, refer to: www.aspfaq.com/2120 for some ideas.
Anith|||Anith Sen wrote:
> Can you post some sample data & expected results for other to test? In the
> meantime, refer to: www.aspfaq.com/2120 for some ideas.
>
Thanks Anith ,
I 'ld come up with the stored procedure only after reading th elink
given by u in the post.
Horstmanns RowCount method. I wanted a where clause in my query to make
it efficient and fetch only few records when comapred to a large set (in
my case 180000).
I will post the sample data and aspx page in short time ..
Thanks again
NS

Help me please

Hi,
this is my first post..Please help me out!
I am using a dataset from Analysis services and i built a report in tabular
fashion with drilldown ( I used report wizard ). I also created a simple bar
chart...
Now my problem is:
Is it possible to make a drilldown in the graph as well...?
i.e.for example: if the graph loads initally with country (x-axis) and
sales(y-axis)
i need the graph to give me the sales data with individual states in the
country and split sales data..am i making sense?..
Thanks for your valuable time.
P.S: I am using Sql server 2005 Beta 2.
Sincerely,
SankarI answered your question on the MSDN Forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=22371
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sankar" <sankar_s_07@.hotmail.com> wrote in message
news:%23m8gjEDeFHA.3376@.TK2MSFTNGP10.phx.gbl...
> Hi,
> this is my first post..Please help me out!
> I am using a dataset from Analysis services and i built a report in
> tabular
> fashion with drilldown ( I used report wizard ). I also created a simple
> bar
> chart...
> Now my problem is:
> Is it possible to make a drilldown in the graph as well...?
> i.e.for example: if the graph loads initally with country (x-axis) and
> sales(y-axis)
> i need the graph to give me the sales data with individual states in the
> country and split sales data..am i making sense?..
> Thanks for your valuable time.
> P.S: I am using Sql server 2005 Beta 2.
> Sincerely,
> Sankar
>|||Thank you Robert..
I shall download the latest version.
Sincerely,
Sankar
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e$PYQPHeFHA.544@.TK2MSFTNGP12.phx.gbl...
> I answered your question on the MSDN Forum:
> http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=22371
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "sankar" <sankar_s_07@.hotmail.com> wrote in message
> news:%23m8gjEDeFHA.3376@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > this is my first post..Please help me out!
> >
> > I am using a dataset from Analysis services and i built a report in
> > tabular
> > fashion with drilldown ( I used report wizard ). I also created a simple
> > bar
> > chart...
> > Now my problem is:
> >
> > Is it possible to make a drilldown in the graph as well...?
> >
> > i.e.for example: if the graph loads initally with country (x-axis) and
> > sales(y-axis)
> > i need the graph to give me the sales data with individual states in the
> > country and split sales data..am i making sense?..
> >
> > Thanks for your valuable time.
> > P.S: I am using Sql server 2005 Beta 2.
> >
> > Sincerely,
> > Sankar
> >
> >
>

Help me getting started

I am new to ssrs,

Iam facing the following problem with reportviewer parameter

1) I have a dataset added to the project under app_code which has the following querry select * from emp

2) In the rdlc i have two columns empname,deptid,i have added a parameter called deptid with type as int

3) I have a webpage with reportviewer i have mapped the reportviewer to the rdlc file and datasource

4)on click of a button i want to display emp details from a particular deptid alone , so i added these lines on click of the button

ReportViewer1.ShowReportBody =true;

ReportParameter rpt =newReportParameter("deptid", Text1.Text);

this.ReportViewer1.LocalReport.SetParameters(newReportParameter[] { rpt });

But stil it is showing all the emp details

HI,dareprasanna:

After you setting the filter parameter to the ReportView, you should make it refresh to see the result.

ReportViewer1.LocalReport.Refresh();

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI Rex Lin,

Even after setting , ReportViewer1.LocalReport.Refresh();

I get all the details instead of getting those employee who are from the corresponding department,

Should i write a seperate method to fetch the employees of the corresponding dept or the reportviewer will automatically fetch the corresponding employees,

If i have not made my question clear in the previous question i will make myself clear now,

1) I added a dataset with the querry select empname,deptid from emp

2) In the rdlc i have added two details column (empname and deptid)
I have added one parameter by right clicking out of rdlc and added a parameter called 'deptid'

3) In the aspx page i have added reportviewer Choosed the rdlc i have created just above this point
I have not done any changes with ObjectDatasource1 which is created on associating reportviewer with rdlc

4) I have added the following piece of code in page load

ReportViewer1.ShowReportBody = true;
ReportParameter rpt = new ReportParameter("deptid", "39");
this.ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rpt });
ReportViewer1.LocalReport.Refresh();

5) Still i get all the employees instead of getting employee from deptid = 39

|||

HI,dareprasanna

Please double check your sql statement and

I'd rather do it in that way:

SELECT LastName, FirstName, EmailAddress, Phone, ContactID
FROM Person.Contact
WHERE (LastName LIKE @.LastName ) AND (Phone LIKE @.Area )

aspx code:

ReportViewer1.ShowReportBody = true;
ReportViewer1.Visible = true;
ReportViewer1.ProcessingMode = ProcessingMode.Local;

strLastName = txtLastName.Text.ToString().Trim();
strArea = txtArea.Text.ToString().Trim();

ReportParameter[] param = new ReportParameter[2];
param[0] = new ReportParameter("LastName", strLastName + "%", false);
param[1] = new ReportParameter("Area", strArea + "%", false);
ReportViewer1.LocalReport.SetParameters(param);

ReportViewer1.LocalReport.Refresh();

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI Rex Lin,

Thank you very much for your kind help,

This discussion has definetly given me some idea about ssrs and report viewer control,

Again Thanks a lot

Regards

prasanna

|||

You are welcome, prasanna.

It's my pleasure!