3 tables on one report

Dear all
I want to display three tables on one report. This works fine as long I
place them on the same horizontal line (3 columns). If I use 2 columns (first
column table 1, second column tables 2 and 3) I have the problem, that table
3 starts where table 1 ends. Because table 1 is longer as table 2 I have a
lot of empty space between table 2 and 3.
How can I change this?
Thanks for your answer,
MarcWell, I have a workaround: I use a subreport.
Regards, Marc|||Marc,
I am not sure what you are trying to do, but I have several reports that
need multiple tables on one report. I have had to put them within nested
list boxes at times to get a particular order of the tables to appear (since
otherwise then tend to follow one after the other and I wanted them
interlaced) - In other cases I have used rectangles to enforce placement
within a page. There are other ways to handle without a subreport. If you
give a better idea of what you want then maybe I can suggest something.
Thanks
"Marc" wrote:
> Well, I have a workaround: I use a subreport.
> Regards, Marc|||Hi MJT
Thanks for your answer. Well I'm quite new to SQL Server 2005 Reporting
Services so I struggled with placing tables on a report and arranging them as
the requirement says. Like you proposed I useed the rectangle - this works
very well! What I did not understand is the List component. When I place
several tables inside a List, this component requires a dataset. So I guess
the List is used for other purposes.
Best regards,
Marc|||Hi Marc,
A list data region repeats with each group or row in the dataset. A list
can be used for free-form reports or in conjunction with other data regions.
You could refer the following article in the SQL Books Online:
Working with List Data Regions
http://msdn2.microsoft.com/en-us/library/ms159121(d=ide).aspx
Hope this will be helpful to you to understand the List Control. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

3 tables join

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.

For eg.

TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}

the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be

id recordType column1

1 TableB value of TableB column1
2 TableC value of TableC column1

Please help.

Cheers
NickOn Nov 8, 3:29 pm, Nick <nachiket.shirwal...@.gmail.comwrote:

Quote:

Originally Posted by

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.
>
For eg.
>
TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}
>
the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be
>
id recordType column1
>
1 TableB value of TableB column1
2 TableC value of TableC column1
>
Please help.
>
Cheers
Nick

Hi Nick,

Try:
SELECT a.id, a.recordType, CASE WHEN b.column1 IS NULL THEN c.column1
ELSE b.column1 END AS column1
FROM TableA a
LEFT OUTER JOIN TableB b
ON b.id = a.relatedkey
AND a.recordType = 'TableB'
LEFT OUTER JOIN TableC c
ON c.id = a.relatedkey
AND a.recordType = 'TableC'

Good luck!
J|||On Thu, 08 Nov 2007 07:29:06 -0800, Nick wrote:

Quote:

Originally Posted by

>I have have 3 tables TableA, TableB and TableC. TableA holds the keys
>to TableB and TableC. I need a query which will display the details
>from TableB and TableC depending on the key in TableA.
>
>For eg.
>
>TableA - columns {id, relatedkey, recordType} ===recordType will hold
>values like TableB or TableC
>TableB - columns{id, column1}
>TableC - columns{id, column1}
>
>the query should match the related key to the id of TableA or table B
>based on recordType and show the column1 value with the TabelA id so
>output for this should be
>
>
>id recordType column1
>
>1 TableB value of TableB column1
>2 TableC value of TableC column1
>
>Please help.

Hi Nick,

The solution jhofmeyr posted will work for you. But I think you should
question your design. If TableB and TableC are actually the same thing,
they should be a single table. And if they are different things, then
TableA should have two referencing columns plus a CHECK constraint to
ensure that mey not both be NOT NULL.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

24-hour time format

I need to display a datetime in the following format:
31-10-2006 23:15:05 (day, month year with 24-hour time format).
I have tried this:
=Format(Fields!Date_Logged.Value,"dd-MM-yyyy hh:mm:ss")
but it shows the hour as 8:00 for AM and 8:00 for PM. I do NOT want to use
AM and PM (I know that would be 'tt' at the end), because I live in Denmark,
where 24-hours is used. How do I get the 24-hour hours to show?
Thanks in advance for any tips.You are very close. Just put the following in the format code exactly as I
have here:
MM/dd/yy HH:mm:ss
Also, although you can use the expression like you have done below, you
don't need to. Just put the expression code is, no = sign, no quotes.
Same thing for formatting numbers. I use this quite a bit
#,##0
The canned formats don't have this which I prefer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogi bear" <yogi bear@.discussions.microsoft.com> wrote in message
news:DC1A6B4A-00FB-4617-9098-28BB2830E957@.microsoft.com...
>I need to display a datetime in the following format:
> 31-10-2006 23:15:05 (day, month year with 24-hour time format).
> I have tried this:
> =Format(Fields!Date_Logged.Value,"dd-MM-yyyy hh:mm:ss")
> but it shows the hour as 8:00 for AM and 8:00 for PM. I do NOT want to use
> AM and PM (I know that would be 'tt' at the end), because I live in
> Denmark,
> where 24-hours is used. How do I get the 24-hour hours to show?
> Thanks in advance for any tips.
>

2005: displaying multi-select parameter values in a text box

I am using Reporting Services 2005. My parameter is a multi-select one. I
want to display what parameters the user chose when they run the report so
others know what data the report was filtered by. When I put the below text
string in a text box, I get an error. When I put the index value after the
string it shows that item if there is one for that index #, and an error if
there isn't. If they chose 5 items for that parameter, I want all 5 to show
up automatically and not have to put the index number for each one to display
it.
=Parameters.pState.value (this gives a query error)
=Parameters.pState.value(3) (this displays the value of the 4th parameter
value they chose. If they didn't choose 4 parameters, then it gives an array
out of bounds error)
If they chose 4 items, I want a piece of code that will automatically show
all 4 (e.g. AZ, NM, NY, FL)
thanks in advance!If you change a report parameter to be multi value, the .Value property will
return an object[] rather than an object. Hence you can no longer e.g. write
expressions like =Parameters!MVP1.Value.ToString().
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
Hope this helps,
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:2BE1BECC-8CDC-4C0A-9735-0CB291BDD301@.microsoft.com...
>I am using Reporting Services 2005. My parameter is a multi-select one. I
> want to display what parameters the user chose when they run the report so
> others know what data the report was filtered by. When I put the below
> text
> string in a text box, I get an error. When I put the index value after
> the
> string it shows that item if there is one for that index #, and an error
> if
> there isn't. If they chose 5 items for that parameter, I want all 5 to
> show
> up automatically and not have to put the index number for each one to
> display
> it.
> =Parameters.pState.value (this gives a query error)
> =Parameters.pState.value(3) (this displays the value of the 4th parameter
> value they chose. If they didn't choose 4 parameters, then it gives an
> array
> out of bounds error)
> If they chose 4 items, I want a piece of code that will automatically show
> all 4 (e.g. AZ, NM, NY, FL)
> thanks in advance!|||Thank you Robert. You are a God among men! This was SO helpful...
"Robert Bruckner [MSFT]" wrote:
> If you change a report parameter to be multi value, the .Value property will
> return an object[] rather than an object. Hence you can no longer e.g. write
> expressions like =Parameters!MVP1.Value.ToString().
> To access individual values of a multi value parameter you can use
> expressions like this:
> =Parameters!MVP1.IsMultiValue
> boolean flag - tells if a parameter is defined as multi value
> =Parameters!MVP1.Count
> returns the number of values in the array
> =Parameters!MVP1.Value(0)
> returns the first selected value
> =Join(Parameters!MVP1.Value)
> creates a space separated list of values
> =Join(Parameters!MVP1.Value, ", ")
> creates a comma separated list of values
> =Split("a b c", " ")
> to create a multi value object array from a string (this can be used
> e.g. for drillthrough parameters, subreports, or query parameters)
> See also MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
> * http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
>
> Hope this helps,
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:2BE1BECC-8CDC-4C0A-9735-0CB291BDD301@.microsoft.com...
> >I am using Reporting Services 2005. My parameter is a multi-select one. I
> > want to display what parameters the user chose when they run the report so
> > others know what data the report was filtered by. When I put the below
> > text
> > string in a text box, I get an error. When I put the index value after
> > the
> > string it shows that item if there is one for that index #, and an error
> > if
> > there isn't. If they chose 5 items for that parameter, I want all 5 to
> > show
> > up automatically and not have to put the index number for each one to
> > display
> > it.
> >
> > =Parameters.pState.value (this gives a query error)
> > =Parameters.pState.value(3) (this displays the value of the 4th parameter
> > value they chose. If they didn't choose 4 parameters, then it gives an
> > array
> > out of bounds error)
> >
> > If they chose 4 items, I want a piece of code that will automatically show
> > all 4 (e.g. AZ, NM, NY, FL)
> >
> > thanks in advance!
>
>

2005 MS Options

is there a way to display the size in bytes of the results from a query
in management studio?
Not sure if it's quite what you're after but if you select the "Include
Client Statistics" button in Management Studio (generally it's the one to
the left of the exclamation mark for SQLCMD mode) then you'll get an
additional tab in your results for Client Statistics and one of the result
rows in there is the "Bytes received from server"
HTH
Jasper Smith (SQL Server MVP)
www.sqldbatips.com
<jw56578@.gmail.com> wrote in message
news:1163545339.608468.225020@.h48g2000cwc.googlegr oups.com...
> is there a way to display the size in bytes of the results from a query
> in management studio?
>