New Topic

Community Topic Question Report Builder

David Registro

APAC SD Team

Assigned

Nathan Schroeder

NA Business Team

Category Report Builder

Product Version 7.1

How to display the row number on the report?

Mohammad Rehman shared this question 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

The same question

Replies (1)

Nathan Schroeder ●

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

Reply URL

Mohammad Rehman ●

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

URL

Mohammad Rehman ●

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

Regards

Mohammad

URL

Nathan Schroeder ●

Ho Mohammad,

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

Regards,

Nathan

URL

Mohammad Rehman ●

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?

URL

Nathan Schroeder ●

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

URL

Mohammad Rehman ●

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

Regards

Mohammad

URL

Nathan Schroeder ●

Hi Mohammad,

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

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

Regards,

Nathan

URL

megha Gawali ●

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.

URL

David Registro ●

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

URL

Ayub ●

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

URL

Ayub ●

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

URL