logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
RobertG  
#1 Posted : Thursday, February 28, 2019 10:28:07 AM(UTC)
RobertG

Rank: Newbie

Groups: Registered
Joined: 2/27/2019(UTC)
Posts: 4
Poland

Hello,
Is it possible to add column to data tabel to display row number for each record? Something like auto counter?
Thanks for you help.
Best Regards
Robert
epf  
#2 Posted : Friday, March 1, 2019 2:42:10 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Yes, but it should come from your SQL query (could be ROMNUM in ORACLE or ROW_NUMBER() in SQLServer).

In your model, just add a column from your table, set the type to Numeric, and set the Custom SQL property to the new SQL (like ROW_NUMBER() OVER(ORDER BY name ASC)) and it should work.
Sekharg  
#3 Posted : Monday, April 12, 2021 2:53:08 AM(UTC)
Sekharg

Rank: Member

Groups: Registered
Joined: 2/19/2021(UTC)
Posts: 10
India

Thanks: 1 times
For Pivot Table ? Eric?
epf  
#4 Posted : Monday, April 12, 2021 6:08:01 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
For a pivot table, you cannot do it with SQL as the table is pivoted by the Seal engine...

So you have to change add a task to change the final tables or change your View Template to do it.
This requires the understanding of the Seal Object Model, plus C# skills.
Sekharg  
#5 Posted : Wednesday, April 21, 2021 10:34:59 PM(UTC)
Sekharg

Rank: Member

Groups: Registered
Joined: 2/19/2021(UTC)
Posts: 10
India

Thanks: 1 times
Pls Give me an example code so it will easy for us to make, if possible
epf  
#6 Posted : Thursday, April 22, 2021 6:26:26 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Here is my sample in a dedicated task:

Code:
@using System.Data
@{
    //Format final result cells before rendering
    ReportTask task = Model;
    Report report = task.Report;   
    //Note that this Task MUST BE executed at the step: 'Models generated, before rendering'
    foreach (var model in report.Models) 
    {
        foreach (var page in model.Pages) 
        {
            //Add a column
            for (int i = 0; i < page.DataTable.Lines.Count; i++) 
            {
                var lineList = page.DataTable.Lines[i].ToList();
                var newCell = new ResultCell();
                newCell.FinalCssClass = "text-right";
                if (i == page.DataTable.BodyStartRow -1) {
                    newCell.FinalValue = "Number";
                    newCell.IsTitle = true;
                }
                else if (i >= page.DataTable.BodyStartRow && i < page.DataTable.BodyEndRow) {
                    newCell.FinalValue = (i - page.DataTable.BodyStartRow).ToString();
                } 
                
                lineList.Add(newCell);
                page.DataTable.Lines[i] = lineList.ToArray();
            }       
        }
    }
}


and here is the report add row number column in a pivot table.srex (5kb) downloaded 2 time(s).
Sekharg  
#7 Posted : Thursday, April 29, 2021 7:39:49 AM(UTC)
Sekharg

Rank: Member

Groups: Registered
Joined: 2/19/2021(UTC)
Posts: 10
India

Thanks: 1 times
Thanks, Eric.

It worked but below are the consequences for this task addition

How we can skip the sub total columns for this Task ?

In excel it is not displaying repeating 4 times,

In PDF repeating 4 times with showing 4 times,

Might be issue ?

Edited by user Thursday, April 29, 2021 8:06:00 AM(UTC)  | Reason: Not specified

epf  
#8 Posted : Friday, April 30, 2021 6:58:11 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
here is the update of the task script to make it work for CSV, Excel and Sub-totals:
Code:
@using System.Data
@{
    //Format final result cells before rendering
    ReportTask task = Model;
    Report report = task.Report;   
    //Note that this Task MUST BE executed at the step: 'Models generated, before rendering'
    foreach (var model in report.Models) 
    {
        foreach (var page in model.Pages) 
        {
            //Add a column
            int rowNumber = 1;
            for (int i = 0; i < page.DataTable.Lines.Count; i++) 
            {
                var lineList = page.DataTable.Lines[i].ToList();
                var newCell = new ResultCell();
                newCell.FinalCssClass = "text-right";
                if (i == page.DataTable.BodyStartRow -1) {
                    newCell.Value = "Number";
                    newCell.IsTitle = true;
                }
                else if (i >= page.DataTable.BodyStartRow && i < page.DataTable.BodyEndRow) {
                    if (!lineList[0].IsSubTotal) {
                        newCell.Value = rowNumber;
                        rowNumber++;
                    }
                }                 
                lineList.Add(newCell);
                page.DataTable.Lines[i] = lineList.ToArray();
            }       
            task.LogMessage("Column count after = {0}", page.DataTable.ColumnCount);
        }
    }
}



add row number column in a pivot table2.srex (5kb) downloaded 2 time(s).
Sekharg  
#9 Posted : Saturday, May 1, 2021 4:56:55 AM(UTC)
Sekharg

Rank: Member

Groups: Registered
Joined: 2/19/2021(UTC)
Posts: 10
India

Thanks: 1 times
Thanks for quick response,

However, We found one issue in excel & PDF downloads.

Excel : https://postimg.cc/8fRL4DrH

PDF : https://postimg.cc/w3L5g5bm


Number column is repeating multiple times

Edited by user Saturday, May 1, 2021 5:01:24 AM(UTC)  | Reason: Not specified

epf  
#10 Posted : Saturday, May 1, 2021 8:22:29 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Yes, here is the final script (check that the Page has been done or not using the Tag)

Code:
@using System.Data
@{
    //Format final result cells before rendering
    ReportTask task = Model;
    Report report = task.Report;
    
    //Note that this Task MUST BE executed at the step: 'Models generated, before rendering'
    foreach (var model in report.Models)
    {
        foreach (var page in model.Pages.Where(i => i.Tag == null)) 
        {
            page.Tag = 1;        
            //Add a column
            int rowNumber = 1;
            for (int i = 0; i < page.DataTable.Lines.Count; i++) 
            {
                var lineList = page.DataTable.Lines[i].ToList();
                var newCell = new ResultCell();
                newCell.FinalCssClass = "text-right";
                if (i == page.DataTable.BodyStartRow -1) {
                    newCell.Value = "Number";
                    newCell.IsTitle = true;
                }
                else if (i >= page.DataTable.BodyStartRow && i < page.DataTable.BodyEndRow) {
                    if (!lineList[0].IsSubTotal) {
                        newCell.Value = rowNumber;
                        rowNumber++;
                    }
                }                 
                lineList.Add(newCell);
                page.DataTable.Lines[i] = lineList.ToArray();
            }       
            task.LogMessage("Column count after = {0}", page.DataTable.ColumnCount);
        }
    }
}
Sekharg  
#11 Posted : Wednesday, January 25, 2023 1:55:17 AM(UTC)
Sekharg

Rank: Member

Groups: Registered
Joined: 2/19/2021(UTC)
Posts: 10
India

Thanks: 1 times
In Excel download it was not coming Eric,

Please let me know how can i?
Sekharg  
#12 Posted : Thursday, March 16, 2023 1:46:54 PM(UTC)
Sekharg

Rank: Member

Groups: Registered
Joined: 2/19/2021(UTC)
Posts: 10
India

Thanks: 1 times
Eric, We cant able to get that Sno in Excel how can i do that,

Please respond
epf  
#13 Posted : Thursday, March 16, 2023 5:34:45 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
I made a quick test and it works fine, even in Excel:
test Row Number.srex (7kb) downloaded 1 time(s).
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.