SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a Debit/Credit Report parameter in SSRS


Creating a Debit/Credit Report parameter in SSRS

Author
Message
stevenplee
stevenplee
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 307
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
josephcanwork
josephcanwork
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 107
Could you just filter out any record where the balance = 0?
stevenplee
stevenplee
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 307
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68463 Visits: 9671
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52474 Visits: 21180
I'll second that - a simple method which avoids sending unwanted data across the network.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
stevenplee
stevenplee
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 307
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68463 Visits: 9671
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...
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52474 Visits: 21180
Using a stored proc as the source for your report makes this task easy enough to run on the server.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
stevenplee
stevenplee
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 307
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
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68463 Visits: 9671
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search