January 5, 2017 at 11:57 am
Hello, this is a design/approach question because I'm still clueless as to SSRS capabilities.
Dev environment: Visual Studio 2010 Shell, SQL Server 2012 on Windows Server 2012.
I'm tasked with creating SSRS reports for about a dozen products from this facility.
Each report represents batch data. Data generated when that batch of product was produced.
User reqs:
1) User enters a date/time range using calendar widgets and selects a product.
2) A list of batches for that product that occured in that time span is displayed.
3) User clicks on a batch in the list and a new window is displayed containing details of that batch data - the batch data report.
For each product a different set of query parameters is needed to mine the data.
My first thought was - if I had a dozen csv files containing parameters for each of the product types then my code (of a single report) would cyle through that list and send a query for each line of the csv file.
The end result of those queries would produce a dataset which would map to a table that was consistent across all the products.
And used within a single report definition.
But I don't think my csv idea is possible with SSRS. So I'll probably need to make a report for each product.
I'm thinking a main report for the time range and product type selection.
And then the main report offers a list of drillthrough report links.
But can I dynamically choose which product report to use based upon the initial parameter selection of the product type?
And can dynamic parameters be initially generated from a query? For example could I query the database for all products and offer that list to the user's initial selection? Although if a new product was entered in the DB *before* a respective report was built to handle it... I'm not sure how I'd handle that scenario.
I know these aren't specific coding questions but any advice and insight is appreciated.
Thanks
January 6, 2017 at 3:19 am
This sound like what you're after are cascading parameters, which is easily possible in SSRS, yes. Note, however, that your report would only have one format. if you want to display different reports depending on different parameter values, then I would suggest creating different reports and training your end users to use the correct report for their needs.
Also, SSRS can only get data from SQL, not CSV. Any data you need will need to be on your SQL Server.
Now, back to the topic at hand 🙂
Let's say you have 3 parameters that cascade, Office, Team and Employee, these respecting come from 3 tables in your database which are also called Office, Team and and Employee.
Firstly, let's create a dataset (call is Offices) to get the list of Offices from your Database, with some nice simple SQL:
SELECT O.OfficeID, O.OfficeName
FROM Office O
WHERE O.OfficeStatus = 1;
This returns a list of all of your offices with are still open. Now create a parameter on your SSRS report called @office. Open up it's properties and change it's data type to represent your OfficeID datatype (let's say it's INT). Now the Available Values Pane and select the Get values from a query Radio button. Click the drop down for dataset (the warning is only really applicable is you have a HUGE number of available values) and select your Offices Dataset. In the Value field select OfficeID, and OfficeName for the label Field.
Your first Parameter is now ready 🙂
Next your first cascading parameter, Team. Again, create a new Dataset (Teams), and again some nice simple SQL:
SELECT T.TeamID, T.TeamName
FROM Team T
WHERE T.OfficeID = @office;
SSRS will realise that you're now using your previous parameter to get a list from this dataset. Repeat the steps above, but for the Team Dataset and Parameter instead. if you were to run the SSRS report now, you would see that the Team Parameter drop down list is greyed out. This is because the values for Team are unknown at that time. Select an Office, and suddenly your drop down box is alive and well, and (most importantly) has a list of teams only at the office you selected.
Finally, add your Employee Dataset. Unsurprisingly, the SQL would be:
SELECT E.EmployeeID, E.EmployeeName
FROM Employee E
WHERE E.TeamID = @Team;
Again, add your parameter and available values.
Voila! You now have cascading parameters! You can then have your final dataset(s), that returns the data for your report, using your @Employee parameter, and you can guarantee that the @Team and @office Parameters will be valid, and your values have cascaded down.
You'll need to apply this to your own report, but hopefully that all makes sense. Also, note, that when adding paramters they need to display in the order of the cascade. For example, if you expanded your Parameters folder in your Report Data Pane and it displayed:
@Office
@Employee
@Team
This would result in a report failure, and SSRS would attempt to evaluate @Employee before @Team, which is impossible. You would need to ensure your parameters were listed in the order of:
@Office,
@Team
@Employee
If you want to experiment, here is some DDL and sample data for the Office, Team, Employee tables:
USE DevTestDB;
GO
CREATE TABLE Office (OfficeID INT IDENTITY(1,1),
OfficeName VARCHAR(30),
City VARCHAR(50));
CREATE TABLE Team (TeamID INT IDENTITY(1,1),
TeamName VARCHAR(20),
OfficeID INT);
CREATE TABLE Employee (EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(50),
TeamID INT);
GO
INSERT INTO Office (OfficeName, City)
VALUES ('Sandwich House', 'Nottingham'),
('Devon Mansion', 'London'),
('South Down House', 'Brighton');
GO
INSERT INTO Team (TeamName, OfficeID)
VALUES ('Blue', 1),
('Green', 1),
('Red', 1),
('SallyKats', 2),
('DaveDogs', 2),
('Cube', 3),
('Pyramid', 3);
GO
INSERT INTO Employee (EmployeeName, TeamID)
VALUES ('Jane', 1),
('Paul', 1),
('Steve', 2),
('Jenna', 2),
('Steven', 2),
('George', 3),
('Harry', 4),
('Richard', 4),
('Tom', 4),
('Katie', 5),
('Sally', 5),
('Leila', 6),
('Fred', 6),
('Dave',7),
('Lynda', 7);
GO
SELECT *
FROM Office O;
SELECT *
FROM Team T;
SELECT *
FROM Employee E;
SELECT O.OfficeName, O.City,
T.TeamName,
E.EmployeeName
FROM Office O
JOIN Team T ON O.OfficeID = T.OfficeID
JOIN Employee E ON T.TeamID = E.TeamID;
/*
--Clean up
GO
DROP TABLE Employee;
DROP TABLE Team;
DROP TABLE Office;
GO
*/
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 6, 2017 at 7:07 am
Awesome. Exactly the kind of specific guidance I needed!
Thanks, Thom. Very clear description.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply