xp cmdshell: dataset

Showing posts with label dataset. Show all posts

Wednesday, March 28, 2012

problem retrieving correct max(date) row from this dataset

Hello,
I respectfully request if someone could help me modify the following query:
The goal is to retrieve a record with a curExpireDate of 12/31/05 where ther
e
is more than one row for a given curRecordID - note: in the following
dataset there is only one correct row that I want to retrieve. So for given
curRecordID there is more than one row for a curExpireDate >= 12/31/05. The
n
if curEntryDate for a curExpireDate of 12/31/05 is greater than the
curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then I
want to retrieve that row.
The following subdataset (the actual dataset contains thousands of rows
where I need to do the same thing) contains one row where Max(curExpireDate)
= 12/31/05. I don't want that row. There are 3 more curRecordID's with
curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
18537 this ID contains a row for curExpireDate = 12/31/05 where
Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
12/31/06 for ID = 18537. None of the otherID's has this condition. I need
to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
12/31/05.
Here is the dataset and the query I have been experimenting with:
create table #temp4(curRecordID int,
curEntryDate datetime, curExpireDate datetime)
insert Into #temp4
select 12783, '2005-04-07', '2005-12-31' Union
select 12783, '2004-01-12', '2005-12-31' Union
select 12783, '2006-02-03', '2006-12-31' Union
select 12783, '2005-01-11', '2006-12-31' Union
select 29714, '2005-06-29', '2005-12-31' Union
select 29714, '2005-02-02', '2006-12-31' Union
select 29714, '2005-12-01', '2006-12-31' Union
select 18537, '2004-12-10', '2005-12-31' Union
select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
select 18537, '2006-01-10', '2006-12-31' Union
select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
SELECT * FROM #temp4 as X
WHERE
curRecordID NOT IN --the list of recid from beginning
(SELECT curRecordID FROM #temp4 GROUP BY curRecordID
HAVING MAX(curExpireDate)='12/31/05')
AND
curExpireDate = '12/31/05' --order by curRecordID
AND curEntryDate >
(select top 1 curEntryDate from #temp4 as Y
where X.curRecordID = Y.curRecordID
and Y.curExpireDate =
(select max(curExpireDate) from #temp4 as Z
where X.curRecordID = Z.curRecordID))
This is the current resultset - which is not the desired resultset
curRecID curEntryDate curExpireDate
12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
There is no way for me to isolate my desired row from this resultset
The desired resultset is this:
curRecID curEntryDate curExpireDate
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
Any help appreciated,
Thanks,
RichI think I found my problem. I changed
(select top 1 curEntryDate from #temp4 as Y...
to
(select Max(curEntryDate) from #temp4 as Y
This is now giving me the desired result in my test case. Hopefully, will
do the same for the actual data.
"Rich" wrote:

> Hello,
> I respectfully request if someone could help me modify the following query
:
> The goal is to retrieve a record with a curExpireDate of 12/31/05 where th
ere
> is more than one row for a given curRecordID - note: in the following
> dataset there is only one correct row that I want to retrieve. So for giv
en
> curRecordID there is more than one row for a curExpireDate >= 12/31/05. T
hen
> if curEntryDate for a curExpireDate of 12/31/05 is greater than the
> curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then
I
> want to retrieve that row.
> The following subdataset (the actual dataset contains thousands of rows
> where I need to do the same thing) contains one row where Max(curExpireDat
e)
> = 12/31/05. I don't want that row. There are 3 more curRecordID's with
> curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
> 18537 this ID contains a row for curExpireDate = 12/31/05 where
> Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
> 12/31/06 for ID = 18537. None of the otherID's has this condition. I nee
d
> to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
> 12/31/05.
> Here is the dataset and the query I have been experimenting with:
> create table #temp4(curRecordID int,
> curEntryDate datetime, curExpireDate datetime)
> insert Into #temp4
> select 12783, '2005-04-07', '2005-12-31' Union
> select 12783, '2004-01-12', '2005-12-31' Union
> select 12783, '2006-02-03', '2006-12-31' Union
> select 12783, '2005-01-11', '2006-12-31' Union
> select 29714, '2005-06-29', '2005-12-31' Union
> select 29714, '2005-02-02', '2006-12-31' Union
> select 29714, '2005-12-01', '2006-12-31' Union
> select 18537, '2004-12-10', '2005-12-31' Union
> select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
> select 18537, '2006-01-10', '2006-12-31' Union
> select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
> SELECT * FROM #temp4 as X
> WHERE
> curRecordID NOT IN --the list of recid from beginning
> (SELECT curRecordID FROM #temp4 GROUP BY curRecordID
> HAVING MAX(curExpireDate)='12/31/05')
> AND
> curExpireDate = '12/31/05' --order by curRecordID
> AND curEntryDate >
> (select top 1 curEntryDate from #temp4 as Y
> where X.curRecordID = Y.curRecordID
> and Y.curExpireDate =
> (select max(curExpireDate) from #temp4 as Z
> where X.curRecordID = Z.curRecordID))
>
> This is the current resultset - which is not the desired resultset
> curRecID curEntryDate curExpireDa
te
> 12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
> 29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
> There is no way for me to isolate my desired row from this resultset
> The desired resultset is this:
> curRecID curEntryDate curExpireDa
te
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
> Any help appreciated,
> Thanks,
> Rich

Monday, March 26, 2012

problem recreating a report - 2nd dataset doesn't exist?

Hello,
I am just starting out with reporting services (I have a book which I have
not read yet). I have copied the basic part of a report to a new report for
learning purposes. This contains a parameter which I included in the query.
This works so far. But the original report also includes a table of fields
that are based on a separate dataset. I am getting an error now. How do I
add/imlement this secondary dataset? The error message says that this
dataset does not exist. I presume it means the dataset does not exist in the
report because the tables in sql server exist. How do I create this dataset?
Thanks,
Richopen report project by using visual studio
open report document (double click the report file - solution explorer)
go to Data tab (Data / Layout / Preview)
click dataset dropdown and choose <new dataset...>
have fun
"Rich" <Rich@.discussions.microsoft.com> escreveu na mensagem
news:D8EE2D83-6A8B-49AF-B875-DF9191DAD51A@.microsoft.com...
> Hello,
> I am just starting out with reporting services (I have a book which I have
> not read yet). I have copied the basic part of a report to a new report
> for
> learning purposes. This contains a parameter which I included in the
> query.
> This works so far. But the original report also includes a table of
> fields
> that are based on a separate dataset. I am getting an error now. How do
> I
> add/imlement this secondary dataset? The error message says that this
> dataset does not exist. I presume it means the dataset does not exist in
> the
> report because the tables in sql server exist. How do I create this
> dataset?
> Thanks,
> Rich|||Nevermind. I figured it out. Goto Data, New Dataset.
I didn't check the entire list of datasets in the original report.
"Rich" wrote:
> Hello,
> I am just starting out with reporting services (I have a book which I have
> not read yet). I have copied the basic part of a report to a new report for
> learning purposes. This contains a parameter which I included in the query.
> This works so far. But the original report also includes a table of fields
> that are based on a separate dataset. I am getting an error now. How do I
> add/imlement this secondary dataset? The error message says that this
> dataset does not exist. I presume it means the dataset does not exist in the
> report because the tables in sql server exist. How do I create this dataset?
> Thanks,
> Rich|||Thanks. I am sort of having fun. At least I can get my report to run.
Note: I am outputting this report in Excel format. The first set of fields
(about 3 of them) are based on the first dataset - the master data. Then I
have the 2nd dataset which is the detail data (about 6 fields). When I run
the report (outside of VS, like from an Access database app) the first
dataset displays on the first sheet of an Excel workbook, but the 2nd
dataset, the detail data, is displaying on a 2nd Excel sheet. How can I keep
the detail data on the same sheet as the master data? Is there something I
need to do in the Layout view in VS?
Thanks,
Rich
"Ricardo" wrote:
> open report project by using visual studio
> open report document (double click the report file - solution explorer)
> go to Data tab (Data / Layout / Preview)
> click dataset dropdown and choose <new dataset...>
> have fun
> "Rich" <Rich@.discussions.microsoft.com> escreveu na mensagem
> news:D8EE2D83-6A8B-49AF-B875-DF9191DAD51A@.microsoft.com...
> > Hello,
> >
> > I am just starting out with reporting services (I have a book which I have
> > not read yet). I have copied the basic part of a report to a new report
> > for
> > learning purposes. This contains a parameter which I included in the
> > query.
> > This works so far. But the original report also includes a table of
> > fields
> > that are based on a separate dataset. I am getting an error now. How do
> > I
> > add/imlement this secondary dataset? The error message says that this
> > dataset does not exist. I presume it means the dataset does not exist in
> > the
> > report because the tables in sql server exist. How do I create this
> > dataset?
> >
> > Thanks,
> > Rich
>
>

Friday, March 23, 2012

Problem Printing local report in Sql reporting services

I have a local report for which i am binding the Dataset dynamically. I am trying to print this report using a seperate button on the page. I saw in the forums saying that the reportviewer can be converted to an EMF file, bind this to an image control and can Print this image using Javascript.

Can anyone help me with the sample code to print local report from the reportviewer (it may be in any approach.)

Thanks in advance.

Sekhar TThis thread should help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790287&SiteID=1&mode=1&PageID=0

or at least get you on the right path.|||

Am sorry, my app is not windows, it is web based

I tried it but didn't help me a lot. My requirement is to allow clients to print reports from their browsers. I am using a single reportviewer control to display reports by dynamically binding different business objects and reports. One important thing to notice is that my connection string changes on user selection.

I am not really sure if there is another way using server mode to do this. I don't want to create reports to each database on the server. I am looking for a solution where i can use the same report for different databases.

|||When you say "One important thing to notice is that my connection string changes on user selection." you mean you're connecting to different reporting servers?

Here's my class. at the moment i'm confused as to why the 2nd device info has a start page of + 1 where the 1st one is set to 0. But it should work for you, assuming you know how to change the properties on each report server, aka changing the reportview.serverreport.reportpath, and the reportview.serverreport.reportserverurl. (these contain the same info as the connection string)

Hope this helps.

using System;
using System.IO;
using System.Data;
using System.Text;
using System.Drawing.Imaging;
using System.Drawing.Printing;
using System.Collections.Generic;
using System.Collections.Specialized;
using Microsoft.Reporting.WebForms;
using Microsoft.Reporting;
using System.Drawing;
public partial class _Default : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{
Demo demo = new Demo();
demo.Run();
}
}

public class Demo : IDisposable
{

private int m_currentPageIndex;
private IList<Stream> m_streams;

private void Export(ServerReport report)
{
string deviceInfo =
"<DeviceInfo>" +
"<OutputFormat>EMF</OutputFormat>" +
" <MarginTop>0.25in</MarginTop>" +
" <MarginLeft>0.25in</MarginLeft>" +
" <MarginRight>0.25in</MarginRight>" +
" <MarginBottom>0.25in</MarginBottom>" +
" <StartPage>0</StartPage>" +
"</DeviceInfo>";

m_streams = new List<Stream>();

string encoding;
string mimeType;
string extension;
Warning[] warnings;
string[] streamIDs = null;
Byte[][] pages = null;

//Create Byte array containing the rendered image. of the 1st page.
Byte[] firstPage = report.Render("Image", deviceInfo, out mimeType, out encoding, out extension, out streamIDs, out warnings);

m_streams.Add(new MemoryStream(firstPage));

// The total number of pages of the report is 1 + the streamIDs
int m_numberOfPages = streamIDs.Length + 1;
pages = new Byte[m_numberOfPages][];

// The first page was already rendered
pages[0] = firstPage;

for (int pageIndex = 1; pageIndex < m_numberOfPages; pageIndex++)
{
// Build device info based on start page
deviceInfo = String.Format(
"<DeviceInfo>" +
"<OutputFormat>EMF</OutputFormat>" +
" <MarginTop>0.25in</MarginTop>" +
" <MarginLeft>0.25in</MarginLeft>" +
" <MarginRight>0.25in</MarginRight>" +
" <MarginBottom>0.25in</MarginBottom>" +
" <StartPage>{0}</StartPage>" +
"</DeviceInfo>", pageIndex+1);

//Render the page to a byte array.
pages[pageIndex] = report.Render("Image", deviceInfo, out mimeType, out encoding, out extension, out streamIDs, out warnings);

//create a stream of the page's byte array
m_streams.Add(new MemoryStream(pages[pageIndex]));

//set the position of the stream to 0 to make sure when the stream is read
//it starts from the beginning.
m_streams[m_streams.Count-1].Position = 0;
}

m_currentPageIndex = 0;
}

private void PrintPage(object sender, PrintPageEventArgs ev)
{
//create a new metafile based on the page that we're trying to print.
Metafile pageImage = new Metafile(m_streams[m_currentPageIndex]);

//draw the an image deciding what to draw, and where to place it on the page.
ev.Graphics.DrawImage(pageImage, 0, 0);
//increment to the next page.
m_currentPageIndex++;

//decide if we've read our last stream.
ev.HasMorePages = (m_currentPageIndex < m_streams.Count);
}

private void Print()
{
//the name of the printer.
const string printerName = "Microsoft Office Document Image Writer";//;"\\\\NPSERVER\\MAIN"

//if there's nothing to print return
if (m_streams == null || m_streams.Count == 0)
return;

//create the printdocument object
PrintDocument printDoc = new PrintDocument();

//set the printername, deal w/ the printer being invalid
printDoc.PrinterSettings.PrinterName = printerName;
if (!printDoc.PrinterSettings.IsValid)
{
string msg = String.Format("Can't find printer \"{0}\".", printerName);
System.Diagnostics.Debug.WriteLine(msg);
return;
}
//attatch an event handler that will fire for each page that is printed.
printDoc.PrintPage += new PrintPageEventHandler(PrintPage);
//call print method, which get's the process going and ultimately calls the printpage method via the eventhandler.
printDoc.Print();
}

public void Run()
{
//create a new reportviewer object
ReportViewer rv = new ReportViewer();

//set the serverreport properties
rv.ServerReport.ReportPath = "/CRAMReports/ConcentrationReport";
rv.ServerReport.ReportServerUrl = new System.Uri("http://localhost/reportserver");

//configure params
List<ReportParameter> myParams = new List<ReportParameter>();
ReportParameter param = new ReportParameter("NAV", "0", false);
myParams.Add(param);
//set the params based on the param list.
rv.ServerReport.SetParameters(myParams);

//call the export method that builds the stream list.
Export(rv.ServerReport);

//call the print funciton
Print();

//call dispose which closes all the streams.
Dispose();
}

public void Dispose()
{
if (m_streams != null)
{
foreach (Stream stream in m_streams)
stream.Close();
}
}
}
|||

I am using reportviewer in local mode, where i am not using sql reporting server.

what i meant saying different connection strings is for different business objects from different databases in sql server 2005. we have different databases in sql server 2005.

Does your solution print from the browser? shall give it a try now

Thnks

|||

Hi, i have add that code

and everything is very good when i run it local through the visual Studio 2005

but when i upload the Application and run it thought the IIS the printing doent work and i dont know why.

Note: i am using local report.

i hope any one help coz i need it so much

|||

Hi, i have add that code

and everything is very good when i run it local through the visual Studio 2005

but when i upload the Application and run it thought the IIS the printing doent work and i dont know why.

Note: i am using local report.

|||What type of printers do you have? What are the print drivers?|||

hi,

i dont know if i understand your question well or not but iam using printer HP DeskJet 1220C from my client PC,

i hope i answer your question ,

i hope you help me soon because i need this part so much.

Maylo

|||

Guys,

There seem to be a couple of confusions here (I may be misreading the messages, but I'm going to give this a try anyway).

1. Sekhar T, even though you are using "local mode", not server mode, it's not like the actual databinding is happening on the client. It's happening on your web server. So to my mind the fact that you are dynamically binding is not all that relevant. You have the same opportunity in a "separate button" as you do when the page is normally posted back, to evaluate the user's needs and re-bind.

2. While it is true that report pages can be rendered to EMF, that doesn't make sense in your case. First, because you would be doing that on the web server (which has nothing to do with printing in the browser, agreed) and second, because I am not sure localmode reports can render to EMFs.

3. *HOWEVER* localmode reports can render to PDFs and of course PDFs can be easily printed by the user. While the user can already export to PDF from the reportviewer interface, you can in fact use a separate button for this purpose on the web page. See code below; I will include some binding code.

Code Snippet

Partial Class pages_Default

Inherits System.Web.UI.Page

' here's a sketch of how to send the PDF

' back from your separate button

Protected Sub Button1_Click( _

ByVal sender As Object, ByVal e As System.EventArgs) _

Handles Button1.Click

Dim buffer As Byte(), f As String, fs As System.IO.FileStream

f = System.IO.Path.GetTempFileName()

System.IO.Path.ChangeExtension(f, "PDF")

' there is probably a better way to set up the rendered PDF

' for redirecting to the Response output, but this one works.

' here is the binding bit. Revise to suit your dynamic situation.

' if you aren't really dynamically binding data against one

' loaded report, but rather changing

' reports to suit the user's needs, that will work too.

Dim ReportDataSourceX = _

New Microsoft.Reporting.WebForms.ReportDataSource()

ReportDataSourceX.Name = "DataSet1_Recipient"

ReportDataSourceX.Value = Me.SqlDataSource1

With Me.ReportViewer1.LocalReport

.DataSources.Clear()

.DataSources.Add(ReportDataSourceX)

buffer = .Render("PDF", Nothing, Nothing, Nothing, _

Nothing, Nothing, Nothing)

End With

fs = New System.IO.FileStream(f, System.IO.FileMode.Create)

fs.Write(buffer, 0, buffer.Length)

fs.Close()

fs.Dispose()

Response.ContentType = "Application/pdf"

Response.WriteFile(f)

Response.End()

System.IO.File.Delete(f)

End Sub

End Class

4. I am not so sure that it makes sense to have the separate button actually do the printing instead of bringing back the PDF and having the user print. If the user has to push a button anyway, shouldn't the user be able to make the normal printing decisions (such as what printer and features to use)? This is kind of the nature of a browser-based application.

5. If you really wanted javascript to print, the code would be a little different than what you see below, but not all that much. Instead of being a server-side control, I would use a "regular" button. The client side script would post to a URL that contained the required information for this user to get the report and data the way the user needed it. The client side script would receive back this data from its post and then probably save the file locally, finally sending the file out to the printer. However I don'tt think this is going to work in a whole bunch of security-limited cases. It requires too much control by script in the browser complex. Try what I"m suggesting in points 3 and 4, please, and think it over...

HTH,

>L<

Problem Printing local report in Sql reporting services

Can anyone help me with the sample code to print local report from the reportviewer (it may be in any approach.)

Thanks in advance.

Sekhar T
This thread should help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=790287&SiteID=1&mode=1&PageID=0

or at least get you on the right path.
|||