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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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