Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simpleton
Starting Member
25 Posts |
 Posted - 2008-07-21 : 14:21:04
|
| I have a report on item numbers starting with 1, 2, 3, and 5's.Report is coming up fine, but i need to subtotal after the item number (first digit) changes to the next number. 181711272619878187671662711227Need subtotal here28392287672102023123Need subtotal here How can i subtotal/total based on their first number?? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-21 : 14:25:17
|
| You need to add a new field in you dataset that contains only initial digit of figures(1,2,3...) and add a grouping in your report on that new field. Give the SUM(Fields!Figure.Value) as expression in group footer to get the total values. |
 |
|
|
simpleton
Starting Member
25 Posts |
Posted - 2008-07-21 : 15:32:03
|
| Very cool. Thanks.So I need to create 4 new datasets? What kind of expression do I use in them?=Fields!ITEM.Value |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-22 : 13:56:02
|
quote:
Originally posted by simpleton
Very cool. Thanks.So I need to create 4 new datasets? What kind of expression do I use in them?=Fields!ITEM.Value
4 datasets? for what? i just told to add a field to your current dataset which is nothing more than just first digit from current field. then in your layout use this field to group data in table and use expression posted earlier to get total. |
|
|
|
simpleton
Starting Member
25 Posts |
Posted - 2008-07-23 : 10:46:32
|
| Got it. Thanks.I added another select item in my query to grab just the first digit.SUBSTRING(dbo.TABLE.ITEM, 1, 1) AS ITEM_FIRSTThen i just told the report to group based on that new Column. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-23 : 11:04:21
|
quote:
Originally posted by simpleton
Got it. Thanks.I added another select item in my query to grab just the first digit.SUBSTRING(dbo.TABLE.ITEM, 1, 1) AS ITEM_FIRSTThen i just told the report to group based on that new Column.
yeah thats what i told. You can also use left instead of substringLEFT(dbo.TABLE.ITEM, 1) AS ITEM_FIRST |
|
|
|
|
|
|
Can I subtotal based on first number in item?