How to total values from subreports onto the body of the main report

[nsimmons]

Hi All,

I have a report that contains 5 subreports. Each subreport calculates a value. I need to be able to total the values in each subreport and display this on the body of the main report. In Crystal Reports I could create shared variables in each subreport to pass to the main report, and then total them, but I can't see a way of do the equivalent in SQL 2005 Reporting services....

Any help would be appreciated.

Thanks,
Neil.

 

[xlbo]

Why do you need to calculate the values in subreports ? Why not have multiple datasets in 1 report ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nsimmons]

Hi - Thanks very much for your reply.

That seems a bit repetitive to me (if I'm thinking of it correctly)

Each of the 5 sub reports outputs a number of fields and a total using a dataset created for it.

So you're saying I should recreate the 5 dataset on the main report which calculate only the totals? I could then add these up to give me my overall total.

This does work and gives me the correct result, it's just that it seems a bit inefficient as the same dataset has to run twice, once to output the subreport info, and once again to calculate the total for that subreport to add to the overall total.

Hope this make sense. I have managed to produce the correct result using the method you suggested, I just hoped there would be a slicker way of doing it.

 

[xlbo]

You shouldn't need to run the dataset twice.

From each of the 5 dataasets, if you have the result of each in a textbox (for example) you can use the ReportItems collection to reference them by name and sum them up e.g.

In "Total Value" textbox

=ReportItems!Textbox1.value + ReportItems!Textbox2.value etc etc etc ad nauseum

You could also (if the calc is simple enough) perform the calculations in a report variable and then simply reference that as the result....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mckenneyj]

ReportItems!
The function can only be used to reference items in the header or footer of the report

Thanks,
John McKenney
Work Hard... Play Harder

 

[xlbo]

??? other way round I think - it CAN'T be used to reference items in the header or footer

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Peager]

The following does work in Headers and footers:

=ReportItems("ObjectName").Value

Where "ObjectName" (Must be quoted) is the name property of the source report object in a data region.

The source object can be hidden but DO NOT HIDE THE LINE the source object is on as it will not render in a PDF or Web Archive correctly.

 

[xlbo]

I believe I was saying that you cannot use ReportItems to REFERENCE an object in the header / footer. You can certainly use the ReportItems syntax in a textbox located in the header / footer but I don;t think you can use it in a textbox to return data from another textbox which is IN the header / footer

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question