Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am creating a pivot table through a macro after making some selections which act as the dimensions.
The macro works fine when run on the server but when trying to run on IE, the pivot table is created but it is all black in color making it unreadable, even if i send it to excel, the font and the background color is black
Unable to resolve this issue
Please help
Regards
Peter
Peter,
Are you dynamically setting the background colour? I had something similar recently and as none of the conditions were true QV defaulted it to black. To test if the values are actually there change the fill colour to none for the cells in the exported spreadsheet (if the text colour is black then they are simply being hidden).
Gordon
Dear Gordon
Yes there is data in the table but since both the background and the text color is black, it is not readable
Please suggext a way where we can change the qv default from black to white
thanks in advance
regards
Peter
So are you setting the background colour? If you are it sounds like the conditioning is wrong (no colour is being returned) If you want to post the conditioning....
Regards,
Gordon
No back ground color is set , please find code as below
sub
tabel
set
s=ActiveDocument.Sheets("SH02")
for
i=0 to orignumber-1
next
set
Hlo=ActiveDocument.GetSheet("SH02").CreatePivotTable
set
arrslc=ActiveDocument.Fields("$Field").GetSelectedValues(200)
for
i=0 to arrslc.Count-1
if
slc<>"AutoID"then
end
if
next
"sum(if(ValidTo>=[DateFrom] and ValidTo<=[DateTo],(if([Variable1]='BPARate',BPARate,if([Variable1]='BAF',BAF,if([Variable1]='CAF',CAF))))))"
set
cp = Hlo.GetProperties
set
expr = cp.Expressions.Item(0).Item(0).Data.ExpressionVisual
"BPARate"
set
Prop = Hlo.GetProperties
true
end
sub
I suggest you explicitly set the background colour to white - see BkgColor and BkgColorExp in the API guide.
Regards,
Gordon
Gordon
Tried using the same but error still persists
Can you send me an example or code ?
Thanks
Peter
The API guide should show examples, but here is some code I wrote a while ago which you are welcome to pick over. It creates a straight table with dynamic expressions based on the entries in a tablebox and sets the background color based on a numeric entry in the tablebox.
sub CH_TOTALS_Expr
' Set the expressions for the current repairs
set chart = ActiveDocument.GetSheetObject("CH_TOTALS")
set cp = chart.GetProperties
' remove all existing expressions from chart
n = cp.Expressions.Count
Do While n > 0 ' will end when no more expressions or unxepected error from removeexpression
n = chart.RemoveExpression(0)
Loop
' add expression for every repair
set tbox = ActiveDocument.GetSheetObject( "TB_HEADERS" )
For r = 1 to tbox.GetRowCount-1 ' rows numbered from 0 but skip table box header row
set cell = tbox.GetCell(r,2) ' get RBF Code
chart.AddExpression "sum(if ([RBF Code]='" & cell.Text & "', [Reqd Qty], 0))"
Next 'r
' now set the corresponding repair description as the column label and the background colour to the group number
' have to do this as a second loop as after adding an expression would otherwise need to 'GetProperties' each time
set cp = chart.GetProperties
For r = 1 to tbox.GetRowCount-1 ' rows numbered from 0 but skip table box header row
set expr = cp.Expressions.Item(r -1).Item(0).Data 'expressions numbered from 0
set cell = tbox.GetCell(r,3) ' get Repair
set ev = expr.ExpressionVisual
ev.Label.v = cell.Text
ev.ColWidth = 265
set cell = tbox.GetCell(r,0) ' get Group No
set bce = expr.AttributeExpressions.BkgColorExp
bce.Definition.v = "color(" & cell.Text & ")"
Next 'r
cp.TableProperties.StyleNumber = -1 ' set style to 'custom'
chart.SetProperties cp
ActiveDocument.Save
end sub
What happens though after your table is created and you change the background colour manually?
Regards,
Gordon
Gordon
Thanks for the reply
I have sorted out the problem
Now instead of creating a new pivot table, i am removing the existing fields and adding new fields in already existing Pivot table, this is working fine
regards
Peter
Hi, I intend to create the same kind of "dynamic" pivot table with a macro to modify dimensions and expressions but i read in the QlikView manual that interaction with objects (using SetProperties for instance) is not allowed as soon as QlikView server is used...
But it seems you implemented it and tested with server. Is this working fine?
Thanks in advance for your feedback.