Can we capture DataSet?

hi,
I would like to use the generated dataset in rdl file to generate
charts(third party component) if needed, As of now just the report is
displayed with out any graph.
In order to achieve this i have to capture the dataset (mdx query) through
custom code or any other means which will allow me to use the same dataset to
populate the chart if required.
Any examples would be very useful.
Many ThanksThis is hard to achieve within RS 2000.
RS 2005 will have a new CustomReportItem feature which will expose the
(processed, RDL grouped, filtered, sorted) data to a custom processing
control which can then generate an image/chart from the data.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"VP" <VP@.discussions.microsoft.com> wrote in message
news:A8F4ADE7-66B4-46C6-87A0-263FDAE43DFD@.microsoft.com...
> hi,
> I would like to use the generated dataset in rdl file to generate
> charts(third party component) if needed, As of now just the report is
> displayed with out any graph.
> In order to achieve this i have to capture the dataset (mdx query) through
> custom code or any other means which will allow me to use the same dataset
> to
> populate the chart if required.
> Any examples would be very useful.
> Many Thanks
>|||Thanks for the information.
VP
"Robert Bruckner [MSFT]" wrote:
> This is hard to achieve within RS 2000.
> RS 2005 will have a new CustomReportItem feature which will expose the
> (processed, RDL grouped, filtered, sorted) data to a custom processing
> control which can then generate an image/chart from the data.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "VP" <VP@.discussions.microsoft.com> wrote in message
> news:A8F4ADE7-66B4-46C6-87A0-263FDAE43DFD@.microsoft.com...
> > hi,
> >
> > I would like to use the generated dataset in rdl file to generate
> > charts(third party component) if needed, As of now just the report is
> > displayed with out any graph.
> > In order to achieve this i have to capture the dataset (mdx query) through
> > custom code or any other means which will allow me to use the same dataset
> > to
> > populate the chart if required.
> > Any examples would be very useful.
> >
> > Many Thanks
> >
> >
>
>

Can This Query Be Improved?

The query displayed below currently takes approximately 5-6 seconds to run in SQL Query Analyzer. It returns 685 rows. In my opinion, 5 seconds seems way to long, so I am wondering if there is a way to optimize this query. Does anyone have suggestions on what I could do to improve the performance of this query?

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON

SELECT DISTINCT
SalesLead.SalesLeadID,
SalesLead.Prefix,
SalesLead.FirstName,
SalesLead.LastName,
SalesLead.Email,
SalesLead.Phone,
SalesLead.LastContact,
Schools.SchoolID,
Schools.SchoolName,
Schools.City AS 'SchoolCity'

FROM SalesLead
INNER JOIN jnSalesLeadSchool
ON SalesLead.SalesLeadID = jnSalesLeadSchool.SalesLeadID
INNER JOIN Schools
ON jnSalesLeadSchool.SchoolID = Schools.SchoolID
LEFT OUTER JOIN jnSalesLeadDepartment
ON SalesLead.SalesLeadID = jnSalesLeadDepartment.SalesLeadID
LEFT OUTER JOIN Department
ON jnSalesLeadDepartment.DepartmentID = Department.DepartmentID
LEFT OUTER JOIN jnSalesLeadOpportunity
ON SalesLead.SalesLeadID = jnSalesLeadOpportunity.SalesLeadID
LEFT OUTER JOIN AdoptionOpportunity
ON jnSalesLeadOpportunity.OpportunityID = AdoptionOpportunity.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames
ON AdoptionOpportunity.CourseNameID = CourseNames.CourseNameID
LEFT OUTER JOIN SalesLeadNotes
ON SalesLead.SalesLeadID = SalesLeadNotes.SalesLeadID

WHERE
SalesLead.Active = 1
AND (
SalesLead.FirstName + ' ' + SalesLead.LastName LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Address1 LIKE '%' + @.SearchTerm + '%'
OR SalesLead.City LIKE '%' + @.SearchTerm + '%'
OR SalesLead.Email LIKE '%' + @.SearchTerm + '%'
OR SalesLeadNotes.Note LIKE '%' + @.SearchTerm + '%'
OR Schools.SchoolName + ' - ' + Schools.City LIKE '%' + @.SearchTerm + '%'
OR Department.Name LIKE '%' + @.SearchTerm + '%'
OR CourseNames.CourseName LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Term LIKE '%' + @.SearchTerm + '%'
OR AdoptionOpportunity.Chances LIKE '%' + @.SearchTerm + '%'
)

ORDER BY SalesLead.LastName

Thanks in advance!
AaronPerhaps the execution plan has a hint. How much has the distinct? Don't know how expensive it really is. It might be an option to rewrite the whole thing to match the exact search-type so there's no OR-left.

Whats the rowcount without the where-clause?|||Creating an index on Saleslead.Active might help a bit. Enabling full text indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_74oj.asp) will help a lot, but at a significant cost in disk space and INSERT/UPDATE performance.

-PatP|||According to the execution plan, the DISTINCT is costing 2%, but I need that to filter out the duplicate information that the OUTER joins cause.

The rowcount without the where clause is 15,617.

According to the execution plan, the two biggest costs are:
38% - SORT(Sorting the Input) - ARGUMENT: ORDER BY:(jnSalesLeadSchool.SalesLeadID ASC)

19% - INDEX SEEK(Scanning a particular range of rows from a non-clusted index) - OBJECT: SalesLeadNotes.IX_SalesLeadNotes.SalesLeadID, SEEK: SalesLeadNotes.SalesLeadID = SalesLead.SalesLeadID|||LIKE '%something'??

That's a scan everytime|||I understand that the wildcard matches are probably the biggest problem area, but is there any way around them besides a full text search?|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||I was gonna say...

"Drop back and punt"...

Ah the Giants of yesteryear....

how does this do?

AND (
SalesLead.FirstName + ' ' + SalesLead.LastName
+ SalesLead.Address1
+ SalesLead.City
+ SalesLead.Email
+ SalesLeadNotes.Note
+ Schools.SchoolName + ' - ' + Schools.City
+ Department.Name
+ CourseNames.CourseName
+ AdoptionOpportunity.Term
+ AdoptionOpportunity.Chances
LIKE '%' + @.SearchTerm + '%'
)|||Brett,
I actually tried exactly that yesterday before going home. Here is the problem: if any of those fields are null (which some are), the entire string will default to NULL - which means rows are not matched. To combat this, I tried using an ISNULL(field, '') on each field while concatenating, but the overhead of the ISNULL cancelled out the gain.|||OK, then punt...

Or simplify the criteria...

or...and this is my last shot before FULLTEXT

DECLARE @.SearchTerm varchar(200)
SET @.SearchTerm = 'john'

SET NOCOUNT ON
SELECT DISTINCT * FROM (
SELECT
x.SalesLeadID,
x.Prefix,
x.FirstName,
x.LastName,
x.Email,
x.Phone,
x.LastContact,
x.SchoolID,
x.SchoolName,
x.City AS 'SchoolCity'

FROM ( SELECT * FROM SalesLead a
INNER JOIN jnSalesLeadSchool b
ON a.SalesLeadID = b.SalesLeadID
INNER JOIN Schools c
ON b.SchoolID = c.SchoolID

WHERE SalesLead.Active = 1
AND ( REPLACE(a.FirstName, @.SearchTerm, '') <> a.FirstName
OR REPLACE(a.LastName, @.SearchTerm, '') <> a.LastName
OR REPLACE(a.Address1, @.SearchTerm, '') <> a.Address1
OR REPLACE(a.City, @.SearchTerm, '') <> a.City
OR REPLACE(a.Email, @.SearchTerm, '') <> a.Email
OR REPLACE(c.SchoolName, @.SearchTerm, '') <> c.SchoolName
OR REPLACE(c.City, @.SearchTerm, '') <> c.City
) AS X
LEFT OUTER JOIN jnSalesLeadDepartment d
ON X.SalesLeadID = d.SalesLeadID
LEFT OUTER JOIN jnSalesLeadOpportunity f
ON X.SalesLeadID = f.SalesLeadID
LEFT OUTER JOIN SalesLeadNotes i
ON X.SalesLeadID = i.SalesLeadID
LEFT OUTER JOIN Department e
ON X.DepartmentID = e.DepartmentID
LEFT OUTER JOIN AdoptionOpportunity g
ON X.OpportunityID = g.AdoptionOpportunityID
LEFT OUTER JOIN CourseNames h
ON g.CourseNameID = h.CourseNameID

WHERE
OR REPLACE(i.Note, @.SearchTerm, '') <> i.Note
OR REPLACE(e.[Name], @.SearchTerm, '') <> e.[Name]
OR REPLACE(h.CourseName, @.SearchTerm, '') <> h.CourseName
OR REPLACE(g.Term, @.SearchTerm, '') <> g.Term
OR REPLACE(g.Chances, @.SearchTerm, '') <> g.Chances
) AS XXX
ORDER BY LastName|||Thanks Brett, but that query takes exactly the same amount of time. I like your idea of using Replace rather than the wildcard comparison, that was clever.

It looks like fulltext is my last resort - are there any rules agains using fulltext searching on columns that contain such a small amount of text (usually less than 100 characters)?|||ow btw: why the order by on a distinct ?|||What you want is really a full-text search. We are beating this mouse bloody when you really want an ox. Byte the bullet and move on!

-PatP

Can Report (rdlc) Table or Matrix Column Width Be Set at Runtime?

Using an rdlc report in ReportViewer, I need to create a table or matrix where the number of columns and the kinds of data displayed in the columns changes with each report. For example, in one report, the second column may hold price information. In another report, the second column may hold a product description.

Obviously, a column with descriptive text needs to be wider than a column with price info. So, the column widths can't be firmly set in the designer. But, I can't figure out how to change the column width during runtime, in either the Table control or Matrix control. (I prefer the matrix, but can use either.)

Setting the header or details textbox CanGrow property to true doesn't help for two reasons. One is that (in a Table control) the textbox grows in width only to the first linebreak opportunity (a space between words). The other is that we need to export the report to PDF and even if the CanGrow widens the column in the viewer, when exported to PDF the column returns to the design width.

Tried setting the Width property of the column to an expression, but it wouldn't let me.

Spent a week on this. Is it possible? If not, if I switched to a Crystal Report (don't want to, I'm Crystal-Report challenged), is there a way to make it work?

There is one very ugly workaround: the Hidden property of a table column can be set to an expression. I could set up the table with two or three columns of different widths for each column in the final report. Depending on the column header ('Price', 'Description', etc.), certain columns would be hidden based on how wide a column was needed. For example, there could be two design columns (one narrow, one wide) for column two of the final report. If the heading on column 2 is Description, make the narrow column hidden so only the wide column displays with the Description text.

Seems it would be better to set the column widths, if possible.

Thanks.

I never found a pretty solution, so went with an ugly one.

The original ugly workaround I proposed worked great for viewing the report on-screen, but did not work when exporting to PDF. By having three columns for every visible column in the final report, the table was 22 inches wide in design view, forcing the report to be 22 inches wide. The export to PDF didn't like that, creating lots of extra blank pages.

The ugly solution that worked was creating multiple tables to acccount for all varieties of column widths and number of columns The tables are stacked on top of one another. The "Hidden" property in each table is set to an expression that checks a report parameter to determine if that table is hidden or visible [=IIF(Parameters!paramTableId.Value = 33, False, True)]. The parameter value is passed in from the codebehind on the aspx page that holds the ReportViewer.

Currently there at 23 tables in the stack. All of the tables are centered horizontally. When I had just 5 or 6 tables, the non-hidden table would appear on the screen centered in the report. With 23 tables, the non-hidden table is left justified. But, in both scenarios, the table is centered in the PDF version, which is all I care about.

|||

I also had this issue - you're dirty hack solution worked well for me - cheers for sharing

rich