I am using Crystal Reports 9 to build a report that involves connections to both an Excel spreadsheet and an Oracle database. The problem is, the Oracle database contains an ID field stored as text (although the data is currently strictly numeric). The same ID field appears in the excel spreadsheet. I am trying to join the spreadsheet to the database. My problem is that, since ID field in the spreadsheet contains only numeric data, Crystal Reports imports the field as a numeric field then expects it match to the Oracle database's text ID field! Obviously, this creates a type error and the report fails.
My question is: is there any way to force Crystal Reports to regard a field in Excel as text rather than numeric data? Changing the cell attribute to text in Excel didn't seem to work; Crystal still saw numbers in the field and assumed the data was numeric.
This is an urgent matter so any help at all would be appreciated. Thanks!When joining the tables convert oracles table's id to numericsql
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Monday, March 26, 2012
Forcing Reporting Services to update with new query?
Take a look and see how long the old version of the report is cached for. Andrew Watt MVP - InfoPath wrote in message news:cf32036e-8c31-4697-b1aa-a45e1bf9089c@.discussions.microsoft.com... >I had a minor error in my query that i corrected, but RS for some reason
> is still using the 'old' query rather than the new 1. >
> I have redeployed the report, rebooted the machine and restarted IIS,
> but to no avail, it is still using the incorrect 'old query. > >
> Any help?
>I had a minor error in my query that i corrected, but RS for some reason is still using the 'old' query rather than the new 1.
But thanks anyway
|||You could try to delete existing report from report server before deploying.
> is still using the 'old' query rather than the new 1. >
> I have redeployed the report, rebooted the machine and restarted IIS,
> but to no avail, it is still using the incorrect 'old query. > >
> Any help?
>I had a minor error in my query that i corrected, but RS for some reason is still using the 'old' query rather than the new 1.
I have redeployed the report, rebooted the machine and restarted IIS, but to no avail, it is still using the incorrect 'old query.
Any help?|||There are many reasons that this could be happening, for example, did you check that there were no errors in the build/deploy. Is the report being deployed to the correct server and to the correct folder.
Have you tried deleteing the old report then redeploying it, this will help you to determine where this behavior is coming from. If you try this and it still doesn't work then the problem is somewhere in the report project.
Try this and post what happens.|||Thanks, i have indeed deleted the datasource and the report and redeployed them, but still the same problem.
The report works perfectly in VS2005 preview, yet i get the same error when its is deployed
(If i select an @.FromDate from any month besides september i get the error 'The value provided for the report parameter 'FromDate' is not valid for its type. (rsReportParameterTypeMismatch)' )
Here is my code:
I am trying to select clicks between date ranges
SELECT MIN(A.hitDate) AS minDate, D.AffiliateName, B.ProdType, COUNT(DISTINCT A.ipAddress) AS TOTAL
FROM ArchiveApplyTracking A LEFT OUTER JOIN
ExpertMatchConfiguration.dbo.Affiliates D ON A.affiliateID = D.AffiliateID LEFT OUTER JOIN
ArchiveCampaignIncoming C ON A.campaignID = C.ID LEFT OUTER JOIN
ExpertMatchConfiguration.dbo.ProductType B ON A.productTypeID = B.ProdNo
WHERE (A.hitDate >=@.FromDate) and (A.hitDate <@.ToDate) AND (A.ipAddress NOT IN
(SELECT ipAddress
FROM [ExpertMatchTracking].[dbo].ipTracking)) AND (B.ProdType IN (@.ProductType)) AND (B.ProdType NOT LIKE 'refused%') AND (A.deal = 1) AND (D.AffiliateName IN (@.AffiliateName)) AND (B.ProdType NOT LIKE '%prequalify%')
GROUP BY D.AffiliateName, B.ProdType
ORDER BY D.AffiliateName
There must be an error in the deployment as it works 100% in the preview
Thanks for your input
|||
Only thing I can think of is the format of the date. If the format is incorrect then it will appear to work for some dates but then it will not work for others.
|||Thanks, i cant fathom why it works fine in preview, but falls over completly in the deployed report. Nor do i think there is anything from with the date format, it is pretty straight-forward.But thanks anyway
|||You could try to delete existing report from report server before deploying.
forcing report to refresh
I have a RS report that I am integrating in to an ASP web site. I have
a menu page that gathers some user parameters (report dates etc) and
then opens the report in a new browser window, passing the user
parameters as querystring variables. I have noticed that the report
does not refresh if you use the same parameters twice in a row. For
example, I opened the report, tweaked the underlying data then ran it
again with the exact same parameters. The report did not channge. It
did change when I hit the refresh button in the report itself though. I
would prefer if my users did not have to perform this additional step.
Is there a parameter I can pass, or some other way to force this to
refresh every time?I never found a parameter that RS recognizes to automatically refresh
the report. What I ended up doing was to make up my own. Basically, I
pass all the parameters the report takes, plus another querystring
variable I called "GenDate", which contains the current date and time.
Than way the report parameters are always different, so the report
always refreshes. The problem with this is that RS does not like to
receive any parameters that it is not expecting, so I have to add this
parameter to every report. I just add it as a report parameter, but
don't associate it with anything in the recordset.
If anyone knows of a parameter the RS will recognize, please let me
know. For that matter, is there a master list somewhere? I have a few
that RS tacks on automatically (rs:Command=Render, rs:Format=HTML4.0,
rc:Toolbar=True, rc:JavaScript=True, rc:LinkTarget=_top,
rc:Area=Report), but the documentation is rather sparse.|||I finally came across rs:ClearSession=True which appears to accomplish
what I'm after.sql
a menu page that gathers some user parameters (report dates etc) and
then opens the report in a new browser window, passing the user
parameters as querystring variables. I have noticed that the report
does not refresh if you use the same parameters twice in a row. For
example, I opened the report, tweaked the underlying data then ran it
again with the exact same parameters. The report did not channge. It
did change when I hit the refresh button in the report itself though. I
would prefer if my users did not have to perform this additional step.
Is there a parameter I can pass, or some other way to force this to
refresh every time?I never found a parameter that RS recognizes to automatically refresh
the report. What I ended up doing was to make up my own. Basically, I
pass all the parameters the report takes, plus another querystring
variable I called "GenDate", which contains the current date and time.
Than way the report parameters are always different, so the report
always refreshes. The problem with this is that RS does not like to
receive any parameters that it is not expecting, so I have to add this
parameter to every report. I just add it as a report parameter, but
don't associate it with anything in the recordset.
If anyone knows of a parameter the RS will recognize, please let me
know. For that matter, is there a master list somewhere? I have a few
that RS tacks on automatically (rs:Command=Render, rs:Format=HTML4.0,
rc:Toolbar=True, rc:JavaScript=True, rc:LinkTarget=_top,
rc:Area=Report), but the documentation is rather sparse.|||I finally came across rs:ClearSession=True which appears to accomplish
what I'm after.sql
Forcing matrix static elements to display despite empty record set
I created a nice matrix report and it works great when the parameter I pass
returns some records, however when no records are returned the matrix simply
does not render at all leaving a big empty hole where that part of the report
should appear.
The matrix has static row heading that I want to always appear even if no
data appears in the columns, even better if there was a way to set a default
value to the columns in case no data is returned say 0s. This is an example
of my matrix.
Static Heading
Column Group
Cars
people
animals
So, Cars, People, and Animals are static row headings and the values fill in
next to them for the column groupings. Now, if no records are returned I
would still like to have the static column heading and static row heading to
appear even if there is no data to display. How is that done?
ThanksTry setting something in the NoRows property - like display a message "no
data for this timeframe" or something and I think that will make your
headings show in addition to the message. I have used this for tables ...
havent tried it with a matrix but I would think it should work.
"Ramez" wrote:
> I created a nice matrix report and it works great when the parameter I pass
> returns some records, however when no records are returned the matrix simply
> does not render at all leaving a big empty hole where that part of the report
> should appear.
> The matrix has static row heading that I want to always appear even if no
> data appears in the columns, even better if there was a way to set a default
> value to the columns in case no data is returned say 0s. This is an example
> of my matrix.
> Static Heading
> Column Group
> Cars
> people
> animals
>
> So, Cars, People, and Animals are static row headings and the values fill in
> next to them for the column groupings. Now, if no records are returned I
> would still like to have the static column heading and static row heading to
> appear even if there is no data to display. How is that done?
> Thanks
>
>
>
returns some records, however when no records are returned the matrix simply
does not render at all leaving a big empty hole where that part of the report
should appear.
The matrix has static row heading that I want to always appear even if no
data appears in the columns, even better if there was a way to set a default
value to the columns in case no data is returned say 0s. This is an example
of my matrix.
Static Heading
Column Group
Cars
people
animals
So, Cars, People, and Animals are static row headings and the values fill in
next to them for the column groupings. Now, if no records are returned I
would still like to have the static column heading and static row heading to
appear even if there is no data to display. How is that done?
ThanksTry setting something in the NoRows property - like display a message "no
data for this timeframe" or something and I think that will make your
headings show in addition to the message. I have used this for tables ...
havent tried it with a matrix but I would think it should work.
"Ramez" wrote:
> I created a nice matrix report and it works great when the parameter I pass
> returns some records, however when no records are returned the matrix simply
> does not render at all leaving a big empty hole where that part of the report
> should appear.
> The matrix has static row heading that I want to always appear even if no
> data appears in the columns, even better if there was a way to set a default
> value to the columns in case no data is returned say 0s. This is an example
> of my matrix.
> Static Heading
> Column Group
> Cars
> people
> animals
>
> So, Cars, People, and Animals are static row headings and the values fill in
> next to them for the column groupings. Now, if no records are returned I
> would still like to have the static column heading and static row heading to
> appear even if there is no data to display. How is that done?
> Thanks
>
>
>
Friday, March 23, 2012
Forcing Column Headings
Hi...
I have a report that does not use the detail line. It groups the information it needs and prints out in the lowest section heading (Product Type).
The problem arises when we have an employee ( next group up) who has worked on loads of product types in the date range selected.
The report prints perfectly except that when it skips to a new page we only get the page heading, not the section headings.
I have tried moving the section headings to the page headings - report "looks" fine, but does not work as it fails to recognise any changes in the groups.
Anyone any ideas how I can "force" it to print a heading when a certain number of lines have been printed??
Am new to Crystal so am sorry if this is a stupid posting...Will checking the 'repeat group header on each page' box do the trick for you?|||Thanks for the response.
Was not aware of that box, and finally tracked it down.
Unfortunately tried it and it did not change anything. Will carry on experimenting, but if you have any other ideas they will be gratefully received!!
Thanks again|||Update....
The more digging round I did, the more your suggestion seemed to be what I wanted, so I could not understand why it did not work.
So I went back and tried it again... and it now works!
No idea what I did before - can only assume I put it in the wrong section. I can only blame it being early and a lack of caffeine...
But it now works, as I said.
Many, many thanks for your help.sql
I have a report that does not use the detail line. It groups the information it needs and prints out in the lowest section heading (Product Type).
The problem arises when we have an employee ( next group up) who has worked on loads of product types in the date range selected.
The report prints perfectly except that when it skips to a new page we only get the page heading, not the section headings.
I have tried moving the section headings to the page headings - report "looks" fine, but does not work as it fails to recognise any changes in the groups.
Anyone any ideas how I can "force" it to print a heading when a certain number of lines have been printed??
Am new to Crystal so am sorry if this is a stupid posting...Will checking the 'repeat group header on each page' box do the trick for you?|||Thanks for the response.
Was not aware of that box, and finally tracked it down.
Unfortunately tried it and it did not change anything. Will carry on experimenting, but if you have any other ideas they will be gratefully received!!
Thanks again|||Update....
The more digging round I did, the more your suggestion seemed to be what I wanted, so I could not understand why it did not work.
So I went back and tried it again... and it now works!
No idea what I did before - can only assume I put it in the wrong section. I can only blame it being early and a lack of caffeine...
But it now works, as I said.
Many, many thanks for your help.sql
Forced Output Format - RS2000
A few details first:
The report server is remote to the development server (VS 2003).
The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.
I have a couple of questions:
a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?
b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.
Many thanks and kindest regards,
TwoForTea
Yes, you can link to a report using a hyperlink such ashttp://localhost/ReportServer?/AWReporter/Sales By Territory&
rs:Command=Render
If you want to force the format you can use this:
http://localhost/ReportServer?/AWReporter/Sales By Territory&
rs:Command=Render&rs:Format=PDF
or
http://localhost/ReportServer?/AWReporter/Sales By Territory&
rs:Command=Render&rs:Format=EXCEL
(Look in the books online for other format settings such as HTML, MHTML, CSV)
Forced Output Format - RS2000
A few details first:
The report server is remote to the development server (VS 2003).
The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.
I have a couple of questions:
a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?
b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.
Many thanks and kindest regards,
TwoForTea
Yes, you can link to a report using a hyperlink such ashttp://localhost/ReportServer?/AWReporter/Sales By Territory&
rs:Command=Render
If you want to force the format you can use this:
http://localhost/ReportServer?/AWReporter/Sales By Territory&
rs:Command=Render&rs:Format=PDF
or
http://localhost/ReportServer?/AWReporter/Sales By Territory&
rs:Command=Render&rs:Format=EXCEL
(Look in the books online for other format settings such as HTML, MHTML, CSV)
Forced auto refresh to avoid page caching
Good Afternoon!
I've run into an issue whilst developing a report using VS.NET, RS and SQL
Enterprise Server 2000.
The problem I have is that I'm using RS to develop a web based app that can
be used to edit base table data. I have the app working fine, but it keeps
taking it's data from the cached reports. Obviously, this means that every
time a change is made to the base data, the user needs to refresh the page -
not an easy thing to remember when processing several hundred entries.
I have the "Do not cache temporary copies of this report" radio button
selected (by default). Also, the "Autorefresh" text box is not an option I
can take as it is unusable when there are groups set up on the table - they
keep expanding and collapsing.
I've seen ways of embedding some (rather shaky) HTML to force the refresh.
See below:
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
<META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
Is anyone aware of how I can write this into the .rdl, or report? Or, is
there a way to force the page to refresh?
Thanks in advance
JonTry adding the rs:ClearSession=true to the report url.
--
Adrian M.
MCP
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Good Afternoon!
> I've run into an issue whilst developing a report using VS.NET, RS and SQL
> Enterprise Server 2000.
> The problem I have is that I'm using RS to develop a web based app that
> can be used to edit base table data. I have the app working fine, but it
> keeps taking it's data from the cached reports. Obviously, this means
> that every time a change is made to the base data, the user needs to
> refresh the page - not an easy thing to remember when processing several
> hundred entries.
> I have the "Do not cache temporary copies of this report" radio button
> selected (by default). Also, the "Autorefresh" text box is not an option
> I can take as it is unusable when there are groups set up on the table -
> they keep expanding and collapsing.
> I've seen ways of embedding some (rather shaky) HTML to force the refresh.
> See below:
> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
> Is anyone aware of how I can write this into the .rdl, or report? Or, is
> there a way to force the page to refresh?
> Thanks in advance
>
> Jon
>
>|||I tried it manually, and it didn't appear to change anything.
Is there a way I can embed that statement in the rdl?
Thanks
Jon
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:euodxncHFHA.4048@.TK2MSFTNGP15.phx.gbl...
> Try adding the rs:ClearSession=true to the report url.
> --
> Adrian M.
> MCP
> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> Good Afternoon!
>> I've run into an issue whilst developing a report using VS.NET, RS and
>> SQL Enterprise Server 2000.
>> The problem I have is that I'm using RS to develop a web based app that
>> can be used to edit base table data. I have the app working fine, but it
>> keeps taking it's data from the cached reports. Obviously, this means
>> that every time a change is made to the base data, the user needs to
>> refresh the page - not an easy thing to remember when processing several
>> hundred entries.
>> I have the "Do not cache temporary copies of this report" radio button
>> selected (by default). Also, the "Autorefresh" text box is not an option
>> I can take as it is unusable when there are groups set up on the table -
>> they keep expanding and collapsing.
>> I've seen ways of embedding some (rather shaky) HTML to force the
>> refresh. See below:
>> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
>> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
>> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
>> Is anyone aware of how I can write this into the .rdl, or report? Or, is
>> there a way to force the page to refresh?
>> Thanks in advance
>>
>> Jon
>>
>
I've run into an issue whilst developing a report using VS.NET, RS and SQL
Enterprise Server 2000.
The problem I have is that I'm using RS to develop a web based app that can
be used to edit base table data. I have the app working fine, but it keeps
taking it's data from the cached reports. Obviously, this means that every
time a change is made to the base data, the user needs to refresh the page -
not an easy thing to remember when processing several hundred entries.
I have the "Do not cache temporary copies of this report" radio button
selected (by default). Also, the "Autorefresh" text box is not an option I
can take as it is unusable when there are groups set up on the table - they
keep expanding and collapsing.
I've seen ways of embedding some (rather shaky) HTML to force the refresh.
See below:
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
<META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
Is anyone aware of how I can write this into the .rdl, or report? Or, is
there a way to force the page to refresh?
Thanks in advance
JonTry adding the rs:ClearSession=true to the report url.
--
Adrian M.
MCP
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Good Afternoon!
> I've run into an issue whilst developing a report using VS.NET, RS and SQL
> Enterprise Server 2000.
> The problem I have is that I'm using RS to develop a web based app that
> can be used to edit base table data. I have the app working fine, but it
> keeps taking it's data from the cached reports. Obviously, this means
> that every time a change is made to the base data, the user needs to
> refresh the page - not an easy thing to remember when processing several
> hundred entries.
> I have the "Do not cache temporary copies of this report" radio button
> selected (by default). Also, the "Autorefresh" text box is not an option
> I can take as it is unusable when there are groups set up on the table -
> they keep expanding and collapsing.
> I've seen ways of embedding some (rather shaky) HTML to force the refresh.
> See below:
> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
> Is anyone aware of how I can write this into the .rdl, or report? Or, is
> there a way to force the page to refresh?
> Thanks in advance
>
> Jon
>
>|||I tried it manually, and it didn't appear to change anything.
Is there a way I can embed that statement in the rdl?
Thanks
Jon
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:euodxncHFHA.4048@.TK2MSFTNGP15.phx.gbl...
> Try adding the rs:ClearSession=true to the report url.
> --
> Adrian M.
> MCP
> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> Good Afternoon!
>> I've run into an issue whilst developing a report using VS.NET, RS and
>> SQL Enterprise Server 2000.
>> The problem I have is that I'm using RS to develop a web based app that
>> can be used to edit base table data. I have the app working fine, but it
>> keeps taking it's data from the cached reports. Obviously, this means
>> that every time a change is made to the base data, the user needs to
>> refresh the page - not an easy thing to remember when processing several
>> hundred entries.
>> I have the "Do not cache temporary copies of this report" radio button
>> selected (by default). Also, the "Autorefresh" text box is not an option
>> I can take as it is unusable when there are groups set up on the table -
>> they keep expanding and collapsing.
>> I've seen ways of embedding some (rather shaky) HTML to force the
>> refresh. See below:
>> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
>> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
>> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
>> Is anyone aware of how I can write this into the .rdl, or report? Or, is
>> there a way to force the page to refresh?
>> Thanks in advance
>>
>> Jon
>>
>
Wednesday, March 21, 2012
Force to Render to PDF
Hi
Is it possible to force a report to always render in a specific format (in
this case PDF) and remove the option for the user to change the format in
Report Manager
Thanks
PuddyUsing the standard front end, the answer is no...
You have the capability to modify XML configuration files to expose new
render formats, or to prevent the display and use of existing formats, but
this applies to all users..
To do a custom thing, you'd have to write the front using the API or URL
access with the render command...
You could also perhaps hide the report, then add an html page which simply
calls the URL passing the PDF renderer - That might work for you..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it'''s
community of SQL Professionals.
"Puddy" wrote:
> Hi
> Is it possible to force a report to always render in a specific format (in
> this case PDF) and remove the option for the user to change the format in
> Report Manager
> Thanks
> Puddysql
Is it possible to force a report to always render in a specific format (in
this case PDF) and remove the option for the user to change the format in
Report Manager
Thanks
PuddyUsing the standard front end, the answer is no...
You have the capability to modify XML configuration files to expose new
render formats, or to prevent the display and use of existing formats, but
this applies to all users..
To do a custom thing, you'd have to write the front using the API or URL
access with the render command...
You could also perhaps hide the report, then add an html page which simply
calls the URL passing the PDF renderer - That might work for you..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it'''s
community of SQL Professionals.
"Puddy" wrote:
> Hi
> Is it possible to force a report to always render in a specific format (in
> this case PDF) and remove the option for the user to change the format in
> Report Manager
> Thanks
> Puddysql
Force to Bottom of page
I have a text box as the last item on the report. It's just above the Page Footer. I want this textbox to always display at the bottom of the last page of the report. I can't seem to find a way to force this.
All help is appreciated.
Is there no way to do this? Is that why I've had no response?
|||You could move the text box into the page footer and use a visibility expression:
=iif(Globals!PageNumber = Globals!TotalPages, false, true)
|||Thanks for the response.
That is a good thought but the textbox contains a Paramater to the report which could be a multi line string. The textbox will grow with the string but the Footer doesn't seem to dynamically grow. And the size properties on the footer do not allow expressions.
Is there a way to force the footer to grow and shrink with the size of the objects in it?
Dave
Monday, March 19, 2012
Force No Page Break
Hi --
I have a Report that generates a table with a lot of rows. Reporting
Services breaks my report up into 13 pages. I'd like it to be all one giant
table. Is there a way to force no page breaks?
Thank you!In RS 2005, html only you can do this. It is a report property (not a table
property) called interactive size. I believe you put in 0 for it no not have
page breaks.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dev648237923" <dev648237923@.noemail.noemail> wrote in message
news:%23$WNcbc0GHA.3568@.TK2MSFTNGP03.phx.gbl...
> Hi --
> I have a Report that generates a table with a lot of rows. Reporting
> Services breaks my report up into 13 pages. I'd like it to be all one
> giant table. Is there a way to force no page breaks?
> Thank you!
>|||Thanks -- I set it to 0,0 and that worked great!
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eL6Iubd0GHA.1040@.TK2MSFTNGP06.phx.gbl...
> In RS 2005, html only you can do this. It is a report property (not a
> table property) called interactive size. I believe you put in 0 for it no
> not have page breaks.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "dev648237923" <dev648237923@.noemail.noemail> wrote in message
> news:%23$WNcbc0GHA.3568@.TK2MSFTNGP03.phx.gbl...
>> Hi --
>> I have a Report that generates a table with a lot of rows. Reporting
>> Services breaks my report up into 13 pages. I'd like it to be all one
>> giant table. Is there a way to force no page breaks?
>> Thank you!
>>
>
I have a Report that generates a table with a lot of rows. Reporting
Services breaks my report up into 13 pages. I'd like it to be all one giant
table. Is there a way to force no page breaks?
Thank you!In RS 2005, html only you can do this. It is a report property (not a table
property) called interactive size. I believe you put in 0 for it no not have
page breaks.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dev648237923" <dev648237923@.noemail.noemail> wrote in message
news:%23$WNcbc0GHA.3568@.TK2MSFTNGP03.phx.gbl...
> Hi --
> I have a Report that generates a table with a lot of rows. Reporting
> Services breaks my report up into 13 pages. I'd like it to be all one
> giant table. Is there a way to force no page breaks?
> Thank you!
>|||Thanks -- I set it to 0,0 and that worked great!
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eL6Iubd0GHA.1040@.TK2MSFTNGP06.phx.gbl...
> In RS 2005, html only you can do this. It is a report property (not a
> table property) called interactive size. I believe you put in 0 for it no
> not have page breaks.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "dev648237923" <dev648237923@.noemail.noemail> wrote in message
> news:%23$WNcbc0GHA.3568@.TK2MSFTNGP03.phx.gbl...
>> Hi --
>> I have a Report that generates a table with a lot of rows. Reporting
>> Services breaks my report up into 13 pages. I'd like it to be all one
>> giant table. Is there a way to force no page breaks?
>> Thank you!
>>
>
Force new page.
I have a report that has multiple graphs on it. I would like to force a page break (new page) in between the different reports causing a report to be generated on it own page. Im assuming that Im missing something here because it seems like a easy/usable feature. Any help would be great.
Thanks,
ZDB
Hello ZDB,
Click on your chart, then press F4 to open the Properties window. In here, set the PageBreakAtStart or PageBreakAtEnd to True, depend on where you want the page break.
Hope this helps.
Jarret
Force Excel Column type when exporting from SSRS
Hi all,
I have a tricky behavior here. I have a column in my report which contains alphanumeric codes. When I have a code like 17E001 and I export the report to Excel, excel kindly shows that alphanumeric code to 1+E7 and the value of the column is changed to 1700 which is defintly not what I want.
So I was wondering if there is any way to force the column types when exporting from SSRS?
Sbastien.
By the way if there is a way to force all columns to be formated as Text that will do for me as the excel reports are only used to process data using SSIS.
Force columns to appear in matrix.
I have created the report below in SSRS to show the total by month for the calendar year.
As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.
As I go to project this report for January 2007 I am having trouble.
I want the report to appear as below with the 12 requisite columns.
But as only data for the first column is returned from my query I only get one column to appear.
How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?
Hi,
One option I can think of is adding some dummy records to the query, using the UNION statement. One for each month of the year, with a value 0.
Regards, Jeroen
|||At the risk of sounding like a total noob. Can you modify the query below or give an example?
SELECT
bo.AS400Billingid,
OrgName,
ih.date,
ih.rev
FROM billingorg bo
INNER JOIN
(SELECT
AS400BillingID,
substring(InvoiceDate,4,2) date,
sum(SalesAmount) rev
FROM
InvoiceHeader
WHERE
left(InvoiceDate,3)='106'
GROUP BY
AS400BillingID,substring(InvoiceDate,4,2)) ih
ON bo.AS400BillingID=ih.AS400BillingID
|||I found a solution.
SELECT
id,
OrgName,
AddedDate,
SalesTerritory,
IndustryCode,
AccountManager,
[01] AS 'JAN',
[02] AS 'FEB',
[03] AS 'MAR',
[04] AS 'APR',
[05] AS 'MAY',
[06] AS 'JUN',
[07] AS 'JUL',
[08] AS 'AUG',
[09] AS 'SEP',
[10] AS 'OCT',
[11] AS 'NOV',
[12] AS 'DEC'
FROM
(SELECT
bo.AS400Billingid id,
bo.OrgName,
bo.AddedDate,
bo.SalesTerritory,
bo.IndustryCode,
bo.AccountManager,
substring(ih.InvoiceDate,4,2) date,
ih.SalesAmount rev
FROM
billingorg bo INNER JOIN InvoiceHeader ih
ON bo.AS400BillingID=ih.AS400BillingID
WHERE
left(InvoiceDate,3)='106') as data
PIVOT
(
sum(rev)
FOR date IN([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
) XTab
Force at least one parameter (2K5)
I am trying to build a report that has about 6 different parameters. All of the parameters have been set to allow null, however, I would like the user to put in at least one "search" criteria in order to run the report.
How would I be able to do that?
In addition, when I am viewing the report, it automatically runs and does not wait until I click "View Report". How can I set it not to execute when the report is viewed, but when the user clicks the button?
ThanksHow about setting one of the parameters to not allow blank but setting the default to blank? Might solve both problems in one go, at least forcing the user to think about what they enter... Haven't tested it though 
The program will not allow you to set a blank default value with the "Allow Blank Value" checked.
Thanks for trying ;)
|||DotNetSavvy,
Unfortunate if all of your parameter have null values and nulls are allow, then your report will automatically run. I personally set default to all my parameters but one.
On the forcing a parameter selection, you could raise an return error from SQL if all parameters are null.
hammer
|||Solution:
Create embedded code function that takes as parameters your report parameters values (like this MyFunction(p1value, p2value, p3value,...) ) Inside do something like this: iff(IsNothing(p1value)=true,0,1)+iff(IsNothing(p2value)=true,0,1)+... and if the result is < 1 then throw error/message box with some text (like: "Please assign at least one parameter value") and return null else return 1 or other not null value.
Add internal parameter that cannot be null to your report and in the "expression" of the default value use your code function: =Code.MyFunction(p1.value, p2.value, ...).
Here is an example of using function and parameter to validate report execution http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=829175&SiteID=1
Should work.
Maciej
Monday, March 12, 2012
Foramtting chart axis colour using expression
I have a chart on a report that shows value in ￡ on the Y Axis. The values can be both positive and negative. I would like to have the negative labels to show in red and the positive in black. What is the expression syntax to achieve this? i know it will be an IIF statement, but what is the syntax to show the Y Axis label as <0?
eg if i was doing this for a field it would be iif(Field!Fieldname.value <0, "RED","BLACK") but what would i replace the FIELD!Fieldname.vaue with so the expression would change the Y Axis label.
Thanks in advance for any help
Dave Munro
Hi Dave,
I'm a brazillian guy, but i try to help you !
First of all, You will have to create a code to fill the bars.
REPORT - REPORT PROPERTIES - CODE (tab)
Function ColorBar(VlField) As String
Dim Bar_Colour as String
if VlField > 0 then
Bar_Colour = "BLACK"
Else
Bar_Colour = "RED"
End if
Return Bar_Colour
Afther that, click in chart and set the properties.
click in TAB (VALUES) - VALUE - edit - Appearance (Tab) - Series Style - Fill - expression (color)
=code.ColorBar(Fields!XXXXXX.Value)
OK....
Bye,
Nitrini
Foramtting chart axis colour using expression
I have a chart on a report that shows value in ￡ on the Y Axis. The values can be both positive and negative. I would like to have the negative labels to show in red and the positive in black. What is the expression syntax to achieve this? i know it will be an IIF statement, but what is the syntax to show the Y Axis label as <0?
eg if i was doing this for a field it would be iif(Field!Fieldname.value <0, "RED","BLACK") but what would i replace the FIELD!Fieldname.vaue with so the expression would change the Y Axis label.
Thanks in advance for any help
Dave Munro
Hi Dave,
I'm a brazillian guy, but i try to help you !
First of all, You will have to create a code to fill the bars.
REPORT - REPORT PROPERTIES - CODE (tab)
Function ColorBar(VlField) As String
Dim Bar_Colour as String
if VlField > 0 then
Bar_Colour = "BLACK"
Else
Bar_Colour = "RED"
End if
Return Bar_Colour
Afther that, click in chart and set the properties.
click in TAB (VALUES) - VALUE - edit - Appearance (Tab) - Series Style - Fill - expression (color)
=code.ColorBar(Fields!XXXXXX.Value)
OK....
Bye,
Nitrini
FOR XML streamed?
We are trying to write an XSLT report generator that is pipelined throughout all stages. The first step in the process is to get XML out of our database. We are using 2005 April CTP. The white paper by Michael Rys in the MSDN library seems to indicate that the FOR XML streaming behavior of SQL 2000 is still present in 2005. However, it seems to hint that if you use the TYPE directive to return your XML in an actual native XML field, then the XML is no longer streamed. The entire document may first be created and then sent back all at once as a scaler XML field.
Here are the 2 FOR XML clauses we are expirimenting with:
"For XML Path('PurchaseOrder'), Type, Root('PurchaseOrderReport')" - sends back an XML field instance.
"For XML Path('PurchaseOrder'), Root('PurchaseOrderReport')" - sends back an nvarchar(max) instance.
Can anyone answer if using the TYPE directive eliminates the streaming behavior and therefore defeats our attempt to pipeline?
Thanks!
The TYPE directive will create an XML datatype instance and thus will "Blob" on the server before sending the result back to give you the guarantee of a well-formed XML datatype instance.So: yes, it eliminates the streaming. You can still pipeline after that on the client side though for the rest of your pipeline.
Best regards
Michael|||Thanks for the reply Michael!
Aside from the guarentee of well formed XML, are there other advantages/disadvantages to using the TYPE directive that we should concider when trying to decide between the typed or non-typed route?|||The TYPE directive constructs an XML data type instance, so you can use it for
(a) nested FOR XML
(b) assignment to XML columns, variables and parameters
(c) querying the XML data type result using XQuery
at the server. If your intent is to get the XML to the client in a streamable manner, and (a)-(c) don't apply, then you are better off without the TYPE directive. This also saves you the cost of data conversion to XML data type.
Hope this helps.
Thank you,
Shankar
Program Manager, Microsoft SQL Server
Friday, March 9, 2012
for xml explicit or xsl
I need to create a big xml report (about 60 elements, 9 levels deep). The data is in a single sql server table. For this I have been given an xsd file that the report must match in format.
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>
Sunday, February 26, 2012
For Stored Procs Gurus
Hi,
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael Murphy
Please do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>
The following questions arose during the creation of a Crystal Report Graph.
The amount of data for the graph was so large that making any sense of it by
providing a legend for the colored lines was ridiculous. So we have come up
with a paging system of graphs based on regionIDs (foreign key). Because we
want to add the paging functionality, we basically need to do two SPs one to
gather the data for the Regions and one for the main report that uses the
results of the Regions SP.
Some of these questions may seem trivial, but I was looking for solutions
from those that are better than I at optimizing stored procs.
1. Is there a problem with the passing of 2000 characters in the different
parameters? What if we need to pass more? What are the limits when calling
Stored Procs? Is passing a SQL string built at the program level more
efficient than collecting and sending all the parameters to the SP?
2. Is there a better way to populate the temporary table than calling a
second stored proc for the insert?
3. Can we use an "in memory" table instead of the Temporary table (look for
the new TABLE DATA TYPE in SQL 2000)
4. Is there a better way to "group" the records other than with a cursor? If
not, can anything be done to the cursor to perform better?
5. In the stored proc... is there a better way to fetch the data by using a
VIEW maybe?
I hope you can understand what I have written, if not, let me know and I
will hopefully reword it in a way for you to be helpful.
Thanks,
Michael Murphy
Please do not post the same question independently to multiple newsgroups.
The .programming thread should be used for further communcation.
"Michael D Murphy" <mdmurphy@.scs-techresources.com> wrote in message
news:Oaul5sGjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The following questions arose during the creation of a Crystal Report
Graph.
> The amount of data for the graph was so large that making any sense of it
by
> providing a legend for the colored lines was ridiculous. So we have come
up
> with a paging system of graphs based on regionIDs (foreign key). Because
we
> want to add the paging functionality, we basically need to do two SPs one
to
> gather the data for the Regions and one for the main report that uses the
> results of the Regions SP.
> Some of these questions may seem trivial, but I was looking for solutions
> from those that are better than I at optimizing stored procs.
> 1. Is there a problem with the passing of 2000 characters in the different
> parameters? What if we need to pass more? What are the limits when calling
> Stored Procs? Is passing a SQL string built at the program level more
> efficient than collecting and sending all the parameters to the SP?
> 2. Is there a better way to populate the temporary table than calling a
> second stored proc for the insert?
> 3. Can we use an "in memory" table instead of the Temporary table (look
for
> the new TABLE DATA TYPE in SQL 2000)
> 4. Is there a better way to "group" the records other than with a cursor?
If
> not, can anything be done to the cursor to perform better?
> 5. In the stored proc... is there a better way to fetch the data by using
a
> VIEW maybe?
> I hope you can understand what I have written, if not, let me know and I
> will hopefully reword it in a way for you to be helpful.
> Thanks,
> Michael Murphy
>
Subscribe to:
Posts (Atom)