How to display the row number on the report?

Mohammad Rehman shared this question 6 years ago
Answered

Hello Experts,


Please point me to the existing function or explained earlier, if already exist.

I want to display the row number/S. No on the report.

i.e

Here is my report:

Name

Joe

Alllen

Mary

Peter


I want to display like this.

S.No Name

1 Joe

2 Alllen

3 Mary

4 Peter


Note: we are using 7.1 version which has lots of limitation and features/functions disabled by Vendor BMC.

TIA

Mohammad

Replies (1)

photo
2

Hi Mohammad,


Unfortunately, I am unsure of what the exact limitations of BMC's system are, are you able to create freehand-SQL reports?


If you are okay with these appearing in separate columns, you can create an additional column with the "ascending rank" advanced function based on the field you are sorting the report by.


Is your goal to have these concatenated as part of the same string, or simply to have a row number column?


Regards,

Nathan

photo
1

Thanks Nathan, Objective is to just display the row number separate column.

photo
1

Nathan, you are the best! I had doubt but your suggested solution looks like works in initial testing using ascending rank option.

Regards

Mohammad

photo
2

Ho Mohammad,


Glad I could help! Let me know if you run into problems.


Regards,

Nathan

photo
1

Only one issue though, if i use string column like name or date column then i have no access to this function. only works when i add numeric column. Is it possible to apply the same function on date and time column or text column?

photo
2

Hi Mohammad,


Unfortunately anything past this will need to be done by creating a Freehand SQL report, is this an option for you?


Regards,

Nathan

photo
1

Free SQL does not work due to BMC. Thanks for your time and help as always.

Regards

Mohammad

photo
1

Hi Mohammad,


My colleague recently made me aware of another trick you can do here:


  1. create a calculated field that simply returns the value: 1
  2. apply an accumulative total advanced function to this field!


Regards,

Nathan

photo
1

Hi Nathan,

Adding further on this same question - Can we filter on that row numbers.

E.g. I want to filter only N rows on the basis of user input.

photo
1

Hi Megan,


Unfortunately you won't be able to filter on that row no, as the number is generated by Java (advanced function) and not something you will be able to use via SQL.

Sorry for the bad news on that.


Regards,

David

photo
1

Hi,

I realise this is a an old topic but i was able to implement this solution. However, when the report does not have any rows to display the function still returns 1 which is not ideal.

Is there a workaround for this ?


Thanks

photo
1

nevermind!, it looks like there was a row generated by a union clause on the freehand sql.

photo
Leave a Comment
 
Attach a file