Please help deciphering error message

I just changed my dataset syntaxes from the typical
SELECT col FROM table WHERE col3 = @.value
to
="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
For some reason I'm getting the following pop-up:
--
Processing Errors
--
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
â'ds_Legal_Entityâ'.
--
OK
--
I am not familiar with the CommandText syntax to understand where the error
might be. My query is below, could someone tell me what the problem might be?
Thanks!
Mike
="SELECT DISTINCT dbo.t_d_legal.legal_desc
FROM dbo.t_d_legal
INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
IIF(Parameters!BusKey.Value = 0,"","
AND
(dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
" ORDER BY dbo.t_d_legal.legal_desc"think you had extra single quote:
="SELECT col FROM table WHERE col3 = " & Parameters!Code.Value & " rest of
code"
otherwise if you need quotes due to your parm value being character you'll
need to double up on the quotes. read BOL.
"Bassist695" wrote:
> I just changed my dataset syntaxes from the typical
> SELECT col FROM table WHERE col3 = @.value
> to
> ="SELECT col FROM table WHERE col3 = '" & Parameters!Code.Value & "'
> For some reason I'm getting the following pop-up:
> --
> Processing Errors
> --
> An error has occurred during report processing.
> Cannot set the command text for data set 'ds_Legal_Entity'.
> Error during processing of the CommandText expression of dataset
> â'ds_Legal_Entityâ'.
> --
> OK
> --
> I am not familiar with the CommandText syntax to understand where the error
> might be. My query is below, could someone tell me what the problem might be?
> Thanks!
> Mike
> ="SELECT DISTINCT dbo.t_d_legal.legal_desc
> FROM dbo.t_d_legal
> INNER JOIN dbo.t_pms ON dbo.t_d_legal.legal_key = dbo.t_pms.legal_key
> WHERE (dbo.t_pms.mth_key = " & Parameters!mth_key.Value & ") " &
> IIF(Parameters!BusKey.Value = 0,"","
> AND
> (dbo.t_pms.bus_key = ") & Parameters!BusKey.Value & ")" &
> " ORDER BY dbo.t_d_legal.legal_desc"

Please help - data looks god, but nothing on report!

1. Created DataSet which contains a DataTable.

2. ReportViewer points to .rdlc report.

3. .rdlc report uses DataSet above.

4. Progamatically load DataTable from other tables.

5. In debug, dataset looks good, but NO data on report.

I have been working on this silly report for 4 days! Please help.

My appologies for the typo in the message subject.

Any ideas?

Placeholds for missing rows ... is this possible?

I have a dataset like this that feeds a table data region in a report:

Answer Label Answer Count
Strongly Agree 10
Agree 7
Neutral 12
Disagree 19
Strong Disagree 9

For every Answer Label, I need the table to create a new row that displays the label and count so it looks just like the above. The problem occurs when the dataset does not include one or more Answer Labels because there are no counts associated to them. I still need to see all Answer Labels ... but simply set the count = 0 if it is not found in the dataset.

So working with a resultset like this:

Strongly Agree 10
Neutral 12

My table data region needs to look like such:

Strongly Agree 10
Agree 0
Neutral 12
Disagree 0
Strong Disagree 0

Is there a good way to do this?

Thanks - WaydeIf your dataset doesn't contain the rows it makes no sense to produce them afterwards..
If you have two tables, persons and answers you could left/right join them:

SELECT person.Name, count(answers.answer) as AnswerCount
FROM person LEFT JOIN answers ON person.ID = answers.personID
group by person.ID;

This statement means:
Take ALL persons from person and connect it with found answers of the answer-table, so you always get all persons and blank or "0" values for the answers.. If AnswerCount is empty you could use isnull(count(answers.answer),"0")

|||

Another approach would be:

-- Assume schema:

-- Table: AnswerTypes

-- ID (PK)

-- Label

-- Table: Answers

-- AnswerTypeId (FK)

--

select label, IsNull(a.CountAnswers, 0) as votes from AnswerTypes

left outer join

(

select AnswerTypeId, count(*) as CountAnswers from Answers

group by AnswerTypeId

) a on AnswerTypes.ID = a.AnswerTypeID

Pivoting DataSet

I currently have from SQL a stored procedure that is vaguely doing something
like:
SELECT
FormName,
frmDueDate,
DepartmentName,
Country
frmStatus
FROM
wholeBunchOfTables
So data will be returned like:
Form1 ITDept USA Incomplete
Form2 ITDept UK Completed
Form1 HRDept FR Completed
Form2 HRDept FR Missing
The DataSet resultant from the Stored procedure execution is binded to a
sortable DataGrid. However, I would like the data "pivoted":
i.e.
Form1 Form2
ITDept USA Incomplete Completed
HRDept FR Completed Missing
Is this at all possible (natively)?
- Can't think of any pivoting SQL operators?
- Can't pivot on ASP.NET/ADO.NET? Would I have to resort to
-- creating another DataSet with columns pivoted? or
-- render my own table, etc. and handle by own sorting?
Reporting Services does this quite easily... are you creating a report?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)?
> - Can't think of any pivoting SQL operators?
> - Can't pivot on ASP.NET/ADO.NET? Would I have to resort to
> -- creating another DataSet with columns pivoted? or
> -- render my own table, etc. and handle by own sorting?
|||Patrick
This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
Adrian Moore
http://www.queryadataset.com
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)?
> - Can't think of any pivoting SQL operators?
> - Can't pivot on ASP.NET/ADO.NET? Would I have to resort to
> -- creating another DataSet with columns pivoted? or
> -- render my own table, etc. and handle by own sorting?
|||This article is *Excellent*!! Just about what I wanted!
However, how can I modify the following:
<asp:datagrid id="OutstandingFormsDataGrid" runat="server"
AutoGenerateColumns="False" ShowHeader="true"
EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
OnSortCommand="SortCurrentMonth_OnClick"
HeaderStyle-Height="25px">
<ItemStyle CssClass="data"></ItemStyle>
<HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
DataTextField="?These are pivoted columns?" SortExpression="?"
HeaderText="Form Name"></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Due Date"
</Columns>
</asp:datagrid>
The following are being displayed at the moment with
"AutoGenerateColumns=True":
frmInstId Country Form1 Form2 Form3 Form 4
1 UK complete
2 UK complete
3 UK missing
4 UK complete
5 US missing
6 US missing
7 US complete
8 US complete
Ideally I want:
Country Form1 Form2 Form3 Form 4
UK complete complete missing complete
US missing missing complete complete
(with the frmInstId) embedded as hyperlink the the form status
Is it possible?
"Adrian Moore" wrote:

> Patrick
> This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
> Adrian Moore
> http://www.queryadataset.com
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>
>
|||Hello Patrick,
This seems normal behavior if you use the code from the following link
directly:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
The code adds a new row in the datatable of dataset from the original
table. You need to modify the code so that it can search all the existing
rows in datatable for the row with the same key coulumn such as Country in
your table. If there is a row existing, you shall create a new column to
this row other than create a new row.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Pivoting DataSet
| thread-index: AcV415h2UiiUvr4YRaGnKv5nBmTZ3Q==
| X-WBNR-Posting-Host: 198.240.130.75
| From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@.newsgroup.nospam>
| References: <BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com>
<OsVZAg4dFHA.2288@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Pivoting DataSet
| Date: Fri, 24 Jun 2005 09:13:02 -0700
| Lines: 85
| Message-ID: <1A21D301-964E-4E83-9397-2F6B50C6403D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft .public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:61330
microsoft.public.dotnet.framework.adonet:31648
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| This article is *Excellent*!! Just about what I wanted!
|
| However, how can I modify the following:
| <asp:datagrid id="OutstandingFormsDataGrid" runat="server"
| AutoGenerateColumns="False" ShowHeader="true"
| EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
| OnSortCommand="SortCurrentMonth_OnClick"
| HeaderStyle-Height="25px">
| <ItemStyle CssClass="data"></ItemStyle>
| <HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
| <Columns>
| <asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
| DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
| DataTextField="?These are pivoted columns?" SortExpression="?"
| HeaderText="Form Name"></asp:HyperLinkColumn>
| <asp:BoundColumn DataField="Due Date"
| </Columns>
| </asp:datagrid>
|
| The following are being displayed at the moment with
| "AutoGenerateColumns=True":
| frmInstId Country Form1 Form2 Form3 Form 4
| 1 UK complete
| 2 UK complete
| 3 UK missing
| 4 UK complete
| 5 US missing
| 6 US missing
| 7 US complete
| 8 US complete
|
| Ideally I want:
| Country Form1 Form2 Form3 Form 4
| UK complete complete missing complete
| US missing missing complete complete
|
| (with the frmInstId) embedded as hyperlink the the form status
|
| Is it possible?
|
| "Adrian Moore" wrote:
|
| > Patrick
| >
| > This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
| >
| > Adrian Moore
| > http://www.queryadataset.com
| >
| >
| > "Patrick" <questions@.newsgroup.nospam> wrote in message
| > news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
| > >I currently have from SQL a stored procedure that is vaguely doing
| > >something
| > > like:
| > > SELECT
| > > FormName,
| > > frmDueDate,
| > > DepartmentName,
| > > Country
| > > frmStatus
| > > FROM
| > > wholeBunchOfTables
| > >
| > > So data will be returned like:
| > > Form1 ITDept USA Incomplete
| > > Form2 ITDept UK Completed
| > > Form1 HRDept FR Completed
| > > Form2 HRDept FR Missing
| > >
| > > The DataSet resultant from the Stored procedure execution is binded
to a
| > > sortable DataGrid. However, I would like the data "pivoted":
| > > i.e.
| > > Form1 Form2
| > > ITDept USA Incomplete Completed
| > > HRDept FR Completed Missing
| > >
| > > Is this at all possible (natively)?
| > > - Can't think of any pivoting SQL operators?
| > > - Can't pivot on ASP.NET/ADO.NET? Would I have to resort to
| > > -- creating another DataSet with columns pivoted? or
| > > -- render my own table, etc. and handle by own sorting?
| >
| >
| >
|

Pivoting DataSet

I currently have from SQL a stored procedure that is vaguely doing something
like:
SELECT
FormName,
frmDueDate,
DepartmentName,
Country
frmStatus
FROM
wholeBunchOfTables
So data will be returned like:
Form1 ITDept USA Incomplete
Form2 ITDept UK Completed
Form1 HRDept FR Completed
Form2 HRDept FR Missing
The DataSet resultant from the Stored procedure execution is binded to a
sortable DataGrid. However, I would like the data "pivoted":
i.e.
Form1 Form2
ITDept USA Incomplete Completed
HRDept FR Completed Missing
Is this at all possible (natively)'
- Can't think of any pivoting SQL operators'
- Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
-- creating another DataSet with columns pivoted' or
-- render my own table, etc. and handle by own sorting?Reporting Services does this quite easily... are you creating a report?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)'
> - Can't think of any pivoting SQL operators'
> - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> -- creating another DataSet with columns pivoted' or
> -- render my own table, etc. and handle by own sorting?|||Patrick
This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
Adrian Moore
http://www.queryadataset.com
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)'
> - Can't think of any pivoting SQL operators'
> - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> -- creating another DataSet with columns pivoted' or
> -- render my own table, etc. and handle by own sorting?|||This article is *Excellent*!! Just about what I wanted!
However, how can I modify the following:
<asp:datagrid id="OutstandingFormsDataGrid" runat="server"
AutoGenerateColumns="False" ShowHeader="true"
EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
OnSortCommand="SortCurrentMonth_OnClick"
HeaderStyle-Height="25px">
<ItemStyle CssClass="data"></ItemStyle>
<HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
DataTextField="'These are pivoted columns'" SortExpression="?"
HeaderText="Form Name"></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Due Date"
</Columns>
</asp:datagrid>
The following are being displayed at the moment with
"AutoGenerateColumns=True":
frmInstId Country Form1 Form2 Form3 Form 4
1 UK complete
2 UK complete
3 UK missing
4 UK complete
5 US missing
6 US missing
7 US complete
8 US complete
Ideally I want:
Country Form1 Form2 Form3 Form 4
UK complete complete missing complete
US missing missing complete complete
(with the frmInstId) embedded as hyperlink the the form status
Is it possible?
"Adrian Moore" wrote:

> Patrick
> This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
> Adrian Moore
> http://www.queryadataset.com
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>
>|||Hello Patrick,
This seems normal behavior if you use the code from the following link
directly:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
The code adds a new row in the datatable of dataset from the original
table. You need to modify the code so that it can search all the existing
rows in datatable for the row with the same key coulumn such as Country in
your table. If there is a row existing, you shall create a new column to
this row other than create a new row.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Pivoting DataSet
| thread-index: AcV415h2UiiUvr4YRaGnKv5nBmTZ3Q==
| X-WBNR-Posting-Host: 198.240.130.75
| From: "examnotes" <questions@.newsgroup.nospam>
| References: <BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com>
<OsVZAg4dFHA.2288@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Pivoting DataSet
| Date: Fri, 24 Jun 2005 09:13:02 -0700
| Lines: 85
| Message-ID: <1A21D301-964E-4E83-9397-2F6B50C6403D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:61330
microsoft.public.dotnet.framework.adonet:31648
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| This article is *Excellent*!! Just about what I wanted!
|
| However, how can I modify the following:
| <asp:datagrid id="OutstandingFormsDataGrid" runat="server"
| AutoGenerateColumns="False" ShowHeader="true"
| EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
| OnSortCommand="SortCurrentMonth_OnClick"
| HeaderStyle-Height="25px">
| <ItemStyle CssClass="data"></ItemStyle>
| <HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
| <Columns>
| <asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
| DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
| DataTextField="'These are pivoted columns'" SortExpression="?"
| HeaderText="Form Name"></asp:HyperLinkColumn>
| <asp:BoundColumn DataField="Due Date"
| </Columns>
| </asp:datagrid>
|
| The following are being displayed at the moment with
| "AutoGenerateColumns=True":
| frmInstId Country Form1 Form2 Form3 Form 4
| 1 UK complete
| 2 UK complete
| 3 UK missing
| 4 UK complete
| 5 US missing
| 6 US missing
| 7 US complete
| 8 US complete
|
| Ideally I want:
| Country Form1 Form2 Form3 Form 4
| UK complete complete missing complete
| US missing missing complete complete
|
| (with the frmInstId) embedded as hyperlink the the form status
|
| Is it possible?
|
| "Adrian Moore" wrote:
|
| > Patrick
| >
| > This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
| >
| > Adrian Moore
| > http://www.queryadataset.com
| >
| >
| > "Patrick" <questions@.newsgroup.nospam> wrote in message
| > news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
| > >I currently have from SQL a stored procedure that is vaguely doing
| > >something
| > > like:
| > > SELECT
| > > FormName,
| > > frmDueDate,
| > > DepartmentName,
| > > Country
| > > frmStatus
| > > FROM
| > > wholeBunchOfTables
| > >
| > > So data will be returned like:
| > > Form1 ITDept USA Incomplete
| > > Form2 ITDept UK Completed
| > > Form1 HRDept FR Completed
| > > Form2 HRDept FR Missing
| > >
| > > The DataSet resultant from the Stored procedure execution is binded
to a
| > > sortable DataGrid. However, I would like the data "pivoted":
| > > i.e.
| > > Form1 Form2
| > > ITDept USA Incomplete Completed
| > > HRDept FR Completed Missing
| > >
| > > Is this at all possible (natively)'
| > > - Can't think of any pivoting SQL operators'
| > > - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
| > > -- creating another DataSet with columns pivoted' or
| > > -- render my own table, etc. and handle by own sorting?
| >
| >
| >
|

Pivoting DataSet

I currently have from SQL a stored procedure that is vaguely doing something
like:
SELECT
FormName,
frmDueDate,
DepartmentName,
Country
frmStatus
FROM
wholeBunchOfTables
So data will be returned like:
Form1 ITDept USA Incomplete
Form2 ITDept UK Completed
Form1 HRDept FR Completed
Form2 HRDept FR Missing
The DataSet resultant from the Stored procedure execution is binded to a
sortable DataGrid. However, I would like the data "pivoted":
i.e.
Form1 Form2
ITDept USA Incomplete Completed
HRDept FR Completed Missing
Is this at all possible (natively)'
- Can't think of any pivoting SQL operators'
- Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
-- creating another DataSet with columns pivoted' or
-- render my own table, etc. and handle by own sorting?Reporting Services does this quite easily... are you creating a report?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)'
> - Can't think of any pivoting SQL operators'
> - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> -- creating another DataSet with columns pivoted' or
> -- render my own table, etc. and handle by own sorting?|||Patrick
This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
Adrian Moore
http://www.queryadataset.com
"Patrick" <questions@.newsgroup.nospam> wrote in message
news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
>I currently have from SQL a stored procedure that is vaguely doing
>something
> like:
> SELECT
> FormName,
> frmDueDate,
> DepartmentName,
> Country
> frmStatus
> FROM
> wholeBunchOfTables
> So data will be returned like:
> Form1 ITDept USA Incomplete
> Form2 ITDept UK Completed
> Form1 HRDept FR Completed
> Form2 HRDept FR Missing
> The DataSet resultant from the Stored procedure execution is binded to a
> sortable DataGrid. However, I would like the data "pivoted":
> i.e.
> Form1 Form2
> ITDept USA Incomplete Completed
> HRDept FR Completed Missing
> Is this at all possible (natively)'
> - Can't think of any pivoting SQL operators'
> - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> -- creating another DataSet with columns pivoted' or
> -- render my own table, etc. and handle by own sorting?|||This article is *Excellent*!! Just about what I wanted!
However, how can I modify the following:
<asp:datagrid id="OutstandingFormsDataGrid" runat="server"
AutoGenerateColumns="False" ShowHeader="true"
EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
OnSortCommand="SortCurrentMonth_OnClick"
HeaderStyle-Height="25px">
<ItemStyle CssClass="data"></ItemStyle>
<HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
<Columns>
<asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
DataTextField="'These are pivoted columns'" SortExpression="?"
HeaderText="Form Name"></asp:HyperLinkColumn>
<asp:BoundColumn DataField="Due Date"
</Columns>
</asp:datagrid>
The following are being displayed at the moment with
"AutoGenerateColumns=True":
frmInstId Country Form1 Form2 Form3 Form 4
1 UK complete
2 UK complete
3 UK missing
4 UK complete
5 US missing
6 US missing
7 US complete
8 US complete
Ideally I want:
Country Form1 Form2 Form3 Form 4
UK complete complete missing complete
US missing missing complete complete
(with the frmInstId) embedded as hyperlink the the form status
Is it possible?
"Adrian Moore" wrote:
> Patrick
> This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
> Adrian Moore
> http://www.queryadataset.com
>
> "Patrick" <questions@.newsgroup.nospam> wrote in message
> news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
> >I currently have from SQL a stored procedure that is vaguely doing
> >something
> > like:
> > SELECT
> > FormName,
> > frmDueDate,
> > DepartmentName,
> > Country
> > frmStatus
> > FROM
> > wholeBunchOfTables
> >
> > So data will be returned like:
> > Form1 ITDept USA Incomplete
> > Form2 ITDept UK Completed
> > Form1 HRDept FR Completed
> > Form2 HRDept FR Missing
> >
> > The DataSet resultant from the Stored procedure execution is binded to a
> > sortable DataGrid. However, I would like the data "pivoted":
> > i.e.
> > Form1 Form2
> > ITDept USA Incomplete Completed
> > HRDept FR Completed Missing
> >
> > Is this at all possible (natively)'
> > - Can't think of any pivoting SQL operators'
> > - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
> > -- creating another DataSet with columns pivoted' or
> > -- render my own table, etc. and handle by own sorting?
>
>|||Hello Patrick,
This seems normal behavior if you use the code from the following link
directly:
http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
The code adds a new row in the datatable of dataset from the original
table. You need to modify the code so that it can search all the existing
rows in datatable for the row with the same key coulumn such as Country in
your table. If there is a row existing, you shall create a new column to
this row other than create a new row.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Pivoting DataSet
| thread-index: AcV415h2UiiUvr4YRaGnKv5nBmTZ3Q==| X-WBNR-Posting-Host: 198.240.130.75
| From: "=?Utf-8?B?UGF0cmljaw==?=" <questions@.newsgroup.nospam>
| References: <BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com>
<OsVZAg4dFHA.2288@.TK2MSFTNGP14.phx.gbl>
| Subject: Re: Pivoting DataSet
| Date: Fri, 24 Jun 2005 09:13:02 -0700
| Lines: 85
| Message-ID: <1A21D301-964E-4E83-9397-2F6B50C6403D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft.public.sqlserver.server
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:61330
microsoft.public.dotnet.framework.adonet:31648
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| This article is *Excellent*!! Just about what I wanted!
|
| However, how can I modify the following:
| <asp:datagrid id="OutstandingFormsDataGrid" runat="server"
| AutoGenerateColumns="False" ShowHeader="true"
| EditItemStyle="data" HeaderStyle-CssClass="header" AllowSorting="true"
| OnSortCommand="SortCurrentMonth_OnClick"
| HeaderStyle-Height="25px">
| <ItemStyle CssClass="data"></ItemStyle>
| <HeaderStyle Height="25px" CssClass="header"></HeaderStyle>
| <Columns>
| <asp:HyperLinkColumn DataNavigateUrlField="frmInstId"
| DataNavigateUrlFormatString="ShowForm.aspx?id={0}"
| DataTextField="'These are pivoted columns'" SortExpression="?"
| HeaderText="Form Name"></asp:HyperLinkColumn>
| <asp:BoundColumn DataField="Due Date"
| </Columns>
| </asp:datagrid>
|
| The following are being displayed at the moment with
| "AutoGenerateColumns=True":
| frmInstId Country Form1 Form2 Form3 Form 4
| 1 UK complete
| 2 UK complete
| 3 UK missing
| 4 UK complete
| 5 US missing
| 6 US missing
| 7 US complete
| 8 US complete
|
| Ideally I want:
| Country Form1 Form2 Form3 Form 4
| UK complete complete missing complete
| US missing missing complete complete
|
| (with the frmInstId) embedded as hyperlink the the form status
|
| Is it possible?
|
| "Adrian Moore" wrote:
|
| > Patrick
| >
| > This might help: http://weblogs.sqlteam.com/jeffs/articles/5091.aspx
| >
| > Adrian Moore
| > http://www.queryadataset.com
| >
| >
| > "Patrick" <questions@.newsgroup.nospam> wrote in message
| > news:BA374BD6-B1A2-41BE-9109-A611EFCC394E@.microsoft.com...
| > >I currently have from SQL a stored procedure that is vaguely doing
| > >something
| > > like:
| > > SELECT
| > > FormName,
| > > frmDueDate,
| > > DepartmentName,
| > > Country
| > > frmStatus
| > > FROM
| > > wholeBunchOfTables
| > >
| > > So data will be returned like:
| > > Form1 ITDept USA Incomplete
| > > Form2 ITDept UK Completed
| > > Form1 HRDept FR Completed
| > > Form2 HRDept FR Missing
| > >
| > > The DataSet resultant from the Stored procedure execution is binded
to a
| > > sortable DataGrid. However, I would like the data "pivoted":
| > > i.e.
| > > Form1 Form2
| > > ITDept USA Incomplete Completed
| > > HRDept FR Completed Missing
| > >
| > > Is this at all possible (natively)'
| > > - Can't think of any pivoting SQL operators'
| > > - Can't pivot on ASP.NET/ADO.NET' Would I have to resort to
| > > -- creating another DataSet with columns pivoted' or
| > > -- render my own table, etc. and handle by own sorting?
| >
| >
| >
|