September 5, 2013 at 8:59 pm
I have a hopefully simple question. I’m trying to learn SSRS a little better, and so I’m trying to abstract something out a little bit. I have a database that consists of a lot of simple frequency counts. I created one report with a table that summarizes the frequency counts and then has a pie chart next to it that is just a graphical representation of the table.
What I was wondering was is there an easy way to essentially “clone” the entire (sub)report and change the dataset to another frequency count.
Each query is a minor modification of something like this:
CREATE PROC [dbo].[usp_ECOG]
@ProtocolNo varchar(30)
AS
SELECT enrollmentID
, e_ProtocolNo
, ECOG
FROM enroll
WHERE e_ProtocolNo = @ProtocolNo;
The table I am looking at is “enrollment” – when a patient is enrolled in a study, a bunch of tests are run (once) and the measurements are recorded. There’s not much you can do with non-continuous data, except frequency counts, so I was looking to build the basic subreport and then reuse it for other datasets. (basically change the stored procedure and go from there?)
CREATE TABLE [dbo].[Enroll](
[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,
[e_PatientID] [int] NOT NULL,
[e_ProtocolNo] [varchar](30) NOT NULL,
[enrollDate] [datetime] NULL,
[enrollOK] [bit] NULL,
[leaveDate] [datetime] NULL,
[PWeek] [int] NULL,
[ECOG] [tinyint] NULL,
[Histology] [tinyint] NULL,
CONSTRAINT [PK_Enroll] PRIMARY KEY CLUSTERED
I'll pretend for a minute that the design of this table doesn't look properly normalized. (Maybe that's the problem!) But the subreports for ECOG, Histology etc are just frequency counts with pie charts and relative frequencies (Frequency/EnrollCount). They work, but the design seems really bass ackwards.
The idea was that I would create a "Main" report and then the users could drop in the subreports they wanted for a given Protocol (because they're similar, but some of the subreports change).
Apologies if this doesn't make any sense! It's been one of those daze.
Thanks!
Pieter
September 9, 2013 at 5:05 pm
Not sure if this helps you out. In a similar situation, I created a report parameter with values same as the sub-report. For my sub-report, I have a sql stored procedure as a dataset. Now, I pass report parameter value as a parameter to my sql stored procedure. While rendering the report, various charts are generated based on report parameter selection. Of course, in my case, chart type is same. I guess you can parameterize that also.
April 28, 2014 at 2:48 pm
Sorry, I completely missed this. What you describe is exactly what I ended up doing. Lots of parameterized subreports that I could include in a "main" report. At that point, building a new main report (if I based it on a template) took less than a minute.
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply