How can I request row #3 in a dataset?

I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, ElevationFROM [PointTable]WHERE (RouteID = 5)

But I cannot (or do not know how to) add a clause that says

AND (Num = 3)

So can someone show me how to request a specific row?

It's really easy, and the trick to it will serve you in good stead in many other situations.

When you issue a select statement, you are selecting "from" something. That something might be a table, or it might be a view.

But what IS a view? It's a query that's given a name, an alias if you will. When a view is used, the actual query statement it represents is substituted for the view's name in the sql statement as part of the parsing process.

How does that knowledge help?

Because you don't have to bother to give a view a name, you can just fill in a query for yourself. Just pop in inside a set of parentheses and supply an alias

So:

SELECT *

FROM

(

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, Elevation
FROM [PointTable]
WHERE (RouteID = 5)

) AS temporary_result_set

WHERE temporary_result_set.Num = 3

|||WOW! Thank you David, that did the trick. You're explaination just doubled my understanding on how T-SQL works and got my mind thinking of other things to try.

How can I read data from a dataset directly into a sql database table using IS ?

Hi !

I've got a series of datasets which I need to transfer directly into a set of database tables. I can't find any tools in the IS for doing this, except using a XML source, which means I have to use a temporary file, which I of course won't have.

What am I missing ? What is the way to do this ?

Any help appreciated !Where do these "datasets" reside?

-Jamie|||They reside in .net assemblies. The data comes from a system where we can generate .net "proxies", that is ordinary .net assemblies, where the data is delivered as typed datasets.

How can I print a field that is in the dataset on each page and show the table hearder on each p

How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."

How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.

Thanks.

DanYeung

Hello Dan,

For the table header, right click on the row and go to properties. Change the RepeatOnNewPage property to True.

For the data field, you can add a textbox in your report header and reference the field by the textbox name that it is located in. Like this:

=First(ReportItems!TextBox9.Value)

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your help. I took care the table header.

For the data field, I added a textbox in the body and the value is =Fields!vchrProjectName.Value . I made the textbox invisible. I also added a textbox in the page header and the value is =First(ReportItems!TextBox9.Value) as you suggested. It printed on the first page only. I think the textbox in the body has no value on the second page, so the textbox in the page header got noghting. What did I missed? Do I have put something in the RepeatWith property?

Thanks.

DanYeung

|||

I fixed the page header problem by adding one more table header row before the table header. Added a textbox on the row and used =Fields!ProjectName.value in the value property. Thanks for the idea.

DanYeung

|||

I have the same issue and I tried your suggestion by adding the text box in the body and reference that in the page header. Only the first page work, not others. Any other suggestions or I did something wrong?

Regards

Eric

|||

You have to add a table in the body. Select the table header row. Change the RepeatOnNewPage property to true. Change the table header background color to while. Add a textbox on the table header and add the field in the textbox. Adding the textbox in the body prints only once. You need the table.

DanYeung

|||

Forgive me, I don't understant the second last statement. "Adding the textbox in the body prints only once". Can you explain a little bit more? Also, why to change background color to white?

Best Regards,

Eric

|||

Adding a textbox directly in the body won't print on the second page. If you want to repeat on every page, you have to add a table. The default background color for table header is grey. You don't have to change to white if you prefer grey.

DanYeung

|||

Hey I couldn't find an easy fix so what I would up doing was creating a report parameter set it as internal so it did not prompt when the report was being created and then for available and default values i selected it as a "from query" and pulled the value from there (with a linked dataset). Works like a charm for me in preview and exporting to PDFs. Here is the XML view of the report I created pulling fields, EmpName, MgrName and EmpPosition as EmpName, MgrName, and Position. The name of my dataset is dsGoals.

Code Snippet

<ReportParameter Name="EmpName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="MgrName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="Position">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

How can I print a field that is in the dataset on each page and show the table hearder on each p

How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."

How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.

Thanks.

DanYeung

Hello Dan,

For the table header, right click on the row and go to properties. Change the RepeatOnNewPage property to True.

For the data field, you can add a textbox in your report header and reference the field by the textbox name that it is located in. Like this:

=First(ReportItems!TextBox9.Value)

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your help. I took care the table header.

For the data field, I added a textbox in the body and the value is =Fields!vchrProjectName.Value . I made the textbox invisible. I also added a textbox in the page header and the value is =First(ReportItems!TextBox9.Value) as you suggested. It printed on the first page only. I think the textbox in the body has no value on the second page, so the textbox in the page header got noghting. What did I missed? Do I have put something in the RepeatWith property?

Thanks.

DanYeung

|||

I fixed the page header problem by adding one more table header row before the table header. Added a textbox on the row and used =Fields!ProjectName.value in the value property. Thanks for the idea.

DanYeung

|||

I have the same issue and I tried your suggestion by adding the text box in the body and reference that in the page header. Only the first page work, not others. Any other suggestions or I did something wrong?

Regards

Eric

|||

You have to add a table in the body. Select the table header row. Change the RepeatOnNewPage property to true. Change the table header background color to while. Add a textbox on the table header and add the field in the textbox. Adding the textbox in the body prints only once. You need the table.

DanYeung

|||

Forgive me, I don't understant the second last statement. "Adding the textbox in the body prints only once". Can you explain a little bit more? Also, why to change background color to white?

Best Regards,

Eric

|||

Adding a textbox directly in the body won't print on the second page. If you want to repeat on every page, you have to add a table. The default background color for table header is grey. You don't have to change to white if you prefer grey.

DanYeung

|||

Hey I couldn't find an easy fix so what I would up doing was creating a report parameter set it as internal so it did not prompt when the report was being created and then for available and default values i selected it as a "from query" and pulled the value from there (with a linked dataset). Works like a charm for me in preview and exporting to PDFs. Here is the XML view of the report I created pulling fields, EmpName, MgrName and EmpPosition as EmpName, MgrName, and Position. The name of my dataset is dsGoals.

Code Snippet

<ReportParameter Name="EmpName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="MgrName">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>MgrName</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<ReportParameter Name="Position">

<DataType>String</DataType>

<DefaultValue>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<ValidValues>

<DataSetReference>

<DataSetName>dsGoals</DataSetName>

<ValueField>EmpPosition</ValueField>

</DataSetReference>

</ValidValues>

</ReportParameter>

How can I place a value from my dataset into the page header?

My report has a parameter of "End_Date" which is used to create my dataset from a stored procedure. In the dataset, a column called "Begin_Date" is returned with various other information used in the report creation.

Currently, the parameter "End_Date" is used in the page header as part of my report title. My user's whould also like to see the "Begin_Date" value in the page header.

Both End and Begin dates are unique values occuring once per report run. How do I get the "Begin_Date" contained in my dataset as a value to be displayed in the page header title?

many thanks

The current method is to add a hidden text box to the body with that value. Then reference that text box value in the page header.

In future releases we are looking at adding field references to the other page sections.

|||

Thank you Brad for the response. Please excuse my lack of understanding. In the Page header, any time I reference a field in the body I get a message basically saying "Fields can not be used in any page headers or footers".

I have tried placing a hidden text box in the body with the value and referencing it in the page header, but with no success. I continue to receive the "Fields can not be used in any page headers or footers" error message.

Have I misunderstood your response? thanks

|||

you need to give reference like these for the header textbox

=ReportItems!txtReportHeader.Value

where txtReportHeader is the text box in the body, which contains the value of the dataset field

|||

Thanks rakam, it worked like a charm....

I didn't realize you could reference ReportItems from headers and footers. Appreciate the information.

How can I place a value from a dataset into the page header?

Hello,

I know that I can do it through:

1. A hidden parameter

2. Report Items

The first method is not good for my report because I have another application that renders the report to a PDF.

And the second method I cannot refer more than one report item and I need to refer two items.

Is there any other method that I can use?

Thanks,

Abdel

Abdel,

I believe that those are your only options, I use the parameter trick and I'm able to export successfully to PDF. Can you state why this is failing?

Ham

how can i pass dataset to stored procedure

hi
i have customized dataset. i want to insert into database. how can i insert
on a single round trip. how can i pass dataset to stored procedure'examnotes (sdfsdf@.discussions.microsoft.com) writes:
> i have customized dataset. i want to insert into database. how can i
> insert on a single round trip. how can i pass dataset to stored
> procedure'
Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes but i have to first save the xml file. mine is web application so how ca
n
i serialize file.
is there any other alternative?
"Erland Sommarskog" wrote:

> examnotes (sdfsdf@.discussions.microsoft.com) writes:
> Make an XML string of it, and then use OPENXML in SQL Server to unpack it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Hi,
What I think Erland was saying to create XML object ( e.g MSXML.DomDocument)
in your application and not file. This XML can be created from dataset, and
while calling SP pass the XML string as parameter ( Varchar lenght is the
constraint here).
Inside SP we can access the Node values from XML string using OpenXML.
Regards,
Vishal Khajuria
"sdfsdf" wrote:
> yes but i have to first save the xml file. mine is web application so how
can
> i serialize file.
> is there any other alternative?
>
> "Erland Sommarskog" wrote:
>|||Vishal Khajuria (Vishal Khajuria@.discussions.microsoft.com) writes:
> What I think Erland was saying to create XML object ( e.g
> MSXML.DomDocument) in your application and not file.
Yes. (Except that I don't really know how you build the XML string.
I only know that it's doable.)

> This XML can be created from dataset, and while calling SP pass the XML
> string as parameter ( Varchar lenght is the constraint here).
Pass the XML string as ntext, and length should not be a problem. (Unless
you exceed the 2GB limit for large objects.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

How can I Pass a DataSet to a DPE

Hi,
I have a DataSet which I want to pass to the DPE,
how i pass the DataSet as a parameter to the DPE
Please Help!
Thanks
Girish KumarGirish,
You can serialize the dataset to XML and pass it a parameter. As a side
note, my custom DPE does exactly this.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> Hi,
> I have a DataSet which I want to pass to the DPE,
> how i pass the DataSet as a parameter to the DPE
> Please Help!
> Thanks
> Girish Kumar|||Hi Teo
I Have a DataSet with me which I want to pass directly to the DPE without
any XML serialization I ve seen ur sample application where u pass the xml
file as report parameter in the browser,
But my requirement is not that I want to pass the Dataset to DPE
programmatically at the run time how can i do that
Please Help!!
Regards,
Girish
"Teo Lachev [MVP]" wrote:
> Girish,
> You can serialize the dataset to XML and pass it a parameter. As a side
> note, my custom DPE does exactly this.
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > Hi,
> >
> > I have a DataSet which I want to pass to the DPE,
> > how i pass the DataSet as a parameter to the DPE
> >
> > Please Help!
> >
> > Thanks
> > Girish Kumar
>
>|||Girish,
My extension supports reporting off datasets which are persisted to files.
In this case, instead of passing the serialized copy, you pass the file path
as a report parameter.
Did this address your question?
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> Hi Teo
> I Have a DataSet with me which I want to pass directly to the DPE
without
> any XML serialization I ve seen ur sample application where u pass the xml
> file as report parameter in the browser,
> But my requirement is not that I want to pass the Dataset to DPE
> programmatically at the run time how can i do that
> Please Help!!
> Regards,
> Girish
> "Teo Lachev [MVP]" wrote:
> > Girish,
> >
> > You can serialize the dataset to XML and pass it a parameter. As a side
> > note, my custom DPE does exactly this.
> >
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > Hi,
> > >
> > > I have a DataSet which I want to pass to the DPE,
> > > how i pass the DataSet as a parameter to the DPE
> > >
> > > Please Help!
> > >
> > > Thanks
> > > Girish Kumar
> >
> >
> >|||Hi Teo,
I need a custom DPE which can take input a Dataset I have tried using your
sample but it needs me to again create an xml and xsd file update the files
in initialize component method.
But i in turn have a Dataset got from an external application using
webservices i want to pass that dataset to this dpe and run my report.
I think you got my scenario.
Please Help!!!
Regards
Girish (INDIA)
"Teo Lachev [MVP]" wrote:
> Girish,
> My extension supports reporting off datasets which are persisted to files.
> In this case, instead of passing the serialized copy, you pass the file path
> as a report parameter.
> Did this address your question?
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> > Hi Teo
> >
> > I Have a DataSet with me which I want to pass directly to the DPE
> without
> > any XML serialization I ve seen ur sample application where u pass the xml
> > file as report parameter in the browser,
> >
> > But my requirement is not that I want to pass the Dataset to DPE
> > programmatically at the run time how can i do that
> >
> > Please Help!!
> >
> > Regards,
> > Girish
> >
> > "Teo Lachev [MVP]" wrote:
> >
> > > Girish,
> > >
> > > You can serialize the dataset to XML and pass it a parameter. As a side
> > > note, my custom DPE does exactly this.
> > >
> > >
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a DataSet which I want to pass to the DPE,
> > > > how i pass the DataSet as a parameter to the DPE
> > > >
> > > > Please Help!
> > > >
> > > > Thanks
> > > > Girish Kumar
> > >
> > >
> > >
>
>|||Hi Teo,
I need a custom DPE which can take input a Dataset I have tried using your
sample but it needs me to again create an xml and xsd file update the files
in initialize component method.
But i in turn have a Dataset got from an external application using
webservices i want to pass that dataset to this dpe and run my report there
is no point of storing a file on my webserver even temporarly
I think you got my scenario.
Please Help!!!
Regards
Girish (INDIA)
"Teo Lachev [MVP]" wrote:
> Girish,
> My extension supports reporting off datasets which are persisted to files.
> In this case, instead of passing the serialized copy, you pass the file path
> as a report parameter.
> Did this address your question?
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> > Hi Teo
> >
> > I Have a DataSet with me which I want to pass directly to the DPE
> without
> > any XML serialization I ve seen ur sample application where u pass the xml
> > file as report parameter in the browser,
> >
> > But my requirement is not that I want to pass the Dataset to DPE
> > programmatically at the run time how can i do that
> >
> > Please Help!!
> >
> > Regards,
> > Girish
> >
> > "Teo Lachev [MVP]" wrote:
> >
> > > Girish,
> > >
> > > You can serialize the dataset to XML and pass it a parameter. As a side
> > > note, my custom DPE does exactly this.
> > >
> > >
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a DataSet which I want to pass to the DPE,
> > > > how i pass the DataSet as a parameter to the DPE
> > > >
> > > > Please Help!
> > > >
> > > > Thanks
> > > > Girish Kumar
> > >
> > >
> > >
>
>|||No, I don't. You are saying that you need to pass the dataset as a
parameter, then you are saying that you don't want to pass it as a
parameter... I am confused. Can you explain in steps what are you trying to
do?
My extension supports both serialized datasets and datasets saved as files
and, no, you don't have to create a xsd file. A schema file just makes it
easier for the report author to lay out the report.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
news:DE5CE16C-2A19-4C63-B2D3-8823FEF71F1C@.microsoft.com...
> Hi Teo,
> I need a custom DPE which can take input a Dataset I have tried using
your
> sample but it needs me to again create an xml and xsd file update the
files
> in initialize component method.
> But i in turn have a Dataset got from an external application using
> webservices i want to pass that dataset to this dpe and run my report
there
> is no point of storing a file on my webserver even temporarly
> I think you got my scenario.
> Please Help!!!
> Regards
> Girish (INDIA)
>
> "Teo Lachev [MVP]" wrote:
> > Girish,
> >
> > My extension supports reporting off datasets which are persisted to
files.
> > In this case, instead of passing the serialized copy, you pass the file
path
> > as a report parameter.
> >
> > Did this address your question?
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in message
> > news:E8DDDEB5-B9EC-4CAC-8A5F-5FDC9A182EAF@.microsoft.com...
> > > Hi Teo
> > >
> > > I Have a DataSet with me which I want to pass directly to the DPE
> > without
> > > any XML serialization I ve seen ur sample application where u pass the
xml
> > > file as report parameter in the browser,
> > >
> > > But my requirement is not that I want to pass the Dataset to DPE
> > > programmatically at the run time how can i do that
> > >
> > > Please Help!!
> > >
> > > Regards,
> > > Girish
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > > > Girish,
> > > >
> > > > You can serialize the dataset to XML and pass it a parameter. As a
side
> > > > note, my custom DPE does exactly this.
> > > >
> > > >
> >
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > Publisher website: http://www.manning.com/lachev
> > > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > > Home page and blog: http://www.prologika.com/
> > > > ---
> > > >
> > > > "Girish Kumar" <GirishKumar@.discussions.microsoft.com> wrote in
message
> > > > news:0F9E03F2-58FD-4018-A950-48CE6131E073@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I have a DataSet which I want to pass to the DPE,
> > > > > how i pass the DataSet as a parameter to the DPE
> > > > >
> > > > > Please Help!
> > > > >
> > > > > Thanks
> > > > > Girish Kumar
> > > >
> > > >
> > > >
> >
> >
> >

How Can I obtain the Top 3 of a dataset

If I have say 20 records of sales employees, for example, how can I get the top 3 locations for $$$sales for EACH employee? Each employee can have multiple locations where they have sold(let's say up to 50). I only want the names of the top 3 locations. The closest I can get is filtering the dataset by a HAVING clause > a dollar amount but this still gives me between 3 - 12 records for each plus I have to literally enter each salesperson's number as it stands now. Is this a loop or a cursor? Thanks.

ddaveOnce you have your SQL set up to return the sales dollars for all of the locations, just add TOP 3 before the select:

SELECT TOP 3 Sum(Dollars) FROM MyTable;


Note that you can also SELECT TOP n PERCENT.|||General solution to your problem:

The TOP N clause in TSQL is useful, but is limited by the fact that it only applies to the entire recordset, and that it will not accept a variable as a parameter.

This method will return any requested number of records for each group of one or more column values in a dataset. It uses a type of join called a Theta join, where the values in two datasets are compared, but do not necessarily have to be equal:


declare @.N int
set @.N = 5 --The number of records to return for each grouping.
select YourTable.YourColumns
from YourTable
inner join YourTable ThetaTable
on YourTable.GroupColumns = ThetaTable.GroupColumns
and YourTable.SortColumn <= ThetaTable.SortColumn
group by YourTable.YourColumns
having count(*) <= @.N|||The TOP function does not work as blindman indicates as it gives me only the top 3 of 120+ records, not the top 3 for EACH person. I will try the Theta join. Thanks to both for replying.

ddave|||blindman, your queries are starting to look good :)

but the join should be outdented

use foo and bar if more than one column is intended (YourColumns alone does not show comma syntax)

and shorter aliases make it easier to read ;)select T.foo, T.bar
from YourTable as T
inner join YourTable as Th
on T.GroupColumns = Th.GroupColumns
and T.SortColumn <= Th.SortColumn
group by T.foo, T.bar
having count(*) <= n|||blindman, your queries are starting to look good :)

Looks can be deceiving...it doesn't work

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(SiteId int, EmpId int, Sales money)
GO

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 1, 1, 10.00 UNION ALL
SELECT 2, 1, 15.00 UNION ALL
SELECT 3, 1, 20.00 UNION ALL
SELECT 4, 1, 50.00 UNION ALL
SELECT 5, 1, 10.00 UNION ALL
SELECT 6, 1, 5.00 UNION ALL
SELECT 1, 2, 100.00 UNION ALL
SELECT 2, 2, 1500.00 UNION ALL
SELECT 3, 2, 2000.00 UNION ALL
SELECT 4, 2, 5000.00 UNION ALL
SELECT 5, 2, 1000.00 UNION ALL
SELECT 6, 2, 500.00 UNION ALL
SELECT 1, 3, 1.00 UNION ALL
SELECT 2, 3, 1.50 UNION ALL
SELECT 3, 3, 2.00 UNION ALL
SELECT 4, 3, 5.00 UNION ALL
SELECT 5, 3, 1.00 UNION ALL
SELECT 6, 3, .50
GO

DECLARE @.N int
SET @.N = 3 --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId
FROM myTable99 a
JOIN myTable99 b
ON a.SiteId = b.SiteId
AND a.EmpId = b.EmpId
AND a.Sales < = b.Sales
GROUP BY a.SiteId, a.EmpId
HAVING COUNT(*) <= @.N
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

I know I've seen this work somehow though|||At least to me, it is a lot more intuitively obvious to do this via a sub-query, something like: DECLARE @.N int
SET @.N = 3 --The number of records to return for each grouping.

SELECT a.SiteId, a.EmpId, a.Sales
FROM myTable99 a
WHERE (SELECT Count(*)
FROM myTable99 b
WHERE b.EmpId = a.EmpId
AND a.Sales <= b.Sales) <= @.N
ORDER BY a.EmpID, a.Sales DESC, a.SiteIDThe problem with any set-based solution to this kind of problem is that it does not deal well with "ties" in the data... They make your results a bit "funky", but I don't know any reliable way to resolve that using set based logic.

-PatP|||I know I've seen this work somehow thoughyeah, it's tricky

here's what you were searching for -- select a.EmpId
, a.SiteId
, a.sales
from myTable99 a
inner
join myTable99 b
on a.EmpId = b.EmpId
and a.Sales <= b.Sales
group
by a.EmpId
, a.SiteId
, a.sales
having count(*) <= 3
order
by a.EmpId
, a.sales descwhich produces the following (correct) results:1 4 50.0000
1 3 20.0000
1 2 15.0000

2 4 5000.0000
2 3 2000.0000
2 2 1500.0000

3 4 5.0000
3 3 2.0000
3 2 1.5000pat, i like the subquery method too, i guess i just got used to the join solution after so many times showing pre-4.1 mysql people how to do it

:) :)|||I indents 'em as I wants 'em, thank you! :)|||Hey thanks guys...

And yes Pat, I agree it may be more intuitive...but have a look at the plans...it looks like Rudy's Join is more effecient...even with the group by.

I would not have guessed this. Anyone care if I blog this? With appropriate references of course.

It's just one of things I know can be done...and I forget how to contruct it.

Anyway, here's the plans|||blog away

:cool: :cool: :cool:|||OK, You've all been Blogged (http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx)|||OK, You've all been Blogged (http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx)ta very much

and usually i just ignore your frequent typos, but in this case, i must insist that you correct the spelling of my surname

thanks in advance|||mia culpa...done|||mia culpa...donethanks

and now, would you mind changing the link so that it points to the correct person

:) :)|||Done...it's been a rough week...they've got me doing project plans...aaaahhhhhhhhh|||"The problem with any set-based solution to this kind of problem is that it does not deal well with "ties" in the data... They make your results a bit "funky", but I don't know any reliable way to resolve that using set based logic."

That is the catching point. I have 20 people, for each of whom I wish to get the "TOP 3" records. When I have a "tie" it fails, giving me only 2 of the 3 desired records.

I inserted a couple of extra records to the table given above by:

INSERT INTO myTable99(SiteId, EmpId, Sales)
SELECT 2, 2, 15.00
SELECT 2, 2, 1500.00

The 1500.00 will create a tie with another record, already having a value of 1500.00.

Here are all the records:

select *
from myTable99

Results:
1 1 10.0000
2 1 15.0000
3 1 20.0000
4 1 50.0000
5 1 10.0000
6 1 5.0000
1 2 100.0000
2 2 1500.0000
3 2 2000.0000
4 2 5000.0000
5 2 1000.0000
6 2 500.0000
1 3 1.0000
2 3 1.5000
3 3 2.0000
4 3 5.0000
5 3 1.0000
6 3 .5000
2 2 1500.0000
2 2 15.0000

When I run your query:

select a.EmpId
, a.SiteId
, a.sales
from myTable99 a
inner
join myTable99 b
on a.EmpId = b.EmpId
and a.Sales <= b.Sales
group
by a.EmpId
, a.SiteId
, a.sales
having count(*) <= 3
order
by a.EmpId
, a.sales desc

My results are here:

1 4 50.0000
1 3 20.0000
1 2 15.0000
2 4 5000.0000
2 3 2000.0000
3 4 5.0000
3 3 2.0000
3 2 1.5000

I need Employee 2 to show at least one of his two 1500.00 records. Any way to do this?

ddave|||This can still be done with a set-based solution:

select a.EmpId,
a.SiteId,
a.sales
from myTable99 a
inner join myTable99 b
on a.EmpId = b.EmpId
and (a.Sales < b.Sales
or a.Sales = b.Sales and a.EmpID < b.EmpID)
group by a.EmpId,
a.SiteId,
a.sales
having count(*) <= 3
order by a.EmpId,
a.sales desc

Output:

EmpId SiteId sales
---- ---- -------
1 3 20.0000
1 2 15.0000
1 1 10.0000
1 5 10.0000
2 3 2000.0000
2 2 1500.0000
2 5 1000.0000
3 3 2.0000
3 2 1.5000
3 1 1.0000
3 5 1.0000|||No kidding...

SELECT a.EmpId, a.SiteId, a.sales
FROM (SELECT DISTINCT EmpId, SiteId, sales FROM myTable99) a
INNER JOIN (SELECT DISTINCT EmpId, SiteId, sales FROM myTable99) b
ON a.EmpId = b.EmpId
AND a.Sales <= b.Sales
GROUP BY a.EmpId, a.SiteId, a.sales
HAVING COUNT(*) <= @.N
ORDER BY a.EmpId, a.sales desc
GO|||without getting into the actual sql, the problem of ties isn't really a problem of sql, it's more of a problem of semantics

blindman's last example is perfect --

3 3 2.0000
3 2 1.5000
3 1 1.0000
3 5 1.0000

as far as i'm concerned, a tie across the last place requires that all rows with that value be included

let's say we had 25 people in a classroom, and we wanted the top 3 students based on marks

joe has a gpa of 3.90, mary has a gpa of 3.85, and all twenty-three others have exactly the same gpa, 3.80

who ya gonna exclude?

How can I have a variable number of parameter values in a dataset?

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:

For iCount = 0 To UBound(sArray)
strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "
Next

Can I do this is a dataset method? How?

If I can't, what are my options?

Diane

make use of temp table or table variable

declare @.word table

(

word varchar(100)

)

select *

from sometable t inner join @.word w

on t.Description like '%' + w.word + '%'

|||

Hi,

From the question you mentioned, do you want to make a query with multiple input variables? If so, I just want to know how to split them in your solution?

Actually, the user input the keywords in the textbox and they use space on the keyboard to split each word. And when we recieve the request from the user,

we may use split method to separate each words into array.

string s = "keyword1 keyword2 keyword3";
string[] myar = s.Split(' ');

for (int i = 0; i < myar.Length; i++) {

strSQL = strSQL & "Description LIKE '%" & myar(i) & "%' OR "
}

After the sql statement is created, you may put it into the SqlDataAdapter and fill into a DataSet. So you can get the query result which matches the multiple keywords.

SqlDataAdapter myadpt = new SqlDataAdapter(strSQL, myconn);
DataSet myds = new DataSet();
myadpt.Fill(myds);

If this does not answer your question, please feel free to reply. Thank you!

|||

Hi,

You can search more efficiently using 'Contains' in SQL server. For this you need to enable Full-Text search in sql server for a field.

Contains(FieldName, '+''''+@.GroupKeyWord+''''+')'

@.GroupKeyWord is the keyword you can directly pass, i.e. the user input string. See SQL Server help for more details. It is really interesting. If you don't want to use this feature then you can go for split. But split needs a delimiter string, i.e. a single space or a particular character. The resultant array can be looped from lower bound to upper bound to create Sql statement.

The advantage of using contains is its flexibility. User can create Boolean search himself and you just have to pass the value user entered into the textbox directly to the sql statement.

Don't forget to mark this as Answer if this post helps you

|||

KH, you have me totally confused <grin>!

Michael, I'm not having a problem spliting the variables. Say I have a table with fields name, company, address, description. I know how to use a parameter in a strongly typed dataset, say to search for Name=@.name in a method that expects a name parameter. What I need is a method that returns all records where the description field contains one or more of the words the user entered. I can build the select string, but I don't know how to implement this in a dataset method.

Hamlin, this would be optimal. Unfortunately, full text search isn't enabled, and I can't get it enabled. I asked my host, and they won't permit it.

I'm wondering if I should maybe use an SqlDataSource instead of the dataset?

Diane

|||

Hi Mainship,

Sorry for my misunderstanding. Now I know that your problem is focused on how to implement the select string in a dataset method cause you don't know the number of parameter values.

Actually you can use DataSet.TableName.Select(SelectString) Method instead of inputing the select string in the TableAdapter configuration wizard. For example:

northwindDataSet.Products.Select(string);

For more details about DataTable.Select Method ,pls check:http://msdn2.microsoft.com/en-us/library/system.data.datatable.select(VS.80).aspx

If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Thank you!

Diane

how can i give customize dataset to sql report

hi
i am new to sql reporting service, i am using for financial reporting,
in one of my requirement i have to calculate how much year customer's fund
will last and i have to show each year's balance.
in crystal report i get directly assign dataset, but how can i
assign dataset in reporting service, plz so urgentIn SSRS, you can assign an application dataset to server-based report only
if you use a custom dataset extension. In your case, I will gravitate toward
performing the data manipulation at the data source level, e.g. inside a
stored procedure. If you use SQL Server 2005 as a data source, consider
using a CLR stored procedure.
I demonstrate how this could be done in this code sample
(http://www.prologika.com/downloads/Pass/). The report invokes a CLR stored
procedure that uses data mining for sales forecasting.
As a side note, for financial reporting, I strongly suggest you evaluate
Analysis Services especially if you need to support account charts where
values substract or add (income, expenses, etc). Otherwise, you may find
yourself reinventing the wheel. I know from personal experience.
HTH
--
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Lovenish" <Lovenish@.discussions.microsoft.com> wrote in message
news:CE811BDF-1C05-4C34-9853-166E60A39D65@.microsoft.com...
> hi
> i am new to sql reporting service, i am using for financial reporting,
> in one of my requirement i have to calculate how much year customer's fund
> will last and i have to show each year's balance.
> in crystal report i get directly assign dataset, but how can i
> assign dataset in reporting service, plz so urgent|||Thx Teo Lachev,
but the code you have send is i think in visual studio 2005, i am
using 2003. so can u help me further,
u were talking about customize dataset extension but i cant get how to use
this so plz plz help me