September 4, 2010 at 4:51 am
Hi All.... Im struck up at this point since yesterday..Please help.
I have a an ageing report which shows all the amounts of the customer.
I have all the debitted and creditted amount of the customer in a single column that i get from a dataset. Based on the ageing i display the amount accordingly like records < 30 days, > 30 days and so. I have to find out the total of the column adn also the total of debitted amount and creditted amount separately.
Let me show how my report looks like :
CustomerGroup CustomerId Items< 30 Amount< 30 Items >30 Amount>30 TotalItems TotalAmount
AG 1 1 100 2 100 3 200
SI 11 0 0 1 -250 1 -250
LG 5 2 150 1 100 3 250
------------------------------------------------------------------------------------------------------
Total 3 250 4 -50 7 200
CR 0 -250 -250
DR 250 200 450
Iam able to find the grand totals of all the columns but finding it tough to find the total of cr and dr amounts.
With Regards,
Anu..;-):hehe:
September 4, 2010 at 7:36 am
would something like the following help?
SUM(CASE WHEN col1> 0 THEN col1 ELSE 0 END) AS DEBIT,
SUM(CASE WHEN col1< 0 THEN col1 ELSE 0 END) AS CREDIT
If not, please post table def and sample data of the underlying table in a ready to use format as described in the first link in my signature together with your expected result.
September 5, 2010 at 8:42 pm
Thanks Lutz for your reply...
I cannot use it in the sql part. The requiremment is such that i should get the raw data from the tables and perform all others transformations from the ssrs side. Also I need to display these credit and debit totals in the report footer.
I tried something like :
Iif (ReportItems!textbox1.Value < 0,Sum(ReportITems!textbox1.Value,0) in txtCRTotal textbox.
Iif(ReportItems!,textbox1.Value =0 OR ReportItems!.textbox1.Value>0, Sum(ReportITems!textbox1.Value),0)
But it is giving the error that multiple report items cannot be used.
If i pass a numeric values to the iif statement like,
IIF(ReportItems!textbox1.Value <0 , 1, 0) it works fine.
Is it something like we cannot pass reportitems in the iif statement ?
Any clue...?
With Regards,
Anu..;-):hehe:
September 6, 2010 at 2:29 am
anuhya.mudumba (9/5/2010)
Thanks Lutz for your reply...I cannot use it in the sql part. The requiremment is such that i should get the raw data from the tables and perform all others transformations from the ssrs side. Also I need to display these credit and debit totals in the report footer.
...
It's kind of a philosophical question whether you consider using a query in SSRS as a data source (e.g. to preaggregate the data) instead of plain tables being part of SSRS process or not... 😉
I would question such a requirement.
September 6, 2010 at 5:21 am
Hey Lutz......
U r right....i have the same doubts though but i instructed to bring the max benifits of using ssrs and it is one among them.
i have to do all the calculations , aggregations , grouping , sorting, everything only in ssrs and not strictly in sql :crying:
And this one is eating out my mind.....
Any help ???
With Regards,
Anu..;-):hehe:
September 6, 2010 at 6:10 am
anuhya.mudumba (9/6/2010)
Hey Lutz......U r right....i have the same doubts though but i instructed to bring the max benifits of using ssrs and it is one among them.
i have to do all the calculations , aggregations , grouping , sorting, everything only in ssrs and not strictly in sql :crying:
And this one is eating out my mind.....
Any help ???
All I can do is to repeat what I stated before: building a query as a data source in SSRS will fulfill the requirement to use SSRS only. T-SQL is part of SSRS, too.
September 6, 2010 at 8:50 am
anuhya.mudumba (9/5/2010)
Thanks Lutz for your reply...I cannot use it in the sql part. The requiremment is such that i should get the raw data from the tables and perform all others transformations from the ssrs side. Also I need to display these credit and debit totals in the report footer.
I tried something like :
Iif (ReportItems!textbox1.Value < 0,Sum(ReportITems!textbox1.Value,0) in txtCRTotal textbox.
Iif(ReportItems!,textbox1.Value =0 OR ReportItems!.textbox1.Value>0, Sum(ReportITems!textbox1.Value),0)
But it is giving the error that multiple report items cannot be used.
If i pass a numeric values to the iif statement like,
IIF(ReportItems!textbox1.Value <0 , 1, 0) it works fine.
Is it something like we cannot pass reportitems in the iif statement ?
Any clue...?
I think you want to sum the result of the conditional like this...
Sum(Iif (ReportItems!textbox1.Value < 0,ReportITems!textbox1.Value,0)) in txtCRTotal textbox.
Sum(Iif (ReportItems!textbox1.Value > 0,ReportITems!textbox1.Value,0)) in txtDBTotal textbox.
I hope I got it right, but I couldn't test it. I hope you get the idea though.
September 7, 2010 at 12:01 am
Thanks Tom...
I already tried the expression u mentioned but i got the error as :
The Value expression for the textbox txtCRTotal refers to more than one report item. An expression in a page header or footer can refer to only one report item.
The expression I tried is :
SUM(IIF ( ReportItems!txtNoOfItems30.Value < 0 ,ReportItems!txtNoOfITems30.value , 0))
I used the above expression in the page footer.
The report executes well if I pass numerics as iif result sets.
With Regards,
Anu..;-):hehe:
September 7, 2010 at 7:12 am
September 7, 2010 at 7:39 am
If I understand correctly:
You want to totals of the debits and of the credits.
The debits and credits do not show individually in the detail because you have put them in a single column.
You, for some reason, are restricted from doing this in the SQL of the data set
I think you might find it easier to make 2 hidden columns in your detail. One for credits and one for debits. Once you have the debits and credits in separate columns, you should find it much easier to get totals for each.
September 8, 2010 at 5:59 am
Dbowlin...
Your understanding is correct...
Actually my problem is that in the report i am not referring the dataset field directly for amount and count. I get the raw data and group that on the Product_Name column and display only the sums and counts of the group.
For eg ,
I have a table with few records
Table1 :
Product_Name ID Amount Posting_Date
AA 1 10 08/5/2010
AA 2 20 08/5/2010
BB 3 110 08/06/2010
CC 4 -90 08/07/2010
AA 5 -10 07/30/2010
Now based on the Posting_Date i have to filter the records that has date diff of <=5 days from the current date.
Now in the report, I select this data in a dataset.
select Product_Name,ID,Amount,Posting_Date FROM Table1
NOw add a group on the Product_Name cloumn to get the counts and amounts of the table group by product.
To filter the data based on my aging condition i wrote the expression for NOOfItems and Amount as below respectively:
Count(IIF(DateDiff("d",Posting_Date,TOday) < =5, ID,Nothing))
and
Sum(IIF(DateDiff("d",Posting_Date,Today) < = 5,Amount,0))
The report now looks like:
Product NoOfItems Amount
AA 2 10
BB 1 110
CC 1 -90
Now iam adding totals to the columns NoOfItems and Amount in the table footer as:
Count(IIF(DateDiff("d",Posting_Date,TOday) < =5, ID,Nothing))
and
Sum(IIF(DateDiff("d",Posting_Date,Today) < = 5,Amount,0))
The report now looks like :
Product NoOfItems Amount
AA 2 10
BB 1 110
CC 1 -90
Total 4 30
Till here everything works fine. Now what iam unable to do is i have to find the totals of credits and debits in the column amount and not the dataset field Amount.
I want the report in the format as :
Product NoOfItems Amount
AA 2 10
BB 1 110
CC 1 -90
----------------------------------------------------
Total 4 30
CRTotal 1 -90
DRTotal 3 120
I cannot refer the dataset field directly i have to use the textbox value to accomplish this.
for this i wrote a custom code whch looks like:
Public Function AddCRValue(ByVal val as Decimal) As Decimal
Dim CRTotal as Decimal = 0
If val < 0 Then
CRTotal = CRTotal+val
End IF
CRTotal = CRTotal
Return CRTotal
END Function
Public Function AddDRValue(ByVal val as Decimal) As Decimal
Dim DRTotal as Decimal = 0
If val < 0 Then
DRTotal = DRTotal+val
End IF
DRTotal = DRTotal
Return DRTotal
END Function
And Iam calling these functions in the textboxes for CRTotal and DRTtoal as :
=Code.ADDCRValue(ReportItems!txtAmount.Value) for CRTotal
and
=Code.ADDDRValue(ReportItems!txtAmount.Value) for DRTotal
the report is getting executed but the values are displayed properly. The problem is that it is showing only the last row data everytime.
That is, everytime i run the report it loops through all the rows and performs the function but it is not saving the value anywhere. Everytime it refreshes and the last row 's value is getting displayed.
How do I loop through the column values.?? Do I have to change anything in the code??
Please help....
With Regards,
Anu..;-):hehe:
September 8, 2010 at 7:23 am
I will go back to what I said and add 2 hidden columns, one each for debit and credit and then sum those columns.
You say
Now iam adding totals to the columns NoOfItems and Amount in the table footer as:
The report now looks like :
Product NoOfItems Amount
AA 2 10
BB 1 110
CC 1 -90
Total 4 30
Here you have 3 columns consisting of text boxes. Lets say the text boxes are named txtProduct, txtNoOfItems and txtAmount. I am suggesting adding 2 additional columns that are hidden in the main body. Call them txtCredits and txtDebits.
The value for txtCredits would be =IIF(ReportItems!txtAmount.value < 0,0,ReportItems!txtAmount.value)
The value for txtDebits would be =IIF(ReportItems!txtAmount.value < 0,ReportItems!txtAmount.value, 0)
Then in your totals area your credits would be =Sum(ReportItems!txtCredits.Value) and your debits would be =Sum(ReportItems!txtDebits.Value)
Hope this helps. The code above came out a bit funky. The < is actually a <
September 8, 2010 at 7:34 am
Thanks dbowlin... will try and get back to you..:-)
With Regards,
Anu..;-):hehe:
September 8, 2010 at 11:00 pm
Dbowlin....
I tried whatever u suggested. I have created hidden columns for both credit and debit.
And got the values in the columns successfuly.
But when i try to sum these values in the table footer i get the following errors :
The Value expression for the textbox 'txtCreditSum' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
The Value expression for the textbox ‘txtCreditsSum’ refers to the report item ‘txtCredits’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.
Any clue ???
With Regards,
Anu..;-):hehe:
September 9, 2010 at 6:14 am
Apologies. I have had similar problems, I should have thought of this before my answer.
Usually when I run into this, I have to go back to the SQL in the dataset. You could try one change to see if it helps, but other than that I have no further suggestions. You could try to add a scoping statement to your sum formula.
=Sum(ReportItems!txtCredits.Value)
--changes to
=Sum(ReportItems!txtCredits.Value,"ScopeOftxtCreditsField")
I am not sure what the proper scope would be in your case. It is frequently a group name, possibly the name of the tablix/table.
Good luck.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply