Return Dataset Field Collection With SSRS Web Service
Apr 15, 2008
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.
In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.
I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).
Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.
Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).
=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.
I have to create a CLR Proc for Reporting Services report that will use SharePoint (MOSS 2007) assemblies, so my datasource won't be a database, but the SharePoint object model that returns a collection.
How can I return these data (it's not necessary to be a collection exactly) without having to create a temp table just to return the SqlDataReader?
Thanks.
And sorry for my previous message, I just forgot that I was posting a message at the Global MSDN forums.
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.
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.
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
I have SCCM 2012 R2. I'm working on a custom report, using MS SQL Server Report Builder. I have other custom reports that work fine....they prompt for a collection and return results just on that one collection. The problem with the one I'm working on now is that it runs fine except for that....it prompts for a collection, lets you choose one collection,returns the desired info on each line, but it returns all systems, not just the one collection you selected. IOW, I'm having trouble getting the "where" clause to work right.
Here is the code for a report that works (it returns the selected collection and counts the patches needed by each machine):
select CS.Name0, CS.UserName0, CS.ResourceID, case when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 then 1 else 0 end)as int)))
[code]....
Here is the code for the report that I'm having trouble with. What you see runs but it has none of the collection selection code I've tried since I haven't gotten it to work:
SELECT DISTINCT(CPU.SystemName0) AS [System Name], CPU.Manufacturer0 AS Manufacturer, CPU.Name0 AS Name, COUNT(CPU.ResourceID) AS [Number of CPUs], CPU.NumberOfCores0 AS [Number of Cores per CPU],
[Code] ....
get the collection selection code to work in the second (processor count) sample?
I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.
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...?
I am looking at implementing some sort of throttling whereby "expensive" reports are setup as Subscriptions (and hence are run by the Windows Service), while all other requests go the Web Service.
My question is:
Do the two systems share any underlying systems? I understand (of course) that they use the same DB, etc. etc. However, I can't quite tell whether there is an underlying engine that is a common base or if they really just spin up different instances of the same objects and essentially each host the application separately?
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets) Here is the function to fill the RBL: 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 cmd.CommandText = "usp_responses_sel" 5 cmd.CommandType = Data.CommandType.StoredProcedure 6 7 ' Fill usp_ with Parameters 8 cmd.Parameters.AddWithValue("QuestionID", QuestionID) 9 cmd.Parameters.AddWithValue("LocaleID", LocaleID) 10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum) 11 12 Dim da As New SqlDataAdapter 13 da.SelectCommand = cmd 14 Dim ds As New DataSet 15 da.Fill(ds, "response") 16 Return ds 17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist. Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2) 2 rblResponses2.DataSource = ds 3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound? Thanks
Hi thereI have the following two tablesmainprofile (profile varchar(20), description)accprofile (profile varchar(20), acct_type int)Sample data could bemainprofile----------------prof1 | profile oneprof2 | profile twoprof3 | profile threeaccprofile--------------prof1 | 0prof1 | 1prof1 | 2prof2 | 0Now doing a join between these two tables would return multiple rows,but I would like to know whether it would be possible to returnacct_type horizontally in a column of the result set, e.g.prof1 | profile one | [0,1,2]prof2 | profile two | [0]I could probably manage this with cursors, but it would be veryresource intensive. Is there a better way?Regards,Louis
I have the following stored procedure for SQL Server 2000: SELECT a.firstName, a.lastName, a.emailfrom tbluseraccount ainner join tblUserRoles U on u.userid = a.useridand u.roleid = 'projLead' Now, this is not returning anything for my dataset. What needs to be added?Here is the code behind:Dim DS As New DataSetDim sqlAdpt As New SqlDataAdapterDim conn As SqlConnection = New SqlConnection(DBconn.CONN_STRING)Dim Command As SqlCommand = New SqlCommand("myStoredProcdureName", conn)Command.CommandType = CommandType.StoredProcedureCommand.Connection = connsqlAdpt.SelectCommand = CommandsqlAdpt.Fill(DS) Then I should have the dataset, but it's empty.Thanks all,Zath
I'm not sure if anybody else is having a problem with the Return Value of Stored Procedures where you get the "Specified cast not valid" error, but I think I found a "bug" in VS2005 that prevents you from having a return value other than Int64 datatype. I tried to look for the solution for myself on the forums but unfortunately I just couldn't find it. Hopefully, this will help out anyone who had come across the same problem. Basically, I have a stored procedure that I wanted to call as an Update for my ObjectDataSource that returns a Money value. Everytime I do this, I keep getting that error saying "Specified cast not valid" even when I try to change the @RETURN_VALUE data type to Currency or Money. After a long session of eye gouging moments, I decided to look at the code for my dataset. There, I noticed that the ScalarCallRetval for my StoredProcedure query was still set to System.Int64. I changed it to System.Object and, like a miracle, everything works like its suppose to. Ex. protected void SomeObjectDataSource_Updated(object sender, ObjectDataSourceStatusEventArgs e) {GrandTotalLabel.Text = ((decimal)e.ReturnValue).ToString("C"); }
hi,I have a page Price List with GridView to display only two columns of a table (Product name & price). Page size=20. I use SqlDataSource for it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"In my case the SqlDataSource control return data as a DataSet object. So the DataSet object contains all the data in server memory. I need to create Print Preview page with other columns (Product name & price & Vendor and Date Issue) without paging.I'm going to save dataSet in Session and in Print Preview page use it as datasource (without having to run another query).But I have no idea how to save this DataSet in Session. I don't know the DataSet Name. Any ideas? Thanks.
I have a great deal of experience in Intrebase Stored Procedures, and there I had the FOR SELECT statement to loop through a recordset, and return the records I wish (or to make any other calculations in the loop). I'm new in MS SQL Stored Procedures, and I try to achieve the same if possible. Below is a Stored Procedure written for MS SQL, which returns me a calculated field for every record from a table, but it places different values in the calculated field. Everything is working fine, except that I receive back as many datasets as many records I have in the Guests table. I would like to get back the same info, but in one dataset:
ALTER PROCEDURE dbo.GetVal AS
Declare @fname varchar(50) Declare @lname varchar(50) Declare @grname varchar(100) Declare @isgroup int Declare @id int Declare @ListName varchar(200)
DECLARE guests_cursor CURSOR FOR SELECT id, fname, lname, grname, b_isgroup FROM guests
OPEN guests_cursor
-- Perform the first fetch. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS =0 BEGIN if (@isgroup=1) Select @grname+'('+@lname+', '+@fname+')' as ListName else Select @lname+', '+@fname as ListName -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM guests_cursor into @id, @fname, @lname, @grname, @isgroup
I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.
I try to get a value from ArtikkelId from a SQL database into a textbox. Can anyone help me? My Code----------------------------------------- Imports System.DataImports System.Data.SqlClient Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim Conn_News_db As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("NEWS_CS1").ToString()Dim SQL_conn As New SqlConnection(Conn_News_db)Dim SQL_cmd1 As New SqlCommand()Dim myDataSet As New DataSetDim Select_ArtId_Max As String = "Select ArtikkelId from Hoved where ArtikkelId=(Select MAX(ArtikkelId) from hoved)"Dim SQL_DA As New SqlDataAdapterSQL_DA.Fill(myDataSet)TextBox1.Text = myDataSet.Tables.Item["ArtikkelId"] End Sub----------------------------------
Hi All, I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.
I used a function to create dataset as below: Public Function GetSQLDataSet(ByVal SQL As String) As DataSet ...... MyConnection = New SqlConnection(MyConnectionString) MyCommand = New SqlCommand(SQL, MyConnection) MyDataSet = New DataSet MySQLDataAdapter = New SqlDataAdapter(MyCommand) MySQLDataAdapter.Fill(MyDataSet) ...... End function It works fine. How to code a function to return a dataset in which there are two tables and relationship?
I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario.
Am having an issue using a dataset returned from a web service method. The webservice grabs data from a desktop SQL server database and returns it as a dataset.
I can call the web service fine and load the dataset into a datagrid (testing purposes). etc
However am having an issue with inserting the dataset data into the Windows CE Database.
Web Service Method
<WebMethod(Description:="Query a database. Returns a dataset.")> _ Public Function GetData(ByVal connectionString As String, ByVal selectcommand As String) As DataSet Dim ds As DataSet = New DataSet() Dim connection As SqlClient.SqlConnection = New SqlClient.SqlConnection() Dim Command As SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand() Dim DataAdapter As SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter()
ds = x.GetData("Data Source=Stephen01;Initial Catalog=BoxDB; uid=ste;pwd=1234;", "Select * from Swipes")
Try For Each dr As DataRow In ds.Tables(0).Rows db.Update("INSERT INTO Swipes (CardNo,Jobcode,ClockedAt,ClockType) VALUES(" & dr.Item(0) & "," & dr.Item(1) & "," & dr.Item(2) & ",'" & dr.Item(3) & "')") Next
db.Close() Catch ex As Exception MsgBox(ex.ToString) End Try(All Values are int's except Dr.item(3) which is a datetime field)
The when i click this button i get the error.
"Data Conversion Failed. [OLE DB status value (if known) = 2]"
I am guessing this is an issue to do with the date but to be perfectly honest i have no idea. Wish CE errors were more indepth!
this is sanjeev, i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
Is there any way to find the dependency between two datasets in ssrs report. I want to delete one of dataset but need to find if I delete that it will work or any relation with the other dataset.
I am currently working With SSRS . I have created a Store Procedure To show Data from different table in my Database,I have Used a temp table with #xxx Name,when i am creating DataSet for this Store procedure . but its not Showing the Fields of Store Procedure, but when i create other Store procedure without temp table with #xxx then its showing the fields in Data Set.
So I have this great MDX query that runs fine in Management Studio and I need to use it in a report. The statement contains a LookupCube call. I've tried everything but I am unable to get access to the fields in the dataset when I go to design the report.
I've tried adding the =" in the beginning and the " at the end. I've also tried escaping the " with "". Everything I've tried however never gives me the ability to access the 'PREV BATCH ELIGIBLE BALANCE' field.
Before you ask, I'm using LookupCube because the below code is just a piece of a much large MDX query. I just figured I'd start small and see how it goes.
WITH MEMBER [Measures].[PREV BATCH ELIGIBLE BALANCE] AS LookupCube("CLAIMS"," ([Measures].[TOTAL BALANCE] ,[CLIENT].[ATC Healthcare, Inc.] , [ELIGIBILITY].[TRUE], [ROLL_FWD].[TRUE], " + [Batch].PrevMember.UniqueName + ")") SELECT { [Measures].[PREV BATCH ELIGIBLE BALANCE] } ON COLUMNS, { [BATCH].[MyComp Healthcare, Inc.].Children } ON ROWS FROM [CLAIMS]
I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005. The BO reports used data regions based on multiple datasets, which is not feasible in SSRS. So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database ! is there a better way to do this ?
here is the query
Code Snippet
ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values] -- Add the parameters for the stored procedure here @TimePeriodDesc varchar(255), @TotalMarket varchar(255), @datasetname varchar(255), @VisibleProducts varchar(500), @DoctorSubTerritory varchar(255) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT xpospecialty.specialtydesc, a.doctorsubterritory , a.doctorbrick , a.doctorgroup , xpomarket.productdesc , s.totalmarketrank , s.prod1ranktotal , s.prod2ranktotal , SUM ( CASE WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN xpomeasures.rxvalues ELSE 0 END ) AS totalrxvalues, COUNT ( CASE WHEN (xpoproductsize.prodsizedesc <> 'Non -') AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT') AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN 1 ELSE NULL END ) AS rxrcount, COUNT ( CASE WHEN (xpoproductsize.prodsizedesc = 'High') AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN 1 ELSE NULL END ) AS [high], COUNT ( CASE WHEN (xpoproductsize.prodsizedesc = 'Medium') AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN 1 ELSE NULL END ) AS [medium], COUNT ( CASE WHEN (xpoproductsize.prodsizedesc = 'Low') AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN 1 ELSE NULL END ) AS [low], COUNT ( CASE WHEN (xpoproductsize.prodsizedesc = 'Very Low') AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN 1 ELSE NULL END ) AS [very low], COUNT ( CASE WHEN (xpoproductsize.prodsizedesc = 'Very High') AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) THEN 1 ELSE NULL END ) AS [very high], (SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues FROM dbo.xpotimeperiod INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid WHERE (xpomarket.productdesc = @TotalMarket) AND (xpomeasures.datasetname = @DatasetName) AND (xpotimeperiod.datasetname = @DatasetName) AND (xpomarket.datasetname = @DatasetName) AND (xpogeography.datasetname = @DatasetName) AND (xpogeography.doctorgroup = a.doctorgroup) AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) ) AS rxvaluesdoctorgroup FROM dbo.xpotimeperiod INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid INNER JOIN dbo.XpoMarketSize ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId INNER JOIN dbo.xpoproductsize ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid INNER JOIN dbo.xpogeography a ON xpomeasures.geographyid = a.geographyid INNER JOIN dbo.xpospecialty ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' ) ON xpomarket.productdesc = fn_mvparamsorted.parame LEFT JOIN ( SELECT xpogeography.doctorgroup, SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues, ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank, ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal', ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal' FROM dbo.xpotimeperiod INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid WHERE xpomeasures.datasetname = @DatasetName and (xpomarket.datasetname = @DatasetName) and (xpogeography.datasetname = @DatasetName)
AND xpotimeperiod.timeperioddesc = @TimePeriodDesc GROUP BY xpogeography.doctorgroup ) AS s ON s.doctorgroup = a.doctorgroup WHERE (xpomeasures.datasetname = @DatasetName) AND (xpomarketsize.datasetname = @DatasetName) AND (xpoproductsize.datasetname = @DatasetName) AND (xpotimeperiod.datasetname = @DatasetName) AND (a.datasetname = @DatasetName) AND (XpoMarket.datasetname = @DatasetName) AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc) AND (a.doctorsubterritory = @DoctorSubTerritory) GROUP BY xpospecialty.specialtydesc, a.doctorsubterritory , a.doctorbrick , a.doctorgroup , fn_mvparamsorted.parame , fn_mvparamsorted.sortid , xpomarket.productdesc , s.totalmarketrank , s.prod1ranktotal , s.prod2ranktotal ORDER BY s.totalmarketrank, a.doctorgroup , fn_mvparamsorted.sortid END