Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I am creating a dynamic report using the sample posted by John Witherspoon.
I have a requirement where i have to display Total value(partial sums) for a couple of fields.
How do i go about it ? any suggestions ?
Punar
Hi punarkarthik,
Can you please post a sample of your requirement.. Then it will be more clear to understand and someone will help you on the issue..
Thanks and Regards,
Nilupa
I am using the following Macro to generate dynamic reports in qlikview.
MACRO:
sub rebuild()
ActiveDocument.GetApplication.WaitForIdle 'wait for QlikView to finish before modifying report
set selectedExpressions = ActiveDocument.getField("Expressions").getSelectedValues
set selectedDimensions = ActiveDocument.getField("Dimensions").getSelectedValues
selectedExpressionMax = selectedExpressions.Count - 1
selectedDimensionMax = selectedDimensions.Count - 1
if selectedExpressionMax + selectedDimensionMax > -2 then 'bypass if nothing is selected
'--------------------------- EXPRESSIONS ------------------------------------------------------
set chart = ActiveDocument.getSheetObject("CH01")
set chartProperties = chart.GetProperties
set chartExpressions = chartProperties.Expressions
chartExpressionMax = chartExpressions.Count - 1
dim selectedExpression(100) 'runs slightly faster if first set up a simple array
for j = 0 to selectedExpressionMax
selectedExpression(j) = selectedExpressions.Item(j).Text
next
for i = 0 to chartExpressionMax 'for every possible expression defined to the chart
e = chartExpressions.Item(i).Item(0).Data
chartExpression = e.ExpressionVisual.Label.v
expression = e.ExpressionData
if chartExpression <> "Default" then
expression.Enable = false 'disable the expression
end if
for j = 0 to selectedExpressionMax 'check all selected expressions
if selectedExpression(j) = chartExpression then 'if user selected the expression
expression.Enable = true 'enable it
j = 9999 'and exit the inner loop
end if
next
next
chart.SetProperties chartProperties
'--------------------------- DIMENSIONS --------------------------------------------------------
chartDimensionMax = chart.GetColumnCount - 1
for i = chartDimensionMax to 0 step -1 'remove existing dimensions
chart.removeDimension i
next
for j = 0 to selectedDimensionMax 'add selected dimensions
chart.addDimension selectedDimensions.Item(j).Text
next
end if
end sub
This works perfectly.Now i want to display the partial sum for a couple of fields in the dynamic report.Is it possible ?
Here's an example to play with. In this case, I check if there's an expression selected, and if so, I do partial sums on EVERY selected dimension. If you only want the partial sums on some of the selected dimensions, you'll need some way to identify them. There's probably a slightly cleaner way to do it, but I just stuck the extra code down at the bottom of the macro for simplicity.
if selectedExpressionMax > -1 then
set chartProperties = chart.GetProperties
for i = 0 to selectedDimensionMax
chartProperties.dimensions(i).showpartialsums = true
next
chart.SetProperties chartProperties
end if
end if
end sub
Thanks a lot John ! This is exactly what i want.Perfect