May 16, 2011 at 3:05 pm
This should be raither simple but I just can seem to figure it out. I have an accounting report in SSRS which has a totals column. I have been asked to create another report that has a parameter that when selected will opnly show records where the total is Negative(Credit) or Positive(Debit). I am planning on just adding a filter to the tablix and passing the paramter value to the filter on the tablix.
My problem is that I haven't been able to figure out how to set the available values of the parameter to <0 for Credit and > 0 for Debit.
As I said this should an easy one but maybe I have looked at it for to long.
Any suggestions would be greatly appreciated
Thanks
Steven
May 16, 2011 at 9:13 pm
Could you just filter out any record where the balance = 0?
May 16, 2011 at 9:34 pm
I need to be able to have a parameter that has two option.
DEBIT = where totals are > 0
CREDIT = where totals are < 0
The user would select one or the other and the report would filter accordingly.
May 17, 2011 at 5:25 am
The way I'd personanlly do this is the send that parameter in the query that fills the dataset. So in theory you return only 50% of the data everytime.
Then in the report you have 1 colum for debit and 1 for credit.
Just hide the column you don't need depending on the parameter value.
May 17, 2011 at 5:57 am
I'll second that - a simple method which avoids sending unwanted data across the network.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2011 at 6:35 am
The issue with this apprach is that the filter is applied on gtoupings. If the net of the grouping is < 0 then is would be returned by selecting the Credit from the parameter, if >0 the they would be returned by the Debit from the parameter
May 17, 2011 at 6:45 am
that can still be done in t-sql.
Put the results into #temp table.
Then delete where grouping in (SELECT Group from #tmp group by Group HAVING SUM(transaction) > 0)
The filter is still doable in ssrs, I just don't have it on top of my head...
May 17, 2011 at 7:08 am
Using a stored proc as the source for your report makes this task easy enough to run on the server.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 17, 2011 at 8:09 am
After running my query to a #temp table I have tried this
DECLARE @Parameter varchar(12)
BEGIN
IF @Parameter = 'Credit'
THEN
(SELECT [Doc Number],[Customer Name],[Owner],[Rev Type1],[Mail Date],[Revenue Date],[Days Outstanding],[0-30 Days],[31-60 Days],
[61-90 Days],[91-120 Days],[121-150 Days],[151 + Days],[Customer ID],
[Total Outstanding],([0-30 Days]+[31-60 Days]+[61-90 Days]+[91-120 Days]+[121-150 Days]+[151 + Days]) as [Total Outstanding2],[Reference]
FROM #t1
WHERE ([0-30 Days]+[31-60 Days]+[61-90 Days]+[91-120 Days]+[121-150 Days]+[151 + Days]) < 0)
ELSE
SELECT [Doc Number],[Customer Name],[Owner],[Rev Type1],[Mail Date],[Revenue Date],[Days Outstanding],[0-30 Days],[31-60 Days],
[61-90 Days],[91-120 Days],[121-150 Days],[151 + Days],[Customer ID],
[Total Outstanding],([0-30 Days]+[31-60 Days]+[61-90 Days]+[91-120 Days]+[121-150 Days]+[151 + Days]) as [Total Outstanding2],[Reference]
FROM #t1
WHERE ([0-30 Days]+[31-60 Days]+[61-90 Days]+[91-120 Days]+[121-150 Days]+[151 + Days]) > 0
END
But I am getting "Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'ELSE'."
Any suggestions
May 17, 2011 at 8:15 am
Here you go. But I'm not sure this is what you want to do. You were talking about groups and I see nothing about that in the query.
DECLARE @Parameter varchar(12)
IF @Parameter = 'Credit'
BEGIN
SELECT
[Doc Number]
, [Customer Name]
, [Owner]
, [Rev Type1]
, [Mail Date]
, [Revenue Date]
, [Days Outstanding]
, [0-30 Days]
, [31-60 Days]
, [61-90 Days]
, [91-120 Days]
, [121-150 Days]
, [151 + Days]
, [Customer ID]
, [Total Outstanding]
, ( [0-30 Days] + [31-60 Days] + [61-90 Days] + [91-120 Days]
+ [121-150 Days] + [151 + Days] ) as [Total Outstanding2]
, [Reference]
FROM
#t1
WHERE
( [0-30 Days] + [31-60 Days] + [61-90 Days] + [91-120 Days]
+ [121-150 Days] + [151 + Days] ) < 0
END
ELSE
BEGIN
SELECT
[Doc Number]
, [Customer Name]
, [Owner]
, [Rev Type1]
, [Mail Date]
, [Revenue Date]
, [Days Outstanding]
, [0-30 Days]
, [31-60 Days]
, [61-90 Days]
, [91-120 Days]
, [121-150 Days]
, [151 + Days]
, [Customer ID]
, [Total Outstanding]
, ( [0-30 Days] + [31-60 Days] + [61-90 Days] + [91-120 Days]
+ [121-150 Days] + [151 + Days] ) as [Total Outstanding2]
, [Reference]
FROM
#t1
WHERE
( [0-30 Days] + [31-60 Days] + [61-90 Days] + [91-120 Days]
+ [121-150 Days] + [151 + Days] ) > 0
END
May 17, 2011 at 8:21 am
Thank you the grouping I refered to happens in the report. The user want to be able to drill into the groups so I have to provide the data at the most graular level. That's why there's no groupin in the query
May 17, 2011 at 8:27 am
I understand that. I guess I just misunderstood part of the requirement.
So you're all set?
May 17, 2011 at 8:57 am
YEs...Thank you so much for your help!!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply