Cross Table Creations

[priyarane]

Hi Experts,

I have requirement to build  and attached Requirement as Req  and Data as source.

My requirement is already been done by Petter Skjolden. - FRB Report(Developed One). But I am not able to understand script, how he has done with the help of cross table and others.

Script:

[DataSheet]:

CrossTable(AP, Data, 1)
LOAD
Item*10 AS Item,
date AS Actual,
[PQ 1],
[PQ 2],
[PQ 3],
[PQ 4],
[PQ 5],
[PQ 6],
[PQ 7],
[PQ 8],
[PQ 9]
FROM
Souce.xlsx
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[RowHeaders]:
LOAD
RecNo() AS RowNo,
@1 * 10 AS Item,
If( IsNum(@1) , Peek(ItemTxt) , @1) AS ItemTxt,
@2 AS [Item Text]
FROM
Souce.xlsx
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(shorter, 0)), 0)
));

so My question is, what is: CrossTable(AP, Data, 1)
and @1 * 10 AS Item,
If( IsNum(@1) , Peek(ItemTxt) , @1) AS ItemTxt,
@2 AS [Item Text]

Please check my source and how he built as per requirement.

I have many reports like that to build, If I can understand better then I will create reports quickly.

Thank you.

[petter]

Just let me explain very quickly why I did the:

Item * 10 AS Item

I wanted to have some way of adding extra lines between the different so called Item Lines in the FRB ... like presentation rows for spacing etc... It was not necessarily a best practice but just a quick-and-dirty approach...

[priyarane]

Thanks Petter,

But how did you get this,

[RowHeaders]:

LOAD
RecNo() AS RowNo,
@1 * 10 AS Item,
If( IsNum(@1) , Peek(ItemTxt) , @1) AS ItemTxt,
@2 AS [Item Text]
FROM
Souce.xlsx
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(shorter, 0)), 0)
));

which format it is:
(ooxml, no labels, table is Sheet1, filters(
ColXtr(1, RowCnd(CellValue, 1, StrCnd(shorter, 0)), 0)
));

I am trying to get it, but I am failing what is @1 like this