Cascading(?) Parameters Help

I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.

Thanks!

Yes. Set the default on the text boxes to NULL.

Then it shouldnt try to populate until both are not null.

I just created a test report and it did not populate until both names were entered.

BobP

|||

In RS2005? I just did the same thing, and the 3rd Parameter became available after I entered something in the 1st parameter (no values because the query is where firstname = @.param1 and lastname = @.param2). However, the real query is using firstname LIKE @.param1 and lastname LIKE param2 and thus populates the list as soon as I enter the first name. Which is confusing because param2 is empty. Any ideas?

|||

Ah.. I see. I had the 'Allow blank value' selected. However, not pretty, it works.

|||

So it IS working for you, right?

BobP

|||Yes.. thanks.

Cascading query with parameters

I need to get at some information and this is the only way I know how to do it but I need to run these queries in order and using the previous query output. I don't know how to set up the output parameters so they can be used in the second query and the second query results to be used in the third query.

In a nutshell: The first query gets a number from a user as a parameter and pulls one record containing multiple fields. Some of those fields will be needed as parameters in the second query to pull mutltiple records. I need to put some where statements in to narrow down the result set. Then this part I haven't figured out either, but then the third query takes one record from the second result set and pulls mutliple records to put in a report.

I hope at least a good part of this makes sense. Please HELP ME!

ALTER PROCEDURE sp_getOfficerList

@.compositeNumber int

as

SELECT tblCampus.fldCampusCode, tblGroup.fldGroupCode, tblContract.fldGraduationMonthCode, tblComposite.fldCompositeNumber,

tblContract.fldContractCode, tblGroup.fldGroupName, tblCampus.fldCampusName

FROM tblComposite INNER JOIN

tblContract ON tblComposite.fldContractID = tblContract.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID INNER JOIN

tblCampus ON tblOrganization.fldCampusID =tblCampus.fldCampusID

WHERE (tblComposite.fldCompositeNumber = @.compositeNumber)

declare

@.campusCode varchar(10),

@.groupCode varchar(5),

@.graduationMonthCode varchar(2)

SELECT tblComposite.fldCompositeNumber

FROM tblContract INNER JOIN

tblComposite ON tblContract.fldContractID = tblComposite.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblCampus ON tblOrganization.fldCampusID = tblCampus.fldCampusID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID

WHERE (tblCampus.fldCampusCode = @.campusCode) AND (tblGroup.fldGroupCode = @.groupCode) AND

(tblContract.fldGraduationMonthCode = @.graduationMonthCode)

declare

@.lastCompositeNumber int

SELECT DISTINCT tblCameraCard.fldTitle

FROM tblCameraCard INNER JOIN

tblComposite_CameraCard_Link ON tblCameraCard.fldCameraCardID = tblComposite_CameraCard_Link.fldCameraCardID INNER JOIN

tblComposite ON tblComposite_CameraCard_Link.fldCompositeID = tblComposite.fldCompositeID

WHERE (tblComposite.fldCompositeNumber = @.lastCompositeNumber) AND (NOT (tblCameraCard.fldTitle IS NULL)) OR

(tblCameraCard.fldTitle = '')

Ok, let me rephrase my question. I want to take the result from query A and make it be the parameter for query B and take the result from query B and make that the parameter for query C. The results from query C is what I want to put in my report.

I did some research and found I could set a query equal to something. So I did that but how do I get that returned value to the next query?

Cascading parameters.

how to set a cascading parameter in the sql reporting(mdx)?

Use the StrToMember method. Using AdventureWorks, if you wanted to limit the list of Product Subcategories from the selected category, @.Category.

Code Snippet

WITH MEMBER [Measures].[ParameterLabel] AS

[Product].[Subcategory].CurrentMember.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS

[Product].[Subcategory].CurrentMember.UniqueName

SELECT {

[Measures].[ParameterLabel],

[Measures].[ParameterValue]

} ON COLUMNS,

NON EMPTY

{ [Product].[Subcategory].[Subcategory].Members }

ON ROWS

FROM [Adventure Works]

WHERE (

StrToMember(@.Category, CONSTRAINED)

)

Cascading Parameters, Drop down lists & the use of "ALL"

I have a report I'm working on (data dictionary) where the team wants to be
able to do the following:
1) Choose "All Tables" and get a report listing all tables with each tables
column names & properties underneath the proper table (ie, the columnName
param would be greyed out if this is chosen).
OR
2) Choose a single table by name, then choose "All Columns" and get a report
with ONLY that table and only the columns belonging to that table underneath
it with their properties.
OR
3) Choose a single table by name, then choose a single column by name and
only get that single column's properites.
Obviously, 3 is the easiest and what I was able to get done first. I have a
seperate report (my first report) where I've got a list of all tables with
drilldown enabled that shows each table's columns (all of them) when
expanded. Of course, once I got that report done, I was asked to add this
additional functionality.
Can this be done in one report or will I need to resort to multiple,
seperate reports to accomplish this?
I've got the code written for "All Tables" and "All Columns", but when I try
to pick a single table, I still get a ColumnList that lists all columns in
the entire DB, not the columns specific for that table.
Can someone give me advice on how to accomplish what I need? Thanks!!!
CatadminWe do something similar (although no cascading parameters). For "All", we
pass in the value zero, and the query is written like this:
Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
If you're doing actual database objects and are using the sysobjects and
syscolumns tables, I would imagine your syscolumns query would include a
similar clause:
Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid =@.ColumnID)
I don't use cascading parameters, so I may be completely missing the point,
and if so, I apologize...
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
>I have a report I'm working on (data dictionary) where the team wants to be
> able to do the following:
> 1) Choose "All Tables" and get a report listing all tables with each
> tables
> column names & properties underneath the proper table (ie, the columnName
> param would be greyed out if this is chosen).
> OR
> 2) Choose a single table by name, then choose "All Columns" and get a
> report
> with ONLY that table and only the columns belonging to that table
> underneath
> it with their properties.
> OR
> 3) Choose a single table by name, then choose a single column by name and
> only get that single column's properites.
> Obviously, 3 is the easiest and what I was able to get done first. I have
> a
> seperate report (my first report) where I've got a list of all tables with
> drilldown enabled that shows each table's columns (all of them) when
> expanded. Of course, once I got that report done, I was asked to add this
> additional functionality.
> Can this be done in one report or will I need to resort to multiple,
> seperate reports to accomplish this?
> I've got the code written for "All Tables" and "All Columns", but when I
> try
> to pick a single table, I still get a ColumnList that lists all columns in
> the entire DB, not the columns specific for that table.
> Can someone give me advice on how to accomplish what I need? Thanks!!!
> Catadmin
>|||I appreciate the suggestion, but I'm not sure it will work.
I want to be able to grey out the column parameter if "All Tables" is
chosen. Make it not accessible. Then, if a single table is chosen, not
print the other table on the report. My first problem is, that even when I
choose a single table, it prints out ALL the tables with the single column I
chose & all associated properties underneath all tables, even the tables that
have no such column. My second problem is creating the second parameter's
drop down list based only the columns associated with a single table picked
in the first parameter. My third problem is forcing all columns to
automatically be chosen, blocking out the second parameter, if "All Tables"
is chosen, so that all tables print, in order, with all of the columns
associated with that table and only that table.
Thank you for your time, though.
Catadmin
"DJM" wrote:
> We do something similar (although no cascading parameters). For "All", we
> pass in the value zero, and the query is written like this:
> Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
> If you're doing actual database objects and are using the sysobjects and
> syscolumns tables, I would imagine your syscolumns query would include a
> similar clause:
> Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid => @.ColumnID)
> I don't use cascading parameters, so I may be completely missing the point,
> and if so, I apologize...
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
> >I have a report I'm working on (data dictionary) where the team wants to be
> > able to do the following:
> >
> > 1) Choose "All Tables" and get a report listing all tables with each
> > tables
> > column names & properties underneath the proper table (ie, the columnName
> > param would be greyed out if this is chosen).
> >
> > OR
> >
> > 2) Choose a single table by name, then choose "All Columns" and get a
> > report
> > with ONLY that table and only the columns belonging to that table
> > underneath
> > it with their properties.
> >
> > OR
> >
> > 3) Choose a single table by name, then choose a single column by name and
> > only get that single column's properites.
> >
> > Obviously, 3 is the easiest and what I was able to get done first. I have
> > a
> > seperate report (my first report) where I've got a list of all tables with
> > drilldown enabled that shows each table's columns (all of them) when
> > expanded. Of course, once I got that report done, I was asked to add this
> > additional functionality.
> >
> > Can this be done in one report or will I need to resort to multiple,
> > seperate reports to accomplish this?
> >
> > I've got the code written for "All Tables" and "All Columns", but when I
> > try
> > to pick a single table, I still get a ColumnList that lists all columns in
> > the entire DB, not the columns specific for that table.
> >
> > Can someone give me advice on how to accomplish what I need? Thanks!!!
> >
> > Catadmin
> >
>
>

Cascading Parameters with no default

Let's say I have 3 dropdown Sites->Depts->Areas
Each ones have their dataset, and use from query.
How can I only have Sites with a default and not Depts?
If Sites has a default, it populates Depts automatically with values and
<Select a value>. That's fine. But in order to make the report work, I also
have to put a default to Depts so Areas gets something with <Select a value>
otherwise I get an error "The value provided for the report parameter
'AreaId' is not valid for its type".
I don't want that, I want something like this:
Sites Depts Areas
<Site A> <Select a value> <Select a value>
ThanksHello joe,
When you use the Cascading Parameters, how you query for the Areas?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||From query.
"Wei Lu [MSFT]" wrote:
> Hello joe,
> When you use the Cascading Parameters, how you query for the Areas?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||hello,
I would like to get your query.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Sites
--
ALTER PROCEDURE [dbo].[repGetSites]
(
@.parentid int=null
)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
if @.parentid is null
Select * from sites
else
Select * from sites where parentid=@.parentid
END
Depts
--
ALTER PROCEDURE [dbo].[repGetDepts]
(
@.siteid int=null
)
AS
BEGIN
SET NOCOUNT ON;
if @.siteid is null
Select DeptId,DeptName,parentid from Departments
else
Select DeptId,DeptName,parentid from Departments where parentid=@.siteid
END
Areas
--
ALTER PROCEDURE [dbo].[repGetAreas]
(
@.parentid int=null
)
AS
BEGIN
SET NOCOUNT ON;
if @.parentid is null
select Null as AreaId,'Any' as AreaName
union
Select AreaId,AreaName from Areas
else
if exists(Select AreaId,AreaName from Areas where parentid=@.parentid)
select Null as AreaId,'Any' as AreaName
union
Select AreaId,AreaName from Areas where parentid=@.parentid
else
select Null as AreaId,'Any' as AreaName
END
Parameters definition
--
<ReportParameter Name="siteid">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>Sites</DataSetName>
<ValueField>SiteId</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Site:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Sites</DataSetName>
<ValueField>SiteId</ValueField>
<LabelField>SiteName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="deptid">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>Departments</DataSetName>
<ValueField>DeptId</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Department:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Departments</DataSetName>
<ValueField>DeptId</ValueField>
<LabelField>DeptName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Areaid">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<AllowBlank>true</AllowBlank>
<Prompt>Area:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Areas</DataSetName>
<ValueField>AreaId</ValueField>
<LabelField>AreaName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
By the way, is there any way I can change the layout of how Parameters are
displayed?
Thanks for your help. Much appreciated.
"Wei Lu [MSFT]" wrote:
> hello,
> I would like to get your query.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Joe,
I tested on my side.
The report server could not valid for the third cascading parameter if the
second one did not have default value.
Also, why your Area dataset is using the @.parentId parameter?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Yes Wei, each parameter in the chain must get a valid param from previous.
The thing is why the report engine is not letting the user doing the
selection one at the time? It is so common behaviour!! You select one value
from first dropdown (parent), the second dropdown(child) gets the param
value, execute the query and populate its list. You select a value from
second dropdown(parent for third dropdown), the third dropdown(child) gets
the param value, execute the query and populate its list. You select a value
from third dropdown and there you go, report generates! I can't imagine that
it is not feasible! What a big flaw it is if we can't do it! I will have to
use the ReportViewer Web Control and handle this myself and pass the
parameters to the report and I don't like that. Reporting Services is
suppose to save me time... well... it is not.
I am surprised not seeing many posts complaining about that. Anyway I do.
Not counting the fact that we cannot disposed the parameters the way we want
on that toolbar...
About the Area parent id, it is there because I reused the query from
something else.
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello, Joe
Please submit your idea to the http://connect.microsoft.com/sqlserver.
The product team would monitor this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

cascading parameters with multi-select

In SQL 2005 report server. When viewing a report that uses cascading
parameters with multi-select and one of the lower level cascading
parameters only populates with one item (that's all the query brought
back), the scroll left and right bar (for the parmeter box) covers up
the item when the item is longer in length than the parameter box.
Short parameter names are no problem. Is there any way to make the
parameter box wider or the drop down longer. This seems to be a bug as
it doesn't add any length when displaying the scroll bar.All of that business is out of our control... unfortunately... There are
many times when adjusting the parameter layout and size would be useful...
When it is really necessary, I put an HTML page in front of the report which
gathers the parameters... then calls the report...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Jim" wrote:
> In SQL 2005 report server. When viewing a report that uses cascading
> parameters with multi-select and one of the lower level cascading
> parameters only populates with one item (that's all the query brought
> back), the scroll left and right bar (for the parmeter box) covers up
> the item when the item is longer in length than the parameter box.
> Short parameter names are no problem. Is there any way to make the
> parameter box wider or the drop down longer. This seems to be a bug as
> it doesn't add any length when displaying the scroll bar.
>

Cascading Parameters ValidValues via the Webservices?

I'd like to access validvalues in a report through the webservices. Anyone
know a way to get validvalues of a parameter that is dependent on a chosen
value of another param? Thanks.If the valid values query for the second parameter (B) references the value of the first parameter
(A), the valid values list of B should dynamically change based on A.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Landis" <landiss@.bus.oregonstate.edu> wrote in message
news:um8bvJUWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> I'd like to access validvalues in a report through the webservices. Anyone
> know a way to get validvalues of a parameter that is dependent on a chosen
> value of another param? Thanks.
>|||Can I do this via the webservices? I'm still not clear on how to do it.
The query for the second parameter (B) is parameterized using the first
parameter (A) and works fine using the report manager to render the report.
However, if I do
ReportParameter[] parameters = rs.GetReportParameters(myReportPath, null,
false, null, null);
then parameters[0] will have valid values, but parameters[1] won't yet.
This makes sense, because it doesn't have a value from the first parameter
to query with. Is there a way for me to now send a value picked out of the
first parameter's valid values back to the server and get the valid values
for the second parameter? I've tried recalling GetReportParameters with a
ParameterValue[], but that doesn't seem to work.
Thanks.
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:OqkzusUWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> If the valid values query for the second parameter (B) references the
value of the first parameter
> (A), the valid values list of B should dynamically change based on A.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Steve Landis" <landiss@.bus.oregonstate.edu> wrote in message
> news:um8bvJUWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > I'd like to access validvalues in a report through the webservices.
Anyone
> > know a way to get validvalues of a parameter that is dependent on a
chosen
> > value of another param? Thanks.
> >
> >
>|||Hi Steve:
I have an example here: http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
On Wed, 23 Jun 2004 09:52:12 -0700, "Steve Landis"
<landiss@.bus.oregonstate.edu> wrote:
>I'd like to access validvalues in a report through the webservices. Anyone
>know a way to get validvalues of a parameter that is dependent on a chosen
>value of another param? Thanks.
>|||Thanks!
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:p3njd0t8smjh6kun5dnu1vocpu7qe24vui@.4ax.com...
> Hi Steve:
> I have an example here: http://odetocode.com/Articles/123.aspx
> HTH,
> --
> Scott
> http://www.OdeToCode.com
> On Wed, 23 Jun 2004 09:52:12 -0700, "Steve Landis"
> <landiss@.bus.oregonstate.edu> wrote:
> >I'd like to access validvalues in a report through the webservices.
Anyone
> >know a way to get validvalues of a parameter that is dependent on a
chosen
> >value of another param? Thanks.
> >
>

Cascading Parameters Unreliable

I find that cascading parameters work intermittently. In addition, a
report with a drop down list will sometimes attempt to execute before
the user has made a selection.make sure you have the parameters in the proper order for cascading to work
properly. i.e. country parm first in your parameter list, state parameter
second, city parameter third... all the cascading parameters have to be
ahead of your other non-cascading parameters. to ensure the report will not
execute before a user selects parms... just leave 1 parm without default
value.
"mjhillman@.msn.com" wrote:
> I find that cascading parameters work intermittently. In addition, a
> report with a drop down list will sometimes attempt to execute before
> the user has made a selection.
>|||I am calling two stored procedures that take the same parameter. I
only have one parameter in the parameter list. Does this suggestion
apply to this scenario as well? Do I have to modify the SP to use a
different parameter name? (I did not write the SPs I am just executing
reports against an existing WMS.). Thanks.

Cascading parameters problem : First parameter is textbox

Hi,
I am using cascading parameters in my report. The first paramter is a
servername which user types in a TextBox. The second parameter is
populated based on the first parameter.
The problem is secord paramter drop down list doesn't get populated
unless I press "View Report" button in the preview mode.
Is there any way to populate second parameter (drop downlist) as user
finished typing the first parameter (TextBox)
please help,
regards,
SAchinUser needs to press "Tab" key to populate and need not press "view report"
Amarnath
"sachin laddha" wrote:
> Hi,
> I am using cascading parameters in my report. The first paramter is a
> servername which user types in a TextBox. The second parameter is
> populated based on the first parameter.
> The problem is secord paramter drop down list doesn't get populated
> unless I press "View Report" button in the preview mode.
> Is there any way to populate second parameter (drop downlist) as user
> finished typing the first parameter (TextBox)
> please help,
> regards,
> SAchin
>

Cascading Parameters problem

Are there any way to update parameter values first after a several Cascading Parameters (text fileds) is set?

Ex:
First I would like to fill the parameter fields (text fields):
From Date - To Date,
From Customer - To Customer

then the process begins to filter the rest of the fields values.

ChristoferYou could put the fields that you want filtered by the Date and Customer into a subreport then filter the subreport by the value in the Date and Customer fields.

Cascading parameters problem

Hi,

I have a problem with cascading parameters. I have 10 parameters in my report.

Parameters 1, 2, 9 and 10 are independant. Parameters 3 to 5 are cascading parameters as well as parameters 6 to 8.

Parameters 3 to 5 have no relationship with 6 to 8. But when I change the value for parameter 3 the values for parameters 4 to 10 are reset ?! I don't understand why, only parameters 4 to 5 should be reset.

Can someone please tell me how to avoid parameters 6 to 10 being reset ?

Are you sure that parameters 4 to 10 don't reference the value in parameter 3? With referencing I mean as well by queryparameters as by default values.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Parameter 3 is not referenced in query for parameters 6 to 10. Parameters 6 to 10 have default values but the default values are not related to the values selected in parameters 3 to 5|||

Can you sent me the RDL file than I will take quick look at it.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Cascading Parameters problem

I am using two parameters in my report. DepartmentID and EmployeeID. The employeeID is cascaded and it expects a DepartmentID as the query parameter in stored proc. The sp works fine in SQL and even from the datasets created in report designer. But when I preview the report and select a department to get the employee list populated, I get the following weirdo error:

An error occured during local report processing

The value provided for the report parameter 'EmployeeID' is not valid for its type.

I have checked and rechecked, and had some one else take a look at the report params, data types, and stored procs, but for the life of it, I can't figure out why I am getting this error? It seems the report designer is goofed up or something. Any ideas?

Never mind,, I resolved it. Somehow the allow blank values was highlighted even though the parameter data type was selected as integer.

Cascading Parameters problem

I am using two parameters in my report. DepartmentID and EmployeeID. The employeeID is cascaded and it expects a DepartmentID as the query parameter in stored proc. The sp works fine in SQL and even from the datasets created in report designer. But when I preview the report and select a department to get the employee list populated, I get the following weirdo error:

An error occured during local report processing

The value provided for the report parameter 'EmployeeID' is not valid for its type.

I have checked and rechecked, and had some one else take a look at the report params, data types, and stored procs, but for the life of it, I can't figure out why I am getting this error? It seems the report designer is goofed up or something. Any ideas?

Never mind,, I resolved it. Somehow the allow blank values was highlighted even though the parameter data type was selected as integer.

Cascading parameters problem

I have the following conundrum:
I have a report that utilises 3 cascading report parameters: region, area manager and business manager. They are in the correct order in the list of report parameters i.e. region then area manager then business manager.
When I select the region, then an area manager within that region, after having made the selection the area manager dropdown list changes to some arbitrary choice.
I also have a textbox equal to the label value of the area manager report parameter and this label displays something entirely different from my choice and the one that seems to be the "default".
Please note that I don't have any actual defaults for the parameters.

My question is why does the area manager dropdown change after the postback from my choice to something other?Found out what the problem was.
When using a dataset to populate a report parameter it would appear that the Value has to be unique / distinct, otherwise it looks like on the postback, it selects the first or whatever label it comes across.
Oh well, live and learn I guess

cascading parameters in Webpart

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..
Can anybody help me?Do you get the same behavior when not in the webpart?
--
| Thread-Topic: cascading parameters in Webpart
| thread-index: AcWCyilGfXMDE2rkQn6X17V43/TYaw==| X-WBNR-Posting-Host: 195.6.59.36
| From: "=?Utf-8?B?cjM4ODA0Mg==?=" <r388042@.discussions.microsoft.com>
| Subject: cascading parameters in Webpart
| Date: Thu, 7 Jul 2005 01:02:03 -0700
| Lines: 7
| Message-ID: <9C86DC82-335A-4E95-93FB-4D3DE94B72A4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47555
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| 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..
| Can anybody help me?
|
||||No. The application runs correctly in Visual Studio.net or in the reporting
services server.
I thinks that the problem is due to the parameters or a property of the
Webpart. But I don't know exactly.
""Brad Syputa - MS"" wrote:
> Do you get the same behavior when not in the webpart?
> --
> | Thread-Topic: cascading parameters in Webpart
> | thread-index: AcWCyilGfXMDE2rkQn6X17V43/TYaw==> | X-WBNR-Posting-Host: 195.6.59.36
> | From: "=?Utf-8?B?cjM4ODA0Mg==?=" <r388042@.discussions.microsoft.com>
> | Subject: cascading parameters in Webpart
> | Date: Thu, 7 Jul 2005 01:02:03 -0700
> | Lines: 7
> | Message-ID: <9C86DC82-335A-4E95-93FB-4D3DE94B72A4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47555
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | 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..
> | Can anybody help me?
> |
> |
>

Cascading Parameters in Report Builder Reports!

Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.

(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).

Thanks in advance

I need to know if it is possible. I have checked Report Builder and didn't find a way for it.

I am curios if i have missed something or if Report Builder doesn't support cascadign parameters.

Cascading Parameters in Dev environment Convulsing

I'm usring RS SP2 and have a cascading parameter. Both parameters are drop
down list. When you change the first drop down, the dev environment goes
into convulsions and starts flashing. I've done some research and found this
issue in a few places on the net but have not found a solution. All
indications ar ethat it will work fine once deployed. Anyone know of the
solution to this issue or a work around?I don't have this happen with all cascading parameter reports, just some of
them. I don't have a pattern for when it happens and when it doesn't. For
those reports where this happens I test by deploying to a test server rather
than testing from the dev environment. As you say, they work when deployed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen Farmer" <Stephen.Farmer@.LucrumInc.Com> wrote in message
news:eADozujWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> I'm usring RS SP2 and have a cascading parameter. Both parameters are drop
> down list. When you change the first drop down, the dev environment goes
> into convulsions and starts flashing. I've done some research and found
> this issue in a few places on the net but have not found a solution. All
> indications ar ethat it will work fine once deployed. Anyone know of the
> solution to this issue or a work around?
>
>

cascading parameters in a Webpart

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..
Can you help me?Hi,
I am having the exact same problem. It seems that the default values set initially in the report aren't being carried over to all of the parameters in the web parts.
I have not been able to find a solution yet. Please let me know if you have an answer.
Thanks!|||Hi
I got something similar did you get an answer?

Cascading Parameters error

I am new to SQL Reporting services - (3 weeks in fact)
I have a report that uses cascading parameters down 2 levels
I select from a dropdown which populates the second dropdown.
I then select from the second drop down list.
There are three more parameters to be entered in by the user - (these
are not drop down lists)
type of test
start date
end date
This runs fine in the test environment
I deploy
then select from my first dropdown.
It crashes out because start date and end date are not entered
It appears that the page is refreshing with all the parameters even
though they are not entered.
Any help is greatly appreciated.
ThanksTry changing the order of your parameters. Put the start and end date
at the beginning.
Or put in defaults. Start and end date of yesterday and today for
example.|||How are you implementing cascading parameters. I have been needing to do
something like this, but do not see how it would be done.
Thanks
John
"Ches Weldishofer" wrote:
> Try changing the order of your parameters. Put the start and end date
> at the beginning.
> Or put in defaults. Start and end date of yesterday and today for
> example.
>|||I am not experienced enough to explain it to you correctly - RS books
online explains it quite well|||Changing the order etc as you say works fine
Thank you very much
Can you recommend a good book for a beginner?

Cascading Parameters based off Analysis Services

Hello,

I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?

For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.

When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.

Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].

Thank you.

-Gumbatman

Yes it is possible. When you use the query designer, by default each parameter depends on the one before it having a value, even if the parameters are not really related. For this, you want to maintain that link. Then, in your MDX that creates the dataset for parameter #2 you can refer to parameter #1 (it will be called something like @.MyParameterName), i.e. you can use it as part of a STRTOSET or STRTOMEMBER function.|||

Sluggy,

Thank you so much for this answer. I was banging my head against the wall trying to figure out how to do this.

-Gumbatman

|||

Could you please post an example of this? My head also aches a lot

I just need the MDX syntax of the second dataset, the one of the parameter that depends on the first one selected.

Is there any way, from within SSRS to "filter" based on the previous parameter inside the MDX sentence?

Using SQL tables datasources and cascading parameters is quite straightforward, not the same with Olap cubes as datasources.

Thank you

Mike