.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Pulling report, query almost complete......

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server

I have a table structure and query below......

in the activity table there is a field named isCompleted and if this =1 then the activity is completed...

i want to pull this same report but only for the ppl in the XREFEmployeesInvitations that have completed activityId 10

USE [Wellness2010_Current]

/****** Object: Table [dbo].[XREFEmployeesInvitations] Script Date: 09/28/2010 15:03:05 ******/


CREATE TABLE [dbo].[XREFEmployeesInvitations](
	[EmployeeId] [int] NOT NULL,
	[ActivityId] [int] NOT NULL,
	[IsSpouse] [bit] NOT NULL,
	[HasBeenViewed] [bit] NOT NULL,
	[IsCompleted] [bit] NOT NULL,

View Complete Post

More Related Resource Links

Query Command to Report


I would like to generate a crystal report showing the report data based on my custom SQL query.

Where I can provide my SQL query or stored procedure.

Please give me good tutorial links on this too.

Thanks in advance.

Find SQL Query when report is executed

Hi All, I am using SSRS 2008 R2 verison of reporting services. I have a report and the data source for this report is Oracle 10i, it takes around 2 mins to execute the report. I want to find out the query, which is sent by the reporting services to the Oracle. Is there a way to find it out. Please help me with this. Thanks Vineeshvineesh1701

SQL Reporting Service 2005 - share schedule report performace T-SQL query?

Hi, I have SQL 2005 reporting services Shared Schedules and each schedule has its own subscribed report. I would like to have T-SQL 2005 to find out performance loading on each schedule. i.e. MySchedule_1 has 10 reports in it and AVEGARE report eaxecutiontime is like 3mins 5sec      MySchedule_2 has 7 reports in it and AVEGARE report eaxecutiontime is like 4mins 9sec Pls can I have T-SQL 2005 on ReportServer database to find out load on each schedules (and more drill-down to each report level for execution time)?

SSRS 2008 R2 - Query builder freezes and needs to be shut down Report Builder 3 when attempting quie

Hi, In SSRS, using Report Builder 3.0 (SSRS R2), after choosing a Report Model as the report's embedded data source, launched "Query Deisinger…" from GUI to build data source in report.  First 1-2 attempts at querying underlying data source (Oracle back-end), are successful, but later attempts freeze application to the point where application freezes and overall Report Builder application needs to be forcibly shut-down. This system freeze has occurred when referencing tables containing a very small number of records as well as larger data sets.  Suspect that application freezing may be caused by timing-out of connection to back-end database. but I am not sure. Any suggestion to solve this issue? Thanks   Environment - Distributed deployment Server1(report_srv) --Windows server 2003R2 64bit, Installed sql server 2008R2 reporting service, configuration tools Server2 (report_db_srv) --windows server 2003r2 64bit, sql server 2005 for report server database

Having problem accessing multi-choice parameter in SQL Query in Report.

Hi, I have a report with a multi-choice input parameter. My report contains a dataset that uses CHARINDEX on this multichoice parameter. The dataset query is in text, not in stored procedure. When I run the report I get "the charindex requires 2-3 arguments the reason being that the SQL is run as follows (You can see the multi-choice list screws up the string: exec sp_executesql N'Select test.Region [Region], test.Location [Location], nvarchar3 [Year], nvarchar4 [StatisticType], nvarchar5 [StatisticType2], ntext2 [Detail], float1 [Amount]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListId and AL.tp_Title=''Statistics''   left outer join   (       Select UD.tp_id [ID],nvarchar1 [Region],     nvarchar3 [Location]   from [WSS_Content].[dbo].[AllUserData] UD   inner join [WSS_Content].[dbo].[AllLists] AL on AL.tp_ID = UD.tp_ListID and AL.tp_Title=''Regions''   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1   ) test on test.id = UD.int1   where UD.tp_ListId = AL.tp_ID   and UD.tp_ListId = AL.tp_ID   and UD.tp_DeleteTransactionId = 0x0   and tp_IsCurrentVersion = 1  &n

Query doesn't complete - pls help

Hi, the following query which has been used many times before, just never seems to complete this time. Other than a table name, nothing has changed. The table being updated has 16 million rows and the 2 views used in the select have 216 and 360 million rows. The query has been running for 18hrs now. It has necessary indexes. my search online, found some results where it says that sometimes with SQL 2005, SP2, some people have had this problem, but did not find anything that would help me. I checked for indexes, ran the DBCC free proccache killed and restarted the query with a different session (out of desperation) nothing made difference. When I checked activity monitor, someitmes, it would display as sleep_task. The CPU and Disk I/O numbers keep changing, but the timestamp for the last batch is the same as when I started the query. Also tried the SQL profiler, but did not find anything useful, as my query did not even show up in that trace. There are actually multiple queries like this, and this is only one of them. The server is a pretty robust one and also there were times, when there was nothing else running on the SQL server and still the query won't complete. Query: with cte as (select distinct p.PID id , p.HouseHoldID, Q.[Living Unit ID] , q.[source code] sc, q.[sequence number] sn from vw_All_HH_Persons p join vw_All_Household_Details h on p.hhde

SQL Query for Ticket Report

I'm having difficulty with a query for a ticket report.  The report looks like a sheet of business cards with a dynamic ticket number in the corner.  I have the layout working just fine and the report displays as it should with some fake test data.  I need to dynamically run this report with parameters and can't figure out the query.  Table1 has 1 row in it with Field1 which holds the number of tickets which need to be printed.  It also the rest of the info needed for the ticket: date, time, ticket name, etc.  What I'd like the query to do is return the number of rows the field has in it.  In a For Loop it would be pretty simple but I need to do it in SQL.  How can I take 1 row and return it to the report as 5 or 8 or 10 rows? Data example:  SessionId TicketsToRun SessionName DateTime 1 5 Session 1 10/06/2010 12:00:00 2 15 Session 2 10/07/2010 14:00:00 3 10 Session 3 10/09/2010 10:00:00 4 18 Session 4 10/10/2010 11:00:00  SessionId 1 would print 5 tickets, SessionId2 would print 15 tickets, etc...

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Report Builder 3.0 for Oracle with Graphical Query Designer

Is there a possibility to have Graphical Query Designer in Report Builder 3.0 in case the connection is made to an Oracle database (through .NET Provider for Oracle for example)? I need to offer to the end users the means to build their own reports. Every user has associated a group of database objects and as soon as users are added new database objects are added. Therefore I cannot create a model because it needs to be changed frequently and the model in this case would contain objects to which the user does not have access. The only option is that the user should create his report query in a friendly manner. So the query text editor is of no good in this case.   I've found in documentation that for Oracle and other databases there is no Graphical Query Designer: In Report Builder 2.0, to specify a query for data source types Oracle, OLE DB, ODBC, and Teradata, you must use the text-based query designer (http://msdn.microsoft.com/en-us/library/dd220607(SQL.100).aspx). Is there a way to have Graphical Query Designer in Report Builder 3.0 or should I look for other products? In case Report Builder cannot solve this problem can you make some product recommendations - ad-hoc web reporting for .NET?

Sub report query based on main report (Microsoft Report Viewer)



I searched the forum but couldn't find anyone with the same problem with me.  They were close but didn't help me solve my issue.

I'm trying to learn how to make the sub-report get data from SQL Server based on a primary key passed from the main-report.

An example:
Main report displays all student names and the sub-report would display all their contact phone numbers.

A visual:
Bobby Smith

Home Phone: 555-1212
Cell Phone: 555-2323
Work Phone: 555-3434

Jane Doe

Home Phone: 555-2020
Cell Phone: 555-3030
Work Phone: Null

Ginger Doe

Home Phone: 555-5062
Cell Phone: 555-9586
Work Phone: 555-2356

Student ID   |   Name
1                | Bobby Smith
2                 | Jane Doe
3                 | Ginger Doe

Phone ID &n

Error in Adhoc report MDX query


Hi Experts,          

Now i have another issue the Following MDX query is working fine in MDX query analyzer.


call SQLQuery.ExecuteSql(
    "Provider=SQLNCLI10.1;Data Source=TMDEVDWHSRV01;Integrated Security=SSPI;Initial Catalog=BankingDWHStg"
    ,"Exec DecryptPANKey 1")


SQLQuery  means Assemblyname



but in Adhoc report i want to use with select statment for the above MDX query.


In Adhoc report MDX is not supporting the above query


call SQLQuery.ExecuteSql(
    "Provider=SQLNCLI10.1;Data Source=TMDEVDWHSRV01;Integrated Security=SSPI;Initial Catalog=BankingDWHStg"
    ,"Exec DecryptPANKey 1")


Please help to use the above query in ADHOC report MDX.



SQL Server 2008 R2 Report builder - Cannot update a list of fields for the Query



I have existing report connected to SSAS cube. When I try to change the query and click OK, i get this message "Could not update a list of fields for the query. Verify that you can connect to the data source and  your query syntax is correct"

My Query syntax and access to data source dont have any problem.  Because fo this issue, i cannot update the query used in the report. How do i resolved this issue?



SSRS 2005 Report Writer Limitation- 128 Max Length for Query Strings Constructed in Data Tab???!!!


I'm trying to construct a SELECT query within the Report Writer 2005 Data Tab as follows- ="SELECT blah..." & SomeFunctionToConstructWhereClause() " ORDER BY 1"

When the length exceeds 128 characters, I get the following error (works otherwise)- Microsoft Report Designer Incorrect syntax near '='. <QUERY TEXT> The identifier that starts with is too long. Maximum length is 128.

Say it isn't so or that there is a reasonable workaround for this. 128 characters isn't that much when you're talking about complex query strings. Thanks in advance.

Why does modifying a Query causes all fields in the dataset to disappear in the Report Data pane


We routinely import reports from Microsoft CRM and amend them in Visual Studio 2008 eg to add column totals, change layouts etc

If the report needs more fields from CRM, I amend the SQL query in the CRM Dataset, adding the new field references (rather than recreate the report in CRM with new fields, import into  VS2008 and redo all  the formatting etc).

This worked fine until this week.

Whenever I modify the SQL query in the CRM Dataset, all the fields in the Dataset disappear in the Report Data pane.

Even changing a character in a field name which isn't used in the report - which should have absolutely no impact on the report - causes the fields to disappear.

As far as I'm aware, nothing has changed on our systems.

We use SQL 2008 reporting services and Microsoft Visual Studio 2008.

Any suggestions greatly appreciated!

how to modify mdx query in SSRS report


I've created a ssrs report using cube and i drag and drop the columns in the ssrs report  and it works fine.

Issue arise when i would like to update the mdx query. If i modify the mdx and try to save then it pops up a window with a message -

"If you modify the MDX statement associated with this query and then return to design mode, your changes will be lost."

After this message my changes get lost.

Pls let me know how to make the changes in the MDX after designing a ssrs report. 

Passing report parameters to a query using a WCF data source


Hello everyone,


I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.

Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place:




          <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest">


          <Parameter Name="Criteria" Type="XML">









difference running from query designer to report preview


So I have a simple query and parameter in query designer and I run it and it does what I expect.  This is a simple test query/report and I'm just trying to learn how to get parameters working like I'd like.  The single parameter is there to filter by year-month ...and it does in designer.   When I go into report preview, I get the parameter list of values I expect but wne I go "view report" I get:

Query execution has failed for dataset "GetInfo".

Query (1,72) the restrictions imposed by the CONSTRAINED flag in the STRTOSET were violated.

Here is the dataset for GetInfo (which runs in designer):


And here is the dataset for the parameter:

 {} ON 0, NONEMPTY([Pay Date].[Year-Month].Members, [Measures].[Pay Amount])
ON 1 FROM [Pays Data] 

I did change the generated dataset on the parameter since by default it would list all the year-month combinations

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend