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: Tuesday, October 14, 2014 12:54 PM
Points: 72, Visits: 287
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: Tuesday, October 14, 2014 12:54 PM
Points: 72, Visits: 287
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, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Tuesday, October 14, 2014 12:54 PM
Points: 72, Visits: 287
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, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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: Tuesday, October 14, 2014 12:54 PM
Points: 72, Visits: 287
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, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
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