Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vanny
Starting Member
9 Posts |
 Posted - 2008-12-29 : 11:40:07
|
| I've developed a few simple reports with Reporting Services, however a more complex report I am working on requires some additional formatting/organization. I've seen that you can use a SQL query in a dataset that has multiple records and columns and build a report with a table the loops through all records and prints the columns as you lay them out. I've also used grouping and seen how you can pass parameters to a subreport.I run into problems when I build a query, print the information in that query and then want to add additional sub querys/information about that account.I'm not talking SQL code either, I am using several joins, a union and a subquery, I like to keep all the core information I can in one query, but to think that I could or should fit every piece of information for the report in one result set is unreasonable.For example (I'm trying to keep it simple and just show the issue, this is not the real report info, just a model after):Create Dataset1 with a SQL query that pulls back every accountnumber, name, email, phone.Create a table that populates every account number, name, email, phone.Now for each account, as it's printed in the report I want a separate set of information posted under the account.So moving on I'd like to:Create Dataset2 with a SQL query that pulls all individual ticketnumber, timetaken, totaltimetake for that account.Create Dataset3 with a SQL query that pulls all joint ticketnumber(ones with more than one account attached), timetaken, totaltimetaken for that account.Create Dataset4 with a SQL query that pulls all projectsnumber, timetaken, totaltimetaken for that account.So each account has it's core information, but then I have completely separate information that needs to be queried for using that account as the criteria and then that information needs to be posted under each account, in different boxes/tables and formatted with subtotals and grand totals to make sense visually.Simply put a single query that prints a single set of columns over and over is far to restricting, has anyone built reports like this? Any ideas? Help is greatly appreciate, thank you for reading the lengthy post. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-29 : 12:27:50
|
| one way of achieving this is by use of subreport. the subreport will show information related to each account and this will be called by next row of table in main report. the second row will be hidden by default and will show only when you click icon on side of account (+ sign). this can be achieved by using toggle visibility based on report item property available in table cell. Another way is to bring all the info in same dataset as a hierarchy with a level field showing level of each data. then bind table to this dataset and apply column filters to show only reqd levels on each row. then toggle visibility based on previous level as in previous case. |
 |
|
|
|
|
|
|
|
Advanced Multiquery Report (Formatting/Organize)