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 needed with this sproc

Hi, I am trying to Implement Multi parameter...

If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.

This is my sproc

ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]@.ClientIdnvarchar(max)=NULL,@.StartDatedatetime,@.EndDatedatetimeASDeclare @.SQLTEXT nvarchar(max)If @.ClientIdISNULLBeginSelect o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInnerJoin ClientPlan cpon o.PlanId = cp.PlanIdInnerJoin ClientUser con o.CreatedByUserId = c.UserIdWHERE--cp.ClientId = @.ClientId--AND o.OrderDateBETWEEN @.StartDateAND @.EndDateORDER BYo.OrderIdDESCENDELSEBEGIN SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' + @.ClientId +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'execute (@.SQLTEXT)END

any help will be appreciated.

Regards

Karen

Try this:

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ' +Convert(varchar,@.StartDate) +' AND ' +convert(varchar, @.EndDate) +' ORDER BYo.OrderId DESC'Exec(@.SQLTEXT)

If you still have errors, post exactly how you are calling the proc.

|||

i am first execting the stored proc using query analyser and this is how i am calling it

usp_GetOrdersByOrderDate'7,4','12/17/2007','12/20/2007'

|||

ahh.. you need quotes around the dates too..

SELECT @.SQLTEXT ='Select o.OrderId,o.OrderDate,o.CreatedByUserId, c.LoginId,o.Quantity,o.RequiredDeliveryDate,cp.PlanId, cp.ClientPlanIdFROM[Order] oInner Join ClientPlan cp on o.PlanId = cp.PlanId Inner Join ClientUser c on o.CreatedByUserId = c.UserIdWHERE cp.ClientId in (' +Convert(Varchar,@.ClientId) +')AND o.OrderDate BETWEEN ''' +Convert(varchar,@.StartDate) +''' AND ''' +convert(varchar, @.EndDate) +''' ORDER BYo.OrderId DESC'

|||

thanks,,,

Yes that did it.

Regards

Karen