cascading parameters in a Webpart

[r388]

Hi,

In a Webpart, I have a report with 2 parameters.. the second parameter is dependent on the first parameter.. I have noticed that when I changed the first parameter, the values of the second parameter doesn't changed.., so I got an error..
What is the solution?

 

[Catadmin]

What is Webpart?

In Reporting Services, the solution is to fake out your second parameter. Instead of pulling it from a dataset that supplies 1 list of values, create a TempTable with an Identity column. Stick your value(s) from the first parameter into the Temp table, then do a SELECT INTO to get the values your regular 2nd param dataset would have contained.

Here's an example. I was doing a data dictionary. The first Parameter gives the user a choice of ALL TABLES or a table list. If ALL TABLES is chosen, the 2nd param is auto populated with ALL COLUMNS automatically (not allowing single columns to be chosen). If a specific table is chosen, the 2nd param populates with ALL COLUMNS and with a list of columns from that table.

Here's the code I used for the dataset which I set the 2nd Param to equal. Following is the stored procedure which does an IF/Then to return the proper result set.

[b]DataSet[/b]

exec ddsp_ListAllColumnNames @tblName     
 

[b]ddsp_ListAllColumnNames[\b]

Create Procedure ddsp_ListAllColumnNames @tblName varchar(255)
as
If  @tblName = 'All Tables'
    Begin
	Declare @collist Table (id int identity(1,1), ColName VarChar(255))

	Insert into @collist(ColName) Values('All Columns')

	Select ColName from @collist

    End
Else
    Begin
	Declare @collist2 Table (id int identity(1,1), ColName VarChar(255))

	Insert into @collist2(ColName) Values('All Columns')

	Insert into @collist2
	Select distinct c.[name] as ColName
	From sysobjects o inner join syscolumns c on o.[id] = c.[id]
	Where  o.xtype = 'U' and
		o.[name] = @tblName
	
	Select * from @collist2
	Order by id asc

    End

GO

Hope this helps you out.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[r388]

Thanks you for you response!
But for my application, I used OLAP cubes. Moreover, my application is integrated in a webpart. It is used in a teamplace with SharePoint. I have downloaded Rswebpart composant. I think that the problem is due to a configuration of my reporting services composants.

This is the code of my two parameters:
- the first:
select
{[Measures].[Unit Sales] } on columns,
{[Product].[Product Family].members} on rows
from Sales

-the second :
="WITH MEMBER [MEASURES].[Subcategory_nom] AS '[Product].CurrentMember.UniqueName' SELECT {[MEASURES].[Subcategory_nom] } ON AXIS(0), {DESCENDANTS({" & Parameters!category.Value & " }, [Product].[Product Department])} on AXIS(1) FROM [Sales]"

This application runs correctly in the reporting services server.

 

[GShort]

Web part is a part of Service pack 2 here is what the SP2 notes say on Web Parts:

4.1 New Functionality
4.1.1 Reporting Services SharePoint Web Parts
New in SP2. A set of Reporting Services SharePoint Web parts are installed with SP2. These Web parts are called Report Explorer and Report Viewer. Using Report Explorer, you can browse available reports on a report server. Using Report Viewer, you view reports hosted on a report server. Included with Report Explorer is a subscription function that allows you to receive reports by e-mail. Both Web parts are optimized to run within the SharePoint environment; however, they can be run as standalone components also.

These Web parts will work on SharePoint (SharePoint Portal Server or Windows SharePoint Services) and Microsoft SQL Server 2000 Reporting Services SP2.

Note To use the Reporting Services SharePoint Web parts, Report Server and Report Manager must both be installed.

Installing Web Parts
Web parts are delivered to a SharePoint server as a cabinet (.cab) file. If you want users within your organization to use this functionality, run the Stsadm.exe tool on the .cab file. To learn more about the Stsadm.exe tool and deployment of Web parts for Microsoft Windows SharePoint servers, see the Microsoft Developer Network Web site.

To install the .cab file from the command line, run the following code:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\BIN\STSADM.EXE -o addwppack -filename "C:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\SharePoint\RSWebParts.cab"
To run a Web part on SharePoint, the control must be added to the <SafeControls> section of the Web.config file for each virtual server that uses the Web part. While the Stsadm.exe tool automatically adds the control to the <SafeControls> section for the virtual server specified on the command line, you need to add the control to the <SafeControls> section of the Web.config file for each additional virtual server.

If you use the -globalinstall switch to add the Web parts to the global assembly cache (GAC), the strong name for the assembly must be used instead of the friendly name in the Web.config file.

When creating new virtual servers, you can add the Web part assembly to the <SafeControls> section of the default configuration file you are using. For more information about adding custom configuration settings for extending virtual servers, see the Microsoft Developer Network Web site.

Adding the Web Parts to a Web Part Page
Once the Web parts are installed, users can then add Report Explorer and Report Viewer Web parts to a Web Part Page through the Sharepoint window.

To add the Report Explorer and Report Viewer Web Parts to a Web Part Page

From an existing site in SharePoint, click the Create button in the SharePoint toolbar.


Scroll down to the Web Pages section, and then click Web Part Page.


Type a name, select a layout template, and then enter the location where you want to save your Web Part Page.
The Web Part Page appears and is divided into sections called zones.


Click the Create button at the bottom of the page.


From the new page, click Modify Shared Page, point to Add Web Parts, and then click Browse.


Select the name of the gallery where the Reporting Services Web parts were installed.


From the Web part list, select the Report Explorer Web part or the Report Viewer Web part, and then drag it to the zone where you want the Web part to appear.
Connecting Reporting Services Web Parts
If you add the Report Explorer and the Report Viewer Web parts to the same SharePoint page, you can connect them so that when you select a report in the Report Explorer, the report appears in the Report Viewer. If the Report Explorer and Report Viewer are not connected, the selected report appears in a separate page.

To connect the Report Explorer and Report Viewer Web Parts

Click Modify Shared Web Part.


On the Report Explorer toolbar menu, click the down arrow, point to Connections, point to Show Report In, and then click Report Viewer.


Click OK.

 

[r388]

Ok, you can see more informations in:

http://blogs.sqlxml.org/bryantlikes/articles/628.aspx

I have do all of that... Any ideas for my problem concerning cascading parameters?