Dataset Becomes Empty After Adding Query Parameter
Mar 13, 2008
Hi all,
hope anyone can help me on this one.
I have a parameterized report based on a MDX query (so no drag and drop of filters and fields).
Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter.
when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.
Has anyone experienced this problem before, or am i forgetting something here?
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
hi, i want to execute a finctionX() based on the returned resultset of SQLDataSource. For example, if the SELECT command returns nothing based on a specific search criteria then I want to execute functionX(). Currently, I have a string that displays "No Result" in the GridView in such case. How to catch the resultset of SQLDataSource? -nero
It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:
reportMonth - Just a non-queried list of months for the user to select reportYear - Generated using the following query from dataset "Years":
declare @curYear int set @curYear = 2000 declare @yearTable table (repYear int not null) while @curYear <= year(getdate()) begin insert into @yearTable(repYear) values (@curYear) set @curYear = @curYear + 1 end select * from @yearTable
This generated a list like:
2000 2001 2002 etc.
I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.
I have a report that prompts the user to select a parameter, for simplicity, let's say the parameter is for color choice, options are Red, Yellow, Blue or *. The * is for include all colors. I am passing that parameter back to the dataset query which, again for simplicity is
select Hue from AvailableColors whereColor = @ColorParam.
For a specific color this works fine, for the "*" selection it returns a null. It would seem that I need to convert the * to % but I am not sure how.....
HI, I am using XmlDataDocuments returned by webservices and query in the reports to populate the reports.
The issue I have is if the dataset used to populate the XML document has any nulls or empty spaces in any field the whole column or row is missing in the data generated by my query in the Reports. Sometimes the whole data is not being returned to the reports.
I have tested the webservice with sample data and it works well, returns data.
I use the ISNULL() in my stored procedures to avoid nulls but with empty strings, replacing them with a default value in the managed code is expensive and slows down the reports. Is there some feature I am missing because of which nulls and empty strings are causing this problem.
I'm performing an insert, and I want to insert specified text, if the field is empty. I thought it should be a simple matter of a case expression, which I have below, but it's not working:
CASE PropertyBuilding when '' then [PropertyStreetAddress] else [PropertyBuilding]end
I am mapping an entity from SQL 2005 to another entity in another system on SQL 2000. Since the destination system has its own ID generator, I want to keep the generated ID for each row of my table in a column of my table in SQL 2005. The new column is in the dataset now , but I don't know how to update my table to have that column values (The OleDbDestination just insert new items.)
Hi, I can't seem to get this where clause to function as I wish.. must besomething simple but I can't see it!have triedac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%'ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%'ac.coursename like CASE len(@course) WHEN 0 THEN '%' ELSE '%' + @course +'%'All work fine when I enter something in the parameter, but not when I leaveit blank! Any ideas why this is the ...case?Cheers!Chris
Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:
WHERE ( [Field1] = @Field1_Filter OR @Field1_Filter IS NULL )
How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.
I also don't know how you would check for the "not entered". Someone suggested the following SQL:
WHERE ( [Field1] IN (@Field1_Filter) OR @Field1_Filter IS NULL )
This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".
I am have a reporting server which connects to a analysis server which connects to a sql server and a db2 server. I am using windows integrated security all the time except for the DB2 connection. That is a fixed username/password.
Reporting server (RS), analysis server (AS) and sql server are all on the same machine.
I created a local group and added the correct users to it. I gave that group browse rights on the reports so that works correctly.
I created a role in AS and added that group to that role. Gave all read rights to that role.
I created a login for that group on the SQL server and gave that group db_datareader role.
When I connect as an administrator every reports runs fine.
When the test user connects they can browse the reports but the drop down boxes for the parameters are empty.
hi, i am having 2 columns in a table in a dataset. i want to add those two columns and bind the resultant total as a single column to the datagrid. is it possible. if yes, how o acheive this? please help me. thanks in advance. muppidi.
Hi every body... I have a probleme I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime)) I need to call this method. But my problem is how pass parameter ? I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example... now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression : =First(Fields!idEq.Value, "EquipmentDataSet") but when i execute the query, the promter display <NULL>... So I don't know how to do and if it is possible ! I hope someone can help me ! Thank you !
I have a database running in an instance of SQL Server.
I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.
PROBLEM:
I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:
Microsoft Visual Studio is Busy.
Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.
Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.
Hopefully someone out there can help.
Additional Services running:
IIS (Whatever version comes with Windows XP Pro. I think 5.1)
SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch
UPDATE: I am editing this post with an update.
I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.
Hello, I have created SSIS package programmatically, I want to add Lookup transformation, How can I add column from reference dataset to the transformation? I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup. IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New(); outputColumn.Name = col.Name; outputColumn.Description = "Staging table output"; outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; outputColumn.ErrorOrTruncationOperation = "Copy Column"; outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);
Please suggest other way to add column from reference dataset to transformation output.
Hi,I run a stored procedure with a parameter given from a text box in an accessadp. If the text box is empty then what is passed to the parameter? I can'tseem to get it to flag up as either null, or 0 length.Any tips?Cheers,Chris
If a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:IF @Parameter IS NULL BEGINor IF @Parameter = '' BEGINThe only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:IF @Parameter = '99' BEGIN<Code Here>END
Hi, I'm pretty new to ASP.NET and VB, but I'm working on a project which is essentially a staff directory. Using VS 2005, I've setup a basic grid view which connects to an object which connects via a data layer to a SQL database with Name, Surname, Email, Extension No and Department fields. I have it working so that if a user enters a name and surname for example, it will return all records with either the name matching or surname matching input parameters. What I want to do is to set up a SQL query which is: SELECT * FROM records WHERE (Name LIKE @Name) AND (Surname LIKE @Surname) AND (Email LIKE @Email) AND (Dept LIKE @Dept) so that if the user only enters the first name and surname for example as above, it ONLY returns the record which matches the first name and surname and not all records with either/or. One way I've thought of to do this is to convert the empty fields to "Nothing" so that it fullfils the search parameter for the empty fields. If someone can explain how to do this, or can suggest a better way, I'd be grateful. Cheers, Tom
I have an SSIS script task using c#. i need to refere an .xsd dataset in the c# code. i tried to set property below.Build action to Content or Compile Copy to output directory Copy always But still i m unable to use the dataset in my code.
I keep getting "Value cannot be null. Parameter name: dataSet" when I run this statement and bind it to a dataset using a sqldataadapter in ASP.NET using VB.NET.
"SELECT playerstats.playerid, playerstats.gameid, SUM(playerstats.fta), SUM(playerstats.ftm), SUM(playerstats.tpm), SUM(playerstats.rb), SUM(playerstats.fga), SUM(playerstats.fgm), SUM(playerstats.tp), SUM(playerstats.st), SUM(playerstats.a), 100(SUM(playerstats.fgm)/SUM(playerstats.fga)) AS fgp, player.playerid, player.lname + ', ' + player.fname AS fullname FROM playerstats, player WHERE playerstats.playerid = player.playerid AND player.leagueid = " & ddlLeague.SelectedValue & " ORDER BY " & strSortField
I have two datasets (for querying active directory but this is kind of irrelevant for my problem which is a basic one)
The first gets a list of groups and I want to display GroupName in a dropdown list:
SELECT DistinguishedName, GroupName, GroupDescription FROM tvf_GetListOfGroups('LDAP://OU=Applications,OU=Groups,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree') ORDER BY GroupName
The above is fine and works but then I want to use the corresponding DistinguishedName of the GroupName selected in the dropdown to drive the second query to populate the report
SELECT * FROM tvf_GetGroupMembers('LDAP://' + @DistinguishedName) ORDER BY UserLogin
My problem is how to populate @DistinguishedName with the correct value to run run the query.
Im trying to pass a integer parameter to my dataset query depending on the selection made from the user. It is suppose to get the Top number off records based on what number the user chooses
I get the error Incorrect syntax near '@Param_TopNo, If i take the top @param_topno out the query works
The cutdown version off the query is...
Select Top @Param_TopNo SaleDate, Consultant, Productname
From tblSales
where product = 2000
Im not sure if the method above is even achievable !!!! does anyone know how i can pass a variable/parameter into my dataset and maybe provide an example off the code.
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 !
Hi, Is there any work around to passing a set of strings to a parameter in a Typed Dataset for example I am pasing '4226222172004','4212012182004' which I build on my code the number of items will vary passed on the user selection but since the Typed Dataset uses sp_executesql and the parameters are change to '''4226222172004'',''4212012182004''' Any ideas how I can format the Parameter I am passing.so that it will end like where in ( '4226222172004','4212012182004') instead of where in ('''4226222172004'',''4212012182004''') I again the number of parameters will very. Thanks Julio D
I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.
How can I do this?
I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.
Now I can query dataset and bind to report parameter. But I have some problem about setting default value. I don't know, how to select max value from dataset by using expression.
Example, I query disticnt year from my table. The result has 2 years "2007" and "2008" select distinct year(mytime) as myyear from mytable.
I set available parameter by using this dataset. Then, I set default value for this parameter by the other dataset ...
select max (distinct year(mytime)) as maxyear from mytable.
I want to select max value by using expression, not select max by using dataset.
I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray) strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method? How? If I can't, what are my options?Diane
