CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
Thanks in advance,
sck10
I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

CASE Statement

Hello,
I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
Denied or NULL). On my web page, I have a dropdown box which the user can
select 3 items (Approved, Denied or Pending). When they choose "Pending",
I want to retrieve the fields that are NULL. I've tried the following WHERE
statement, but I can't capture the NULL fields.
@.strParm03 can equal "All, Approved, Denied or NULL)
WHERE
ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
WHEN 'Pending' THEN NULL
ELSE @.strParm03 END
Any help with this would be appreciated.
--
Thanks in advance,
sck10I would use a script like:
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and ApprovalStatus is null)
[/code]
or
[code]
where @.strParm03 = 'All'
or (@.strParm03='Approved' and ApprovalStatus='Approved')
or (@.strParm03='Denied' and ApprovalStatus='Denied')
or (@.strParm03='Pending' and isnull(ApprovalStatus,'') ='')
[/code]
HTH,
Cristian Lefter, SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oafy0raXFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have a varchar field (ApprovalStatus) that can have 3 results (Approved,
> Denied or NULL). On my web page, I have a dropdown box which the user can
> select 3 items (Approved, Denied or Pending). When they choose
> "Pending",
> I want to retrieve the fields that are NULL. I've tried the following
> WHERE
> statement, but I can't capture the NULL fields.
> @.strParm03 can equal "All, Approved, Denied or NULL)
> WHERE
> ApprovalStatus LIKE CASE @.strParm03 WHEN 'all' THEN '%'
> WHEN 'Pending' THEN NULL
> ELSE @.strParm03 END
> Any help with this would be appreciated.
> --
> Thanks in advance,
> sck10
>

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 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 Parameter issue with RS2005... It worked in RS2000

Our company is attempting to convert several RS2000 reports to RS2005. We have 4 default parameters on every report.

1) Date Range (dropdown) – populated via Non-Queried Available values (Ex. Today, Week to Date, Month to Date, Fiscal Period)
2) Fiscal Period (cascading dropdown) – populated via Query Available values (if Date Range = ‘Fiscal Period’, select the last 24 fiscal months, else select Date Range value)
3) Start Date (cascading textbox) – populated via Query Default values (depending on Fiscal Period value calculate Start Date)
4) End Date (cascading textbox) – populated via Query Default values (depending on Fiscal Period value calculate End Date)

The user starts by selecting a Date Range which auto populates the Fiscal Period the auto populates the 2 Date textboxes. This works great in RS2000; however it’s not 100% in RS2005. Here’s what happens:

If a user selects ‘Today’ or ‘Month to Date’ in Date Range, everything works fine. At this point there is only 1 value in Fiscal Period dropdown. If the user selects ‘Fiscal Period’ in the Date Range, the Fiscal Period is populated with 24 values (1 for the last 24 fiscal months) and is set to the top value (June – 2006). The 2 Date textboxes are also populated correctly.

The problem is when the user changes the value in the Fiscal Period dropdown. Let's say the pick 'Mar - 2004'. The 2 Date fields are not refreshed or updated. If the user changes the selection back in the first dropdown (Date Range), everything is refreshed and updated appropriately. I don’t under stand why the first dropdown will refresh all the dependent parameters but the second dropdown won’t refresh the 2 Date fields below it?

Any input would be very helpful. I’ve tested this on 3 different development boxes and 2 different RS2005 servers. All tests had the same results.

Thanks,
Nick

Any ideas why this thing works in RS2000, but not in RS2005? Any input would be valued. Thanks,

Nick