SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


5 parameters in a report


5 parameters in a report

Author
Message
Mac1986
Mac1986
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 791
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64482 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Mac1986
Mac1986
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 791
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64482 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Mac1986
Mac1986
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 791
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


Mac1986
Mac1986
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 791
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.
Mac1986
Mac1986
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 791
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.
THE-LIP
THE-LIP
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search