sql server reporting services: Table layout -- sql server reporting services

RE: Table layout

magendo_man
8/31/2006 2:33:01 PM

You probably need to use a FULL OUTER JOIN to a table (temporary table, table
variable or table expression) containing the 6 required values in your
dataset query or stored procedure. e.g. if you need to have rows with values
A to F from MyColumn1 in MyTable, which may not always have values you could
do something like:

DECLARE @tblAllMyLetters TABLE (MyLetter CHAR(1))
INSERT INTO @tblAllMyLetters VALUES('A')
INSERT INTO @tblAllMyLetters VALUES('B')
INSERT INTO @tblAllMyLetters VALUES('C')
INSERT INTO @tblAllMyLetters VALUES('D')
INSERT INTO @tblAllMyLetters VALUES('E')
INSERT INTO @tblAllMyLetters VALUES('F')

SELECT Y.MyLetter, X.MyColumn2, X.MyColumn3
FROM MyTable X FULL OUTER JOIN
@tblAllMyLetters Y ON Y.MyLetter=X.Column1

HTH,
Magendo_man

[quoted text, click to view]

Re: Table layout

Morgan
8/31/2006 5:17:20 PM

Thanks, but that doesn't answer my question. I need to automagically
create the remainder of table rows when the count is < 6.

[quoted text, click to view]

Re: Table layout

magendo_man
9/1/2006 12:23:02 AM

That is why I think you need to use a FULL OUTER JOIN on a table defining the
key data for each of your six rows.

My sample code was merely to demonstrate that even if there are fewer than 6
entries in MyTable you would still get 6 rows in the dataset provided by my
sample query. There must be some key factor that determines the content of
each row and this has to be defined in the table to which you FULL OUTER JOIN.

If you could be more specific about the content of each row it may be easier
to help, i.e. What are the six categories that you must have a row for?

Regards, Magendo_man

[quoted text, click to view]

RE: Table layout

magendo_man
9/1/2006 1:20:02 PM

I've just twigged what you are wanting to do. Here is how I would do it:

Write a stored procedure/query which
1) creates a temp table or declare a table variable with columns of the same
datatype as required for your report table.
2) INSERT INTO the temporary table using the SELECT statement which
currently populates the report
3) Work out the number of rows now in the temporary table
4) using a WHILE .. END loop insert the number of rows required to take the
table up to 6 rows
5) SELECT * on the temporary table to generate your report table's dataset

HTH, Magendo_man

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.