• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How to create crosstab datawindow object at runtime?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved How to create crosstab datawindow object at runtime?

  1. How-to
0
Votes
Undo
  1. Berka Frenfert
    Berka Frenfert
  2. PowerBuilder
  3. Friday, 1 January 2021 11:55 AM UTC

Hi,

What is the easiest way to generate source for a Crosstab DataWindow based on query user provide at runtime?

Does PB 2019 or later allow SyntaxFromSQL() with "style(type=crosstab)" ?

Who want to share code if have done crosstab generation at runtime? 

Any idea about what lines need to changed in Grid Datawindow source when converting to Cossstab Datawindow?

  1. Crosstab
  2. DataWindow
  3. PowerBuilder (Appeon)
Bad runtime reference - how to control external sy...
dbo.pbcattbl needed for application deployment
Accepted Answer
Berka Frenfert
Berka Frenfert Accepted Answer Pending Moderation
  1. Wednesday, 6 January 2021 09:35 AM UTC
  2. PowerBuilder
  3. # Permalink

Key idea: How simple can it be?

Important Points!

1- No need to play with query directly if you can easily generate grid from a query. And i think this was the most scary thing for developers when working on dynamic crosstabs generated at runtime.

2- Crosstab require columns names for rows, cols and values as design time initial default settings even before we can call crosstab dialog box at runtime (no idea why there was needed for that).

3- There you avoid giving actual columns names. You give immediate static numbers as expressions for all required entries. 

Then i exported the saved crosstab and copied every line from source of it and used those lines in an array in script. I replaced the table() section with the query user provide at runtime. see the function i created which ask for query and return crosstab source at runtime and you will understand every piece of it.

Now Runtime: At this point i have grid generated from the same query user provided (from which i get list of columns instead of poking DB or shuffling sorting/searching query directly). I give call to the function who generate source successfully and then i call the crosstab dialog box function(user can drag actual columns and apply expressions whatever he/she need to do). Finally retrieve() call is made.

Here is the function that successfully generate crosstab source for either a very complex query or for a stored procedure. I checked it and it definitely work like a knife in butter.

 

Paste following code in a function that return Crosstab Source Sting and receive SQL Query String. Please indent the code for yourself because i could not find code block tags. 

String CrosstabSyntax, Merging[], AllCols[], ColType, ColExp
Integer ColNo, MaxCols

/// initializing of constants
/// a global function returns array of columns names for a given datawindow control
AllCols = gf_getdwobjectsarray(graph.tp_data.dw_data, "column", FALSE, FALSE)
MaxCols = UpperBound(AllCols)

/// 1
Merging[1] = "release 12.5;"
Merging[1] += "~r~n"

/// 2
Merging[2] = "datawindow(units=0 timer_interval=0 color=1073741824 brushmode=0 transparency=0 gradient.angle=0 gradient.color=8421504 gradient.focus=0 gradient.repetition.count=0 gradient.repetition.length=100 gradient.repetition.mode=0 gradient.scale=100 gradient.spread=100 gradient.transparency=0 picture.blur=0 picture.clip.bottom=0 picture.clip.left=0 picture.clip.right=0 picture.clip.top=0 picture.mode=0 picture.scale.x=100 picture.scale.y=100 picture.transparency=0 processing=4 HTMLDW=no print.printername=~"~" print.documentname=~"~" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes print.background=no print.preview.background=no print.preview.outline=yes hidegrayline=no showbackcoloronxp=no picture.file=~"~" crosstab.dynamic = yes grid.lines=0 grid.columnmove=no selected.mouse=no )"
Merging[2] += "~r~n"

/// 3
Merging[3] = "header[1](height=72 color=~"536870912~" transparency=~"0~" gradient.color=~"8421504~" gradient.transparency=~"0~" gradient.angle=~"0~" brushmode=~"0~" gradient.repetition.mode=~"0~" gradient.repetition.count=~"0~" gradient.repetition.length=~"100~" gradient.focus=~"0~" gradient.scale=~"100~" gradient.spread=~"100~" )"
Merging[3] += "~r~n"

/// 4
Merging[4] = "header[2](height=72 color=~"536870912~" transparency=~"0~" gradient.color=~"8421504~" gradient.transparency=~"0~" gradient.angle=~"0~" brushmode=~"0~" gradient.repetition.mode=~"0~" gradient.repetition.count=~"0~" gradient.repetition.length=~"100~" gradient.focus=~"0~" gradient.scale=~"100~" gradient.spread=~"100~" )"
Merging[4] += "~r~n"

/// 5
Merging[5] = "summary(height=116 color=~"536870912~" transparency=~"0~" gradient.color=~"8421504~" gradient.transparency=~"0~" gradient.angle=~"0~" brushmode=~"0~" gradient.repetition.mode=~"0~" gradient.repetition.count=~"0~" gradient.repetition.length=~"100~" gradient.focus=~"0~" gradient.scale=~"100~" gradient.spread=~"100~" )"
Merging[5] += "~r~n"

/// 6
Merging[6] = "footer(height=0 color=~"536870912~" transparency=~"0~" gradient.color=~"8421504~" gradient.transparency=~"0~" gradient.angle=~"0~" brushmode=~"0~" gradient.repetition.mode=~"0~" gradient.repetition.count=~"0~" gradient.repetition.length=~"100~" gradient.focus=~"0~" gradient.scale=~"100~" gradient.spread=~"100~" )"
Merging[6] += "~r~n"

/// 7
Merging[7] = "detail(height=84 color=~"536870912~" transparency=~"0~" gradient.color=~"8421504~" gradient.transparency=~"0~" gradient.angle=~"0~" brushmode=~"0~" gradient.repetition.mode=~"0~" gradient.repetition.count=~"0~" gradient.repetition.length=~"100~" gradient.focus=~"0~" gradient.scale=~"100~" gradient.spread=~"100~" )"
Merging[7] += "~r~n"

/// 8
Merging[8] = "table("

/// 9
Merging[9] = "column=(type=number updatewhereclause=yes name=row_column dbname=~"row_column~" )"
Merging[9] += "~r~n" + "column=(type=number updatewhereclause=yes name=val dbname=~"val~" )"

merging[10] = "retrieve=~"" + SQL + "~" "
merging[11] = "sort=~"row_column A ~""
merging[12] = ")"

/// 13
Merging[13] = "text(band=header[1] alignment=~"0~" text=~"Count Of Val~" border=~"0~" color=~"33554432~" x=~"9~" y=~"4~" height=~"64~" width=~"416~" html.valueishtml=~"0~" name=t_1 visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" font.italic=~"1~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[13] += "~r~n"

/// 14 :
Merging[14] = "text(band=header[1] alignment=~"0~" text=~"obj_1~" border=~"0~" color=~"33554432~" x=~"434~" y=~"4~" height=~"64~" width=~"343~" html.valueishtml=~"0~" name=t_2 visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" font.italic=~"1~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[14] += "~r~n"

/// 15 :
Merging[15] = "text(band=header[2] alignment=~"0~" text=~"222~" border=~"0~" color=~"33554432~" x=~"9~" y=~"4~" height=~"64~" width=~"416~" html.valueishtml=~"0~" name=row_column_t visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" font.italic=~"1~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[15] += "~r~n"

/// 16 : @abcd : dynamic distinct label across all columns as if it is each column's title
Merging[16] = "text(band=header[2] alignment=~"0~" text=~"@col~" border=~"0~" color=~"33554432~" x=~"434~" y=~"4~" height=~"64~" width=~"343~" html.valueishtml=~"0~" name=val_t visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" font.italic=~"1~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[16] += "~r~n"

/// 17: Grand Total : /// its static column title for grand totals of every row
Merging[17] = "text(band=header[2] alignment=~"0~" text=~"Grand Total ~" border=~"0~" color=~"33554432~" x=~"786~" y=~"4~" height=~"64~" width=~"686~" html.valueishtml=~"0~" name=grand_count_val_t visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" font.italic=~"1~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[17] += "~r~n"

/// 18: Arrestee : /// dynamic distinct detail column presenting every row's name
Merging[18] = "column(band=detail id=1 alignment=~"1~" tabsequence=32766 border=~"0~" color=~"33554432~" x=~"9~" y=~"4~" height=~"76~" width=~"416~" format=~"[general]~" html.valueishtml=~"0~" name=row_column visible=~"1~" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=no edit.autohscroll=yes font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"400~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[18] += "~r~n"

/// 19: Checks : /// dynamic detail column presenting Values for every row & col
Merging[19] = "column(band=detail id=2 alignment=~"1~" tabsequence=32766 border=~"0~" color=~"33554432~" x=~"434~" y=~"4~" height=~"76~" width=~"343~" format=~"[general]~" html.valueishtml=~"0~" name=val visible=~"1~" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=no edit.autohscroll=yes crosstab.repeat=yes font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"400~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[19] += "~r~n"

/// 20: crosstabsum() : /// compute for grand sum on the most right side of every row
Merging[20] = "compute(band=detail alignment=~"1~" expression=~"crosstabsum(1)~"border=~"0~" color=~"33554432~" x=~"786~" y=~"4~" height=~"76~" width=~"686~" format=~"[general]~" html.valueishtml=~"0~" name=grand_count_val visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[20] += "~r~n"

/// 21: Grand Total : /// Work like static text but nobody know why as compute
Merging[21] = "compute(band=summary alignment=~"1~" expression=~"~~~"Grand Total~~~"~"border=~"0~" color=~"33554432~" x=~"9~" y=~"4~" height=~"76~" width=~"416~" format=~"[general]~" html.valueishtml=~"0~" name=compute_1 visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" font.italic=~"1~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[21] += "~r~n"

/// 22: Values Sum() : /// compute sum of values vertically
Merging[22] = "compute(band=summary alignment=~"1~" expression=~"sum(val for all )~"border=~"0~" color=~"33554432~" x=~"434~" y=~"4~" height=~"76~" width=~"343~" format=~"[general]~" html.valueishtml=~"0~" name=compute_2 visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[22] += "~r~n"

/// 23: GrandCrosstabSum() : /// sum of sums across rows & cols
Merging[23] = "compute(band=summary alignment=~"1~" expression=~"sum(grand_count_val for all )~"border=~"0~" color=~"33554432~" x=~"786~" y=~"4~" height=~"76~" width=~"686~" format=~"[general]~" html.valueishtml=~"0~" name=compute_3 visible=~"1~" font.face=~"Times New Roman~" font.height=~"-9~" font.weight=~"700~" font.family=~"1~" font.pitch=~"2~" font.charset=~"0~" background.mode=~"1~" background.color=~"536870912~" background.transparency=~"0~" background.gradient.color=~"8421504~" background.gradient.transparency=~"0~" background.gradient.angle=~"0~" background.brushmode=~"0~" background.gradient.repetition.mode=~"0~" background.gradient.repetition.count=~"0~" background.gradient.repetition.length=~"100~" background.gradient.focus=~"0~" background.gradient.scale=~"100~" background.gradient.spread=~"100~" tooltip.backcolor=~"134217752~" tooltip.delay.initial=~"0~" tooltip.delay.visible=~"32000~" tooltip.enabled=~"0~" tooltip.hasclosebutton=~"0~" tooltip.icon=~"0~" tooltip.isbubble=~"0~" tooltip.maxwidth=~"0~" tooltip.textcolor=~"134217751~" tooltip.transparency=~"0~" transparency=~"0~" )"
Merging[23] += "~r~n"

/// 24: /// crosstab relarted line
Merging[24] = "crosstab("
Merging[24] += "band = foreground crosstabonly = yes "
Merging[24] += "~r~n"

/// loop on all columns to complete next Merging (collect all columns names)
ColExp=""
FOR ColNo = 1 TO MaxCols
ColExp = AllCols[ColNo] + ", "
NEXT
ColExp = Left(ColExp, Len(ColExp) - 2)

/// 25:
Merging[25] = "columns = ~"111~" rows = ~"222~" values = ~"count(333 for crosstab)~" "
Merging[25] += "sourcenames = ~""+ColExp+"~""

Merging[25] += ")" ///crosstab closed
Merging[25] += "htmltable(border=~"0~" cellpadding=~"1~" cellspacing=~"1~" )"
Merging[25] += "~r~n"

/// 26:
Merging[26] = "htmlgen(clientevents=~"1~" clientvalidation=~"1~" clientcomputedfields=~"1~" clientformatting=~"0~" clientscriptable=~"0~" generatejavascript=~"1~" encodeselflinkargs=~"1~" netscapelayers=~"0~" pagingmethod=0 generatedddwframes=~"1~" )"
Merging[26] += "~r~n"

/// 27:
Merging[27] = "xhtmlgen() cssgen(sessionspecific=~"0~" )"
Merging[27] += "~r~n"

/// 28:
Merging[28] = "xmlgen(inline=~"0~" )"
Merging[28] += "~r~n"

/// 29:
Merging[29] = "xsltgen()"
Merging[29] += "~r~n"

/// 30:
Merging[30] = "jsgen()"
Merging[30] += "~r~n"

/// 31:
Merging[31] = "export.xml(headgroups=~"1~" includewhitespace=~"0~" metadatatype=0 savemetadata=0 )"
Merging[31] += "~r~n"

/// 32:
Merging[32] = "import.xml()"
Merging[32] += "~r~n"

/// 33:
Merging[33] = "export.pdf(method=0 distill.custompostscript=~"0~" xslfop.print=~"0~" )"
Merging[33] += "~r~n"

/// 34:
Merging[34] = "export.xhtml()"
Merging[34] += "~r~n~r~n"

FOR ColNo = 1 TO UpperBound(Merging)
CrosstabSyntax += Merging[ColNo]
NEXT

Return CrosstabSyntax

 

 

Enjoy!

 

 

 

Comment
Load more comments
Berka Frenfert
  1. Berka Frenfert
  2. Wednesday, 6 January 2021 09:59 AM UTC
obviously developer cannot forget Create( dwsyntax_str, ERRORS) and dw_crosstab.SetTransObject(SQLCA) functions. :) missed in detail for answer.

Thank you
  1. Helpful
John Fauss
  1. John Fauss
  2. Wednesday, 6 January 2021 15:01 PM UTC
Impressive!



I'm glad you found a solution and appreciate you sharing it with us.
  1. Helpful
Berka Frenfert
  1. Berka Frenfert
  2. Thursday, 7 January 2021 05:54 AM UTC
Appeon community is helping many developers this way (sharing). I will be very happy if someone find it useful.
  1. Helpful
There are no comments made yet.
Responses (4)
Berka Frenfert
Berka Frenfert Accepted Answer Pending Moderation
  1. Friday, 4 June 2021 07:41 AM UTC
  2. PowerBuilder
  3. # 1

Unfortunately, i could not find anyway to apply filter on corosstab columns (The columns that you drop into columns section of crosstab definition dialog)

The columns in crosstab definition are not shown in the "Specify Filter" dialog box when SetFilter(null) is called on generated crosstab datawindow. Only Rows and Values from "Crosstab Definition" dialog box are shown in "Specify Filter" dialog box but not the columns.

I noticed that crosstab columns are neither columns nor computed fields. They are named like other static text controls of datawindow. 

Without columns the Setfilter() function fails too if expression contains column in it.

I wanted to present an interface where user could apply filters on crosstab data which is not possible without columns.

Alternate way is to change where clause in the query behind crosstab but that is bad idea and nearly impossible thing too. 

At the end of dynamic crosstab road i see a dead end. can someone find an alternate way?

Please see if you can apply filter on column !

Thank you

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Sunday, 3 January 2021 15:51 PM UTC
  2. PowerBuilder
  3. # 2

Hi Berka;

   FWIW: Many people over the past two decades have asked about ways to create a dynamic crosstab DWO. However, none have been successful. There have been many enhancement requests over the years to add support for SyntaxFromSQL() command as well. Both Sybase & Appeon Engineering have responded that the CT DWO is technically way different under-the-hood than any other DW style and thus, almost impossible to support a dynamic option.

Regards ... Chris

Comment
Berka Frenfert
  1. Berka Frenfert
  2. Monday, 4 January 2021 06:41 AM UTC
Hi, Chris,

:) I never tried to write source for crosstab before, today i can try at least once. Some points Miguel wrote may help. Thank you.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Sunday, 3 January 2021 11:13 AM UTC
  2. PowerBuilder
  3. # 3

According to PB help (2019), you cannot specify Crosstab for the style to be used with syntaxfromsql.
(search on "SyntaxFromSQL (PowerScript function)")

Maybe the "Group" style could be useful to generate a temporary dw with values that you can later use to create the final crosstab dw? There's more than 'just the style' that you can specify when using syntaxfromsql, like column specifications, but personally I've never done that. It does sound like a better option than having to do a "create" for every column.

To change a dw style you can try to change the the "processing" attribute to 4 with the Modify() function. I'm not sure if that would work without having to do also lots of other Modifies, as a crosstab dw is quite different from any other type. ("Group" type might be closest?)

"DataWindow.Processing"

  • 4 – Crosstab

So maybe an easier way would be to have like an "template" crosstab datawindow, with maybe "lots" of initially invisible columns.It could be using an external datasource.

Another thing I'm thinking of, is if you might want to use a database table(s) in which you populate the labels and values for your crosstab. Your code would then only have to fill that table every time that you want to run the report and delete the values when done. (don't forget to add a field to make the values specific to a certain user who's running the report at that moment). The amount of needed modifies might be a lot less that way, or maybe not even necessary. It all depends on how much variety in amount and type of columns your dw should be able to cope with.

It's going to be quite a job, but I don't think it's impossible. Sounds like fun!

Happy new year

Comment
Berka Frenfert
  1. Berka Frenfert
  2. Monday, 4 January 2021 06:50 AM UTC
Crosstab is a grid in fact. The best option i want to try is generating every line of source. so bypass SyntaxFromSQL and hit the create function with generated code.



Simple first try without writing my own crosstab code, following was discovered::



Tried with processing 4 / Style-crosstab etc.. following issues encountered

SyntaxFromSQL generate source successfully but wrong code.

Crosstab related options are added in source but incomplete options.

Generated faulty crosstab dwo can be opened in design mode but crash on change

Crosstab data cannot be changed in design time.

The crostabdialog() function always return error.



Thanks.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.