Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

5 parameters in a report Expand / Collapse
Author
Message
Posted Saturday, March 30, 2013 2:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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
Post #1437183
Posted Saturday, March 30, 2013 2:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 13,238, Visits: 11,017
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1437246
Posted Saturday, March 30, 2013 5:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.
Post #1437253
Posted Sunday, March 31, 2013 2:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 13,238, Visits: 11,017
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1437275
Posted Sunday, March 31, 2013 2:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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

Post #1437279
Posted Sunday, March 31, 2013 3:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.
Post #1437281
Posted Sunday, March 31, 2013 11:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 14, 2014 1:28 AM
Points: 219, Visits: 693
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.
Post #1437359
Posted Monday, April 1, 2013 12:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:46 PM
Points: 17, Visits: 30
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.
Post #1437362
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse