Firehose mode: summary

Showing posts with label summary. Show all posts

Wednesday, March 28, 2012

Grouping problem

I need to provide a summary row at the bottom of each group that sums accounts of a specific type and subtracts it from the group total.

Acct Balance Prior Balance

A 10 5

B 4 8

C 7 6

D 9 12

Total 30 31

exp accts 13 20

Final 17 11

Is there an easy way to do this in reporting services?

So you want to perform a conditional sum based on another field (account type) in the dataset?

You need add some additional rows to the group footer and put an Iif statement inside the Sum that returns the value to aggregate for a match and 0 otherwise e.g.

Sum(Iif(Fields!account_type.Value = "exclude", Fields!Balance.value, 0))

Then in the last row subtract one row from the other.

The following is RDL code for an example:

<?xml version="1.0" encoding="utf-8"?>

<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>

</ConnectionProperties>

<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>

</DataSource>

</DataSources>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>balance_1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>prior_balance_1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!prior_balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

<rd:DefaultName>account</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

</Style>

<Value>=Fields!account.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>balance</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>prior_balance</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!prior_balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

</Style>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance

union

select 'B', 2, 4, 8

union

select 'C', 1, 7, 6

union

select 'D', 2, 9, 12</CommandText>

<DataSourceName>master</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

<DataField>account</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>balance</DataField>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Wednesday, March 21, 2012

I hope someone can help me with this one. I can't seem to find a way to solve my problem. I am converting a report from Crystal to RS. In Crystal I am using global variables to keep track of group totals for a final summary. I need a similar result from RS. Data example

Group A PK Field Summary Data Field 1 250 2 300 Group A Total 550 Group B 3 100 4 50 Group B Total 150 Grand Total 700

The underlying query contains detail data and I am using a table with two group levels. All details are hidden.

To calculate the totals at the detail level I need to know what the total value for the entire group is. This leads me to my problem, it is not possible (as far as I can tell) to summarize a summary (I get an error). I have tried using the code window to store variables but the value returns a 0. I found a suggestion here http://msdn2.microsoft.com/en-us/library/bb395166.aspx under Distinct Sum, but I can't call the function using the Sum command given that the formula to calculate the value is already using the sum command. I hope this makes sense.

Thanks,

Simone

Hello Simone,

In your example, I assume that the rows with 1, 2, 3, & 4 are the detail rows?

If so, In your Group A & B footer, put the expression =Sum(Fields!Data.Value)

Then place the same in your Grand Total row, summary column.

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your response. The rows 1, 2, 3, & 4 are actually summary rows of the details. All details are hidden. This is where my problem lies. The details are in the dataset but in the report I have them rolled up. To obtain each summary I have a formula that needs to know the summary for each group to determine the outcome. For example:

Group Summary 1 > 0 Use Formula A

Group Summary 1 <= 0 User Formula B

I can get the correct summary at this level, but I then need to total all of the summary records and obtain a second group total. Finally I need to sum all secondary group totals to obtain a grand total.

Thanks,

Simone

|||

I'm not sure I understand what you are trying to do with the formula, but...

Try using the same expression in your Grand Total summary that you are using in your Group Footer 1 and 2.

Example data:

Fruit Count Date

Apples 35 4/12/07

Apples 10 4/12/07

Apples 15 4/13/07

Apples 10 4/13/07

Pears 5 4/12/07

Pears 16 4/13/07

Pears 4 4/13/07

Plums 30 4/13/07

Here's how I am picturing your table:

GH1

GH2

Details =Fields!Count.Value

GF2 (by Fruit) =Sum(Fields!Count.Value)

GF1 (by Date) =Sum(Fields!Count.Value)

Grand Total =Sum(Fields!Count.Value)

In this case, your report would look like (with the details hidden):

4/12/07

Apples 45

Pears 5

Date Total: 50

4/13/07

Apples 25

Pears 20

Plums 30

Date Total: 75

Grand Total 125

Hope this helps.

Jarret

|||

Thanks again for your detailed answer. You have the right idea with the data, but because the detail data needs the summary for the total group I am not able to carry the expression down. I get an error (can't sum expression with aggregates.). Using your example, we would need to know the summary of total fruit for the day to determine the percentage of daily fruit represented by pears (or apples or oranges). I would then need to summarize all percentages for an overall total. For now I overcame my issue by bringing back group totals in my query. I was just hoping I wouldn't need to do this. In crystal I was able to create a global variable that could be set with each group and displayed at the end. I can't find a way to do this in RS.

|||

Have you try this : RunningValue(Expression, Function, Scope)

|||

I get the same error message:

Aggregate functions cannot be nested inside other aggregate functions.

|||

Maybe this will help. Here is the expression located at the first group level:

=iif(sum(FieldA) < 0
,sum(FieldA)*FieldB
,iif(sum(FieldC) = 0
,0
,iif(sum(FieldD * FieldC)/sum(FieldC) < 0
,(sum(FieldC)-sum(FieldE))*5.25
,(sum(FieldC)-sum(FieldE))*(sum(FieldD * FieldC)/sum(FieldC))
)
)
)

I get the correct result at this level. Now I need to sum all of these values for a grand total. There are no values at the detail level.

|||

Try putting that same exact expression in your other group level, and your report footer (for your grand total).

Jarret

|||I don't get the right total because the formula depends on the summary of each individual group total and not the overall total. The expression above evaluates each detail record for the first group level and produces a result based on that group's total. I did try to add the scope to the expression in the second group level total, but this resulted in a scope error. Unfortunately, there doesn't seem to be an easy solution but I am extremely grateful for the advice.

Group summary totals Help please

Hi,

I am pretty new to Crystal 8.5 and am having problems using formulas on a group summary field.

The database is > 1 million records and I am trying to group a field (for quicker report generation). The field is the time (in seconds) numeric and I want to be able to manipulate this field and get it into hhhh:mm:ss format.

In this application, I want to add up all the seconds for each person in a department, then give a total for each person however in hh:mm:ss format.

Eg Person Table

Name Duration(in seconds)

Peter 50
John 10
Peter 45
John 22
John 80
Peter 100

The Crystal generated SQL will generate

Peter 195
John 112

This is straight forward, however, I now need to convert this to hh:mm:ss format to give me

Peter 00:03:15
John 00:01:52

I can do the above if I look at each individual record (ie no grouping) and use formulas on the Duratin field, however, because of the size of the database, I want to group the records and then manipulate the SUM (Person."Duration") field.

Does anybody have a solution?

(I hope my explanation of my problem is clear ...)

Thanking you in advance.

TheodoreOK Guys,

Solved the problem.

Thanks anyway.

Theodore|||Theo,

I have similar problem, any chance of posting the solution

Regards

Steve Dyson|||Why do you not just use the same formula you used for individual records, to convert the summary? The field would be sum of duration.|||My problem is that i have the following:

Planned and unplanned items.

I can group and Sum them that is the easy bit.

Because this is going to be a sub report, i need these totals to appear in the Page header as i only want to see the Totals

Steve

Wednesday, March 28, 2012

Grouping problem

Wednesday, March 21, 2012

Group Total Summary Help

Group summary totals Help please

Firehose mode

Blog Archive

About Me