NoRows Property in Matrix does not work

I wanted to set the NoRows property for a matrix on a report to inform
the user when this condition occurs. I run the report by supplying
parameters that I am sure returns no data. Specifically, I am
determining the table to query based on user inputs. I provide inputs
that will look for a non existent table. I do an existence check on
the table to see if it is there to prevent a sql server error. If it
finds no table the query is not run. However, given that nothing is
returned the norows property is ignored and I get the error "Invalid
attempt to read when no data is present."
Could it be because the matrix requires an empty dataset back?That is what it was. I now ensure I return an empty dataset if the
table does not exist.

non-required parameter

I using BI development studio to create some reports and one of the stored procedures that I have takes in 4 parameters.

ManagerID int =0
OfficeID int =0
StartDate datetime
EndDate datetime

In the report designer I use 2 other sql queries to populate ManagerID fields and OfficeID Fields.

The report will work if the customer selects both ManagerID and OfficeID fields but will not work if Manager OR OfficeID fields are select. They both have to be selected or an error message will be displayed stating that the non-selected field must have a value.

The the parameters properties section I have changed the type is integer and select allowed NULLs, I have even set the default value to 0 this didn't work. Then I tried changing the type from integer to string and selected 'allowed blanks'.

I still get the popup message stating that the non-select item must have a value.

Is there a way I can select 1 and not the other?

why not to try to add those drop down list to the web page not in the report and pass the selected parameters to the report file

Sample code

Private Sub SetReportParameters()
Dim userid As New ReportParameter("userid",
Security.GetUserID().ToString())
Dim p() As ReportParameter = {userid}
ReportViewer1.ServerReport.SetParameters(p)
End Sub

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

http://www.simple-talk.com/sql/learn-sql-server/beginning-sql-server-2005-reporting-services-part-1/

|||

Tthank you for the reply.

One thing I should have mentioned is that we are using ColdFusion 7 with SQL 2005.
I am still learning a few things about it.
If will try passing in those parameters but I am not sure how it will work with this structure.

|||

I don't think there is any problem but i cam through great web cast for this hope it helps you

SQL Server Reporting Services for Cold Fusion Developers
https://www119.livemeeting.com/cc/mseventsbmo/view?id=1032256029&role=attend&pw=7933C65D

|||

Thank you for the link, I have been looking for this video for awhile and all I got was a word document of the webcast.

I will be watching it soon.

I will mark this as answered.

Non-queried default values not working

This is driving me crazy. I can't get non-queried default values to
work with non-queried string report parameters. I'm using Reporting
Services 2000. Here's a test case: I have a simple Report Parameter: a
string, called "Color". It has three non-queried label-value pairs:
Red-red, Green-green, Blue-blue. I then try to define a default
non-queried value of green. Run the report, it always says "Select a
value". If I change the label-values to single-digit values, like
this: Red-1, Green-2, Blue-3, and set the default value to 2, then
Green comes up as the selected value (which is what I want). But I
cannot use a string for the value - it never matches. I've also tried
two-digit numbers: Red-11, Green-22, Blue-33, and used 22 as the
default. Comes up with "Select a value".
Can you not use strings as default values?Found the answer myself. Yes, you can use strings as default values,
as you would expect. Everything worked great in the Preview pane of
Visual Studio (the defaults would show properly) but when I put the
report on the report server, the dropdowns would say "Select a value".
To fix it, I had to completely delete the report from the server, and
upload a brand new copy of the RDL file. It did not work to simply use
the "Update" option in the Report Properties web page on the server.
It would update the report (i.e., I could see other changes I made) but
for some reason, the parameters would not work properly until I
completed deleted the report and uploaded it again. Go figure.

Non value-type parameters in Assemblies/ CLR UDFs:

I am building a simple utitility to be hosted as a database assembly in SASS 2005. This is a .Net Assembly]

My method is date dependent and I want to pass in a DateTime rather than a string (globalisation issues.. you know the story). Can I do this and how would I pass this parameter in an MDX query?

I also want to pass in such thigs as the StorageMode enum etc. Presumably I can pass in the integer value... (kinda defeats the reason for using enums though)

Update to this, I find that you can pass the DateTime as a string and SSAS does some sort of implicit cast. this is still very sensitive to date string formatting, so is not ideal!|||

Well, it seems that after due consideration I have to answer my own post!

I think the problem is that we can only really pass in basic value types via text in queries, essentially strings and numbers. If I really want to pass in a class/struct I could pass in a string representing the XML Serialized class. For example, if I want to pass in a DateTime rather than a string, I could create an XML version of the DateTime structure. Of course, the alternative for dates would be to specify a universal format, such at the ISO format (yyyy-mm-dd) and ensure that this is rule is always adhered to, but this is coding by convention rather than using strong typing.

Now, my only problem is how to return data in a strongly typed format.... now the XML serialisation seems to be the answer to this one...

no working drilldown in reportViewer, ASP.NET

I have Report A and report B.
Report A has under "navigation->Jump To Report", report B selected from the
list. I also used the parameters button to insert both parameters required
by Report B.
Deploy both reports.
On the catalog (server/reports/<folder>/A), I run the report A and can drill
to report B.
PROBLEM:
When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
reportViewer (report viewer) in my asp.net page to show the report. When I
drill down on report A, the frame of the report seems to be reloading (get
the green thing) but then the same reports appears!
Additional info: I tried adding to my asp.net page code a handler for the
ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
function. But when I put a breakpoint there, it seems I don't even get
there. In any case, all the samples I saw use this function for windows
forms, and then they set the datasource which seems like something I
shouldn't be bothered with.
anyone can help?
regards.I am facing the same problem, if you got the solution pls let me know. Thanx
in advance.
Regards
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>|||We finally got the solution.
Pls check whether the report A is called only once, use postback.
"csmba" wrote:
> I have Report A and report B.
> Report A has under "navigation->Jump To Report", report B selected from the
> list. I also used the parameters button to insert both parameters required
> by Report B.
> Deploy both reports.
> On the catalog (server/reports/<folder>/A), I run the report A and can drill
> to report B.
> PROBLEM:
> When I go to my ASP.NET application, it doesn't;t work. NOTICE I use
> reportViewer (report viewer) in my asp.net page to show the report. When I
> drill down on report A, the frame of the report seems to be reloading (get
> the green thing) but then the same reports appears!
> Additional info: I tried adding to my asp.net page code a handler for the
> ReportViewer1_Drillthrough(object sender, DrillthroughEventArgs e)
> function. But when I put a breakpoint there, it seems I don't even get
> there. In any case, all the samples I saw use this function for windows
> forms, and then they set the datasource which seems like something I
> shouldn't be bothered with.
> anyone can help?
> regards.
>
>

No value given for one or more required parameters.

HI everyone, pls can u tell me whats the error in my code?

I am connecting to an oledb database

sql_edit_record = "UPDATE Anamnese SET ";

sql_edit_record += "ID=?,";

sql_edit_record += "Sexe = ?,";

sql_edit_record += "[Date de Naissance] = ?,";

sql_edit_record += "Classe = ?,";

sql_edit_record += "Ecole = ?,";

sql_edit_record += "Adresse = ?,";

sql_edit_record += "Telphone = ?,";

sql_edit_record += "[Cot de signalement] = ?,";

sql_edit_record += "[Motif de consultation] = ?,";

sql_edit_record += "[Histoire familale] = ?,";

sql_edit_record += "[Histoire mdicale] = ?,";

sql_edit_record += "[Histoire dveloppementale] = ?,";

sql_edit_record += "[Histoire scolaire] = ?,";

sql_edit_record += "[Situation socio-conomique] = ?,";

sql_edit_record += "[Histoire comportementale] = ?";

sql_edit_record += "WHERE Nom = ?";

//sql_edit_record = "UPDATE Anamnese SET Sexe = 'M' WHERE Nom = 'Dory'";

db_adapter.UpdateCommand.Connection = oledb_connection;

db_adapter.UpdateCommand.CommandText = sql_edit_record;

using (db_adapter.UpdateCommand)

{

textBox1.Text = sql_edit_record;

db_adapter.InsertCommand.Parameters.Add("@.ID", OleDbType.VarChar).Value = current_row.ToString();

db_adapter.InsertCommand.Parameters.Add("@.SEXE", OleDbType.VarChar).Value = sexe;

db_adapter.UpdateCommand.Parameters.Add("@.DATEDENAISSANCE", OleDbType.VarChar).Value = date_de_naissance;

db_adapter.UpdateCommand.Parameters.Add("@.CLASSE", OleDbType.VarChar).Value = classe;

db_adapter.UpdateCommand.Parameters.Add("@.ECOLE", OleDbType.VarChar).Value = ecole;

db_adapter.UpdateCommand.Parameters.Add("@.ADRESSE", OleDbType.VarChar).Value = adresse;

db_adapter.UpdateCommand.Parameters.Add("@.TELEPHONE", OleDbType.VarChar).Value = telephone;

db_adapter.UpdateCommand.Parameters.Add("@.SIGNALEMENT", OleDbType.VarChar).Value = signalement;

db_adapter.UpdateCommand.Parameters.Add("@.MOTIF", OleDbType.VarChar).Value = consultation;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_FAM", OleDbType.VarChar).Value = his_familiale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_MED", OleDbType.VarChar).Value = his_medicale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_DEV", OleDbType.VarChar).Value = his_developpementale;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_SCOL", OleDbType.VarChar).Value = his_scolaire;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_SOCIO_ECO", OleDbType.VarChar).Value = his_socio_eco;

db_adapter.UpdateCommand.Parameters.Add("@.HIS_COMPORTEMENTALE", OleDbType.VarChar).Value = his_comportementale;

db_adapter.UpdateCommand.Parameters.Add("@.NOM", OleDbType.VarChar).Value =cbox_name.Text;

db_adapter.UpdateCommand.Connection.Open();

db_adapter.UpdateCommand.ExecuteNonQuery();

db_adapter.UpdateCommand.Connection.Close();

}

Without seeing any error messages it's hard to tell, however the first thing that stands out is the following:

sql_edit_record += "[Histoire comportementale] = ?";

sql_edit_record += "WHERE Nom = ?";

You should insert a space after the question mark on the first line, like so:

sql_edit_record += "[Histoire comportementale] = ? ";

sql_edit_record += "WHERE Nom = ?";

You may also find that you have to wrap the placeholders in single quotes where your table columns are of a textual datatype, for instance:

sql_edit_record += "[Histoire comportementale] = '?' ";

sql_edit_record += "WHERE Nom = '?'";

Is there a reason that you aren't using stored procedures to perform this task?

Chris

|||

Thanks for ur reply, the error is :

OleDBException was Unhandled

No value given for one or more required parameters.

Allthough I put the space after the ?.

|||

Sorry to state the obvious, but could the problem be that you haven't assigned values to all of the parameters?

Thanks
Chris

|||

Would an empty string("") raise such an error?