Avatar of RamyBouchra
RamyBouchra asked on

SQL2005 Reporting Services and RowNumber

Dear Team
I am working with VS2005, Reporting Services
I want to know how to use the Rownumber function for the detail of every group I want to restart the counter how can I do that.
For example:
      1  group 1
            11  Detail1
            12  Detail2
      2  group 2
            21  detail3
            22  detail4
And so on
Please advise what to do.
Thank you
.NET ProgrammingMicrosoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
RamyBouchra

8/22/2022 - Mon
rickchild

You can pass the name of a data region or grouping as scope to Rownumber(Scope).

So for example you can use:

RowNumber(Nothing)
RowNumber("Table1_Group1")
RowNumber("Table1_Group2")

See this article for further information:

http://www.kodyaz.com/articles/article.aspx?articleid=52


An alternative is adding your row numbers in SQL and not in Reporting Services.
ASKER
RamyBouchra

yes but i have a table and 1 group
i did it on the group level but on the table level it is not working
rickchild

On the table you just need to use:

RowNumber(Nothing)

Can you provide an example screenshot of what is not working?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
RamyBouchra

no it is working but it gives the value of 2 on every row
ASKER
RamyBouchra

do you understand me
i want it like this if you please
1  group 1
            1  Detail1
            2  Detail2
2  group 2
            1  detail3
            2  detail4
 the inner row number is working verygood but the outer one is getting value of 2
ASKER
RamyBouchra

please advise me what to do
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rickchild

Can you please post a screenshot image of what is happening now.
ASKER
RamyBouchra

sure
please find out
text box with RedColor: =RowNUmber("table1")
text box with GreenColor: =RowNUmber("table1_Group1")

the problem with RedColor.
i hope it is more easy now.

thank you
SLSMFLSMMonth.pdf
SLSMFLSMMonth.JPG
ASKER
RamyBouchra

i forget to tell you 1 more thing the row with Red color is the group by
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
RamyBouchra

please i need your advise
rickchild

I see your image thanks, I had thought the problem was with the Green and not the Red.

It appears to be totalling the group contents, I will look into this soon for the solution.
ASKER
RamyBouchra

thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
rickchild

Sorry for the delay in getting back to you.  I have tried and I am unable to do this in the layout designer.

It may be a better idea to add the outside group to the data on the select query, and then just display it on the outside group row.  This could be done with a CASE in T-SQL.

Let me know if you need help with this, if you can post your select code I can help.
ASKER
RamyBouchra

thank you but this is not the answer i am looking for.
ASKER CERTIFIED SOLUTION
rickchild

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
RamyBouchra

i will try it and tell you
thank you so much for your kind reply
Your help has saved me hundreds of hours of internet surfing.
fblack61
rickchild

How did you get on with this?  I can upload a .rdl example if you are stuck.
ASKER
RamyBouchra

yes if you please and i will close the question
if you please

thank you so much
rickchild

Hi, I am unable to attach that file to experts exchage so I have uploaded a shot of the expressions.

Give me your email address if you need the file.
report1.PNG
report2.PNG
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
RamyBouchra

Really appreciate the great job
Thank you