| Author |
 Topic  |
|
|
tooba
Posting Yak Master
107 Posts |
 Posted - 07/12/2012 : 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
USA
35007 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/12/2012 : 20:15:15
|
sounds like you're not doing filtering properly in query
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
tooba
Posting Yak Master
107 Posts |
Posted - 07/12/2012 : 20:34:17
|
Ohh sorry, Query is very simple SELECT * FROM SALE_HISTORY.
Thank You. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 07/13/2012 : 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
107 Posts |
Posted - 07/13/2012 : 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
India
47173 Posts |
Posted - 07/14/2012 : 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
107 Posts |
Posted - 07/14/2012 : 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
India
47173 Posts |
Posted - 07/14/2012 : 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
107 Posts |
Posted - 07/15/2012 : 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
USA
696 Posts |
Posted - 07/15/2012 : 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 |
|
|
| |
Topic |
|
Query Help
