Solved

Visual Studio Reporting do report based off of set of values

Posted on 2011-02-18
1
244 Views
Last Modified: 2012-05-11
Right now, I have a working report that passes parameters to an SQL query that fills a dataset, and displays the data using the DataSet.  What I would like to be able to do is change the code so that I can fill the dataset based off a SET of values for one particular field.  The code that I have now works great, I just neeed to know how to change it so that I can send a SET of values as a parameter.  Here is the code I have now


        EstimatingReportViewer.Reset();
        EstimatingReportViewer.ProcessingMode = ProcessingMode.Local;
            string conString = "Data Source=server;Initial Catalog=table;Persist Security Info=True;User ID=userid;Password=password";
            SqlConnection conReport = new SqlConnection(conString);
            SqlCommand cmdReport = new SqlCommand();
            SqlDataReader drReport;
            DataSet DataSetReporting = new dsReporting();
           conReport.Open();
           cmdReport.CommandType = CommandType.Text;
           cmdReport.Connection = conReport;
           cmdReport.CommandText = "SELECT * FROM dbo.table1 WHERE Period >= @StartingDate and Period <= @EndingDate AND column1 = @Project AND column2 = @Version ORDER BY Period,column2";
cmdReport.Parameters.Add(new SqlParameter("@StartingDate", SqlDbType.Int));
                cmdReport.Parameters.Add(new SqlParameter("@EndingDate", SqlDbType.Int));
                cmdReport.Parameters.Add(new SqlParameter("@Project", SqlDbType.NVarChar, 50));
                cmdReport.Parameters.Add(new SqlParameter("@Version", SqlDbType.NVarChar, 50));
                cmdReport.Parameters["@StartingDate"].Value = begindate;
                cmdReport.Parameters["@EndingDate"].Value = endingdate;
                cmdReport.Parameters["@Project"].Value = projectname;
                cmdReport.Parameters["@Version"].Value = versionname;
                drReport = cmdReport.ExecuteReader();
                DataSetReporting.Tables[0].Load(drReport);
                DataTable table = DataSetReporting.Tables[0];
                drReport.Close();
                conReport.Close();
                EstimatingReportViewer.LocalReport.ReportPath = Server.MapPath("Report1.rdlc");
                EstimatingReportViewer.LocalReport.DataSources.Add(new ReportDataSource(EstimatingReportViewer.LocalReport.GetDataSourceNames()[0], table));
                EstimatingReportViewer.Visible = true;
0
Comment
Question by:melli111
1 Comment
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 34931451
You have to change this line.

cmdReport.CommandText = "SELECT * FROM dbo.table1 WHERE Period >= @StartingDate and Period <= @EndingDate AND column1 = @Project AND column2 = @Version ORDER BY Period,column2";


However, most SQLs do not accept sets of values as a parameter.

Which field are you filtering on and what are the Values?

mlmcc
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question