March 30, 2013 at 2:26 am
Hi All, I'm creating a basic report with 5 parameters. I want to populate the drop down menu of the report according to previous selected parameter value.
Example: I'm entering below script in dataset Query.
If @ApplicationName = 'MSCloud'
SELECT Networkname,SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where NetworkName = @ServerName
AND Environment = @EnvironmentName
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime desc
If @ApplicationName = 'Velocity'
SELECT Networkname,SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where NetworkName = @ServerName
AND Environment = @EnvironmentName
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime desc
1st parameter: ApplicationName: MSCloud or Velocity
2nd Parameter: Environment: PROD or UAT
Once these two parameters are selected by the user, he should see only that particular ServerName drop down menu.
Example: List of servers of Velocity are VelocityPROD and VelocityUAT
List of servers of MSCloud are MSCloudPROD and MSCloudUAT
Now if I select ApplicationName 'MSCloud' , Environment 'PROD', I should see only MSCloudPROD in the ServerName drop down menu.
Please help what setting should I do in the parameter ServerName
March 30, 2013 at 2:51 pm
How to: Add Cascading Parameters to a Report (Reporting Services)
By the way, is it me or are the two queries in your dataset exactly the same?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2013 at 5:42 pm
both the queries pull from save view. the result set changes according tot he selected parameters in the report. Thats the plan unless you have better way to do it.
March 31, 2013 at 2:15 am
Mac1986 (3/30/2013)
both the queries pull from save view. the result set changes according tot he selected parameters in the report. Thats the plan unless you have better way to do it.
Since both queries are exactly the same, you don't need the parameter @ApplicationName in your dataset. You only need it as the first parameter in your cascading parameters so you can select the appropriate values.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 31, 2013 at 2:58 am
Looks like I need some serious help. No matter what Option I try I'm getting error on the parameter ServerName. I'm not able to cascade this parameter. Below is the query that i'm using. It looks like this is very basic of SSRS but i'm never build complex reports, i'm having hard time. Please help me.
--For CPU Usage
If @ApplicationName = 'Informatica'
BEGIN
If @EnvironmentName = 'Prod'
BEGIN
SELECT Environment,NetworkName, SampleTime, Max FROM Singularity.dbo.PerfData_CPUServerView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
And SampleTime >= @StartDateTime and SampleTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
Order by SampleTime asc
END
ELSE
BEGIN
SELECT Environment,NetworkName, SampleTime, Max FROM Singularity.dbo.PerfData_CPUServerView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
And SampleTime >= @StartDateTime and SampleTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
Order by SampleTime asc
END
END
If @ApplicationName = 'Velocity'
BEGIN
If @EnvironmentName = 'Prod'
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
ELSE
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
END
If @ApplicationName = 'MSCloud'
BEGIN
If @EnvironmentName = 'Prod'
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
ELSE
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
END
March 31, 2013 at 3:04 am
As you can see in my Query I have 5 parameters for the report.
1) ApplicationName: 'Informatica', 'Velocity' & 'MSCloud'. (I have manually enter the 3 values in parameter)
2) Environment: 'PROD' & 'UAT(ITSM = PROD)' (I have manually enter the 2 values in parameter)
3) ServerName: I want to display only the servers related to above 2 selections.
4) StartDateTime & 5) EndDateTime needs no settings.
Please help how should configure the query and parameter settings.
March 31, 2013 at 11:55 pm
I go it. Thanks a lot. I saw a view in Youtube which helped me figure out how to do this.
Thanks all for the support.
April 1, 2013 at 12:20 am
In your query u'll have these parameters
@ApplicationName = 'Informatica' or 'Velocity' or 'MSCloud' --manually added to parameter
@EnvironmentName = 'PROD' or 'UAT(ITSM = PROD)' --manually added to parameter
and for "@ServerName" parameter u can use this query and add to dataset..
IF OBJECT_ID('TEMPDB..#ServerName','U') IS NOT NULL
DROP TABLE #ServerName
SELECT * INTO #ServerName FROM(
SELECT ServerName = @ApplicationName+@EnvironmentName
)S
SELECT * FROM #ServerName
If you choose ApplicationName = 'MSCloud' and EnvironmentName = 'PROD'
In the servername dropdown it will only show 'MSCloudPROD' .
Hope it will help u a lot.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply