I popped into the Transact-SQL forum to get some help and created the dbo.selectaudit1 table, which can be queried succesfully.
Code Blockcreate table dbo.selectaudit1 (startkey varchar(10) not null,
endvalue varchar(10) null,
endkey as coalesce(endvalue, startkey))
insert into dbo.selectaudit1 (startkey)
select '042'
union all select '140'
union all select '2089'
union all select '2031'
union all select '2051'
union all select '2100'
union all select '2299'
union all select '2300'
union all select '2349'
union all select '2350'
union all select '2389'
union all select '2390'
union all select '2399'
union all select '2732'
union all select '2733'
union all select '2849'
union all select '2850'
union all select '2883'
union all select '2898'
union all select 'V073'
union all select 'V078'
union all select 'V100'
union all select 'V109'
union all select 'V580'
union all select 'V581'
union all select 'V661'
union all select 'V662'
union all select 'V671'
union all select 'V672'
union all select 'V711'
union all select 'V760'
union all select 'V769'
insert into dbo.selectaudit1 (startkey, endvalue)
select '1400' ,'2089'
union all
select '2100', '2299'
union all
select '2300' ,'2349'
union all
select '2350' ,'2389'
union all
select '2390' ,'2399'
union all
select 'V100' ,'V109'
union all
select 'V760' ,'V769'
Then in reporting services, I created a report with the following query in my dataset. This query works fine in a BIDS query windows, however, when I refresh my dataset and attempt to execute the query I am getting the following error:
Cannot find the object "selectaudit1" because it does not exist or you do not have permissions.
(Microsoft SQL Server, Error: 1088)
Code Block
Select
p.MRN
, p.PatientName
, CONVERT(CHAR(20),p.AdmitDate,101) AS AdmitDate
, CONVERT(CHAR(20),p.DischDate,101) AS DischDate
, p.VisitTypeCode
, d.VisitTypeName
, p.AnyDx
, p.PrinDxCode
, p.PrinDxDesc
, p.SecDx1Code
, p.SecDx2Code
, p.SecDx3Code
, p.SecDx4Code
, p.SecDx5Code
, p.SecDx6Code
, p.SecDx7Code
, p.SecDx8Code
, p.SecDx9Code
, p.SecDx10Code
, p.SecDx11Code
, p.SecDx12Code
, p.SecDx13Code
, p.SecDx14Code
, p.SecDx15Code
From dbo.PtMstr p inner join
ampfm.dct_VisitType d on d.VisitTypeCode=p.VisitTypeCode
INNER JOIN dbo.selectaudit1 sel
ON (PrinDxCode between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
OR (SecDx1Code between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
hi all..... i select some records from my database using mysqldatareader.......but i want to load it's result sets into my datatable or dataset.... is it possible and how can i do it this way ? thanks
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi, I need a query which is basically
(query 1) Except (query 2)
SQL server supports (query 1)Union (query 2) but I can't get the Except to work.
Alternatively, I also tried to implement it by using the "NOT IN" but didnt work. Background: 3 tables, accnts, Opty, Opty_postn I want the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001. accnts has fields ID & name, ROW_ID opty has fields acct_id, prob, ROW_ID opty_postn has fields opty_id, close_dt
My query: SELECT accnt.name FROM accnt WHERE accnt.ROW_ID IN ( SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN (SELECT opty_id FROM opty_postn WHERE close_dt<1/1/2002) ) AND accnt.ROW_ID NOT IN ( SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN (SELECT opty_id FROM opty_postn WHERE close_dt >12/31/2001) ) AND accnt.ROW_ID = opty.accnt_ID
my intent was select account.names where id is in set(prob = 100 & sale before 1/1/02) but not in ( prob = 100 & sale after 21/31/01) SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02. I hope i explained this right. Thanks in advance for your help.
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE) SELECT NAME, POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$, LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH FROM TABLE INNER JOIN TABLE2 ON TABLE2.NAME = TABLE.NAME WHERE POST_PD = @LASTPD AND TABLE2.NUM= 539 GROUP BY NAME) AS LASTMONTH INTO #TEMP_SAFROM TABLE INNER JOIN TABLE2 ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH FROM TABLEWHERE TABLE2.NUM = 539 GROUP BY NAME, POST_PDORDER BY NAME, POST_PD SELECT NAME, LAST_MONTH, CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$ FROM #TEMP_SA GROUP BY NAME ORDER BY NAME
We are trying to limit are query that returns items from our database. The query currently returns 32,000 records. We are trying to figure out an effecient way so we can request the 1st 50, or the 3rd 50, or the 5th 50 to display to the screen. We dont want to return the entire 32,000 then limit whats displayed to the screen in ADO. We want the select statment to only return 50 at a time. Any suggestions?
I have a general question I need a few pointers or explanations. I have a top level Data Flow query in a SSIS package that has been running for years for which I have made some minor changes to pull additional fields and rectify a filter condition.
I have noticed that when I run this same query, exactly as entered in the Data Flow task, in the SQL Server 2012 Management Studio, the query returns some 105,000+ records but when run in development by executing the package the top level task only indicates some 21,000 records returned. Since there was not other transformations or filtering performed, I had expected the same record count and am not getting it.
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
I create a Query in Dataset that get values from multiple tables when i execute query in query builder its execute successfuly but whan i am save this query found following message. "The new command text returns data with schema different from the schema of the main query. Check your query's command text if this is not desired" after OK i am check my query result in Preveiwdata its show deffrent result.
Im looking for a query that returns record 3. Ruturning just the records that have a record in which column 2 is a 10 but not a 20. If I enter WHERE = 10 I get 1,3,4. Please help. Thanks!!
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into. Thanks
I've faced the following problem while forming DataSet. I'd like to ask the following SQL query: ___ select Users.UserID,Users.FirstName,Users.LastName, Users.Email, data.Suffix, data.Unit,data.Telephone, data.IM, data.Websitefrom(select UserID, max(case PropertyName when 'Suffix' then PropertyValue end)Suffix, max(case PropertyName when 'Unit' then PropertyValue end)Unit, max(case PropertyName when 'Telephone' then PropertyValue end)Telephone, max(case PropertyName when 'IM' then PropertyValue end)IM, max(case PropertyName when 'Website' then PropertyValue end)Website from (SELECT UserProfile.UserID, UserProfile.PropertyValue, UserProfile.PropertyDefinitionID, ProfilePropertyDefinition.PropertyName FROM UserProfile,ProfilePropertyDefinition WHERE UserProfile.PropertyDefinitionID=ProfilePropertyDefinition.PropertyDefinitionID )table2 group by UserID)datainner join Users on Users.UserID = data.UserIDWHERE data.Suffix = 'IT'; __ Nothing happens while fulfilling as well (i.e. if to set up Breakpoint and watch the DataSet condition - it will be empty there). If to change the request to let's say some standart - then DataSet downloading begins - it seems there's something wrong with the request I'm checking....If to look with the holp of request master and press "perform", data is reflected in a normal way...But as soon as i press "Bild", emptiness is shown as usual Please, prompt me what to do Initial code in the file .aspx.cs: ------------------------------ UserInfo ds = new UserInfo();UserInfoTableAdapters.UserInformationTableAdapter da = new UserInfoTableAdapters.UserInformationTableAdapter(); da.Fill(ds.UserInformation); Info.DataSource = ds.UserInformation; Info.DataBind();
Hello, I have two datasets. One dataset has some delivery informations the other dataset has billing informations. The first dataset builds the resultset out of the selected parameters (those parameters do not correspond to the values in table two). What I wanna do is pass some informations (like custno, artno) to the other dataset so that I can only display the corresponding information. Any ideas?
I am trying to build a report with a prompt to select building. It all works fine except I in the prompt building is list as may times as there is data for it. I want it to only display once, but not effect the other data is there a way I can query this?
I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.
I need to write a query in which some of the columns will be derived from a dataset (join) and the other columns will be derived from another dataset. Is there a way to do this?
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value. This is the code for the Method to return a datareader
public SqlDataReader GetOrgID() { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); SqlConnection con = new SqlConnection(conString); string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlCommand cmd = new SqlCommand(selectString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd;
adapter.Fill(dataset); return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well. So assume that the Guid is a valid entry..I should return a valid dataset but its null.
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.
Is there a way to create an expression that references the DataSets / Queries? I'd like to be able to create an expandable text box that shows the raw sql query used to generate the report.
I have 2 MultiValued Parameters in my reports, our of which first is dependent on the other.
Eg. I have a SubProductGroup Parameter and depending upon the value selected in SubProductGroup, the other parameter which is Product gets filled in combo box.
Both these parameters are MultiValued and I am having "Select ALL" option in both the combo box.
Now, whenever you select any SubProductGroup values, the Dataset for Product gets executed to fill the Product combo box.
The MultiValued Parameters works using the "IN" Clause. All those products are retrieved which are "IN" the selected SubProductGroup.
Whenever user selects "Select All" option for SubProductGroup, there is no need for this "IN" clause, because we actually need all the records for Product.
Similarly, I have the Main Dataset which retrieves the Sales data for the Products selected from the Combo Box. So, the same question arises there also.
Right now I am using STRTOSET( ) and Join( ) function and this is working fine for me.
But when User Selects "Select All" in SubProductGroup, it takes a long time to fill the combo box for Product as well as when user selects "Select All" for Product, it takes a long time for the actual Dataset to retrieve records. But its working fine.
In order to improve the Performance, I am trying to eliminate this IN clause when "Select All" is selected.
I have a web form which is having Drop Down box in which I have 2 selection 'P' and 'C'. Depending upon the selection I have to call the Dataset( If I can make it in code behind) or I can do it in one Complex query if you guys will help me.
Query should be like this.
select a.* , case when selection='P' then select p.column1 from ptable p
case when selection='C' then select c.column1 from ctable c
else a.column1
from atable a
where
acolumn2 = "USERINPUT"
but now the problem is in FROM and Where Clause.
when selection is 'P' then I have to include ptable in FROM Clause and in where clause I have to include two condition like (p.column1 = a.column1 and p.column2="USERINPUT1" or
if selection is 'C' then I have to include ctable in FROM clause and in where clause I have to include two different condition like (c.column1 = a.column1 and c.column2="USERINPUT")
So at once the Query will be any of one like below:
(1) some times this:
select a.* , case when selection='P' then select p.column1 from ptable p
case when selection='C' then select c.column1 from ctable c
else a.column1
from atable a, ptable p
where
a.column2 = "USERINPUT" and
p.column1 = a.column1 and p.column2="USERINPUT1"
----------------------------
(2) some times this:
select a.* , case when selection='P' then select p.column1 from ptable p
case when selection='C' then select c.column1 from ctable c
else a.column1
from atable a, ctable c
where
a.column2 = "USERINPUT" and
c.column1 = a.column1 and c.column2="USERINPUT"
Please let me know if you have any kind of confusion so I can clear this but if you got everything from my description please do help me to solve this. I am really struggling for this.
One way I was thinking to generate 3 dataset one(query or dataset) for 'P' , one for 'C' and one for else so I can get the input (selection) from the form and then decide in codebehind which dataset I have to make call so no need to write complex query. But this is my thought and I dont know how to make a call and everything either so please help me in this as well, if you like this solution .
I have a question that seems easy but I can not figure out...
Premise:
Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.
I have a Dataset that has a calculation for a column within a select statement
Query Pseudocode:
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from
(subquery"blah" )
Union
(Subquery"blah")
Custom Code:
Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double if denominator = 0 then return 0 else return numerator/denominator end if End Function
How To use:
If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.
Problem:
How do I add this code reference in the following dataset select statement
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))... ,year from
TITLE: Microsoft Report Designer ------------------------------
An error occurred while executing the query. Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
Help!
P.S.
this is a Matrix report and this select statement is within one of the datasets that fill a matrix.
Hi, I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!! What's going on? Any thoughts highly appreciated. t.i.a.,ratjetoes.
I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement ..........End Function
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
I've a dataset which is filled using a stored procedure in either MSSQL2005 or 2000.
The procedure may return sql-errors (f.ex 208 if a table is not found), but the procedure will anyway return data which shall populate the dataset, independent on 208 errors.
The procedure works fine if running it from f.ex. SqlMgtStudio, and the dataset is filled if the procedure triggers no errors.
However, if the procedure triggers an error like 208, then I get an exception in the application and the dataset is not populated with the data returned by the procedure.
Is there a way of telling the DataSet/SqlDataAdapter to fill the dataset even if the sql-code genererates som errors?
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 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
I got this Error, Query Execution for Dataset 'Source'.
When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?
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?
