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.