Query Help

• rocky_498

  SSCertifiable

  Points: 6532

  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.

• Andy Hyslop

  SSCrazy Eights

  Points: 9198

  More actions

  July 13, 2012 at 2:12 am

  #1512399

  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

  July 13, 2012 at 3:08 am

  #1512417

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

• rocky_498

  SSCertifiable

  Points: 6532

  More actions

  July 13, 2012 at 8:54 am

  #1512593

  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: 6532

  More actions

  July 13, 2012 at 11:39 am

  #1512671

  Here is more explanation with test data.

  fIRST STEP-

  Main Query

  S.p = USP_SUMMARY

  Create Procedure USP_SUMMARY

  @R_TypeVarchar(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

  (

  IDINT,

  R_TYPEVARCHAR(50),

  T_TYPEVARCHAR(50)

  )

  --Insert some Record

  INSERT INTO Sales (ID,R_TYPE,T_TYPE)

  VALUES ('1','Excel','Med');

  INSERT INTO Sales (ID,R_TYPE,T_TYPE)

  VALUES ('2','CSV','Med');

  INSERT INTO Sales (ID,R_TYPE,T_TYPE)

  VALUES ('3','CSV','Sales1');

  INSERT INTO Sales (ID,R_TYPE,T_TYPE)

  VALUES ('4','CSV','Sales2');

  INSERT INTO Sales (ID,R_TYPE,T_TYPE)

  VALUES ('5','CSV','Sales3');

  INSERT INTO Sales (ID,R_TYPE,T_TYPE)

  VALUES ('6','Excel','Sales3');

  Please let me know if you find any solution.

  Thank You.