OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Apr 17, 2008
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
View 2 Replies
ADVERTISEMENT
Dec 14, 2006
I am trying to report against a cube and I want the user to be able to choose the date for the report.
Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.
I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?
View 3 Replies
View Related
Aug 11, 2005
Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
View 5 Replies
View Related
Jan 11, 2007
Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
View 10 Replies
View Related
Jan 1, 2014
I am new for SQL SP, I have three table,
Table 1 - Merchant
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
Table 2 - Region
RegionID RegionName
1 Region1
2 Region2
3 Region3
Table 3 - Offer
OfferID RegionID MerchantID
1 1 3
2 1 2
3 1 1
4 2 2
5 2 4
I have two input parameter for this SP, @MerchantName = NULL and @RegionName = NULL
Expected Result Table
If input parameter @MerchantName = NULL and @RegionName = NULL
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
If we Search by RegionName="Region1" Ex: @MerchantName = NULL and @RegionName = 'Region1'
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
If we Search by RegionName="Region2" Ex: @MerchantName = NULL and @RegionName = 'Region2'
MerchantID MerchantName Zip
2 Merchant2 1002
4 Merchant4 1004
If we search by MerchantName='Merchant1' Ex: @MerchantName = 'Merchant1' and @RegionName = ''
MerchantID MerchantName Zip
1 Merchant1 1001
How to get the expected result for the above scenario.
View 3 Replies
View Related
Oct 4, 2007
I created a report with a school parameter for all our schools in SQL report. The school parameter gets it's value from a query. The report is generated based on the selection made by the user. If user choose school "A", report is generated for school "A" and if user choose school "B" report is generated for school "B".
I am using a centralized user id and password for all users. A report URL (report link) is sent to user to access the report.
Currently, from the school parameter (school drop-down list) users from any school may/can choose report for other schools other than theirs schoos. The content of the report is sensitive and we do not want anyone to see anyone else report.
I want to create a user based security, so based on the user id for school "A", he/she will only be able to see school "A" report but nothing else.
I was trying to accomplish this by creating linked report but it does not look realistic to create a folder for each school on the report server (which means about 180 or more folder for all our schools ). Does anyone else have better suggestions what can/should I do?
View 4 Replies
View Related
Aug 21, 2015
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
View 5 Replies
View Related
Jul 29, 2015
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
View 2 Replies
View Related
Aug 28, 2015
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies
View Related
Oct 18, 2015
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
View 2 Replies
View Related
Jan 8, 2007
I have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
View 5 Replies
View Related
Feb 4, 2008
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
View 4 Replies
View Related
Aug 10, 2015
I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
View 5 Replies
View Related
Jul 27, 2006
Hi everyone,
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original
- sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS
SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName
FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID
INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID
INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID
INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID
Where @status = A3.tStatus ...
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @date smalldatetime )AS SELECT A1.uLastName, A1.uFirstName, A2.mName, A3.tStatus, A3.tScore, A3.tStartDate, A3.tCompleteDate, A4.cName FROM VSALCP.dbo.[User] AS A1 INNER JOIN FSDLMS.dbo.Student AS A5 ON A1.uID = A5.stID INNER JOIN FSDLMS.dbo.Transcript AS A3 ON A3.t_FK_stID = A5.stID INNER JOIN VSALCP.dbo.Member AS A2 ON A2.mID = A1.u_FK_mID INNER JOIN FSDLMS.dbo.Course AS A4 ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...
"If status = ALL select * status"
Thanks for taking a look!
View 3 Replies
View Related
Jul 2, 2007
Hi,
I am trying to find the best way to use a date parameter on a OLAP report and get the calendar pop to be used instead of a dropdown.
Whenever I change from string to datetime for the parameter I (of course) get an error about different data types, since OLAP is returning the fields as strings and formatted.
Any easy (or best) approach to use to get a calendar pop to work with an OLAP report? I am mainly doing this for date range searches and do not want people to pick from huge dropdowns for dates.
Thanks.
View 1 Replies
View Related
Apr 5, 2007
Hi !!!
I have a problem, I don't know how to do...
I have 2 dataset from 2 differents XML dataSource...
I want the result of one dataSet to be a parameter for the second !
I try to add a parameter in param tab, with expression, I choose DataSet and
=Sum(Fields!ID.Value, "DataSetID")
but when I execute the query, the prompt for the param appear, but the value of id was <NULL>...
Someone have ideas to resolve this problem ?
thank you for your help !
View 1 Replies
View Related
Apr 16, 2007
I'm trying to set up a parameter report from a OLAP cube. I need 3 dynamic parameters(ThisYear, ThisMonth, YESTERDAY).
THISYEAR: =CStr(DatePart("yyyy",Today()))
THISWEEK: =CSTR(DATEPART("ww",Today()))
YESTERDAY: =CSTR(Datepart("d", Today().AddDays(-1)))
So far so good. I want to send this report by email everyday, so I need to set these parameters as a default value. This is where I run into problems.
When I add any of these parameters I get the following error "The restrictions by the CONSTRAINED flag in the STRTOSET function were violated". This problem occur when I choose NON-queried under Report->Report Parameters, and set a default value for THISYEAR under DEFAULT VALUES
=CStr(DatePart("yyyy",Today()))
Hope someone found a way around this problem.
Thanks in advance!
View 2 Replies
View Related
Feb 28, 2007
I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.
Thanks for any ideas.
-Josh R.
View 1 Replies
View Related
May 23, 2007
I am trying to use the code below to set a formview datasource parameter in the page_load section of a user control (ascx file): public void Page_Load(object sender, EventArgs e){ formview_datasource.SelectParameters.Add("@department_id", "e62bbc7d623f44a68e101cba90e839s3");} However I am getting the following error: Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@department_id'.
So it would seem that page_load in my user control isn't being called? I'm not sure why or how to work around it. Has anyone else experienced anything like this or can give me some pointers on where I am going wrong?
ThanksBrad
View 3 Replies
View Related
Jan 22, 2007
Everything I have read says that this is possible in Oracle 9 and greater versions, but I can't get it to work...
Here is my query:
select * from employee where employee_id in ('111','222')
This works fine in the data tab until I replace the values with a parameter. I get the pop-up to enter the parameter values but no matter how I enter them, I don't get any rows returned. I've tried entering them as '111','222' and as 111,222 and changing the parameter type from a string to an int, but nothing seems to work.
I am running this on SQL Server RS 2005 and my Oracle driver (Oracle in OraHome92) is version 9.02.
View 6 Replies
View Related
Feb 23, 2007
hi,
how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?
Thanks,
shanthi
View 3 Replies
View Related
Feb 26, 2007
Hi all,
How must I change the mdx that is generated for the available values for a user parameter in order to get the content sorted?
Regards,
Henk
BTW the exact mdx query is given below (and the label field of the parameter is set to 'ParameterCaption'), but I would already appreciate an example of a simple mdx.
MEMBER [Measures].[ParameterCaption] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[Nummer en Naam] AS '[Organisatie].[Kosten Nummer].CURRENTMEMBER.MEMBER_CAPTION +": "+ [Organisatie].[Level 5 naam].CURRENTMEMBER.MEMBER_CAPTION'
--MEMBER [Measures].[ParameterValue] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Organisatie].[Level 5 naam].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption],
[Measures].[Nummer en Naam]
, [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS
, filter ([Organisatie].[Level 5 naam].MEMBERS,[Measures]) ON 1 ,
[Organisatie].[Kosten Nummer] on 2
FROM ( SELECT ( STRTOSET(@OrganisatieLevelnaam, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurPeriode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@KalenderFactuurJaarNummerLang, CONSTRAINED) ) ON COLUMNS FROM [FMR DWH Afgenomen Dienst])))
View 6 Replies
View Related
Apr 30, 2007
Dear All,
I am facing one problem with reporting services 2005 (SSRS), When i try to pass a parameter to Cube which is built in SSAS, i can not use a parameter where i can type the value instead of choosing it from a dimension.
Any way we can type the value for parameter, instead choosing from the drop down? If can, then how can we create the parameter? and how can i write a MDX to read it?
Thanks.
Regards,
Swarna.
View 4 Replies
View Related
Oct 10, 2007
I'm using the asp.net login controls and am storing the user data in the sql table that get's automaticaly built by visual studio. I display records based on who is logged in. Currently i use a Session parameter in my datasource for the user name and store the username in a session variable on the page load event, Session["UserName"]= User.Identity.Name.
Is there a way i can use a Profile parameter in my data source and skip setting session variable all together? I tried putting Name and UserName and User.Identity.Name in the Profile property but nothing seems to work. Thanks for any help.
View 6 Replies
View Related
Jul 20, 2005
I am trying to select a group of records based on a parameter valuepassed to the db from a web page. The value comes in as @Status andhas a list of statusID's: (1,2,5,9) I've tried to use"Where table.status IN (Select * from @Status AS ValueList)"And also tried"Where table.status IN (@Status)"And neither worked. Any suggestions?
View 2 Replies
View Related
Oct 18, 2006
I am trying to pass multiple values as parameters into my update command:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@selectedusersparam)));I develop my parameter (@selectedusersparam) using the following subroutine: Private Sub btnAddUsersToDeployment_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUsersToDeployment.ClickDim iVal As Integer = 0Dim SelectedCollection As StringSelectedCollection = ""If (lsbUsersAvail.Items).Count > 1 ThenFor iVal = 0 To lsbUsersAvail.Items.Count - 1If lsbUsersAvail.Items(iVal).Selected = True ThenSelectedCollection = SelectedCollection & "," & lsbUsersAvail.Items(iVal).ValueEnd IfNextSelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))Session.Item("SelectedCollectionSession") = SelectedCollectionSqlDataSource4.Update()ltlUsersMessage.Text = String.Empty 'UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (@selectedusersparam))'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"ElseltlUsersMessage.Text = "Select users before adding to deployment. Hold Control for multiselect"End IfEnd SubFor some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the queryI don't understand why.
View 4 Replies
View Related
Jul 26, 2006
I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like....
SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))
The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?
Any help much appreciated!
View 3 Replies
View Related
Feb 12, 2014
I have a SQL query like this:
Select * from myTable where myTable_ID in (2,6,7,9)
I want to build the list of values that are in parenthesis, in my VB code and pass it in through a parameter, so it's like this:
Select * from myTable where myTable_ID in (@myValues)
Is this possible? I tried it where myValues = '2,6,7,9' but am getting a conversion error. I'm starting to believe it's not possible to do what I'm trying to do. Is there another way?
View 2 Replies
View Related
Jan 4, 2007
I have a report with a dataset/parameter to select the salesperson.
SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID
FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN
[DatabaseName$Sales Shipment Header] ON
[DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code]
UNION ALL
SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID
ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name
In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons".
Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
View 3 Replies
View Related
Jul 26, 2007
Hi all
I have a parameter that is populated by a dataset.
How can I set its default value to the first row in the dataset?
Thanks
View 2 Replies
View Related
Dec 12, 2007
I am building a report with several search parameters; three of which are dropdown boxes each populated by a data set. I wanted something more user-friendly than the default "(Null)" in the dropdown, so for each data set, it returns a result set UNIONed with " N/A" to show up first as the default.
For some reason, when the report loads, the first dropdown populates just fine and does indeed have the default value " N/A" but the other two dropdowns are grayed-out. Until you change the first dropdown (to anything, apparently it doesn't matter what you change it to) the other two stay grayed-out. When the first is changed, the other two are populated and default to the desired " N/A" default value.
Why would it do this, when it didn't do it before; i.e. when the default for all of them was simply null. The dropdowns are not dependent on each other; they don't depend on other parameters for their values. Any help would be appreciated.
View 2 Replies
View Related
May 8, 2008
Hello,
I have the following issue:
I have the following statement into a function:
select a,b form T where c IN @parameter
t is the table
c is datatype= integer
@parameter is a input parameter in the function, the @parameter contains more values and passed as a string.
Running the statement above I got an error due to conversion type.
How can I pass a list of parameters in the @parameter variable to make the statement works?
Thank in advance.
View 4 Replies
View Related
Nov 13, 2007
I have a year-month hierarchy that I am using as a parameter in a report. I'm using the From query choice for available values and using the ParameterValue.
My parameter list displasy as:
2006
1
10
11
12
2
3
...
2007
1
10
11
12
2
3
4
Is there a way to get this to display months as integers rather than strings?
Thanks.
View 5 Replies
View Related
