=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value, Nothing)) Released_DT = a date - 09/03/2015 or 09/02/2015 Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
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 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 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.....
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?
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)" The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
does anyone know if there is a way, or perhaps a custom toolbox control that is already developed that allows you to drag an entire dataset into a table, instead of pulling everything in field by field...?
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 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.
does anyone know a way, using native SQL, to store a result of a query in an image field of a certain table.
The case is we have a selfmade replication to communicate with several SQL servers in stores, this replication is over a telephone line. So we collect all the data using SQL statements and store them in a separate table as an image field. This is done know through a Delphi application that streams the resultset to a image field.
How to get the max length of numeric field in a DataSet? I have a DataSet bound to an Access database. Is it possible to get the maximum length of numeric field of a table in the DataSet? Many fields in the database tables have maximum length values set in ...
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
Hello, I am attempting to create a cascading parameter. Parameter's 1 & 2 are multi-valued. Datasets 1 & 2 supply Parameter's 1 & 2. The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'. The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1. Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing. What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?
Hello,I am trying to update the Image type field named as BlobData in sqlServer 2000. I capture the record in a data set to have the schema and try to update the BlobData field of type Image by assigning it a value of buffer as below. but my assignment seems to be wrong and generates an error saying Object reference not set. Code========================= Dim fileBuffer(contentLength) As Byte
Dim attachmentFile As HttpPostedFile = Me.fileUpload_fu.PostedFile
Catch ex As Exception Me.error_lbl.Text = ex.Message Return False Finally cn.Close() End Try ========================== Can anyone please help this one out. Cheers.Imran.
We are trying to update a changed DataSet to at table in MSSQL server
The table have an XML field and that gets converted to System.String when we get from the DB when we try to save the dataset the XML field ind the table is set to NULL
How can this be, the DataSet can convert it to System.String when we SELECT it from the DB, but when we save, nothing happens and we get an empty field. Not even an SQL error...
I have a stored procedure "spDetailsByDay" which takes parameters @StartDateTime as datetime, @Day as int, @Hour as int, @Value1 as varchar(20), @value2 as varchar(20)
My report Parameters are StartDateTime as DateTime, Day as integer, Hour as integer, Value1 as string, Value2 as string, ReportType as string
Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.
I created a dataset based on a stored procedure. There are nine input parameters. When I click on the preview tab, it is asking me for ten input parameters.
I double checked my dataset on the data tab, and it only shows the expected nine parameters when I look at the parameters tab. I can also run the query without issue from the data tab.
I have no idea why it is asking for an extra parameter on the preview tab. It is a column name that does exist in my database, but is in no way used in the stored procedure in question.
I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.
Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()), PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0); The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting. Any suggestions would be most appreciated
I have used my parameter value in where clause of my dataset control SQL query. When I choose a single value in my multiple-value parameter then I get output as expected. Hovewer when I choose multuple values I get an arror:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ---------------------------- Query execution failed for dataset 'DATABASE'. (rsErrorExecutingCommand) ---------------------------- An error has occurred during report processing. (rsProcessingAborted)
I am convinced that it's a syntax problem. Is there a way I can change syntax of data output from query:
select @parameter
I need output to be like: value, value, value ... etc.
The dataset control query looks something like the following:
select ID, a, b, c, d from DATASET.dbo.Table where ID in (select @parameter)
I have been searching through google for weeks to solve this and got this far and got stuck. Is it possible at all to achieve what I am looking for?
I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.
In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.
Any help on what I'm doing wrong greatly appriciated.
- Bruce
ALTER PROCEDURE [dbo].[caller]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @include int,
@Committee bit
execute @include = dbo.callee @Committee
IF @include = 0
BEGIN
select * from ProvCompensation
END
ELSE
BEGIN
select * from ProvCommittee
END
END
ALTER PROCEDURE [dbo].[callee]
(@fName bit)
AS
BEGIN
DECLARE @aBool bit
SET NOCOUNT ON;
select @aBool = @fName from ProvisionsPlanSum where PlanId = -99
