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

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.

RamyBouchra

ASKER

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?

RamyBouchra

ASKER

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

RamyBouchra

ASKER

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

RamyBouchra

ASKER

please advise me what to do

rickchild

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

RamyBouchra

ASKER

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

RamyBouchra

ASKER

i forget to tell you 1 more thing the row with Red color is the group by

RamyBouchra

ASKER

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.

RamyBouchra

ASKER

thank you

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.

RamyBouchra

ASKER

thank you but this is not the answer i am looking for.

ASKER CERTIFIED SOLUTION

rickchild

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RamyBouchra

ASKER

i will try it and tell you
thank you so much for your kind reply

rickchild

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

RamyBouchra

ASKER

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

RamyBouchra

ASKER

Really appreciate the great job
Thank you