Question about SQL Reporting Services

[Jennacat]

Hello!

I've very new to SQL. I have a database in MS Access that works fine and doesn't need to be converted to SQL. However, the Access reports are giving us problems. They keep splitting our data randomly. I've tried everything to stop this, but nothing works. Our IT guy said to try SQL Reporting Services. I took a class on it and even got the developer edition, but I can't get it to work with our data. Our Access reports are designed with a main report (Product, Test Type, Specie, etc.) and a subreport (Results). One product can have as little as one test or as many as 20. SQL RS keeps taking my results and summing them!! How do I get it to stop?!?! It also won't put all the results for the one test together. It's driving me crazy! I think I'm missing some basic skills though so the fault is mine. Can anybody help? Thanks!!!!

 

[SQL_Hell]

I don't think anyone will be able to answer this question without actually seeing the problem, I would get some screen prints going or attach the .rdl file for people to look at.

 

[georgedwilkinson]

Jenna has already presented this to us. Her problem is a bear.

 

[SQL_Hell]

Ok, on another thread?

A bear? What like a Grizzly Bear?

Don't tell me Bears are randomly breaking into to peoples offices and messing with aggregate functions on reports??

 

[Jennacat]

Beware the beast.........

Oh, this one isn't just any bear! This is one on those bears that belongs in the monster hall of fame! Nobody can tame him! He does what he wants and beware all who go near him!

anyways...........

I've worked a bit more with SQL RS and got some of the data to do what I want it to do, but the more I add to it, the more it goes nuts! But, again, I think this is because of my lack of skill. Could somebody please tell me in plain english how to create a report with a subreport in SQL RS? I can connect to my database no problem and access my tables, but the actual set-up is giving my issues. This is how I would like my report to look:

Product Code

Test Type

Specie: Specie
Duration: X hours

Results Values
Lethal Concentration 50% 24 hrs 100 mg/l
Lethal Concentration 50% 48 hrs 85 mg/l
No Observable Effect Concentration 25 mg/l


Specie: Another Specie
Duration: X hours

Results Values
Lethal Concentration 50% 24 hrs 85 mg/l
Lethal Concentration 50% 48 hrs 62 mg/l
No Observable Effect Concentration 10 mg/l

Different Test Type

Specie: Another Specie
Duration: X hours

Results Values
Lethal Concentration 50% 24 hrs 500 mg/l
Lethal Concentration 50% 48 hrs 400 mg/l
No Observable Effect Concentration 100 mg/l

etc.


Product Code, Test Type, Specie, and Duration come from one table and the Results & Values come from a separate table. One product can have multiple tests or only 1. Plus, there are multiple tests for each test type (Static Acute Saltwater, Static Acute Freshwater, Static Acute Renewal Saltwater, etc). And a product can have both freshwater and saltwater testing. The two tables are linked by ID. so, depending on the product, the report can be one page or 5. Any ideas? Thanks!!

Sorry, I didn't give a screen shot. There really wasn't anything to take a picture of...........