.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

Two fields in common for one report

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

Hello I am trying to create a report based on three tables on is the people_tbl which has two child tables that are related.  both have two fields in common [stateservices] and [statecategory] I need to create using both of those for the funding agency.  I want to make sure I'm doign this correctly. 


SELECT   AdultStateActivity_tbl.[Contact Date], People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, AdultStateActivity_tbl.[Event Name], 
           AdultStateActivity_tbl.[Event Location], AdultStateActivity_tbl.[Earned hours], AdultStateActivity_tbl.[State Category], 
           AdultStateActivity_tbl.[State Services], AdultStateActivity_tbl.[Contact Time], TanfActivity_tbl.[State Catagory], TanfActivity_tbl.[Services Covered]
FROM     AdultStateActivity_tbl INNER JOIN
           People_tbl ON AdultStateActivity_tbl.[Parent ID] = People_tbl.[Parent ID] INNER JOIN
           TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID]
WHERE   (People_tbl.Adult_Child = N<

View Complete Post

More Related Resource Links

How do you add duplicate fields to a SSRS custom report and receive differtent output values.

I have built a custom report in SSRS 2008 using fields for supplier and customer.   I have added a Parent Row Group using the Supplier field for the group expression and used the Customer Details as my child details group. This returns a list of all customers for each supplier.   (Supplier | = Customer details ( Name, Dob etc.)   How would I add an additional Supplier field to the customer details returning only values for other Suppliers used by a customer, not the value of the Supplier listed in the Parent Group?

Why do we need Common view fields in content query web part?

I am a bit confused on the purpose of "CommonViewFields" in CQWP. Almost all the write-ups on the web says that if we need a custom column to be available in the web part's XSL, we need to export the webpart file, add the columns to "CommonViewFields", import the web part to the site. However I noticed that, when I create a custom style template in my "ItemStyle.xsl" and simply add a line like <xsl:value-of select="@MyCustomColmn"/>, then "MyCustomColumn" appears automatically under "Fields to display" in the tool pane as a text box prompt, when I edit the web part and select my style template. I can then simply fill-in my column display name under "MyCustomColumn" and the web part works perfectly fine. (There is NO NEED to export the web part, edit the ".webpart" file and add "commonviewfields" etc..etc..etc) If this works, then what is the real need for "commonviewfields" ? Can someone shed light on this? I am totally confused.. (SharePoint 2010)          

Creating Dynamic Group Fields for a Crystal Report?

I have a VB.Net application which generates quite a few Crystal Reports. One report in particular has a Group Section. What I am trying to do with my VB.Net app is pass a value (a sql server column name used in the report). This value is represented by radio buttons. For example, the user may check either "ZipCode" or "State" or "Category" or "Type" or "Vendor". Each of these options represent a column in the database table used in the report. I simply want to be able to write the "SelectionFormula" using one of these options but not sure how to go about it. Thanks, Blake

Reviewer suggested common table expression instead of adding 12 fields

So during a code review a DB Mgr suggested that I use a common table function instead of adding the fields below. No idea how to possibly do this. Anyone have any ideas? Here is a code snippet that he wants to lose: SELECT   LP .leaseID, sum   (paymentAmount1+paymentAmount2+paymentAmount3+paymentAmount4+paymentAmount5+paymentAmount6+paymentAmount7+paymentAmount8+paymentAmount9+paymentAmount10+paymentAmount11+paymentAmount12) as restPayment FROM   leasePaymentScheduleDetails AS L inner join leasePaymentSchedules AS LP ON L.leasePaymentScheduleID = LP.leasePaymentScheduleID WHERE   paymentYear>=@curyear+5 GROUP   BY LP .leaseID   Any input is appreciated.

generate dynamic fields crystal report

  I want to add a Dynamic column in Crystal report at run time. Because I dont know about the columns in design time. The columns will come based on the user selection. So i want to Add a column to Crustal report at run time. I am using Crystal report. If it is not support to add column in Crystal report at runtime. Please kindly give the other solution or other third party tool like. Which version will support to add dynamic column in Crystal report? i already used the crosstab report but thats not work for my scenario.becoz i want to add another colums at right side ( after total of each row ) , which is crosstab report not supported 

Adding Lookups to Report fields in Report Builder 2.0


Since Report Builder 2.0 is targetted at making reporting simple to create by end users, and most databases are normalised, I am looking for a way to add lookups to report fields.  By this I mean that the report should display a description rather than the code actually stored in the record being reported.


I know that this is possible by altering the SQL for the main report dataset to include a "left outer join" to the table of codes and descriptions related to the foreign key column, returning the description rather than the code (see below), but this is not something that I would expect end users to be able to implement.


select m.id,




... etc ..

from    main_table m left outer join

          lookup_table lu on lu.Code = m.code1


For end users, I would expect a right click menu on the

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?



Report Builder returning #Error value in empty fields and working with Date field


Hey guys,

I am trying to build a report using the date field from CRM and only wanting to return the date I am using this expression "=Fields!new_date.Value.ToShortDateString()" but when there is an empty fiel I get #Error. How do I solve this?

Also is there anyway to return the date field with the actual month showing instead of the numerical value? i.e. instead of 1/31/2010 Have it show January 31, 2010 (Doesn't have to be exactly in that format, but want the name value for the month.)

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!

Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Create ID (uniqueid?) from two fields when data is entered


I need to create an ID from two fields when they are entered into the db for the first time.  I thought Uniqueidentifer would do this, but it looks like uniqueidentifier is random and i have no control of the process. 

My user will enter 4 letters into a column called INIT and 4 numbers into a column called NUMB.  What I would like to do is create an id by combining those fields.

How can I do this?



LINQ with GridView and edit fields


My project includes a grid view which displays information of inventory items (Assets), data is quried from AssetTable. The GridView has a command field to allow editing, below is a description of the tables and how data is displayed


BuildingTable: (BuildingID, BuildingName)

AssetTable: (BuildingID,..,....,....)


ItemTemplate: Using a LINQ query I join the two table and displays in a label control a string which includes BuildingID and BuildingName (5 North Building)

 EditItemTemplate: Using a LINQ query I populate a DropDownList with strings of BuildingID and BuildingName same as above.
Here  issue which I need to resolve:
1.  ItemTemplate display the correct information (BuildingID BuildingName) but when I switch to EditItemTemplate the pair that is 
displayed is the first one on the list, how do I make is scroll to the item which was displayed in the label ?
<asp:CommandField ShowEditButton="True" >
                    <ControlStyle Wi

I want Er Diagram for Fees System of small institution ,can u plz guide me what fields are necessary


I want Er Diagram for Fees System of small institution ,can u plz guide me what fields are necessary to store Fees information ...


How to use report viewer 2010 in VS 2008


I downloaded report viewer 2010 from here


Now I want to actually use it in a website.  How do I do that?  Do I have to modify some part of web.config?  Specifically, what do I need to change?


populate report viewer by code


Hi all

I have a button "Show Report" to show some data from table

what I should write under this button to populate the reports ?

A connection could not be made to the report server http://localhost/ReportServer.



  • I created a Report using Business Intelligence Studio. Its working working well, i can able to view records. But when i try to deploy the report an error occurs specifying that "A connection could not be made to the report server http://localhost/ReportServer. (Microsoft Report Designer)".
  • I open the IIS 7 and find out virtual directories "Reports" and "ReportServer" are available.
  • I check on the browser and type "http://localhost/reports".  I find the browser direct to this url: http://localhost/reports/Pages/Folder.aspx and find error specifying the following one:

         Error:The report server is not responding. Verify that the report server is running and can be accessed from this computer.

  • when i type http://localhost/reportmanager, i found the error:

    HTTP Error 404.0 - Not Found

    The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.


Stop Sorting Cross Tab Report Header


Dear, I want to stop sorting in cross tab report header. And also want to sort in specific order. How can i do that? Please help me. Its urgent


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