Parameters, Security question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Parameters, Security question

Author

Topic

jthornton
Starting Member

3 Posts

Posted - 2005-01-18 : 10:15:56

I'm evaluating Reporting Services as I write and, maybe its just my newness to the product, but there doesn't seem to be a way (when interactivity is added) to pass multiple values in a single parameter by using a mult-select box. For instance, if I want to see sales by state but I don't want to see sales for all states or for just one state, it would be nice to be able to select multiple states using the ctrl key. Is this possible with Reporting Services?

Also, is there a way to use custom authentication for security? We have developed our own security tables and single sign-on processes which are stored within SQL Server and all of our web apps authenticate against those. Is it possible for Reporting Services to do the same?

Thanks in advance!

Jim Thornton

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-18 : 12:45:43

1) Not that I am aware of.
2) Yes you can use this type of security. Take a look at the Report Viewer app that comes with RS. We had to go down this route on one of our applications due to this same security architecture.

Tara

jhermiz

3564 Posts

Posted - 2005-01-18 : 17:12:43

It *looks* like you can't but you can...just have to tighten up that SQL, I know just from RS I've learned a lot more SQL in itself.

For an "All" option you could ttry:


CREATE PROCEDURE rsp_customer_sites @ClientID integer,
					    @CustomerID bigint
AS
SET NOCOUNT ON
SELECT     dbo.ClientCustomerSites.SiteID, dbo.Site.Site
FROM         dbo.ClientCustomerSites INNER JOIN
                      dbo.Site ON dbo.ClientCustomerSites.SiteID = dbo.Site.SiteID
WHERE dbo.ClientCustomerSites.ClientID=@ClientID AND dbo.ClientCustomerSites.CustomerID=@CustomerID
UNION SELECT Null, 'All'
ORDER BY dbo.Site.Site
SET NOCOUNT OFF
GO

You could also use "IN" statements and write an interface to pass specific say Customers that you want to run a report on. All this is possible.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-18 : 17:15:10

Jon,

But how does the user select CA, MI, WI, NV? He wants them to be able to use CTRL to highlight multiple entries in the drop down of a parameter.

Tara

jthornton
Starting Member

3 Posts

Posted - 2005-01-18 : 17:20:26

Thanks for your help Tara and jhermiz! I'll definitely take a closer look at the app that comes with RS, and I'll see what I can do with the stored procedure from jhermiz.

Would you guys know if there's a location in which Microsoft takes requests for new functionality in RS?

Thanks again!

Jim

jthornton
Starting Member

3 Posts

Posted - 2005-01-18 : 17:22:40

You gottit Tara! That's exactly what I need...Thanks!

jhermiz

3564 Posts

Posted - 2005-01-18 : 18:07:13

You can still do it, you have to write your own interface to this and pass the parameters to RS.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-18 : 18:11:37

If you write your own interface, then you'd pass the values in as CSV into one parameter. Then:

http://www.sqlteam.com/item.asp?ItemID=11499

Make sure to check out Page47s solution in the comment section for a set-based solution rather than looping in a UDF.

Tara

jhermiz

3564 Posts

Posted - 2005-01-18 : 18:43:31

I wish RS allowed for list boxes, the more I look at RS the more limited I see it is. Then again I could not stand crystal reports. To me this is much simplier to use than crystal reports. I really think they need to add a lot more to it though...especially if you look at the number of controls available.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-18 : 18:46:42

Agreed. But you have to keep in mind that they purchased this product from another company and then threw it in for free (sort of) with SQL Server 2000. With 2005 though, it got a huge rewrite and is supposed to have way more functionality.

And don't forget about SP2 for RS 2000. It's in beta now. Hopefully it'll address the problems that we've seen so far with it.

Tara

jhermiz

3564 Posts

Posted - 2005-01-19 : 14:17:02

quote:
Originally posted by tduggan

Agreed. But you have to keep in mind that they purchased this product from another company and then threw it in for free (sort of) with SQL Server 2000. With 2005 though, it got a huge rewrite and is supposed to have way more functionality.

And don't forget about SP2 for RS 2000. It's in beta now. Hopefully it'll address the problems that we've seen so far with it.

Tara

I agree, maybe the simple not so much functionality was a good idea. I know all of that bunched of functionality with crystal reports just made it hideous...to this day it makes me shiver

.

I love subscriptions though, that was really slick, and I do agree giving that out for a sql license is well worth it...

Anyone using crystal RUN NOW!!!! Don't let it take over your life

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources