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

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

Organizing SQL query to help construct reporting grids

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server

I'm not sure how to phrase this question, or what I even need to be looking for, but here goes.


I have the following table:

Date (datetime)   | Data (varchar)  | State (varchar)

2010-05-08 12:35:29.998 | Interested in proposal | California
2010-06-08 12:35:29.998 | Interested in proposal | California
2010-06-09 12:35:29.998 | Interested in trial   | Texas
2010-08-08 12:35:29.998 | Interested in demo   | California

What I am trying to do is organize the data into a table to generate reports based on some small criteria (Is it a demo, trial, or proposal they are requesting?). What my goal is to end up with something like this:

State    |  January | February | March | April | ....
Alabama   |  3   |  0   |  2  |  10 | ...
California |  10   |  2   |  0  |  9  | ....

I have a query that does some of this. It can group the data in the following format:

state  |  Total  |  Month
Alabama |  10   |  1
Alabama |   2   |  2
Alabama |   3   |  3

The problem I have, is I can't easily loop through this row-by-row and insert it into a table. I've been looking around and thinking of ways t

View Complete Post

More Related Resource Links

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)?

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

Looking for Client Query Analyzer (SQL Server 2008) or Equivalent Reporting Tools


I've recently upgraded from SQL Server 2000 to SQL Server 2008.  My accounting and marketing team is skilled and has been using SQL Query Analyzer for years to run smaller queries and build small Crystal Reports.  Unfortunately Query Analyzer is now bundled into the management studio.  That's a bit too much software for this level of power users.

Is there a client-side query analyzer for SQL Server 2008?  I'd rather use a Microsoft product than a 3rd party tool, but I suppose I could do that too.  Looking for something simple that will:

1.  Show users the database tables and/or views

2.  Run queries

Perhaps query analyzer isn't the way to go and my team needs a different reporting system.  If this is the way MS is heading, could you direct me towards a product or service that allows power users to get the information they need?  They will still need to look at raw data to build Crystal Reports though.

Usage Reporting and Search Query reporting in WSS 3.0


Hi Every one,


I have a following requirement:

I would need to generate a report/fetch the details of what the users are searching for ---->search query logging 

I would need to fetch the details of the most used content from the site ---Site usage logging.


Could you please provide me the solutions how i can proceed in WSS 3.0 







Thanks keshav,Share point Developer

Reporting Services Setup Query


I have installed and configured Reporting Server (default instance and default setup). On Reporting services configuration wizard I see all "green";

Now I want to test the connectivity to reporting services, so i entered http://my-server_01/ReportServer. It gives me login popup. I tried multiple users but cant connect, What should be this user id?

Thanks for your help.

SOAP Error 500 when query in Sql Server 2008 using for reporting a Sharepoint list 2007


I'm doing the reporting services for a SharePoint list, the datasource is configured as a connection with http://<Servername>/_vti_bin/lists.asmx and I add the parameters listName , rowLimit and viewName with the

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Integrate Reporting Services with Silverlight and RIA Services

One of the most wanted future features in Silverlight is the possibility to create business reports using Reporting Services. In this article, I will show how to use the existing Reporting Services technology with Silverlight and RIA Services combination.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

Getting users list reporting to manager from LDAP


Dear All,

I need to get list of users reporting to a particular manager from the Active directory.

In the active directory there is manager property with contains the data in the following way.


Please help me in building the filter condition.

I am using the following

pUserName="Gegore Lid";

strSearch = "(&(objectClass=user)(objectCategory=user)((Manager cn=" + pUserName + "))";

DirectorySearcher dSearcher = new DirectorySearcher(dEntry, strSearch);

            //Account properites to load
            string[] arrPropertiesToLoad = { "displayName", "department", "manager", "sAMAccountName", "EmployeeNumber", "Title" };

          SearchResultCollection sResultColl = dSearcher.FindAll();

 the above code is not returning any result.

Please help me in this.



Getting list of users reporting from specific Manager from Active Directory


Dear All,

I am have requirement like displaying all the user information reporting to specific manager from Active Directory.

Please help me regarding this.



MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Reporting Services SQL 2005 .Net 4 compatibility?


I've been working on my first asp.net application and for the most part it's been pretty smooth.  Right now I'm having trouble getting reporting services working under SQL Server 2005.  I developed the site with Visual Studio 2010 and didn't think about any of the .Net 4.0 compatibility issues that have arisen.  I'm having no problems with the site communicating with SQL Server 2005 for the regular database stuff.  The only problem I'm having is with setting up Reporting Services.  I have the .Net 4.0 framework installed on the server, however Reporting Services is still using 2.x.  I'm not sure if this is a problem with SSRS or with IIS.  When i go to localhost/Reports it's reading my web.config for my application and throwing an error: "Parser Error Message: Unrecognized attribute 'targetFramework'."  This is referring to a line in my web.config where targetFramework is set to 4.0.

Software that I'm using:

  • Visual Studio 2010
  • SQL Server 2005 Standard
  • IIS 7.0
  • .Net 4.0

Any suggestions?  

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