2 DataSet From Xml DataSource, And Parameter ...
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
ADVERTISEMENT
Dec 6, 2007
Hi guys
Firstly, i have a solution that is made up of multiple projects. One of these projects contains the majority of the business based datasets that are used throughout the applications.
One of the front ends that I have in the solution is a web site. I am wanting to use the report viewer control to display a report in local mode. I created a new report and i am trying to set its data source to one of the pre-existing datasets that I have and can not figure out how to do this.
Also these datasets do not have adaptors I populate the datasets manually. So the second half of what i need to know is once I have told a report to use one of my existing reports when do I do the population of the dataset.
Lastly if the answer for how to do this is different in local vs. remote modes i would be interested in what the difference is in this scenario.
Thanks
Anthony
View 3 Replies
View Related
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
View Related
Apr 17, 2007
I have managed to cobble up a VS2005 PPC app that has 10 or so tables in it. Of course when I started binding to the tables in my app, pretty soon I ran into an error I had made defining a datatype for a column in one table. Single instead of string. I have spent most of the day trying to modify something to change this column datatype. No luck.
1. Seems you cannot edit the schema in VS2005 to modify the datatype. After messing around, now the project will not open at all.
2. Cannot get the ADS wizard to go anymore, so cannot make .mdb schema change and pull down updated column/table.
3. Thought SQL Server 2005 had a tool you could just open up a .sdf file and change the schema. No luck. At least no intuitive way showed itself after I installed it.
4. Thought SQL Server Express Editition could open such a .sdf file and modify it so installed it. Again, could not figure out how this works either.
I suppose I could start over from scratch and create a new app datasource/dataset in VS2005, but I have done that so many times before, I am getting crabby thinking about doing it again. There must be a better way??? Isn't there?
Thank you.
View 1 Replies
View Related
Feb 19, 2008
"Short" Story Version:
I have a VS 2005 Web Appplication solution that uses SQL Server Reporting Services 2005. One of the projects is a Report Server Project. In this project I have an RDL file that has:
1.)a non-shared XML DataSource (no credentials),
2.)a DataSet with a Command Type of Text and a Query String that is as follows:
<Query>
<XmlData>
<Root>
<LOAD LOAD_ID_PREFIX="" LOAD_ID="" TRUCK="" DIAGRAM=""/>
</Root>
</XmlData>
</Query>
3.)a field that comes from the Database that is of VarBinary(Max) that is a JPEG Image
What happens is, is that by doing #2 above, at design time I can create tables and hook into my datasets and get the report all designed out the way I want. Then at runtime, I have a Stored Procedure that uses the "FOR XML AUTO" option so that I can represent my results from the database in an XML fashion which will allow me to replace the XML Schema in between the Root tags above (in #2) with my actual data (which still has the same schema). I then save the modified RDL to a database table field and move on to happily ever after......in this case, not. In this case, since an VarBinary is involved, unless I want to get a pointer back for the Diagram Column, I have to use the "FOR XML AUTO, BINARY BASE64" option. This returns an Encoded string to represent the Binary value that was in the Database....and this is the partial source of my angst....read on.
My approach that I described above worked flawlessly in all cases where an image wasn't involved. This report is the only report in the whole system I am working on that will have an image and it is the last one being done, so that's why I didn't run into this earlier.
I have tried all kinds of things at different levels, but the thing that I have tried that seemed to me to have the most potential was to use Embedded Code to take the Encoded ASCII Value and convert into something binary that the SSRS Reporting Engine likes (so my image will show), but I have been unsuccessful thus far. The error message I keep getting now from my Embedded Function is: "Invalid length for a Base-64 char array", which I have not been able to resolve thus far....and I do know that the Binary value in the database is good becuase I can see it in pages in my application otherwise.
This aside, is there anyone out there that knows how to take the value of a VarBinary(Max) field and insert it into the Text (XML) DataSet in the markup of an RDL file....and have it appear correctly when the report is rendered in something like the standard Report Viewer Control (or even when being previewed at design time inside of Visual Studio for that matter)?
Just to be clear, the root of my problem, as I see and understand it, is that I have a VarBinary value in a database that is being converted to a string (ASCII...I am presuming) due to how I am writing my stored procedure and then needs to go back to something else binary that the SSRS Engine will accept so that I can see my Image in the report when I render it.
I am wishful that someone out there understands my problem and has run into this and has a solution......but I am wishful of winning the lottery, too, and that hasn't happened yet...............
Thanks.
View 4 Replies
View Related
Apr 22, 2015
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?
View 3 Replies
View Related
Apr 10, 2007
Hi,
I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.
The idea is the create snapshot nightly and then display the report filtered based on parameters to users.
I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,
Right now I defined it this way :
Left =Fields!RegionCode.Value
Operator =
Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)
I do this for the 4 parameters that are passed, if they are null, make then equals to the field.
I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.
Thanks
View 5 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
Nov 14, 2007
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies
View Related
Jan 15, 2007
Hi
I am Creating Click Once Windows Application with Reporting Services 2005.I have created Report and Published on Report Server.In my windows application I am successfully able to view my published report through report viewer control.
Now in my application I am getting a dataset from my custom webservice. I want this dataset data to be added to my report as datasource at runtime on Client Side ,as my report is on Report Server.
waiting for help!!
Thanks in Advance
Pankaj
View 8 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 5, 2007
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 !
View 3 Replies
View Related
Feb 11, 2005
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 know there is data in the db.
Please help :confused:
View 6 Replies
View Related
Feb 11, 2008
Hi All
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.
How? Please?
Thanks
Pete
View 1 Replies
View Related
Jun 15, 2007
Hello
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.
thanks
View 1 Replies
View Related
May 31, 2007
Any way to have the default value of a parameter for a multi select parameter be the first value of the query of that parameter?
View 7 Replies
View Related
Aug 21, 2007
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
View 1 Replies
View Related
May 3, 2007
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.
View 1 Replies
View Related
May 23, 2007
Let me simplify. The value of my dataset field Dimension_1 is
[SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION]
I want to use parameters for SOB Year and Yr Id like I do on the query.
But if I use
="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]"
it returns null for the values. I don't know how to make these parameters
I am passing in parameters for the table and column to build an MDX query here is my query.
="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Anyone know what value I need to use on the dataset field to access the column?
Thanks,
Roger
View 10 Replies
View Related
May 14, 2007
Hello
I get the following error in a very quick test system i created:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot set the command text for data set 'test'.
Error during processing of the CommandText expression of dataset 'test'.
My sql is:
= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)
There is nothing obviously wrong in the code as far as i can see
The parameter 'test1' is of type 'string'
The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'
I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine
Thanks
View 5 Replies
View Related
Feb 27, 2008
Hi,
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.
How can I do?
Thank you very much.
View 4 Replies
View Related
Apr 20, 2007
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
View 6 Replies
View Related
Oct 22, 2007
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?
Any help is appreciated.
View 2 Replies
View Related
Jul 27, 2007
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.
Thanks
View 10 Replies
View Related
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?
thanks in advance
Steven J
View 1 Replies
View Related
Jul 10, 2007
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
In the dataset, I typed
=IIF(Parameters!ReportType.Value="Day", "EXEC spDetailsByDay " & Parameters!StartDateTime.Value & "," & Parameters!Day.Value & "," & Parameters!Hour.Value & "," & Parameters!Value1.Value & "," & Parameters!Value2.Value", "EXEC spDetailsByMonth")
I am getting syntax errors. Can anyone help me how to pass parameters to stored procedure in dataset.
Thanks.
View 4 Replies
View Related
Apr 11, 2008
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.
Has anyone else run into a similar situation?
View 1 Replies
View Related
Nov 20, 2015
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.....
View 6 Replies
View Related
Oct 25, 2015
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?
View 4 Replies
View Related
May 26, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies
View Related
