Rank: Newbie
Groups: Registered
Joined: 2/27/2019(UTC) Posts: 4 
|
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
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 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.
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 2/19/2021(UTC) Posts: 10  Thanks: 2 times
|
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 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.
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 2/19/2021(UTC) Posts: 10 Thanks: 2 times
|
Pls Give me an example code so it will easy for us to make, if possible
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 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).
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 2/19/2021(UTC) Posts: 10 Thanks: 2 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
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 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).
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 2/19/2021(UTC) Posts: 10 Thanks: 2 times
|
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 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);
}
}
}
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 2/19/2021(UTC) Posts: 10 Thanks: 2 times
|
In Excel download it was not coming Eric,
Please let me know how can i?
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 2/19/2021(UTC) Posts: 10 Thanks: 2 times
|
Eric, We cant able to get that Sno in Excel how can i do that,
Please respond
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
I made a quick test and it works fine, even in Excel: test Row Number.srex (7kb) downloaded 1 time(s).
|
 1 user thanked epf for this useful post.
|
|
|
|
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.
