Query Help

Question

Post reply

Query Help

rocky_498

SSCertifiable

Points: 6516
More actions
July 12, 2012 at 11:24 pm

#259462

I am having a problem, Here is my problem.

I am creating a SSRS Report. In Sales report i have two parameters.

When i preview this report at top i have two tab, First TAB = Report_Type

Second TAB = Table_Type. When i select my Report Type i can see only those Table_Type that belong to selection i made in Report_Type

and then i hit Preview. I should see what i selected but unfortunately i couldn't receive right information.I am seeing everything

in the table. Any one can help me would be great. Let me know if u need more information.

Here is my two Parameter that i created.

1) Parameter Name = R-Type

Select distinct R_Type from Sales

2) Parameter Name = T_Type

Select distinct T_Type from Sales

Where R_Type = @R_Type

Here is my main Query

Select * from Sales

Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Hi I may be misunderstanding but shouldn't your main query be:



SELECT *

FROM

Sales

WHERE

R_Type IN (@R_Type)

AND T_Type IN (@T_Type)

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 2

Sony Francis @EY

SSCommitted

Points: 1539

July 13, 2012 at 3:08 am

#1512417

Make sure Report SP is working fine with the input parameters.

rocky_498 SSCertifiable Points: 6516 More actions · Answer 3

I am creating a report in SSRS that's y i am creating Parameter in SSRS Instead of main query. I can't create same Parameter twice (Main Query & SSRS). If i create a Parameter in main Query in drop down Menu i have duplicate Report_Type and Table_Type that's why i want to use Distinct to give me Distinct value.... I can try with your query and see..

rocky_498 SSCertifiable Points: 6516 More actions · Answer 4

Here is more explanation with test data.

fIRST STEP-

Main Query

S.p = USP_SUMMARY

Create Procedure USP_SUMMARY

@R_Type Varchar(50)

as

Begin

SELECT * FROM Sales

Where R_TYPE IN (@R_TYPE)

End

Second Step:-

First Parameter

Parameter_Name = T_TYPE

SELECT DISTINCT T_TYPE FROM SALES

Third Step:-

Second Parameter

Parameter_Name = R_TYPE

SELECT DISTINCT R_TYPE FROM SALES

WHERE T_TYPE = @T_TYPE

Note:- After i preview my report in SSRS result come out good with R-Type what i selected

but the result through T_Type are not come out right mean some how T_Type is filter is not

doing what they suppose to do.

Here is Test table and data for your test.

--Table Syntax

Create Table Sales

(

ID INT,

R_TYPE VARCHAR(50),

T_TYPE VARCHAR(50)

)

--Insert some Record