How to add parameters and filters?

Hi,

I would like to ask a few questions about Reporting Services.

1. Can we add new parameters (by code on VS2005) at runtime when the report it's on a server? Since the job of the Report Viewer only allow to get/set parameters and not add new parameter, i wonder if there is a way to add parameter without adding manually on the report design mode.

2. Can i add some filter to change the data show on the report? (To be more specific, I didnt mean to add filter at the design mode on VS2005, I want to be able to add filter by code)

3. Can i change the query (dataset) of a report at runtime (once again i mean by code) which it's on the reportserver? (Since we can change the query on a rdlc report , i wonder if we can do the same on a rdl.)

Thanks in advance

1. No, you can only change the # of parameters by republishing the RDL.

2. No, you can only add filters in the RDL. You can affect the filter by runtime parameters, though.

3. You can change the connection string at runtime via a parameter, but not the query. That would require republishing an updated RDL.

BTW, you can call SetReportDefinition programatically, so you can achieve all your objectives roundabout by crafting an updated RDL and republishing in code or with script.

How to Add ControlParameter to SqlDataSource at Runtime?

Hi!

My question is exactly the subject.

My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)

1Dim SQLDSAs SqlDataSource =New SqlDataSource()23 SQLDS.ID ="CustomerDataSource"4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString5 SQLDS.SelectCommand ="select customerid,companyname,contactname,country from customers"6 SQLDS.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.customerid,@.companyname,@.contactname,@.country)"7 SQLDS.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where customerid=@.customerid"8 SQLDS.DeleteCommand ="delete from customers where customerid=@.customerid"910 SQLDS.UpdateParameters.Add(New Parameter("companyname"))11 SQLDS.UpdateParameters.Add(New Parameter("contactname"))12 SQLDS.UpdateParameters.Add(New Parameter("country"))13 SQLDS.UpdateParameters.Add(New Parameter("customerid"))1415 Page.Controls.Add(SQLDS)1617If Not Page.IsPostBackThen18 GridView1.DataKeyNames =New String() {"customerid"}19 GridView1.DataSourceID = SQLDS.ID2021' ... and so on

The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.

How can I synchronize the DetailsView?

I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this:

 
1<asp:SqlDataSource ID="SqlDataSource1" runat="server"2 ConnectionString="<%$ ConnectionStrings:Pubs%>"3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">4 <SelectParameters>5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />6 </SelectParameters>7</asp:SqlDataSource>8
 

Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?

Thanks!

Here is how to add it in code:

ControlParameter cp = new ControlParameter("state", "DropDownList1", "SelectedValue");

SqlDataSource1.SelectParameters.Add(cp);

|||cant get it to work.|||

using Northwind database.

i just want to synchronize the DetailsView to show the record selected in the GridView.

thanks!

the code of the web form:

ASPX.VB

1Imports SqlHelper2Imports System.Data3Imports System.Data.SqlClient45PartialClass Customers6Inherits System.Web.UI.Page78910Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load1112Dim SQLDS_GVAs SqlDataSource =New SqlDataSource()13Dim SQLDS_DVAs SqlDataSource =New SqlDataSource()1415' GridView16 SQLDS_GV.ID ="CustomerDataSource_GV"17 SQLDS_GV.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString18 SQLDS_GV.SelectCommand ="select customerid,companyname,contactname,country from customers"19 SQLDS_GV.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.custid,@.companyname,@.contactname,@.country)"20 SQLDS_GV.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where custid=@.custid"21 SQLDS_GV.DeleteCommand ="delete from customers where customerid=@.custid"2223 SQLDS_GV.UpdateParameters.Add(New Parameter("companyname"))24 SQLDS_GV.UpdateParameters.Add(New Parameter("contactname"))25 SQLDS_GV.UpdateParameters.Add(New Parameter("country"))26 SQLDS_GV.UpdateParameters.Add(New Parameter("custid"))2728'DetailsView29 SQLDS_DV.ID ="CustomerDataSource_DV"30 SQLDS_DV.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString31 SQLDS_DV.SelectCommand ="select customerid,companyname,contactname,country from customers"32 SQLDS_DV.InsertCommand ="insert into customers(customerid,companyname,contactname,country) values(@.custid,@.companyname,@.contactname,@.country)"33 SQLDS_DV.UpdateCommand ="update customers set companyname=@.companyname,contactname=@.contactname,country=@.country where customerid=@.custid"34 SQLDS_DV.DeleteCommand ="delete from customers where customerid=@.custid"3536 SQLDS_DV.UpdateParameters.Add(New Parameter("companyname"))37 SQLDS_DV.UpdateParameters.Add(New Parameter("contactname"))38 SQLDS_DV.UpdateParameters.Add(New Parameter("country"))39 SQLDS_DV.UpdateParameters.Add(New Parameter("custid"))4041'SQLDS_DV.SelectParameters.Add("customerid", "GridView1.SelectedValue")4243 Page.Controls.Add(SQLDS_GV)44 Page.Controls.Add(SQLDS_DV)4546If Not Page.IsPostBackThen4748 GridView1.DataKeyNames =New String() {"customerid"}49 GridView1.DataSourceID = SQLDS_GV.ID5051' CRIAR AS COLUNAS MANUALMENTE52 GridView1.AutoGenerateColumns =False53 GridView1.AllowPaging =True54 GridView1.PageSize = 105556Dim bf1As BoundField =New BoundField()57Dim bf2As BoundField =New BoundField()58Dim bf3As BoundField =New BoundField()59Dim bf4As BoundField =New BoundField()6061 bf1.HeaderText ="ID"62 bf1.DataField ="customerid"63 bf1.ReadOnly =True64 bf1.SortExpression ="customerid"6566 bf2.HeaderText ="Empresa"67 bf2.DataField ="companyname"68 bf2.SortExpression ="companyname"6970 bf3.HeaderText ="Contato"71 bf3.DataField ="contactname"72 bf3.SortExpression ="contactname"7374 bf4.HeaderText ="País"75 bf4.DataField ="country"76 bf4.SortExpression ="country"7778Dim cfAs CommandField =New CommandField()79 cf.ButtonType = ButtonType.Button80 cf.ShowCancelButton =True81 cf.ShowEditButton =True82 cf.ShowSelectButton =True83 cf.ShowDeleteButton =True8485 cf.CancelText ="Cancelar"86 cf.EditText ="Alterar"87 cf.DeleteText ="Excluir"88 cf.UpdateText ="Gravar"89 cf.SelectText ="Selecionar"9091 GridView1.Columns.Add(cf)92 GridView1.Columns.Add(bf1)93 GridView1.Columns.Add(bf2)94 GridView1.Columns.Add(bf3)95 GridView1.Columns.Add(bf4)96'GridView1.Columns.Add(bf5)9798 ' ****************************99100 'Dim cp As ControlParameter = New ControlParameter("customerid", "GridView1", "SelectedValue")101 'SQLDS_DV.SelectParameters.Add(cp)102 'SQLDS_DV.SelectParameters.Add("customerid", "GridView1.SelectedValue")103104105 DetailsView1.DataKeyNames =New String() {"customerid"}106 DetailsView1.DataSourceID = SQLDS_DV.ID107108 DetailsView1.AutoGenerateRows =False109110 Dim bf1_DVAs BoundField =New BoundField()111Dim bf2_DVAs BoundField =New BoundField()112Dim bf3_DVAs BoundField =New BoundField()113Dim bf4_DVAs BoundField =New BoundField()114115 bf1_DV.HeaderText ="ID"116 bf1_DV.DataField ="customerid"117 bf1_DV.ReadOnly =True118 bf1_DV.SortExpression ="customerid"119120 bf2_DV.HeaderText ="Empresa"121 bf2_DV.DataField ="companyname"122 bf2_DV.SortExpression ="companyname"123124 bf3_DV.HeaderText ="Contato"125 bf3_DV.DataField ="contactname"126 bf3_DV.SortExpression ="contactname"127128 bf4_DV.HeaderText ="País"129 bf4_DV.DataField ="country"130 bf4_DV.SortExpression ="country"131132Dim cf_DVAs CommandField =New CommandField()133 cf_DV.ButtonType = ButtonType.Button134 cf_DV.ShowCancelButton =True135 cf_DV.ShowEditButton =True136 cf_DV.ShowSelectButton =True137 cf_DV.ShowDeleteButton =True138139 cf_DV.CancelText ="Cancelar"140 cf_DV.EditText ="Alterar"141 cf_DV.DeleteText ="Excluir"142 cf_DV.UpdateText ="Gravar"143 cf_DV.SelectText ="Selecionar"144145 DetailsView1.Fields.Add(bf1_DV)146 DetailsView1.Fields.Add(bf2_DV)147 DetailsView1.Fields.Add(bf3_DV)148 DetailsView1.Fields.Add(bf4_DV)149 DetailsView1.Fields.Add(cf_DV)150151'SQLDS_DV.SelectParameters.Add("customerid", TypeCode.String, "GridView1.SelectedValue")152153Dim cpAs ControlParameter =New ControlParameter("custid", TypeCode.String,"GridView1", GridView1.SelectedValue.ToString())154 SQLDS_DV.SelectParameters.Add(cp)155156 SQLDS_GV.DataBind()157 SQLDS_DV.DataBind()158End If159 End Sub160161 Protected Sub GridView1_SelectedIndexChanged(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles GridView1.SelectedIndexChanged162'DetailsView1.DataBind()163 'DetailsView1.DataKey = GridView1.SelectedDataKey164End Sub165166End Class167

ASPX 

1<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Customers.aspx.vb" Inherits="Customers" %>23<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">45<html xmlns="http://www.w3.org/1999/xhtml" >6<head runat="server">7 <title>Untitled Page</title>8</head>9<body>10 <form id="form1" runat="server">11 <div>12 <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True" AutoGenerateColumns="False">13 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />14 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />15 <EditRowStyle BackColor="#999999" />16 <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />17 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />18 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />19 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />20 </asp:GridView>21 <br />22 <asp:DetailsView ID="DetailsView1" runat="server" CellPadding="4" ForeColor="#333333"23 GridLines="None" Height="50px" Width="125px" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateInsertButton="True" DefaultMode="Edit">24 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />25 <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />26 <EditRowStyle BackColor="#999999" />27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />29 <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />30 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />31 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />32 </asp:DetailsView>3334 </div>35 </form>36</body>37</html>38

How to add columns at run-time?

I have a table:

column1 column2 column3 column4

data data data data

data data data data

I want to create more columns at runtime, what should I do?

desired output:

column1 column2 column7 column3 column4 column5 column6

data data data data data data data

data data data data data data data

The number of columns to be added is random. Please help.

Unless you pre-process the report definition programatically before the report is rendered, you can't add/remove columns. So your options are:

1. Define the maximum number of dataset fields and conditionally hide the columns you don't need.

2. Use the matrix region to rotate dataset rows to columns (crosstab report).

|||

I have not yet tried using the matrix component, I can't find any samples in the web. I have created a dataset for the column names alone but i'm having a problem mapping the data under it.

here's the type of the table i'm trying to do:

column1 column2 column3

data1 data2 data3

data3subA

data3subB

data4 data5 data6

and so on...

I have some columns that have more rows than the others. In my example column3 have 2 extra rows. If this can be done using a matrix then please direct me to tutorial or something. Thanks. :D

|||You could look into using nested tables. You'd have to predefine the number of columns at each level. I haven't tried it myself but I think it fits your requirements.|||I didn't know I could place a table inside a table cell. The tables have still fixed columns even though I predefine the number of columns to be inserted.|||

The matrix region can have row and column groups. The column groups rotate rows to columns. The row groups on rows. Please take a look at the Company Performance report which comes with the RS sample reports.

|||What if I don't want to display the rows group? How can I remove the column for rows group?|||And where can I get the samples? Can someone please point me to the link... Thanks!|||Assuming you installed them during setup (under Client Tools), by default the samples are installed in the C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports folder.|||One row group is required at least.|||

Hi,

A good example for "Horizontel Tables" can you find at:

http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Hans

|||

Adam Tappis - IMGroup wrote:

You could look into using nested tables. You'd have to predefine the number of columns at each level. I haven't tried it myself but I think it fits your requirements.

I get an error when I do this. The data comes from 2-3 datatables. And I can only add one datasetname property for the main table.

|||

Hans Preuer wrote:

Hi,

A good example for "Horizontel Tables" can you find at:

http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Hans

Is there a simpler one, is there a good reading on how to use the matrix component?

|||

Teo Lachev wrote:

Assuming you installed them during setup (under Client Tools), by default the samples are installed in the C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports folder.

I don't have a Samples folder.

how to add column in database at runtime.

Hello all,

I m trying to add a column in my database (it is a csv file)
but it is giving me following exception.
--exception----
{System.Data.OleDb.OleDbException}
ErrorCode: -2147467259
Errors: {System.Data.OleDb.OleDbErrorCollection}
HelpLink: Nothing
InnerException: Nothing
Message: "Operation not supported on a table that contains data."
Source: "Microsoft JET Database Engine"
StackTrace: " at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior,
Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

Here is my code for this.
---Code-----
Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Dim myCon As New OleDbConnection(ConnectString)

Try

myCon.Open()
'Debug.WriteLine("Connection Opened")

Dim cmd As New OleDbCommand

cmd.CommandText = "ALTER TABLE [sample.csv] ADD mycol VARCHAR(50) NULL"
cmd.Connection = myCon
cmd.ExecuteNonQuery()

Catch ex As OleDbException
debug.WriteLine(ex.Message)
Finally
myCon.Close()
End Try

Any known reasons and workarounds??
Thanks & Regards.NetPointer (shaileshx.s.shah@.intel.com) writes:
> Message: "Operation not supported on a table that contains data."
> Source: "Microsoft JET Database Engine"
>...
> myCon.Open()
> 'Debug.WriteLine("Connection Opened")
> Dim cmd As New OleDbCommand
> cmd.CommandText = "ALTER TABLE [sample.csv] ADD mycol VARCHAR(50) NULL"
> cmd.Connection = myCon
> cmd.ExecuteNonQuery()
> Catch ex As OleDbException
> debug.WriteLine(ex.Message)
> Finally
> myCon.Close()
> End Try
> Any known reasons and workarounds??

The reason should be pretty clear from the message.

For a workaround, you may look for another newsgroup. This group is
focused on SQL Server, and we don't know that much about the Jet engine
or Excel here. Maybe they know more in comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp