Easy Query

Hi,
This is a really simple query and for the life of me I cant remember how to
do it
Name Table
Name
Philip
Steve
Contact Table
Name Position Contact
Philip 1 01244
Philip 2 0777
Steve 2 01352
Steve 3 00000
Join the 2 tables based on name and get the contact number from the second
table based upon the lowest value of the position field,
so the select results would look like
Philip 01244
Steve 01352
Any help would be great, thanks PDUse MIN() agregate fumction along GROUP BY clause
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:902B8202-1189-4503-9498-B57BC7B1D451@.microsoft.com...
> Hi,
> This is a really simple query and for the life of me I cant remember how
> to
> do it
> Name Table
> Name
> Philip
> Steve
> Contact Table
> Name Position Contact
> Philip 1 01244
> Philip 2 0777
> Steve 2 01352
> Steve 3 00000
> Join the 2 tables based on name and get the contact number from the second
> table based upon the lowest value of the position field,
> so the select results would look like
> Philip 01244
> Steve 01352
> Any help would be great, thanks PD
>

Dynamically Positioning the table in the report layout

HI ,

Is it possible to dynamically position the table in the report.

i,e. I will have two table, first table will be displayed only if the query returns data, else it willl be hidden.

When the query does not returns any data, then the second table will be displayed( which will have some text displayed).

But my requirment is, both the table when displayed will be displayed from the first page itslef.

Something like this, First report will be vertically aligned to the top of the report. and the second table will be vertically alligned to the first table. So when the first table is hidden, then the second table will be rolled up while rendering the report

I have done it in other tool, but could not figure out in this tool.

We do something similar here . all of our reports have a small text box located at the top of the body section. This text box has a conditional visibility statement. If the data set returns no data this textbox will display a " no data returned" text string to the report.

All you need to do is add this statement to the visibility expression ( for the table or text box

=IIF(CountRows("DataSet")=0,False,True)

then some text to the table or text box explaining the lack of data.

="No data was returned for the chosen parameters."

hope this helps

|||

Thanks sir,

That was a good idea.

|||

FYI: There is also another way of doing this. Data regions (matrix, table, list, chart) and subreports have a so-called NoRows property. If set, this is the message to display in a textbox (which replaces the data region when no rows of data are available).
Note: the NoRows property is available in the VS properties window.

-- Robert

|||

You are right Robert,

But, the problem with this approach is that the whole table structure will be replaced with the text specified in that property.

some people would like to see the table structure as well , even if the report does not have any data.

Hope i am making some sense.

Dynamically changing position of Textbox

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,

Leks

What does the surrounding content in the layout look like? Do you have a "clear" region in which you could place a very large textbox, representing the all the page area in which the textbox could potentially be placed?

If so, you might be able to do this with padding. IOW, you would place this very large textbox in the layout and then you would pad the top and left to "push" the content of the text box into the customer's preferred position, at runtime.

This is possible because you can express the padding values as expressions. Think of the textbox's true top and left as an offset from 0,0, which you will subtract from the customer's input values (which will be parameters) to get the padded top and left at runtime.

HTH,

>L<

|||

Hi Lisa,

Thanks for your quick reply. But unfortunately my layout is not clear. It contains other data too that I need to position according to the input for them. For each item in the report we have to enter the position. I know this process is a bit tedious. but there is no other ways. They actualy need the report data such that it can be printed in many forms. Please reply as soon as possible.

Thank you dear,

Leks

|||

Hi Leks,

If you have to enter the position of each item in the report, then I think your best option is to edit the RDL on the fly, put your coordinates right in the report definition.

Are you comfortable with doing this? It may be quite straightward. Some things that would enter into it and might change what you have to do would include:

* -- What type of data region are you using (list/table) to contain the dynamic items?

* -- Are there additional items in the report that are *not* dynamically positioned, for example is this an order header you are trying to position so that it fits in a window envelope with a table of order lines that is relatively static?

>L<

|||

Hi Lisa,

Here all the data should change position dynamically. So Your solution shoud work. But I didnt get it. How we can change the rdl on the fly?

Thanking you,

Leks

|||

You can write a whole RDL on the fly if you want to. Look for an example called something like "generate RDL dynamically" on http://gotreportviewer.com/ and there is another example there that will show you how to LoadReportDefinition for a local report from a stream. If the report is a serverreport, you could use the SOAP API to upload the adjusted report definition instead.

In your case, I don't think that it will be necessary to write the whole RDL from scratch (although you will still have to load or upload your revised version). You can have a "template" RDL that you adjust, for each run or each customer, or as needed.

There are two ways to do an RDL template revision, the way I would do it and the way most developers would prefer to do it <g>. I prefer XSLT; in this case I might have a parameter in the XSLT for each positionable item in the report, or pass the entire set of adjustments in the form of a lookup document. If you prefer to load up the RDL in the DOM and select the textboxes as nodes to adjust each of them, this is pretty easy too.

Do you need more help than this, or is this what you're looking for?

>L<

|||

Hi Lisa,

Yes. This was what I was looking for. Now I know that using the XSLT we can position the textbox or any control dynamically.

Thank you so much Lisa,

Leks

|||

Great -- if you get stuck, holler. I am very willing to go the extra mile for other folks who like XSLT <s>.

>L<

Dynamically changing position of Textbox

Hi,

In my report, I've to print the address of the customer in a form which is in a pre-defined format. So while printing, the line number and position should be specified by the user. Is there any ways in SSRS to change the position of a textbox/any control dynamically by inputing the x and y coordinates? If there is a way please let me know that.

Thanks in Advance,

Leks

What does the surrounding content in the layout look like? Do you have a "clear" region in which you could place a very large textbox, representing the all the page area in which the textbox could potentially be placed?

If so, you might be able to do this with padding. IOW, you would place this very large textbox in the layout and then you would pad the top and left to "push" the content of the text box into the customer's preferred position, at runtime.

This is possible because you can express the padding values as expressions. Think of the textbox's true top and left as an offset from 0,0, which you will subtract from the customer's input values (which will be parameters) to get the padded top and left at runtime.

HTH,

>L<

|||

Hi Lisa,

Thanks for your quick reply. But unfortunately my layout is not clear. It contains other data too that I need to position according to the input for them. For each item in the report we have to enter the position. I know this process is a bit tedious. but there is no other ways. They actualy need the report data such that it can be printed in many forms. Please reply as soon as possible.

Thank you dear,

Leks

|||

Hi Leks,

If you have to enter the position of each item in the report, then I think your best option is to edit the RDL on the fly, put your coordinates right in the report definition.

Are you comfortable with doing this? It may be quite straightward. Some things that would enter into it and might change what you have to do would include:

* -- What type of data region are you using (list/table) to contain the dynamic items?

* -- Are there additional items in the report that are *not* dynamically positioned, for example is this an order header you are trying to position so that it fits in a window envelope with a table of order lines that is relatively static?

>L<

|||

Hi Lisa,

Here all the data should change position dynamically. So Your solution shoud work. But I didnt get it. How we can change the rdl on the fly?

Thanking you,

Leks

|||

You can write a whole RDL on the fly if you want to. Look for an example called something like "generate RDL dynamically" on http://gotreportviewer.com/ and there is another example there that will show you how to LoadReportDefinition for a local report from a stream. If the report is a serverreport, you could use the SOAP API to upload the adjusted report definition instead.

In your case, I don't think that it will be necessary to write the whole RDL from scratch (although you will still have to load or upload your revised version). You can have a "template" RDL that you adjust, for each run or each customer, or as needed.

There are two ways to do an RDL template revision, the way I would do it and the way most developers would prefer to do it <g>. I prefer XSLT; in this case I might have a parameter in the XSLT for each positionable item in the report, or pass the entire set of adjustments in the form of a lookup document. If you prefer to load up the RDL in the DOM and select the textboxes as nodes to adjust each of them, this is pretty easy too.

Do you need more help than this, or is this what you're looking for?

>L<

|||

Hi Lisa,

Yes. This was what I was looking for. Now I know that using the XSLT we can position the textbox or any control dynamically.

Thank you so much Lisa,

Leks

|||

Great -- if you get stuck, holler. I am very willing to go the extra mile for other folks who like XSLT <s>.

>L<