How to display partial sum/Total for fields in a dynamic report ?

[#1]

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

[#2]

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

[#3]

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 ?

[johnw]

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

[#5]

Thanks a lot John ! This is exactly what i want.Perfect