How to make the report show fields in multiple columns?

I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

There is an option in Crystal Reports -- "Format with multiple columns". I couldn't find the similar configuration in SQL reporting. There is a "Columns" setting in Body Properties, but it doesn't work as making the report in multiple columns. Please help!!

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

how to make query run faster?

hello i have two queries that i need to combine.... the first queryexecutes within one second and the second query executes within 7 seconds.... I used UNION to join this two query.. but my problem is that when using UNION my query now executes within 3:00 minutes?... how can i make my query run faster?... thanks so much....

here's my query:

SELECT d.question_section_name, d.question_sort_order, d.question_number,
d.question_text, d.Range, d.answer, d.Points, d.audit_id, d.question_type,
d.weighted, d.na, d.pf, d.nw, d.wh_survey_id, d.wh_question_id,
d.audit_date, d.survey_name, d.user_name, d.user_email, d.location_id,
b.question_section_name as question_section_name_2, b.rating,
b.survey_threshold_success, b.survey_threshold_failure, b.survey_threshold,
b.maximum_points, b.scored_points, b.percent_scored_of_max,
b.percent_total_score
FROM vw_audit_detail_report d JOIN (SELECT TOP 1 * FROM
vw_audit_detail_report_summary e
WHERE (e.location_id = 4919
AND (e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))) b
ON d.audit_id = b.audit_id and (d.question_section_name <>
b.question_section_name)
WHERE (d.location_id = 4919
AND (d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))
And d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f where
(f.location_id = 4919
AND (f.audit_date BETWEEN '2003-07-11' AND '2003-07-11')))

UNION

SELECT a.question_section_name, a.question_sort_order, a.question_number,
a.question_text, a.Range, a.answer, a.Points, a.audit_id, a.question_type,
a.weighted, a.na, a.pf, a.nw, a.wh_survey_id, a.wh_question_id,
a.audit_date, a.survey_name, a.user_name, a.user_email, a.location_id,
c.question_section_name as question_section_name_2, c.rating,
c.survey_threshold_success, c.survey_threshold_failure, c.survey_threshold,
c.maximum_points, c.scored_points, c.percent_scored_of_max,
c.percent_total_score
FROM vw_audit_detail_report a
INNER JOIN vw_audit_detail_report_summary c ON a.audit_id = c.audit_id AND
a.question_section_name = c.question_section_name
WHERE (a.location_id = 4919
AND (a.audit_date BETWEEN '2003-07-11' AND '2003-07-11'))

What indexes do you have on the underlying tables?

|||If you are doing this in a stored procedure you might also want to consider dumping the first to a #temp table, and then inserting the 2nd into the same table, with a 3rd query to pull the data out of the #temp table. Once the procedure finishes the #temp table will disappear. Also as the other person suggested, look at your indexes. Put this query in query analyzer and get the execution plan to see where some of the backup is coming from. I see many of your joins are to views as well. Unless they are sorted and indexed as well, that can cause some issue. for something this complicated I usually try to get everything I can directly from the tables involved. views on views on views is never a good thing, and extremely difficult to diagnose!

my 2 cents

|||

A couple of comments on the first query: (NOTE: this may not make any difference...)

The code marked with Blue seems overly restrictive. It will only return rows that occurred at EXACTLY midnight on July 11, 2003. Are you sure you didn't mean to include all rows throughout the date of July 11th?

The code marked with Yellow seems unneeded and 'may' contribute to a slow response. You are doing a JOIN with a subset of data that is already constrained by the same criteria. Does this add anything? Is it possible that the Audit_ID values in either table have would have changed for that date range?

The code marked with Orange seems unneeded. The JOIN conditions with vw_audit_detail_report_summary (derived table 'b') seem to have previous precluded question_section_name being in both tables.

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)
WHERE ( ( d.location_id = 4919
AND d.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
AND d.question_section_name NOT IN (SELECT question_section_name
FROM vw_audit_detail_report_summary f
WHERE ( f.location_id = 4919
AND f.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
)
)

Unless I'm really misreading this (and on Monday, anything is possible), it seems like the query could be revised as:

Code Snippet

FROM vw_audit_detail_report d
JOIN (SELECT TOP 1 *
FROM vw_audit_detail_report_summary e
WHERE ( e.location_id = 4919
AND e.audit_date BETWEEN '2003-07-11' AND '2003-07-11'
)
) b
ON ( d.audit_id = b.audit_id
AND d.question_section_name <> b.question_section_name
)

And then you do a UNION with the second query, so it seems that the

AND d.question_section_name <> b.question_section_name

and the entire second query, could also be eliminated ...

|||thanks so much.... that solved my problem...

How to make part of the report show in 2 columns?

I am writing a report in SQL server 2005 Reporting service. The report has two parts: first part shows basic information about the client; the second part lists all the softwares the client has. My question is how to make the softwares listed in two columns as shown below?

John Smith

Title: MSTP Location: Main Campus IP:127.0.0.1

Softwares:

Adobe Standard 7.0 Access 5.0

Internet Explore 6.0 Office XP

Any suggestion is appreciated.

In Crystal Reports, there is an option called "format with multiple columns". Could not find the similar configuration in SQL reporting. Please help!

|||

HI, MiaF:

You can manualy select the two columns in the data section. Right click and select merge cells.

|||

Rex,

Can you explain a little bit more please? I wanted the results to show in multiple columns, how can I make it happen by merging cells? Also, when you say merging cells, you mean in a table?

I found this article that seems saying that what I asked is impossible:

http://msdn2.microsoft.com/en-us/library/ms155816.aspx

"A multi-column layout applies to the entire report. It is not possible to specify a multi-column layout on the top half of the report, and a tabular layout on the bottom half of the report. " Is there anyway to get around this? Thanks.

How to make a hierarchy in a document map?

How to make a hierarchy in a document map?

In the report designer, I have two levels of grouping: First Level: Country, Second level: City. I have no problem to create a document map containing both grouping.

However, the city and country are listed in a sequential order in the document map.

What else should I do and how, to make them a hierarchy that looks like a tree (file folder) structure?

Thanks.

Are you referring to generating them in a PDF or in the actual previewing of the report in report manager? The PDF map seems to list them in the same tree, but will go to the correct location. In the report design you should also be able to set this up with groups. Having a document map label and a parent group.|||If you are refering to the PDF document map (bookmarks) then I will have something disappointing. The pdf (as of this version) does not support rendering hierarchies. Perhaps this will be implementend in any further version.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How to link datasets for reporting?

I have 2 datasets for a single report. One dataset(1) returns value(s), the
second dataset(2) has many records for each value(s) of dataset(1). Yes, one
to many. On the report I wish to display a result of dataset(1) with a
wrapping text box with the many values of dataset(2), then pagebreak and page
two will be the next result of dataset(1) with the many records of dataset(2)
in a wrapbox etc... I have a key column in both datasets. I need to first
figure out how to link them and use the data on report as I described. thanks
!!Read up on subreports. That is how to solve this problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"T" <T@.discussions.microsoft.com> wrote in message
news:5722C5FE-4DD1-4FAA-ACF8-86FCA2B15ACA@.microsoft.com...
>I have 2 datasets for a single report. One dataset(1) returns value(s),
>the
> second dataset(2) has many records for each value(s) of dataset(1). Yes,
> one
> to many. On the report I wish to display a result of dataset(1) with a
> wrapping text box with the many values of dataset(2), then pagebreak and
> page
> two will be the next result of dataset(1) with the many records of
> dataset(2)
> in a wrapbox etc... I have a key column in both datasets. I need to
> first
> figure out how to link them and use the data on report as I described.
> thanks
> !!|||I have come across similar scenarios and tried to solved it with two
datasets, but each time I come back to include alla data in one "flat"
dataset and use gropuing instead.
Ex. If you have one dataset with adresses and one dataset with related phone
numbers. Like:
Adress
1, "MyStreet", "MyCity"
2, "MySecondStreet", "MyCity"
Phone
1, 1, "Phone", "+46-123456"
2, 1, "Fax", "+46-8-232323"
3, 2, "Phone", "+46-654321"
4, 2, "Fax", "+46-8-44444"
Then join them into one dataset instead of two, like:
1, "MyStreet", "MyCity", 1, 1, "Phone", "+46-123456"
1, "MyStreet", "MyCity", 2, 1, "Fax", "+46-8-232323"
2, "MySecondStreet", "MyCity", 3, 2, "Phone", "+46-654321"
2, "MySecondStreet", "MyCity", 4, 2, "Fax", "+46-8-44444"
Create a new Report using this Dataset.
Set property "HideDuplicates = true" on the Adress columns.
Create a group with "AddressId" as group value, if you like to SUM some
values one on "adress-level"
This way you "simulate" related datasets.
Regards Martin Bring
**********************
"T" wrote:
> I have 2 datasets for a single report. One dataset(1) returns value(s), the
> second dataset(2) has many records for each value(s) of dataset(1). Yes, one
> to many. On the report I wish to display a result of dataset(1) with a
> wrapping text box with the many values of dataset(2), then pagebreak and page
> two will be the next result of dataset(1) with the many records of dataset(2)
> in a wrapbox etc... I have a key column in both datasets. I need to first
> figure out how to link them and use the data on report as I described. thanks
> !!