Can anyone help on rownumber on Group Footer

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.

All Forums

Development Tools

Reporting Services Development

Can anyone help on rownumber on Group Footer

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-21 : 14:38:15

I have tried to get the rownumber for my Group which is labeled 'Customer'. I sum all the 'id.SOExtChargeAmount' on the report side.

I need this so I can limit the grouping to the Top 20 customers for my other Group labeled 'Region'.

I need this done on the report side of things in the layout tab. Can anyone help. Nothing I've tried from anyone has helped and I am at a standstill.

If I need to sum things on the query side then I will, but do not know how to do this. Here is my current query:

SELECT

CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 10 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessTen,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 9 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessNine,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 8 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEight,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 7 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessSeven,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 6 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessSix,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 5 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessFive,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 4 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessFour,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 3 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessThree,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 2 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessTwo,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 1 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessOne,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, @DateFrom) = 0 THEN id.SOExtChargeAmount ELSE 0 END AS CurrentMonth,
CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, @DateFrom) = 0 THEN id.SOExtChargeAmount ELSE 0 END AS CurrentYearTotal,
CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, @DateFrom) = 1 THEN id.SOExtChargeAmount ELSE 0 END AS LastYearTotal,
CASE WHEN DATEADD([year], - 1, @DateFrom) > ih.SOTransDate AND DATEDIFF([YEAR], ih.SOTransDate, @DateFrom)
= 1 THEN id.SOExtChargeAmount ELSE 0 END AS LastYearYTD,

case
when substring(a.Account,5,3)='900' then 'ALE'
when substring(a.Account,5,3)='902' then 'ATO'
when substring(a.Account,5,3)='904' then 'BOW'
when substring(a.Account,5,3)='906' then 'BRY'
when substring(a.Account,5,3)='908' then 'BPT'
when substring(a.Account,5,3)='910' then 'BYD'
when substring(a.Account,5,3)='912' then 'BUF'
when substring(a.Account,5,3)='914' then 'CLE'
when substring(a.Account,5,3)='916' then 'GRN'
when substring(a.Account,5,3)='920' then 'DXN'
when substring(a.Account,5,3)='924' then 'CTH'
when substring(a.Account,5,3)='926' then 'ELC'
when substring(a.Account,5,3)='928' then 'FTL'
when substring(a.Account,5,3)='930' then 'FTW'
when substring(a.Account,5,3)='932' then 'I35'
when substring(a.Account,5,3) IN ('936','000') then 'GAI'
when substring(a.Account,5,3)='939' then 'STW'
when substring(a.Account,5,3)='940' then 'GRE'
when substring(a.Account,5,3)='942' then 'HEN'
when substring(a.Account,5,3)='944' then 'FTS'
when substring(a.Account,5,3)='948' then 'JAC'
when substring(a.Account,5,3)='952' then 'JEN'
when substring(a.Account,5,3)='956' then 'KIL'
when substring(a.Account,5,3)='957' then 'MCA'
when substring(a.Account,5,3)='958' then 'MIN'
when substring(a.Account,5,3)='960' then 'NOC'
when substring(a.Account,5,3)='962' then 'ODE'
when substring(a.Account,5,3)='964' then 'BTP'
when substring(a.Account,5,3)='966' then 'RA'
when substring(a.Account,5,3)='968' then 'RIF'
when substring(a.Account,5,3)='970' then 'SWD'
when substring(a.Account,5,3)='971' then '3PS'
when substring(a.Account,5,3)='972' then 'ROC'
when substring(a.Account,5,3)='976' then 'SJO'
when substring(a.Account,5,3)='978' then 'SMB'
when substring(a.Account,5,3)='980' then 'STO'
when substring(a.Account,5,3)='982' then 'TOL'
when substring(a.Account,5,3)='984' then 'VEL'
when substring(a.Account,5,3)='985' then 'CFP'
when substring(a.Account,5,3)='986' then 'CLM'
when substring(a.Account,5,3)='988' then 'WHI'
when substring(a.Account,5,3)='992' then 'WRA'
when substring(a.Account,5,3)='995' then 'ADM'

ELSE 'Unknown'END AS Location,

case
when substring(a.Account,5,3) IN ('000','900','904','908','910','914','920','930','932','936','939','948','960','964','966','970','971','976','978','980','982','985','986','988','995') then 'North Region'
when substring(a.Account,5,3) IN ('902','957','984') then 'Woodford Region'
when substring(a.Account,5,3) IN ('906','926') then 'South Region'
when substring(a.Account,5,3) IN ('912','924','942','952','956','958') then 'East Region'
when substring(a.Account,5,3) IN ('916','944') then 'Fayetteville Region'
when substring(a.Account,5,3) IN ('928','940','968','972','992') then 'Rockies Region'
when substring(a.Account,5,3) = '962' then 'West Region'

ELSE ' 'END AS Region,

ih.SOTransDate, ih.InvoiceNumber, cm.CustomerName, id.DetailSeqNumber, id.LineType, a.Account, id.SOExtChargeAmount
FROM dbo.MAS_BSC_AR1_CustomerMaster cm, dbo.MAS_BSC_ARN_InvHistoryHeader ih, dbo.MAS_BSC_ARO_InvHistoryDetail id,
dbo.MAS_BSC_GL_Account a
WHERE cm.CustomerNumber = ih.CustomerNumber AND ih.InvoiceNumber = id.InvoiceNumber AND id.SOGLSalesAcct = a.AccountKey AND year(ih.SOTransDate) >= year(dateadd(year,-1, @DateFrom))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-23 : 13:29:05

have you tried the RowNumber() function in reporting services?

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-24 : 09:43:36

yes. it comes back with a SCOPE error in aggregate function. why is it this hard? Crystal Reports is definately an easier program than any of this Microsoft Reporting.

none of these guru's or experts seem to have a clue on how to perform this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-24 : 14:46:53

did you clearly gave correct scope inside function?

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-25 : 10:42:06

I go to my 'layout' tab in Visual Studio (not data tab or preview tab). I go to the group that I'm trying to acquire my rownumber. I 'right-click' on the group and the group window opens. The name I have assigned to this group is Customer.

So don't I just:

- create a new column
- in the new empty field located in the Customer group footer 'right-click' and choose expression
- enter the expression = rownumber(Customer)

???

I try this and I get an error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-26 : 13:30:44

try like this rownumber("Customer")

havij
Starting Member

3 Posts

Posted - 2008-06-02 : 02:28:55

What if we want the rownumber by group?
like this:
1.Group1------------1.Item1
1.Group1------------2.Item2
1.Group1------------3.Item3
2.Group2------------1.Item1
2.Group2------------2.Item2
2.Group2------------3.Item3
3.Group3------------1.Item1
4.Group4------------1.Item1
4.Group4------------2.Item2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 02:52:33

try =RowNumber("YourGroupName")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources