hierarchical selection within a select statment

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))

INSERT INTO RS_A
VALUES ('S', 'shakespeare')
INSERT INTO RS_A
VALUES ('B', 'shakespeare')
INSERT INTO RS_A
VALUES ('P', 'shakespeare')

INSERT INTO RS_A
VALUES ('S', 'milton')
INSERT INTO RS_A
VALUES ('P', 'milton')
INSERT INTO RS_A
VALUES ('B', 'shelley')

INSERT INTO RS_A
VALUES ('B', 'kafka')
INSERT INTO RS_A
VALUES ('S', 'kafka')

INSERT INTO RS_A
VALUES ('P', 'tennyson')

SELECT * FROM RS_A

Now i need a select which selects based on hierarchy

if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row

So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson

Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

If any of you can help me with this right away, its is greatly
appreciated
Thanks in advance(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

SELECT * FROM RS_A
>
Now i need a select which selects based on hierarchy
>
if ColA = 'S', then select only that row
else if ColA = 'B' then select only that row
else if colA = 'P' then select only that row
>
So my results should look like
S shakespeare
S milton
B shelley
S kafka
P tennyson
>
Is there a way to do this within a select statement
I tried using a CASE in WHERE CLAUSE but it put out all rows which
existed/

First translate the codes to numeric values with CASE, you can take
MIN, and then translate back:

SELECT CASE minval WHEN 1 THEN 'S' WHEN 2 THEN 'B' WHEN 3 THEN 'P' END,
ColB
FROM (SELECT ColB, minval = MIN(CASE ColA
WHEN 'S' THEN 1
WHEN 'B' THEN 2
WHEN 'P' THEN 3
END)
FROM RS_A
GROUP BY ColB) AS x

If there are many possible values for ColA, it would be better to
put the mapping in a table and then join with that table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ingenius :
Thank you for the above and the RETURN was what was missing after
RAISEERROR

-RS

Hiding a subreport SSRS

Hi,

I'm a newbie inSSRS, kindly help!!

i have a subreport and it needs to be displayed only based on some selection criteria, in all other case it needs to be hidden.i made the visibility to hidden, but when i run the report,it gives me a blank page where it usually gets me the subreport.How can i remove this blank page from getting displayed ?

PS: this is the expression i have added :

=IIF(Parameters!strChoice.Value="Single",True,False)

Thanks in advance for any help..

Hello,

Is the subreport part of a table?

If it's in a table row, then you will need to set the visibility on the row itself, not the subreport.

Jarret

|||

Thanx Jarret

I tried toggling the visibility of the row having the subreport.

i gave the same expression as well,but i'm still getting the blank page.

Thanx for any help.

Hide/unHide

dear All,

I want to make a Report with attractive selection parameters.
That I mean, there is a parameter, Tparam (Time Param) which will activate other parameters (3 parameters: TimeA_1, TimeA_2 & TimeA_3. These selection values have been registered in the "Aviable values => non-queried".)

when Tparam selected TimeA_1 then Parameter for TimeA_1 will activated (and the others will be disappeared/hidden), vice versa for the other control (TimeA_2 & TimeA_3)

any idea to do that?

thank you...What you want is available, they are called Cascading Parameters, there is a tutorial here
http://msdn2.microsoft.com/en-us/library/aa337426.aspx|||

Hi SNMSDN,

In the link ,you sent i can't find any source regarding hiding or ignoring one parameter using another parameter.

Even i have another problem in setting null values . I have 5 cascading parameters ,level1,level2,level3,level4 and level5

when level1 is compulsory ,i had the remaining 4 parameters to accept for null and are made optional

for this i tried with "Allow null value option" in Report parameters but its not accepting

as an alternative i tried with default value 'Query based' to return null.

None of them directs me towards accepting null

It is aksing to select the parameter

It will be really a great help if some body faced some problem like this and resolved it

Thanks in advance

Raj Deep.A

|||

I have the same problem.

Basically, it appears that if you reference a parameter directly or indirectly via any other parameter's DS, then it will not respect the "Allow Null" setting for that parameter, since it thinks that it is implicitly required.

For example, I have a report with State & County parameters that both need to be set to enable "Allow Null". The County parameter references the State parameter's value within its Dataset, but no other parameter references the County parameter.

As a result, the County parameter respects the "Allow Null" setting, but the "State" parameter does not.

There MUST be a way around this restriction!!!

I have tried to hack around it by creating a dummy parameter called "StateFilter" that uses an expression to either return the Parameter!State.Value or Null, and then the County dataset references this parameter instead of the actual "State" parameter. However, this doesnt work either. Sql RS is "smart" enough to see the indirect reference and continues to treat State as a required parameter.

Help!!!

~Lance

|||

Nevermind.

I had forgotten about this wrinkle in how report parameters worked, but just now realized that you have to provide an option that has a value of NULL in order to select the "Allow Null" option. Makes sense when you think of it, but it would be nice if RS injected the NULL value option in such cases.

See this posting for the details:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1067464&SiteID=17

Hide/Show parameter based on Multi-Valued Parameter Selection

Hi!

I have a parameter that sould be visible only if certains values are selected on a
multi-value parameter. I tried to use an expression for the parameter's hidden property, but I saw it's not possible (at least not in the usual way, as with tables). Does anyone know how to do it?

Thanks!

There is no way to do this through RDL, but if you are using the report viewer controls, you can programmatically hide the parameters using the ReportViewer control API.