Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating a Debit/Credit Report parameter in SSRS Expand / Collapse
Author
Message
Posted Monday, May 16, 2011 3:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:56 AM
Points: 70, Visits: 269
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
Post #1109835
Posted Monday, May 16, 2011 9:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 10:33 AM
Points: 5, Visits: 107
Could you just filter out any record where the balance = 0?
Post #1109924
Posted Monday, May 16, 2011 9:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:56 AM
Points: 70, Visits: 269
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.

Post #1109929
Posted Tuesday, May 17, 2011 5:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #1110086
Posted Tuesday, May 17, 2011 5:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1110112
Posted Tuesday, May 17, 2011 6:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:56 AM
Points: 70, Visits: 269
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
Post #1110136
Posted Tuesday, May 17, 2011 6:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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...
Post #1110148
Posted Tuesday, May 17, 2011 7:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1110178
Posted Tuesday, May 17, 2011 8:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:56 AM
Points: 70, Visits: 269
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
Post #1110243
Posted Tuesday, May 17, 2011 8:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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


Post #1110250
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse