2 questions (Cascading, direct links)

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

2 questions (Cascading, direct links)

Author

Topic

jhermiz

3564 Posts

Posted - 2004-11-02 : 07:45:38

I have 2 maybe simple questions,

My first...I noticed instead of taking users to http://servername/reports and having them sift through and find the report one can take them directly to a report like so:

http://mywebserver/ReportServer?%2fIMS-Reports%2fClient+Customer+Site+Commissions&ClientID=1

Or what not...so thats fine and dandy...however, is it possible to limit the data coming back right directly in this link. Notice the ClientID=1 that is a parameter in the report. Do I have to define EVERY parameter or can I just keep saying &Site='blah'&Customer='blah2'...etc... Meaning is there a way to modify the link to return a more drilled down recordset (kind of like a strict where clause).

Ok if that isnt possible my second question is ... how do I generate or modify parameters so that they are kind of like access cascading combo boxes. Meaning say I have a table of customers, and a table of plants / sites. The sites combo box should be empty until a customer is selected. Once a customer is selected, it should fill the sites for that selected customer.

Is this possible in RS to cascade combo boxes ?

The Reporting Services book I have doesn't really explain any of the advanced filtering or parameters, their example has 2 parameters and one is a date :(.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-02 : 13:00:31

Yes you can change the URL to further modify the report to be rendered. But the modification would be for the report parameters. So as long as you've got a parameter that gets what you want to render, then you can change it in the URL. SQL Server Reporting Services Books Online has a bit of information about modifying the URL.

For the cascading combo boxes, would customers and plants/sites be parameters? You can dynamically generate the list using another data set that is linked to the parameter.

Tara

jhermiz

3564 Posts

Posted - 2004-11-02 : 17:45:54

ok so from what it sounds like the report does need parameters itself, i cant just send parameters to a url.

for your second response, what I want to do is from the web side have combo boxes for these criteria, I can then cascade these combo boxes. For instance:

Customer Ford:

Ford
Romeo
Janesville

Customer GM

GM
Romeo
Mt. Pleasant

As you see the sites are different for these customers (except for romeo). So I have a table which stores all these and I can easily on the web cascade my combo boxes...
Now will these report parameters be cascaded in sql server reporting services is what Im asking. When the report opens that is...

I guess my question is how do I accomplish that ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-02 : 17:53:33

Yes you can do this. Let's say you have a parameter called Customer. So you've got @Customer. Your first parameter uses DataSet1 which is select * from customer where customername = @customer. Your second parameter is something else say CustomerRegion with select * from customerregion where customername = @customer. CustomerRegion is different per customer. So let's say they select @Customer = Ford and Ford has regions San Diego, Los Angeles, and New York. Once they select Ford, the second parameter's drop down list will switch to San Diego, Los Angeles, and New York.

Tara

jhermiz

3564 Posts

Posted - 2004-11-02 : 19:33:36

ok how will this second parameter know about @Customer?

When I select it will it understand which one im referring to, what about when I change it on the fly ?

SELECT RegionID, Region FROM Region
INNER JOIN CustomerRegions
ON CustomerRegions.RegionID = Region.RegionID
WHERE CustomerRegions.CustomerID=@Customer ???

Long day...thats prolly very wrong...im going home for the night!

Jon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-02 : 19:36:19

As long as the data set for the second parameter has @Customer in it, then it'll know about it when the user changes the value. So you've only got one @Customer. Every reference to it in your report will map to the same value.

Tara

jhermiz

3564 Posts

Posted - 2004-11-10 : 10:09:44

quote:
Originally posted by tduggan

As long as the data set for the second parameter has @Customer in it, then it'll know about it when the user changes the value. So you've only got one @Customer. Every reference to it in your report will map to the same value.

Tara

Thank You Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources