Welcome :Guest
Congratulations!!!

Top 5 Contributors of the Month
Pankaj Mishra
Shivakumaran
alex morco
Imran Ghani
Meenakshi

How to add another column to sum/average/count/or any possible calculations to those pivoted columns

Hi All,

I am currently using this Dynamic Pivot Table Queries from this link:

http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

I manage to get the result i required but i need to add further column to it. I have a table such as this.

Name  |   Item   |   Points

Eddy       A             3

Eddy       B             4

John       A             2

I could get the result such as this from the above link after pivot.

Name  |  A   |    B

Eddy      3         4

John       2         null

How do i make it to become like this? Adding a column average so the final result will look like this.

Name | Average |  A  |  B

Eddy     3.5           3      4

John        1

Average Column in fact table - how to aggregate

Hi,

I have a column in my fact table called Average Handling Time, here I keep an average value - this is at a leaf level.

What should I set the aggregate to in my cube. For example, when I aggregate this to team level I don't want the sum but the average.

I did try the AverageOfChildren but didn't get the right value - see below

User Average  Handling Time
a 400
b 1011
c 602
d 298
e 393

sql average of three column

hi

WHAT QUERY SHOULD I WRITE FOR GIVEN BELOW CONDITION..I TRIED SO MANY OPTION BUT IT DIDNT WORK..PLEASE HELP

i have table name "report" which has 4 column 3 are time T1,T2,T3 AND FOURTH IS MONTH

T1  T2  T3  MONTH

2   2    1         1

3   2    2         1

2   1   6          2

3    4   6         2

1    2   1         2

I WANT TO FIND THE AVERAGE OF 3 COLUMN WITH RESPECT TO MONTH

AVERAGE  MONTH

.31               1

.68               2

HOW TO ACHIEVE THIS WITH SQL QUERY...MEANS I NEED FOR EVERY MONTH THE AVERAGE OF PER SUM OF TIME TAKEN BY REPORT PER MONTH DIVIDE BY TOTAL TIME TAKEN BY ALL REPORTS...PLEASE HELP ME

Derived Column Transform with average value (from SQL Server to SQL Server)

Hello everyone,

sorry if the following appears like a total newbie question, but I'm taking my first college course using MS SQL Server & SSIS, so please bear with me :)

I'm currently working on an assignment and face a couple (maybe mundane) problems:

1. I'm trying to create a new (bit) column which should compare sales to an average and if the sales exceed the average the bit should be flipped, i.e. put a "1" into the database. The problem here: the average should be calculated from the same dataset and I'm trying to be smart and have it calculated with every new run of the data flow. But the derived column transform only takes one input, how can I get the average into it? I tried to have an aggregate transform and have that assigned to a variable which I'd have used in the expression statement, but I couldn't figure out how to do that - like I said, a I'm a newbie here.. :(
2. Something similar, I'm trying to transform a gender code ("f"/"m") in the database to a bit value. The transformation itself I managed to get done (Excel output worked out fine), but I'm having trouble writing it back into the database table. In plain SQL (which I used before) I'd just write an UPDATE statement, but one point of the assignment is to use SSIS for all the tasks. When trying to use the OLE DB desti

Issue with Sum and Average of column values in a BDC Webpart

I have retrieved data from SQL server using business data list webpart .Now,I have to take the sum and average of few column values.I am using SPD 2010 to add an additional row where I can store the sum and avg values.On the added row,When I trying to use insert formula,I cannot see that option available.And also I cannot see option to 'create, for conditional formatting.Can any one suggest me about how I can store the values for sum and avg's of the columns in a BDC webpart.

How do we generally get the sum and averages of the column in the BDC Webpart??

Setting a column average SSAS

I have a column on my data warehouse which contains an average, during the creation of my cube on SSAS, how can I do to set this column for what gives me the average of this column and not the sum.

I looked on the type of data that can be defined on groups of measures and I have not found the average standard.

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

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.

<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="PartNumber">
<rd:DefaultName>PartNumber</rd:DefaultName>

Use a column as parameter for a sds in another column; gridview

Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal

Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

GridView mygrid = new GridView();
//.....................................More columns code for the grid

//Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.
string[] dataNavigateUrlFields = { "CustomerID" };

Bug: Does not change column type from nvarchar to ntext

Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Display column from child table. Possible ?

Hello,

I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Rendering data with HTML tags in the DD Gridview for a selected column

I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// Html Encode the cells
}

}

but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1?

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

Filter by Computed Column

Hello!

After looking for a driver to open legacy dbf drivers to connect, the next level trouble a have is this one:

first, i can retrieve the data but when trying to filter by a parameter (somesc columns) i get an error.

The error says about the database can't determine some blah blah!

The error is a columm tha is formated (  1234  ) about  8 space, fixed. The values in the query appears like that. I resolve this by Trimming Ltrim(ColumnName) and i get this (1234).

Now in SqlExpress, how can i filter by the new Trimmed Expression? (This is a primary key that the DBF use)

Sample:

SELECT column1, column2, column3
TABLE FoxProDB

Now

SELECT Ltrim(column1) as NewExp, column2, column3
TABLE FoxProDB
Where NewExpre = @NewExpres                  -Error-

Thanx!

Unique text value in databse column

Hi Everyone!

I know this is probably a very basic question but how do I keep a column's text values unique in SQL server (ie for a unique username)?  The column is not an integer it's varchar(50), and it's not the primary key but I could make it the primary key if recommended.

The front end could handle it;  where the user would enter text, it's validated, then a query is done to see if that username is available.  If so, then user can decide to choose it and an insert is done, if not they pick a new username and process repeats.  With this approach, the responsibility of keeping the value unique solely rests on the front end, nothing on the database side.

Is this the correct way to handle this?

Thank you!

DAL Dataset showing all the column

I follow the tutorial and create DAL with Dataset. I created a Table Adapter with many column, but when i Call a simple Get for 2 column only. The system display 2 column and the rest column with empty data.

My question is that will this affect the performance? because i only need 2 column.

example: Table with 40 column define in Dataset in DAL. I have GetMember from Dataset with 40 column, some request only 1 column (with 39 empty), some request 22 column (with 18 empty column). Will this affact the performance?

Should i create a seperate DatasetAllColumn, Dataset1Column, Dataset22Column ?? so system only return exact column i need. and hofully the performance is optimized to it best.

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