SQL Server Performance

Sequential Line Count in a field

Discussion in 'SQL Server Reporting Services' started by skillmaker, Oct 31, 2005.

  1. skillmaker New Member

    I need to be able to create a report that has a line counter on each row:

    information RowCnt
    datastuff 1
    datastuff 2
    datastuff 3

    Then, I need to change the rowcount back to zero when a break occurs based on a change in a field:

    information RowCnt
    datastuff 1
    datastuff 2
    datastuff 3

    stuffdata 1
    stuffdata 2
    stuffdata 3


    Any ideas?
  2. vbkenya New Member

    A rather quick (and dirty approach). But it works if you tweak it to match your data.

    1. The table:

    CREATE TABLE [stuff] ([data] [varchar] (50) )
    GO



    2. Insert some sample data:


    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('otherstuff')




    3. query to retrieve your report: Note that you may decide to use table variables instead of temporary tables. Hope you can make sense out of this mess.


    CREATE TABLE #temp (rcount int ,data varchar(50))
    Select row=IDENTITY(int,1,1),data into #actualdata from stuff order by data
    Select row=IDENTITY(int,1,1),count(data) as records,data into #data from stuff group by data
    SELECT * FROM #data
    SELECT * FROM #actualdata
    DECLARE @i int
    DECLARE @numitems int
    DECLARE @data varchar(50)
    DECLARE @itemcount int
    DECLARE @j int
    SET @itemcount=0
    SET @j=0
    WHILE @itemcount<(SELECT MAX(row) FROM #data)
    BEGIN
    SET @itemcount=@itemcount+1
    SET @numitems=(SELECT records FROM #data WHERE row=@itemcount)
    SET @i=0
    WHILE @i<@numitems
    BEGIN
    SET @i=@i+1
    SET @j=@j+1
    SET @data=(SELECT data from #actualdata where row=@j)
    INSERT into #temp(rcount,data) values (@i,@data)
    END

    END

    SELECT * FROM #temp

    drop table #temp
    drop table #data
    drop table #actualdata





    Nathan H. Omukwenyi
  3. Madhivanan Moderator

    Isnt this enough?


    CREATE TABLE [stuff] ([data] [varchar] (50) )
    GO



    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('datastuff')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('stuffdata')
    INSERT INTO stuff (data) VALUES ('otherstuff')

    Go

    Select id=identity(int, 1,1),data into #t from [stuff]

    Select data,(select count(*) from #t where id<=T.id and data=T.data) from #t T

    drop table #t
    Drop table [stuff]


    But In Crystal Reports there is an option for running total which can be set and Count and reset to every group. See if you are able to do the same in Reporting services also

    Madhivanan

    Failing to plan is Planning to fail
  4. skillmaker New Member

    quote:Originally posted by Madhivanan

    Isnt this enough?

    But In Crystal Reports there is an option for running total which can be set and Count and reset to every group. See if you are able to do the same in Reporting services also

    Madhivanan

    Failing to plan is Planning to fail

    The Crystal Reports running counter would be a wonderful option, but I am not able to replicate that function in SQL RS. If anyone out there has a method (preferably without having to build and retrieve and remove temp tables), it would be most appreciated.

    Thanks
  5. druer New Member

    Madhivanan's solution works the exact same way using a TABLE variable instead of a #TEMP table to avoid TEMPDB if that was your goal:

    -- Create the TABLE variable and populate it with your values
    declare @tblVar table (id int IDENTITY(1,1), data varchar(50) )
    insert into @tblVar (data)
    select data from [stuff]

    Select data,(select count(*) from @tblVar where id<=T.id and data=T.data) from @tblVar T

    A totall different solution would involve using a cursor for the selects, and simply having a @Cntr value, and a @OldValue variable so that you would check the previous data value and if it matches then you increment the counter, if it doesn't match you set the @OldValue to the new value and reset the @Cntr.

Share This Page