No, you're not the only one who needs this functionality.
I have a report with 11 single cell subreports with which I would like to do some pretty basic arithmetic, however, there seems to be no way to reference the values of subreport output in Reporting Services. Why is it any more difficult to reference a subreport value than a textbox value, considering that you can embed a subreport in place of a textbox?!
This really is disappointing as it's a pretty basic requirement, sort it out Microsoft! It's about time you listen to what your customers want from a product rather than dictating to them what they can have.
|||I think that is indeed a limitation. However, I have a suggested work-around, as I had to do something similar with a report. Pass in the monthly sales figure as a parameter to the subreport. You can then display the adjustment you need to subtract as well as the adjusted total right in the subreport.|||Maybe I'm not fully understanding the problem, but why can't you use navigation from the subreport back to the parent report and simply pass the value that you need back as a parameter?|||Sorry, I'm new at RS. I didn't know I could return a parameter from a subreport back to the main report. If so, that would handle my needs very well. I'll dig in that direction for awhile and see where it leads me.|||I think you all are thinking of this problem in a programmatic sort of way in that you pass parameters from a parent report to a subreport and then return values from the subreport to the parent report.
A subreport is merely a report. You can pass parameters from a subreport to other reports (ie. the parent report).
See if you can pass the value of the field in your subreport back to the parent report and simply output the value.
|||Assuming you are running against a database, and since you appear to only have one value in this subreport, couldn't you use a subquery in the parent reports query to get the value? That would avoid the overhead of a subreport.
|||
steveareno wrote:
Assuming you are running against a database, and since you appear to only have one value in this subreport, couldn't you use a subquery in the parent reports query to get the value? That would avoid the overhead of a subreport.
Well yea. I assumed that there was more in the subreport than one value. If that's all that is the subreport then brush up the SQL in the parent report a bit.
|||
No, you're not the only one who needs this functionality.
I have a report with 11 single cell subreports with which I would like to do some pretty basic arithmetic, however, there seems to be no way to reference the values of subreport output in Reporting Services. Why is it any more difficult to reference a subreport value than a textbox value, considering that you can embed a subreport in place of a textbox?!
This really is disappointing as it's a pretty basic requirement, sort it out Microsoft! It's about time you listen to what your customers want from a product rather than dictating to them what they can have.
I did reporting services beta-testing for MS and they were very receptive to new features. Multi-select parameters, a browser print control, and a version (at the time) for SQL Server 2000 are three examples. All of those came from beta participants.
Eleven subreports is a lot of overhead. I don't know the details of your report, but maybe you could also get the values in some kind of union query or sub query as I stated previously? You're querying the data 11 times in your subreport anyway. For example:
select field1, field2 from table1
union all
select field1, field2 from table2
union all
select field1, field2 from table3
union all
select field1, field2 from table4
union all
etc...
|||I don't pretend to know everything there is to know about Reporting Services and I wish I knew of a better way of compiling this overall report, but I've tried a lot and cannot find one.
Maybe my comments about MS are critically negative but something like this shouldn't be so complicated, and I'm sorry to say this, but MS do seem to do this a lot with their software. They do make some very powerful applications but sometimes it does seem like you need a degree in machine code to make it work to it's full potential.
I appreciate your suggestions but to use union or subqueries would be extremely complicated, if it would work at all.
If you're interested here are some details of what I'm attempting:
The main report is generated by a query with one table on a self join. This table holds period end valuation information on an individual part by part basis. The table has a datetime stamp so by using the self join I can create a report which shows an opening stock valuation, a closing stock valuation and a variance between based on a user selected parameter.
All the subreports are single cell but are passed three parameters by the main report. The opening datetime, the closing datetime as selected by the user and then the part number of the stock item. There's about 600 odd part numbers so the subreports, even though they are single cell in themselves, end up populating columns on the right side of the report detailing stock transaction analysis between the user selected dates for all the part numbers on the main report.
In theory all the transaction analysis information when totalled horizontally should match the variance between start and end of the main report for each part number, but as performing simple mathematics between subreport values seems to be overly comlex (and judging by the amount of other references on the MSDN forum of others having the same problem I don't seem to unique in my opinion) there's no quick way to validate this except by exporting the whole thing to Excel (don't even start me on that one, LOL, I did find a way around Excel ignoring subreports, but this too is complicated IMHO) and performing the calculations there.
Some of the subreports contain queries on WIP, some on stock transactions, some on inspection, some on dispatch, some on subcontract etc etc, so they all pretty much have unique queries based on different combinations of tables.
And to top it all off, the mainframe which hold all of this data is not using a SQL database, it uses a multivalue pic database from which we have to extract the data daily, so some of the tables in some of the queries are multi value, some single value, so to combine all these separate queries into even just a few would be a multivalue nightmare with duplicated values coming out of my ears. By keeping the queries separate controlling duplicate information is much easier.
Still, if you can offer some suggestions on how to make it easier I'm very interested.
All the best,
Paul
|||ugh. nightmare is an understatement! Good luck with that report. I have a (very) remotely similar inventory report that takes snapshots of data (using DTS) before and after our costing runs, and then does a comparision. But that is in sql server and in a relatively normalized database.
I guess when I get into reports as complex as that I have used UDF's, and sometimes even code to get the data to a certain point so that it can be used by RS more easily. I have even set up automated tasks to open Microsoft Access and run a macro to process data into tables, then hook my reports into access. Having said that, I don't envy you!
I also have to admit that subreports in reporting services have MUCH to be desired.
We once used a database called titanium that had such bad a ODBC drivers we ended up doing CSV extracts every night. the extracts were linked into access and the queries written there, or as mentioned above, the data processed and put into tables. Probably not much of an option for you though.
|||"You can pass parameters from a subreport to other reports (ie. the parent report)"How is this done?
|||
In my experience, a subreport is no different than the parent report.
How did you pass the parameter from the parent report to the subreport?
Usually it's done with navigation... Do it the same way but from the subreport to the parent report.
|||"How did you pass the parameter from the parent report to the subreport?"I do it by right-clicking on the subreport and selecting properties. Then in the parameters tab I assign the subreport parameters with values from the parent report. Is this what you mean by navigation? If so, I can't see a way to assign a value from the subreport as the value for a parameter in the parent report using this method.
