Multiple Tables/SubReporting - SSRS 2008 R2
-
I am having an issue getting a report to work correctly. I have 4 tables that I have created in my database all of which are joined by a QuoteID.
PriceQuoteMain
PriceQuoteRawMaterial
PriceQuotePurchases
PriceQuoteYields
In the PriceQuoteMain table there is one row per Quote.
Every other table there can be anywhere from 1-10 rows per Quote.
The Main data is displayed two - two column tables, column 1 is the headers/labels for the data in column 2 - not an issue right now
RawMaterials is displayed under it as a 13 column table with a header row across the top for the column names.
I can't get RawMaterials to work so I haven't even gotten to the Purchases or Yields.
Title of Report
Main Table Info 1 ----------------------------------------------------------------------------- Main Table Info 2
---------------------------------------------------------------------------------------------------------------------------------------
QuoteDate: --- 1-1-11 ------------------------------------------------------------------------- Avail Capacity: --- 3:00hrs
PartNo: --- 12345-01-------------------------------------------------------------------------- Annual Vol Quoted: --- 3
Supplier P/N: --- 6721 --------------------------------------------------------------------------- Lot Size: --- 15
SupplierName: --- ExampleSupplierName --------------------------------------------------- ToolingLife: --- 15
RawMaterials Table Info
(Header Row) Col1, Col2, Col3, Col4, Col5, Col6 --> Col13
Row1
Row2
Row3
Purchases and Yields would follow same formatting and concept as the RawMaterials does.
I am trying to display all quote details (2 tables for Main, 1 table for RawMaterials, 1 table for Purchases and 1 table for Yields) on one page. So each complete quote gets its own tab in the excel file when exported.
I have tried using Shared Data Sources.
I have tried using one Query for all info, I have tried using 4 queries to get the info.
I have tried subreports, I have tried embedding tables within tables.
I have tried dealing with Parameters to pass from Main to RawMaterials.
I have looked at the Lookup/LookupSet/MultiLookup functions
I really don't like to give up or tell people that it cannot be done, so although I have spent a lot of time on this and am still getting nowhere I am not ready to throw in the towel just yet. But I have run out of ideas of my own. If you have any experience or ideas for this issue, please contact me and I can get you some images and more details that I cannot seem to post here.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
-
I'm not sure from your question exactly which part you are having trouble with, could you include some more detail on that?
I have tried using Shared Data Sources:
- These are always good. You create a single data source connection in your solution, and re-use it for all the projects & reports that require it.
I have tried using one Query for all info:
--This should work fine but note you will get all the main table data repeated for each of the rawmaterials and other table rows. To get around that you can using grouping in the main tables to avoid duplicating rows. Using four datasets would work easily if you use the same parameter in all four and then run the report for a single quote at a time.
How many quotes do you have in the system?
Do you want to iterate through all of them and have them all on different pages?
Are you restricting the report in some way, e.g. by client so that the report doesn't end up being huge once you have a lot of quotes in the system?
One option might be to do a single quote at a time. Create a separate dataset that returns perhaps "quote number", "Quote date" and then you can use this dataset to drive a parameter for the main dataset using the quote number to filter. The report would only ever be 1 quote then.
If this is a report to make it easy for your users to print individual quotes then that's the ideal solution.
If you wanted to make it even more useful you could cascade 2 parameters. First dataset gets a list of clients. The client ID then filters the second dataset to only show quote numbers belonging to that client so when the user picks client1 from the first drop-down, the second drop-down only has that client's quotes. Then they can run the report and print it or export to excel.
If you want them to be able to print all quotes for a client, or a date range then that's a bit more complicated but certainly achievable. Options include using a subreport to iterate through each quote number, or using a single table with multiple (separated) grouping regions in it with a master group on quote number, and forcing a page break between each instance of that quote group.
-
The trouble I am having is getting the data to display as I described. Currently I get the two header tables to show correctly but when I try for the Raw Materials I start getting duplicated outputs.
Have you ever tried to do a report as I have described with a total of 5 tables (2 header tables have a 1-1 relationship and 3 detail tables with a 1-many relationship)?
Currently I have only input 14 quotes and only 7 of them did I input the details. In the end we will have around 10,000 quotes and all the corresponding details.
I do need each quote on a single page. I have a specific format I have to duplicate (hence the need for 5 tables on each page as output).
Eventually we will probably set up a filter of some sort to either only output a specific part or output a specific part type, etc.
I have tried using a parameter of QuoteID and it doesn't seem to work properly and I still end up with wrong outputs.
I have tried one big query with all results. I have tried individual queries for each table's data all linked by QuoteID. I have tried sub-reports. I have tried multiple tables and imbedded tables.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
-
Yes, I have created hundreds of reports of all different kinds, from all different datasources, including very standard items like invoices and quotes and very complex reports such as analytical chemistry certificates of analysis. I've worked with reporting tools like crystal and ssrs for about 6 years and I'm pretty good with T-SQL, PL/SQL and MDX, but of course there is always more to learn.
When you created the @QuoteID parameter, how did you use it? Normally you would edit the SQL so that it filters the data prior to it being returned to the report.
e.g.
select field1, field2...from table (join table 2 etc)
where QuoteID = @QuoteID
This is tried and true and it works well. You don't really want to be bringing back 10,000 records to a report and relying on the report to do the filtering (e.g. using it as a tablix filter or row filter) because it will be extremely slow.
That same technique will work if you just use one query, or five. If you do five queries you will need to add that same @QuoteID filter to each query.
When you say "wrong outputs" what exactly do you mean?
If it is just duplicates, then that's going to be due to the nature of datasets.
If you join a header table to a details table, every header field will be repeated on every details row. That's a one to many relationship as you alluded to. That's what happens when you flatten data into a single dataset.
SSRS provides "groups" to deal with this. Grouping the rows, rather than just using the details within a table (which will show every single row in the dataset) you can only show the group rows.
I suggest you look at the results in the query you are using because it will be clear which rows are being duplicated and give you clues as to how to do the grouping.
It might be easier for you to use 5 separate queries because you can restrict each query to single rows for each raw material. The downside to 5 queries is that you will hit the database 5 times.
It sounds as though you have a good grounding in SSRS but are struggling with taking the next step to developing more complex reports. This particular requirement doesn't sound that complicated but I suggest you might need to do some advanced training or read up on SSRS techniques. 90% of the challenge in SSRS is writing good queries and if you come from a procedural coding background this can be very counter-intuitive. The better you are at coding in .NET for example, the harder it is to switch mindsets to start thinking about sets.
Don't be discouraged.
From here, if you want to provide more information then some screenshots, SQL examples, and dataset results will really help to explain what it is you are struggling with.
-
I found another post online where someone is having the same issue as me. Not sure if this helps explain where I am getting stuck.
Looks like the most recent reply basically shoots down both options that were given as a solution. There are alot of people having issues like this. And I have also found alot of posts about people not being able to use the Parameters and Subreporting as they are supposed to work and have worked in previous versions of SSRS.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
-
I finally resolved my issue, report is working perfectly, had nothing to do with my SQL Code. Just had to start all over again in recreating with subreports and parameters.
8th time's the charm.
Respectfully,Kate SchwidSoftware Developer/Data AnalystOzaukee, WI
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply