Problem with sql query reformatting on its own

I'm having a problem with some of my reports in SQL reporting services.
After I enter my sql query, the software reformats removing parenthesis and
moving sections of the statement around. Is there a way I can force the
system to accept my query as is?Use the generic query designer (the button to switch to this is one of the
buttons to the right of the ...)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Diane Chase via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:cc800d5f7276462981022ea63d09d5a6@.SQLMonster.com...
> I'm having a problem with some of my reports in SQL reporting services.
> After I enter my sql query, the software reformats removing parenthesis
> and
> moving sections of the statement around. Is there a way I can force the
> system to accept my query as is?|||Thanks Bruce that worked!
--
Message posted via http://www.sqlmonster.com

Problem with sp2... bug?

After I've installed the sql server 2005 sp2, some reports have a strange
behaviour. For example
In some case the Inscope function in a matrix, in the subtotal column,
returns different result (between sp1 and sp2)
And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
(using the inscope function) , in some case it do the sum of the column.
I think that the two stange behaviour are correlateOn Apr 20, 10:45 am, "abc_abc" <e...@.phones.it> wrote:
> After I've installed the sql server 2005 sp2, some reports have a strange
> behaviour. For example
> In some case theInscope function in a matrix, in the subtotal column,
> returns different result (between sp1 and sp2)
> And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
> (using theinscopefunction) , in some case it do the sum of the column.
> I think that the two stange behaviour are correlate
We just ran across this today too. The Inscope function evaluates
differently depending on where its being evaluated.|||Allen M ha scritto:
> On Apr 20, 10:45 am, "abc_abc" <e...@.phones.it> wrote:
> > After I've installed the sql server 2005 sp2, some reports have a strange
> > behaviour. For example
> >
> > In some case theInscope function in a matrix, in the subtotal column,
> > returns different result (between sp1 and sp2)
> > And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
> > (using theinscopefunction) , in some case it do the sum of the column.
> > I think that the two stange behaviour are correlate
> We just ran across this today too. The Inscope function evaluates
> differently depending on where its being evaluated.
I also have the same problem.
is it a bug of SP2 ?
Alessio|||On May 17, 1:26 am, mxl...@.tiscali.it wrote:
> Allen M ha scritto:
> > On Apr 20, 10:45 am, "abc_abc" <e...@.phones.it> wrote:
> > > After I've installed the sql server 2005 sp2, some reports have a strange
> > > behaviour. For example
> > > In some case theInscope function in a matrix, in the subtotal column,
> > > returns different result (between sp1 and sp2)
> > > And if I calcuted sum(field!FieldName,"row_matrix") in the subtotal column
> > > (using theinscopefunction) , in some case it do the sum of the column.
> > > I think that the two stange behaviour are correlate
> > We just ran across this today too. TheInscopefunction evaluates
> > differently depending on where its being evaluated.
> I also have the same problem.
> is it abugof SP2 ?
> Alessio
Have either of you found anything on this?sql

Problem With RS2000 Export To Excel

We're using RS2000 and there are some reports that are having problems exporting to Excel after having been rendered.

The reports have no more than 30,000 rows, but are about 150 columns wide. The reports run (execute and redner) very quickly in Reporting Services, but when exporting, the "blank rendering window" (the one with the URL: "http://ServerName.Reportserver?ReportName&rs%3aCommand=Render&rs%3AFormat=EXCEL) is open for about half an hour, and the w3wp process pummels our 8 proc box (CPU between 50 and 90% for that time) and memory goes past 1.5 GIG (of the total 8 GIG) usage.

Sometimes we'll get a message asking the user to log in after about 10-15 minutes, after which it fails, other times it makes it to the dialog box asking if you want to open or save the spreadsheet. If it makes it to this dialog, it's OK, if not, it fails on the export. The resulting spreadsheets are only between 25 - 32 MG, not very big at all.

Are there any settings we can tweak to speed this up, or at the very least, be sure it always makes it to the Open/Save dialog?

Thanks in advance.

Anyone? This is quite a problem. Are there any setting changes to IIS or RS2000 that could help, here?

|||One last bump before I give up...any advice welcome.|||

I would suggest trying your scenario on RS 2005 SP2 and comparing the results. We did quite a bit of work in this area. You can download a trial version of SQL Server 2005 at http://www.microsoft.com/sql.

Thank you.

Problem with reporting and queries

I am having a problem with several reports at work. We use an SQL
generator package where we fill in a template, and the system
generates SQL code.

The reports I have been running at a low level return a sales value of
$96,000 for a specific office for 2006.

Here is my filter,

Office = 23

Region = Northeast

Product Cat = (several different categories)

Year = 2006

When I added some additional columns, the sale for the same office
went to over $9 Million. When I analyzed this further, I found all
offices in the region were being returned for the second report, and
thus I ended up with the sales for all of the regions sales.

What I am really confused about is how using the exact same filter, a
simple report can show one number and then by adding some facts or
columns my sales went up. (and I did confirm character by character
we are using the same filter.)

Is this explainable based on some principle of SQL I am unfamiliar
with?

One explanation I received from IT, who is too busy to look at my
problem, is that by adding additional columns, I essentially asked our
SQL generator to set up a larger join than I expected.

If this were true, wouldn't the filter still eliminate records that
don't meet the filter requirements?

I suspect the SQL generator applied the filter at the wrong spot. I
tried looking at the SQL: code, but it is very complicated.

So I am turning to this forum to see if anyone can think of a logical
explanation that would allow SQL to in effect return a larger dataset
than my original report.

Thanks for any help.AF (bscinc@.Yahoo_NoSpam.com) writes:

Quote:

Originally Posted by

When I added some additional columns, the sale for the same office
went to over $9 Million. When I analyzed this further, I found all
offices in the region were being returned for the second report, and
thus I ended up with the sales for all of the regions sales.
>
What I am really confused about is how using the exact same filter, a
simple report can show one number and then by adding some facts or
columns my sales went up. (and I did confirm character by character
we are using the same filter.)
>
Is this explainable based on some principle of SQL I am unfamiliar
with?
>
One explanation I received from IT, who is too busy to look at my
problem, is that by adding additional columns, I essentially asked our
SQL generator to set up a larger join than I expected.
>
If this were true, wouldn't the filter still eliminate records that
don't meet the filter requirements?
>
I suspect the SQL generator applied the filter at the wrong spot. I
tried looking at the SQL: code, but it is very complicated.
>
So I am turning to this forum to see if anyone can think of a logical
explanation that would allow SQL to in effect return a larger dataset
than my original report.

It's of course impossible to debug a tool that I have never seen.
I can think of lots of reasons, including user errors on your
part, errors in the tool you use, or in the data model you access.

If I understood your story correctly, the second report rendered the
filter on office void and useless. That's some kind of clue, but enough
to say "Aha!".

You could at least post the queries, to give us something to work with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem with reportfooter and header

Dear sir,

i am developing reports in sql server businessintelligence development studio...i deployed the reports in reportserver and i was able to see it..but my requirement is all my reports should have the same header and footer as of all other pages must uses custom footers and headers....hw to use thatcustom footer and header in my reports

Regards,

Pradeep

Hi,

do you want to create custom report headers or use the same for all reports ? This is not quite clear.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

dear ,

other than reports pages i have some other pages in that footer and headers(company logo and the project name like that)...i want o use the same thing in my reports

|||Hi,

I always do that by building a report template for portraint as well as for landscape and build my reports on top of that. Once the report design is signed off by anyone who is responsible you can be sure (or a bit more sure) that anything will change afterwards.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Problem with report viewer

Hi,

I recently upgraded by ASP.Net 1.1 application to 2.0. 1.1 application was using awReportViewer to view the reports.

But, when upgraded, awreportViewer doesnt display the RS2005 enhancements like sorting, etc...so I decided to replace it with Report Viewer control in VS2005.

Now the problem starts, Whenever I try accesing the reports through application, it gives me error "Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response."

This is how I have coded,

ReportViewer1.ProcessingMode = ProcessingMode.Remote

ReportViewer1.ServerReport.ReportServerUrl = New Uri(http://localhost/ReportServer/reportservice.asmx)

ReportViewer1.ServerReport.ReportPath = reportname

//Some code to set parameters in Report Paramater "parameters"

ReportViewer1.ServerReport.SetParameters(parameters)

My questions:

1. Am I going wrong somewhere in the above code?

2. Can awReportViewer be modified / upgraded so that it can support RS2005 features like sorting, multivalued parameters?

TIA

Tanmaya

Change the ReportServerUrl to be 'http://localhost/reportserver' and you should be in business.|||Great...its working now...thanks a ton :)

Problem with report DataSource

Hello,
I am deploying reports via the web service. I create a folder, then create
a data source. Once I create a report, I make a reference to the data source
created and change the reports invalid data source to the reference.
When I did this with a data source with credentials stored on the report
server, it worked with no problems. When I changed this to prompt for
crendentials, accessing the report via web services cannot find the data
source associated with the report (the one defined in the rdl does not exist
in the folder fyi). Is this because a reference has no name in the database?
It does work via the Report Manager.
Here are some code snippets:
this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
definition, null);
//change its datasource to the new one
DataSourceReference reference = new DataSourceReference();
reference.Reference = "/" + dbName + "/" + dbName;
DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
dbName + "/" + reportType.ToString() );
for(int i=0;i<dataSources.Length;i++)
{
if(dataSources[i].Item is InvalidDataSourceReference)
{
dataSources[i].Item = reference;
}
}
this.ReportService.SetReportDataSources("/" + dbName + "/" +
reportType.ToString() ,dataSources);
and for credentials:
DataSourceCredentials d = new DataSourceCredentials();
d.DataSourceName = dbName;
d.UserName = userName;
d.Password = password;
this.mDataSourceCredentials = new DataSourceCredentials[]{d};
any help would be greatly appreciated.It seems recently that my posts never get replied to - Im sure others must
share the same frustration. The solution I came up with was to read the .rdl
file in as xml, search for all data source nodes and replace the old name
(attribute) with the new name. I also created the data source as a data
source, not as a reference.
"comet61" wrote:
> Hello,
> I am deploying reports via the web service. I create a folder, then create
> a data source. Once I create a report, I make a reference to the data source
> created and change the reports invalid data source to the reference.
> When I did this with a data source with credentials stored on the report
> server, it worked with no problems. When I changed this to prompt for
> crendentials, accessing the report via web services cannot find the data
> source associated with the report (the one defined in the rdl does not exist
> in the folder fyi). Is this because a reference has no name in the database?
> It does work via the Report Manager.
> Here are some code snippets:
> this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
> definition, null);
> //change its datasource to the new one
> DataSourceReference reference = new DataSourceReference();
> reference.Reference = "/" + dbName + "/" + dbName;
> DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
> dbName + "/" + reportType.ToString() );
> for(int i=0;i<dataSources.Length;i++)
> {
> if(dataSources[i].Item is InvalidDataSourceReference)
> {
> dataSources[i].Item = reference;
> }
> }
> this.ReportService.SetReportDataSources("/" + dbName + "/" +
> reportType.ToString() ,dataSources);
> and for credentials:
> DataSourceCredentials d = new DataSourceCredentials();
> d.DataSourceName = dbName;
> d.UserName = userName;
> d.Password = password;
> this.mDataSourceCredentials = new DataSourceCredentials[]{d};
> any help would be greatly appreciated.
>

Problem with regenerated report model.

Hi,

I regenerated a report model (just added a new field) and now the existing reports created using Report Builder no longer run. I get the following error:

An error has occurred during report processing.
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery '').

When I try to open the report in Repoprt Builder, it does not see any of the entities in the model. Instead, it displays "Unknown Entity".

How can I get the reports to be usable again?

Thanks,

Rocco M.

Hi Rocco,

I am also facing the same issue. Did you find the resolution ?

Thanks

Ashutosh

|||

It sounds like you used the Regenerate Model button in Report Manager, which will delete elements of the model if the underlying schema element is no longer present. The Generate command in a VS Report Model project does not do this -- it is only additive.

It appears model regeneration was unable to discover any tables/columns in the underlying database. I don't know why this might have been the case (messed up connection string?), but unfortunately the model is gone now, and it will be very tedious to resuscitate your reports. If you have a recent backup of your report server database, you are in much better shape, however.

Server-based model generation is for quick-and-dirty scenarios. If you are serious about developing and deploying reports based on a report model, you should create one in Visual Studio (or create an empty project, download the generated one from the server, and add it to the project). You will have much finer control over the evolution of the model over time, and it will be much easier to maintain backups and/or old versions as needed.

|||

Hi Ashutosh,

I did not find a resolution to this problem. All of the reports created with Report Builder needed to be re-created. This was not a good situation.

All of the changes made to the report model were done using Visual Studio.

To prevent this situation from happening again, I have created a duplicate "development" version of the report model. All proposed changes are first done to this development version and some reports created specifically against this development version are used to verify proper operation. If the verification is successful, the changs are then made to the production version of the report model using the exact same steps performed on the development version.

Regards,

Rocco

Problem with regenerated report model.

Hi,

I regenerated a report model (just added a new field) and now the existing reports created using Report Builder no longer run. I get the following error:

An error has occurred during report processing.
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery '').

When I try to open the report in Repoprt Builder, it does not see any of the entities in the model. Instead, it displays "Unknown Entity".

How can I get the reports to be usable again?

Thanks,

Rocco M.

Hi Rocco,

I am also facing the same issue. Did you find the resolution ?

Thanks

Ashutosh

|||

It sounds like you used the Regenerate Model button in Report Manager, which will delete elements of the model if the underlying schema element is no longer present. The Generate command in a VS Report Model project does not do this -- it is only additive.

It appears model regeneration was unable to discover any tables/columns in the underlying database. I don't know why this might have been the case (messed up connection string?), but unfortunately the model is gone now, and it will be very tedious to resuscitate your reports. If you have a recent backup of your report server database, you are in much better shape, however.

Server-based model generation is for quick-and-dirty scenarios. If you are serious about developing and deploying reports based on a report model, you should create one in Visual Studio (or create an empty project, download the generated one from the server, and add it to the project). You will have much finer control over the evolution of the model over time, and it will be much easier to maintain backups and/or old versions as needed.

|||

Hi Ashutosh,

I did not find a resolution to this problem. All of the reports created with Report Builder needed to be re-created. This was not a good situation.

All of the changes made to the report model were done using Visual Studio.

To prevent this situation from happening again, I have created a duplicate "development" version of the report model. All proposed changes are first done to this development version and some reports created specifically against this development version are used to verify proper operation. If the verification is successful, the changs are then made to the production version of the report model using the exact same steps performed on the development version.

Regards,

Rocco

Problem with ProClarity report 'Export' options

Hi, all here,

Thank you very much for your kind attention.

I encountered a problem with export ProClarity reports. The problem is: under the 'Export' option, there is only 'Export to business reporter'. No other options like: 'Export to reporting service', 'Export to PowerPoint', 'Export to outlook' which actually should be available for ProClarity report 'Export' options tho.

Would please any expert here give me any guidance and advices for that.

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Hi Helen:

The ProClarity "export to" options are usually established on installation of the ProClarity Professional. In the installation process you can choose to install the "Integration with Office" components which provide the Export to Excel, Outlook, and Powerpoint. You can verify that this functionality was NOT installed by looking in the subfolder:

C:\Program Files\Common Files\ProClarity\Client\Office

I have three folders here: "Excel", "Outlook", and "PPoint". Each folder contains a .DLL and supporting files for each flavor of export. If you don't have the folders then re-installing the ProClarity Professional with the correct components selected should suffice to fix the problem. If the folders DO exist then something else is wrong. Your shortest route to fixing the situation may be to uninstall the ProClarity Professional and reinstall with the correct components selected.

Hope this helps.

PGoldy

|||

Hi, Pgoldy,

Thank you very much for your guidance and help.

Yes, I have had a look at the folder(C:\Program Files\ProClarity\Client\), but no 'Office' existed there. So yes would try to resinstall the software as you recommended. Thank you very much.

With best regards,

Yours sincerely,

|||

Sorry, please ignore this post.

With best regards,

Yours sincerely,

Problem with Printing of Crystal Report

Hello

I have an application which uses crystal reports.
Only the runtime dll's are shipped with the application in order to design and also print.

I'm unable to print the report .I guess some mandatory runtime dll's are missed
to ship with the application.

Can anyone help me out in listing all the runtime dll's used for crystal report printing? I use Crystal report 9.0 version dll's.

If I install crystal reportDesigner, then the print option works.

Regards
PallCrystal Reports 8.5 comes with a file called runtime.hlp that lists all the runtime files needed and (I believe) their dependencies. Maybe version 9 has something similar?

I use InstallShield to create my Setups. There's a download for InstallShield that will add all the MergeModules for Crystal Reports. (A MergeModule is a collection of system files and their dependecies.) You just check off the ones you need and it takes care of installing all the dependency files.|||Open your crystal reports in EDIT mode and open the printer / printing options. Uncheck the NoPrint option.

This problem is with CR 8.0.

Problem with passing report parameters using POST request.

Hi

We are accessing reports through our web application. Our front end is implemented using tapestry
Our report is using four parameters. The report url is like
http://<servername>/ReportServer/Pages/ReportViewer.aspx?/<Report_Proj_Name>/<ReportName>

We are using POST requests with our report parameters defined as hidden variables.
If we use GET request, with parameters as query string then we are able to view our reports.

When I tried to access the report through web application, it gives out an error message as:

Reporting Services Error
---

An attempt was made to set a report parameter 'formids' that is not
defined in this report. (rsUnknownReportParameter) Get Online Help

---

For POST request, we get the above mentioned error. When I saw the view source I could see that tapestry
internally sets its own hidden variable for persisting its state in session.
formids is one of the hidden variable set by it.

How should I specify in the report that formids is not one of the report parameter.
There may be other hidden variables other than formids too that may get generated at runtime.
What setting should I do at the report end or at the application end?

Please help me find a solution for this problem.

Thanks!

I would suggest using a separate <form> tag in the application and populating this with just the parameters expected by the report. So I would make the change at the application level.|||

Thanks for your response! but I tried doing that still its not working.

Tapestry at runtime generates those hidden variables.

Problem with Parameterized reports - changing default values

Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
1. When I run CRM3 parameterized reports using the RS Report Manager
interface i get error messages like " The 'CRM_Locale' parameter is missing
a value " or "The 'CRM_Locale' parameter is missing a value", however when
these reports are run from the CRM application or ReportServer URL interface
the reports work fine.
2. I inadvertantly changed one of the report parameter default values in
one of the reports to blank, how can i change it back to its original setting
"Override Default". The report now prints with a blank Prepared By: User Name
value.
Any suggestions would be greatly appreciated
GsBring the report up in the web front end- report manager. Go to the
properties and you can change the default parameters.
Doing so from the Development IDE will NOT change the parameters once the
report has been deployed.
--
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.
"Graham Smith" wrote:
> Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> 1. When I run CRM3 parameterized reports using the RS Report Manager
> interface i get error messages like " The 'CRM_Locale' parameter is missing
> a value " or "The 'CRM_Locale' parameter is missing a value", however when
> these reports are run from the CRM application or ReportServer URL interface
> the reports work fine.
> 2. I inadvertantly changed one of the report parameter default values in
> one of the reports to blank, how can i change it back to its original setting
> "Override Default". The report now prints with a blank Prepared By: User Name
> value.
> Any suggestions would be greatly appreciated
> Gs|||Hi Wayne
> Bring the report up in the web front end- report manager. Go to the
> properties and you can change the default parameters.
>
Tried that, No Joy!
> > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > these reports are run from the CRM application or ReportServer URL interface
> > the reports work fine.
The reports do not render at all. It appears that these parameter values are
not being populated are missing when reports are run using the Report Manager
Interface. Reports without parameters run OK.
> 2. I inadvertantly changed one of the report parameter default values in
> one of the reports to blank, how can i change it back to its original setting
> "Override Default". The report now prints with a blank Prepared By: User Name
> value.
>
I cannot change this back to its original state. The report is CRM 3.0
standard Report "User Summary" the parameter is "CRM_FullName" It originally
had a
"Override Default" command button in the default value for this parameter.
When the reports are run from the CRM 3.0 web interface ot the ReportServer
URL web interface the report renders ok but without the value for the User
Name.
regards
GS
"Wayne Snyder" wrote:
> Bring the report up in the web front end- report manager. Go to the
> properties and you can change the default parameters.
> Doing so from the Development IDE will NOT change the parameters once the
> report has been deployed.
> --
> 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.
>
> "Graham Smith" wrote:
> > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> >
> > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > these reports are run from the CRM application or ReportServer URL interface
> > the reports work fine.
> >
> > 2. I inadvertantly changed one of the report parameter default values in
> > one of the reports to blank, how can i change it back to its original setting
> > "Override Default". The report now prints with a blank Prepared By: User Name
> > value.
> >
> > Any suggestions would be greatly appreciated
> > Gs|||What means "No Joy" exact.
Any error message after clicking "Apply" in the properties page?
You need to go to properties after open the report and don't click on execute.
"Graham Smith" wrote:
> Hi Wayne
> > Bring the report up in the web front end- report manager. Go to the
> > properties and you can change the default parameters.
> >
> Tried that, No Joy!
> > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > these reports are run from the CRM application or ReportServer URL interface
> > > the reports work fine.
> The reports do not render at all. It appears that these parameter values are
> not being populated are missing when reports are run using the Report Manager
> Interface. Reports without parameters run OK.
> >
> > 2. I inadvertantly changed one of the report parameter default values in
> > one of the reports to blank, how can i change it back to its original setting
> > "Override Default". The report now prints with a blank Prepared By: User Name
> > value.
> >
> I cannot change this back to its original state. The report is CRM 3.0
> standard Report "User Summary" the parameter is "CRM_FullName" It originally
> had a
> "Override Default" command button in the default value for this parameter.
> When the reports are run from the CRM 3.0 web interface ot the ReportServer
> URL web interface the report renders ok but without the value for the User
> Name.
> regards
> GS
>
> "Wayne Snyder" wrote:
> > Bring the report up in the web front end- report manager. Go to the
> > properties and you can change the default parameters.
> >
> > Doing so from the Development IDE will NOT change the parameters once the
> > report has been deployed.
> > --
> > 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.
> >
> >
> > "Graham Smith" wrote:
> >
> > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > >
> > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > these reports are run from the CRM application or ReportServer URL interface
> > > the reports work fine.
> > >
> > > 2. I inadvertantly changed one of the report parameter default values in
> > > one of the reports to blank, how can i change it back to its original setting
> > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > value.
> > >
> > > Any suggestions would be greatly appreciated
> > > Gs|||Hi
No Joy means it did not work!
There were no error messages after hitting the "Apply" button in the
Properties.
I cant set the Default Value back to its original setting, so the reports
being rendered by the CRM 3.0 Reports function and Reports Web browser URL
Interface are incorrect.
I can send screen shots of the problem if you can send an email alias to
reply to.
Thanks
GS
"Dev Main" wrote:
> What means "No Joy" exact.
> Any error message after clicking "Apply" in the properties page?
> You need to go to properties after open the report and don't click on execute.
> "Graham Smith" wrote:
> > Hi Wayne
> >
> > > Bring the report up in the web front end- report manager. Go to the
> > > properties and you can change the default parameters.
> > >
> >
> > Tried that, No Joy!
> >
> > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > these reports are run from the CRM application or ReportServer URL interface
> > > > the reports work fine.
> >
> > The reports do not render at all. It appears that these parameter values are
> > not being populated are missing when reports are run using the Report Manager
> > Interface. Reports without parameters run OK.
> >
> > >
> > > 2. I inadvertantly changed one of the report parameter default values in
> > > one of the reports to blank, how can i change it back to its original setting
> > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > value.
> > >
> >
> > I cannot change this back to its original state. The report is CRM 3.0
> > standard Report "User Summary" the parameter is "CRM_FullName" It originally
> > had a
> > "Override Default" command button in the default value for this parameter.
> >
> > When the reports are run from the CRM 3.0 web interface ot the ReportServer
> > URL web interface the report renders ok but without the value for the User
> > Name.
> >
> > regards
> > GS
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Bring the report up in the web front end- report manager. Go to the
> > > properties and you can change the default parameters.
> > >
> > > Doing so from the Development IDE will NOT change the parameters once the
> > > report has been deployed.
> > > --
> > > 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.
> > >
> > >
> > > "Graham Smith" wrote:
> > >
> > > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > > >
> > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > these reports are run from the CRM application or ReportServer URL interface
> > > > the reports work fine.
> > > >
> > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > one of the reports to blank, how can i change it back to its original setting
> > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > value.
> > > >
> > > > Any suggestions would be greatly appreciated
> > > > Gs|||Sorry, I don't have the CRM Application.
But I had similare experience with parameters. If I changed parameter
settings on deployed report, the changes where not deployed.
Since this I allways need to change the parameter in the Report Manager
manuell after redeploy those reports. After manuell change of the parameters
I can see the changes in the parameter section of the report. And the URL
Access works again.
Whats a bit difficult is the way "allow null" settings are displayed in the
report manager. If I like to allow null I need to activate "have dafault" and
the value field must be empty. In the designer it is required to set Default
values to non and activate "Allow Null" and "Allow blank".
I assume if you apply the correct settings in the report manager for the
parameter, the url access must work.
"Graham Smith" wrote:
> Hi
> No Joy means it did not work!
> There were no error messages after hitting the "Apply" button in the
> Properties.
> I cant set the Default Value back to its original setting, so the reports
> being rendered by the CRM 3.0 Reports function and Reports Web browser URL
> Interface are incorrect.
> I can send screen shots of the problem if you can send an email alias to
> reply to.
> Thanks
> GS
>
>
> "Dev Main" wrote:
> > What means "No Joy" exact.
> >
> > Any error message after clicking "Apply" in the properties page?
> >
> > You need to go to properties after open the report and don't click on execute.
> >
> > "Graham Smith" wrote:
> >
> > > Hi Wayne
> > >
> > > > Bring the report up in the web front end- report manager. Go to the
> > > > properties and you can change the default parameters.
> > > >
> > >
> > > Tried that, No Joy!
> > >
> > > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > > these reports are run from the CRM application or ReportServer URL interface
> > > > > the reports work fine.
> > >
> > > The reports do not render at all. It appears that these parameter values are
> > > not being populated are missing when reports are run using the Report Manager
> > > Interface. Reports without parameters run OK.
> > >
> > > >
> > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > one of the reports to blank, how can i change it back to its original setting
> > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > value.
> > > >
> > >
> > > I cannot change this back to its original state. The report is CRM 3.0
> > > standard Report "User Summary" the parameter is "CRM_FullName" It originally
> > > had a
> > > "Override Default" command button in the default value for this parameter.
> > >
> > > When the reports are run from the CRM 3.0 web interface ot the ReportServer
> > > URL web interface the report renders ok but without the value for the User
> > > Name.
> > >
> > > regards
> > > GS
> > >
> > >
> > >
> > > "Wayne Snyder" wrote:
> > >
> > > > Bring the report up in the web front end- report manager. Go to the
> > > > properties and you can change the default parameters.
> > > >
> > > > Doing so from the Development IDE will NOT change the parameters once the
> > > > report has been deployed.
> > > > --
> > > > 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.
> > > >
> > > >
> > > > "Graham Smith" wrote:
> > > >
> > > > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > > > >
> > > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > > these reports are run from the CRM application or ReportServer URL interface
> > > > > the reports work fine.
> > > > >
> > > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > > one of the reports to blank, how can i change it back to its original setting
> > > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > > value.
> > > > >
> > > > > Any suggestions would be greatly appreciated
> > > > > Gs

Problem with parameter default when redeploying report

I've written a rss script file to automate publishing of reports to
our server. I've encountered a problem when republishing reports that
have a default value set for a parameter.
If I change the default value of the parameter in the report rdl and
then try to republish, that parameter's default value is not getting
changed on the server. If I add or remove parameters then the server
gets updated correctly. It's only if I change the default value that
the update is not happening.
I've tried with both the CreateReport and SetReportDefinition
functions. The only way I've gotten this to work is to delete the
report and then republish but this is not an acceptable solution
because it also deletes report history and subscriptions.
I'm using RS2005.
Any help is appreciated.Default values for parameters are a little like datasources, in that you
have to explicitly indicate that you want to override a previous definition
of a datasource when you re-publish a report to a server. Basically the idea
is that your testbed, from which you publish, may not be the same as the
server environment, and you want to keep those things separate, and I'm
saying that parameters' defaults are treated like datasources in this
respect.
OK so far?
If you were handling this interactively using the Report Manager interface,
and assuming you have appropriate rights, you know that you can see the Data
Sources and configure them from the Properties tab of a report. Again, the
assumption is not made that the data source information for this report is
re-deployable and automatically written from your test bed.
Similarly, if a report has parameters, when you have selected the Properties
tab, you should see a Parameters item in the left-hand menu along with Data
Sources. Here you can set the default values differently from how they are
currently set -- I don't really understand whether "Override default" works
all the time or not, you will see it in the dialog, though. Never mind.
Here is where you can fix whatever you don't like about how the report got
re-published.
However, you say "I've tried with both the CreateReport and
SetReportDefinition functions", indicating that you are using web services
rather than interactively publishing. I understand this -- just consider
the above explanation a way to conceptualize *why* the parameters work the
way they do and require a separate step, rather than the way you expected.
I think that you may need to use the .SetReportParameters method here,
explicitly providing the new information to indicate that, yes, you want to
change the default values. If not, it may be the .SetProperties method.
I hope this works for you -- if not, you may be able to use the explanation
above to figure out the correct web service approach <s>.
>L<
<bruce42@.gmail.com> wrote in message
news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
> I've written a rss script file to automate publishing of reports to
> our server. I've encountered a problem when republishing reports that
> have a default value set for a parameter.
> If I change the default value of the parameter in the report rdl and
> then try to republish, that parameter's default value is not getting
> changed on the server. If I add or remove parameters then the server
> gets updated correctly. It's only if I change the default value that
> the update is not happening.
> I've tried with both the CreateReport and SetReportDefinition
> functions. The only way I've gotten this to work is to delete the
> report and then republish but this is not an acceptable solution
> because it also deletes report history and subscriptions.
> I'm using RS2005.
> Any help is appreciated.
>|||So, from what you're saying, it's intentional that the parameter
defaults are not being overriden. You mention an "override defaults",
can you be more specific? I can see an "OverwriteDataSources" if I
publish straight from VS, but I've found nothing regarding overwriting
parameter defaults.
I'm familiar with the SetReportParameters method, what I'm trying to
do is avoid writing specific scripts for each report that I need to
publish. The rss script I use to publish now is a generic script that
I can use against any of my reports. Setting the default values
manually via the report manager interface is not really an option for
me. For one reason, it introduces the possibility of me setting
values differently than what was actually used in our test
environment, and two, we are using query based defaults and the report
manager interface doesn't give you enough detail to even be able to
make these changes (i.e. it doesn't show dataset or value field).
Currently the best idea I have for how to resolve this issue is to
publish my report to a temporary location (where it does not already
exist), loop through all of the parameters and capture the defaults, I
can then publish the report to it's normal location and do a
SetReportParameters using the default values I collected. I'm not
especially happy with this approach, it feels a little kludgy to me,
but at least it keeps me from having to write report specific rss
scripts.
Any other ideas?
thanks for your response...
-bruce
On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Defaultvalues for parameters are a little like datasources, in that you
> have to explicitly indicate that you want to override a previous definition
> of a datasource when you re-publish a report to a server. Basically the idea
> is that your testbed, from which you publish, may not be the same as the
> server environment, and you want to keep those things separate, and I'm
> saying that parameters' defaults are treated like datasources in this
> respect.
> OK so far?
> If you were handling this interactively using the Report Manager interface,
> and assuming you have appropriate rights, you know that you can see the Data
> Sources and configure them from the Properties tab of a report. Again, the
> assumption is not made that the data source information for this report is
> re-deployable and automatically written from your test bed.
> Similarly, if a report has parameters, when you have selected the Properties
> tab, you should see a Parameters item in the left-hand menu along with Data
> Sources. Here you can set thedefaultvalues differently from how they are
> currently set -- I don't really understand whether "Overridedefault" works
> all the time or not, you will see it in the dialog, though. Never mind.
> Here is where you can fix whatever you don't like about how the report got
> re-published.
> However, you say "I've tried with both the CreateReport and
> SetReportDefinition functions", indicating that you are using web services
> rather than interactively publishing. I understand this -- just consider
> the above explanation a way to conceptualize *why* the parameters work the
> way they do and require a separate step, rather than the way you expected.
> I think that you may need to use the .SetReportParameters method here,
> explicitly providing the new information to indicate that, yes, you want to
> change thedefaultvalues. If not, it may be the .SetProperties method.
> I hope this works for you -- if not, you may be able to use the explanation
> above to figure out the correct web service approach <s>.
> >L<
> <bruc...@.gmail.com> wrote in message
> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>
> > I've written a rss script file to automate publishing of reports to
> > our server. I've encountered a problem when republishing reports that
> > have adefaultvalue set for aparameter.
> > If I change thedefaultvalue of theparameterin the report rdl and
> > then try to republish, thatparameter'sdefaultvalue is not getting
> > changed on the server. If I add or remove parameters then the server
> > gets updated correctly. It's only if I change thedefaultvalue that
> > the update is not happening.
> > I've tried with both the CreateReport and SetReportDefinition
> > functions. The only way I've gotten this to work is to delete the
> > report and then republish but this is not an acceptable solution
> > because it also deletes report history and subscriptions.
> > I'm using RS2005.
> > Any help is appreciated.- Hide quoted text -
> - Show quoted text -|||Hi Bruce,
>> Any other ideas?
Forgive my lateness of reply (I will CC your e-mail to make sure you see
this) -- I don't get to the forum all that often.
AFAIK you are correct about not having the Overwrite parameter option to
match the Overwrite datasources when you publish straight from VS. When I
mentioned the "override defaults" I was talking only about the interactive
manager interface.
I do agree with you not only that it is confusing but also that, in most
cases, it is not advisable to set values differently in the test environment
than you would set in production. However -- bear with me, I am trying to
envision what was supposed to be the purpose of this "feature" -- we can
imagine that the designers of this system thought it *was* a good idea to
have an "override defaults" so that you could manage the report differently
on different servers, whether for test versus production or deployment of a
generic report to different customers.
For example there might be a sample size used for the test box that would be
different from production, or some sort of customer-specific value that you
wanted to use to brand a generic report.
Now to address your question...
For your generic script purposes, you might need to do something similar to
reflection to "publish" each report.
So far, I'm just restating something that you may have already tried with
your "publish to a temporary location". You could obviously pull the
parameters out of the appropriate Catalog field on the server, or use
GetReportParameters, if you've done that.
However, you don't really need to do that. Remember that the parameters
exist in the RDL, without publication, as a set of XML nodes. So, without
temporarily publishing anywhere, you should be able to read them out of the
RDL and issue the appropriate calls to set them properly on the target.
I hope this makes sense. If you like, you can e-mail me to discuss further
if it doesn't <g>. Again, I don't get here all that much...
>L<
<bruce42@.gmail.com> wrote in message
news:1176117653.014697.204110@.w1g2000hsg.googlegroups.com...
> So, from what you're saying, it's intentional that the parameter
> defaults are not being overriden. You mention an "override defaults",
> can you be more specific? I can see an "OverwriteDataSources" if I
> publish straight from VS, but I've found nothing regarding overwriting
> parameter defaults.
> I'm familiar with the SetReportParameters method, what I'm trying to
> do is avoid writing specific scripts for each report that I need to
> publish. The rss script I use to publish now is a generic script that
> I can use against any of my reports. Setting the default values
> manually via the report manager interface is not really an option for
> me. For one reason, it introduces the possibility of me setting
> values differently than what was actually used in our test
> environment, and two, we are using query based defaults and the report
> manager interface doesn't give you enough detail to even be able to
> make these changes (i.e. it doesn't show dataset or value field).
> Currently the best idea I have for how to resolve this issue is to
> publish my report to a temporary location (where it does not already
> exist), loop through all of the parameters and capture the defaults, I
> can then publish the report to it's normal location and do a
> SetReportParameters using the default values I collected. I'm not
> especially happy with this approach, it feels a little kludgy to me,
> but at least it keeps me from having to write report specific rss
> scripts.
> Any other ideas?
> thanks for your response...
> -bruce
> On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Defaultvalues for parameters are a little like datasources, in that you
>> have to explicitly indicate that you want to override a previous
>> definition
>> of a datasource when you re-publish a report to a server. Basically the
>> idea
>> is that your testbed, from which you publish, may not be the same as the
>> server environment, and you want to keep those things separate, and I'm
>> saying that parameters' defaults are treated like datasources in this
>> respect.
>> OK so far?
>> If you were handling this interactively using the Report Manager
>> interface,
>> and assuming you have appropriate rights, you know that you can see the
>> Data
>> Sources and configure them from the Properties tab of a report. Again,
>> the
>> assumption is not made that the data source information for this report
>> is
>> re-deployable and automatically written from your test bed.
>> Similarly, if a report has parameters, when you have selected the
>> Properties
>> tab, you should see a Parameters item in the left-hand menu along with
>> Data
>> Sources. Here you can set thedefaultvalues differently from how they are
>> currently set -- I don't really understand whether "Overridedefault"
>> works
>> all the time or not, you will see it in the dialog, though. Never mind.
>> Here is where you can fix whatever you don't like about how the report
>> got
>> re-published.
>> However, you say "I've tried with both the CreateReport and
>> SetReportDefinition functions", indicating that you are using web
>> services
>> rather than interactively publishing. I understand this -- just consider
>> the above explanation a way to conceptualize *why* the parameters work
>> the
>> way they do and require a separate step, rather than the way you
>> expected.
>> I think that you may need to use the .SetReportParameters method here,
>> explicitly providing the new information to indicate that, yes, you want
>> to
>> change thedefaultvalues. If not, it may be the .SetProperties method.
>> I hope this works for you -- if not, you may be able to use the
>> explanation
>> above to figure out the correct web service approach <s>.
>> >L<
>> <bruc...@.gmail.com> wrote in message
>> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>>
>> > I've written a rss script file to automate publishing of reports to
>> > our server. I've encountered a problem when republishing reports that
>> > have adefaultvalue set for aparameter.
>> > If I change thedefaultvalue of theparameterin the report rdl and
>> > then try to republish, thatparameter'sdefaultvalue is not getting
>> > changed on the server. If I add or remove parameters then the server
>> > gets updated correctly. It's only if I change thedefaultvalue that
>> > the update is not happening.
>> > I've tried with both the CreateReport and SetReportDefinition
>> > functions. The only way I've gotten this to work is to delete the
>> > report and then republish but this is not an acceptable solution
>> > because it also deletes report history and subscriptions.
>> > I'm using RS2005.
>> > Any help is appreciated.- Hide quoted text -
>> - Show quoted text -
>