Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-07-12 : 17:58:21

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

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-12 : 18:37:14

Those are the queries for your parameters, but what is the query for the report body?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 20:15:15

sounds like you're not doing filtering properly in query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-07-12 : 20:34:17

Ohh sorry, Query is very simple SELECT * FROM SALE_HISTORY.

Thank You.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:42:41

then where are you doing filtering based on parameter values selected?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-07-13 : 13:40:09

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

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-14 : 00:10:25

sorry where are you doing the filtering based on T_TYPE in the main procedure?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-07-14 : 00:26:44

If i change my main Query to

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)
AND T_TYPE IN (@T_TYPE)

End

I am getting error when i run SSRS report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-14 : 22:52:51

quote:
Originally posted by tooba

If i change my main Query to

Main Query

S.p = USP_SUMMARY

Create Procedure USP_SUMMARY
@R_Type Varchar(50),
@T_TYPE Varchar(50)
as
Begin
SELECT * FROM Sales
Where R_TYPE IN (@R_TYPE)
AND T_TYPE IN (@T_TYPE)

End

I am getting error when i run SSRS report.

that was because you didnt declare it in SP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-07-15 : 01:14:53

No No, sorry i forget to right it down here, but i declare in Original SP. When i Preview my report in SSRS i am having problem..... Do i need to other changes in main query or both parameters?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-07-15 : 11:13:06

Well, we cannot see the error you are getting - which would help.

With that said, this will not work because you cannot pass a list to a stored procedure this way. The list will come across as:

'1,2,3,4'

And your query will look like this:

SELECT * FROM Sales WHERE R_TYPE IN ('1,2,3,4')

Since R_TYPE does not contain the string '1,2,3,4' you won't get any values returned for the query. To fix this, you need to use a string splitter function to convert the list into a table and then you can use IN, JOIN or CROSS APPLY.

There are a lot of string splitter functions available - on this site and others. I think the function in this article is one of the better functions: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Note: download the functions in the attachments to the article. The one included in the article has been optimized quite a bit and the attachment contains that updated code.

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources