Case Statements

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

Quote:

Originally Posted by anishap

Can anyone help me with the case statements?
I have customized a new report for our application (vendor software). I want the end user to check 3 boxes to print (they can choose all or one or two).

Below is the case statement I tired in the where clause

Where
STATUS = CASE
WHEN REPORT.SEPARATED = 'y' THEN 'S'
WHEN REPORT.TERMINATED = 'y' THEN 'T'
WHEN REPORT.RETIRED ='y' THEN 'R'
END

I want the user to check one box , two or 3 . Rightnow it is working fine if any one option is checked. Please let me if there is any other way to do this.

many thanks

i think this would be better if you build the WHERE from your form/gui. you have might have to adjust the returned value of your checkbox. depending on your front-end tool.

something like

queryvar = queryvar + ' WHERE STATUS IN ('
for counter = numbercheckbox
if checkbox is checked
queryvar = queryvar + returnedvalueofcheckbox

queryvar = queryvar + ')'

this is a psedocode. am suggesting the technique, not the syntax. depending on your gui, you're going to have to adjust it accordingly

Case Function on a Cell

Hi all,
have a Matrix-based report that tracks project's %complete by period id for a
particular year, as follows:
- 2005
---
01 02 03 04 05 06 07 08 09 10 11 12
project1 10% 12% 18% 25% 38% 52%
project2 13% 29% 68% 89% 100%
project3 25% 68% 100%
:
:
:
I need to know how to change the header grop of Period_id "J F M A M J J A S
O N D" instead of "01 02 03 04 05 06 07 08 09 10 11 12" inside a statement
in the report, since i can not control the sql statement that feeds the
report.
Thank you in advance
Tony
--
Message posted via http://www.sqlmonster.comHave a look at my reply for "Proper syntax of CASE()" etc - in other words
write a little VB .net function in the Report's code box, then call the
funtion to return a string value
"Antony Altobelli via SQLMonster.com" wrote:
> Hi all,
> have a Matrix-based report that tracks project's %complete by period id for a
> particular year, as follows:
> - 2005
> ---
> 01 02 03 04 05 06 07 08 09 10 11 12
> project1 10% 12% 18% 25% 38% 52%
> project2 13% 29% 68% 89% 100%
> project3 25% 68% 100%
> :
> :
> :
> I need to know how to change the header grop of Period_id "J F M A M J J A S
> O N D" instead of "01 02 03 04 05 06 07 08 09 10 11 12" inside a statement
> in the report, since i can not control the sql statement that feeds the
> report.
> Thank you in advance
> Tony
> --
> Message posted via http://www.sqlmonster.com
>

Cascading Prompts

Hi everyone
I've a report set up which requires cascading prompts to select report
criteria. This works fine on my development box, but not once it's been
deployed to the report server.
The error I'm getting is:
One or more data sources is missing credentials.
The scenario is that a list of available years is pulled from the database
(Sql Server), the selected year is passed into a stored procedure to pull out
a list of available, which in turn is used to pull back available days.
I'm sure I've simply over looked something in the data source setup.
I'm using a custom data source, with credentials stored on the server. The
account details are for a domain admin account, with full rights on all boxes.
I have checked "Use as Windows credentials when connecting to the data source"
Any ideas?
TIA
JoeJust go to the datasource again and retype your passwords and save it. When
going to reports you must have just gone into datasource credential part as
well. so it assumes that you are going to enter a new one.
Amarnath
"Joe" wrote:
> Hi everyone
> I've a report set up which requires cascading prompts to select report
> criteria. This works fine on my development box, but not once it's been
> deployed to the report server.
> The error I'm getting is:
> One or more data sources is missing credentials.
> The scenario is that a list of available years is pulled from the database
> (Sql Server), the selected year is passed into a stored procedure to pull out
> a list of available, which in turn is used to pull back available days.
> I'm sure I've simply over looked something in the data source setup.
> I'm using a custom data source, with credentials stored on the server. The
> account details are for a domain admin account, with full rights on all boxes.
> I have checked "Use as Windows credentials when connecting to the data source"
> Any ideas?
> TIA
> Joe
>|||Hi Amarnath
Unfortunately, I've tried that, and still no joy. It's as though the
credentials a being dropped when the page refreshes. When the report first
loads up, it queries the database to get the values for the first drop down
list. This works fine, so the credentials are OK at that point. Once a value
is selected from the first drop down list, I need to use that value to
populate the second. It's at this point I'm getting the error message.
Joe
"Amarnath" wrote:
> Just go to the datasource again and retype your passwords and save it. When
> going to reports you must have just gone into datasource credential part as
> well. so it assumes that you are going to enter a new one.
> Amarnath
> "Joe" wrote:
> > Hi everyone
> >
> > I've a report set up which requires cascading prompts to select report
> > criteria. This works fine on my development box, but not once it's been
> > deployed to the report server.
> >
> > The error I'm getting is:
> >
> > One or more data sources is missing credentials.
> >
> > The scenario is that a list of available years is pulled from the database
> > (Sql Server), the selected year is passed into a stored procedure to pull out
> > a list of available, which in turn is used to pull back available days.
> >
> > I'm sure I've simply over looked something in the data source setup.
> >
> > I'm using a custom data source, with credentials stored on the server. The
> > account details are for a domain admin account, with full rights on all boxes.
> >
> > I have checked "Use as Windows credentials when connecting to the data source"
> >
> > Any ideas?
> >
> > TIA
> >
> > Joe
> >|||check the name of the stored procedure. We had this issue when some
users were not creating the procedures using "dbo.spname".
It works on your box if you were the one who created the procedure, but
will not run when you upload the report to the report server.
Joe wrote:
> Hi Amarnath
> Unfortunately, I've tried that, and still no joy. It's as though the
> credentials a being dropped when the page refreshes. When the report first
> loads up, it queries the database to get the values for the first drop down
> list. This works fine, so the credentials are OK at that point. Once a value
> is selected from the first drop down list, I need to use that value to
> populate the second. It's at this point I'm getting the error message.
> Joe
> "Amarnath" wrote:
> > Just go to the datasource again and retype your passwords and save it. When
> > going to reports you must have just gone into datasource credential part as
> > well. so it assumes that you are going to enter a new one.
> >
> > Amarnath
> >
> > "Joe" wrote:
> >
> > > Hi everyone
> > >
> > > I've a report set up which requires cascading prompts to select report
> > > criteria. This works fine on my development box, but not once it's been
> > > deployed to the report server.
> > >
> > > The error I'm getting is:
> > >
> > > One or more data sources is missing credentials.
> > >
> > > The scenario is that a list of available years is pulled from the database
> > > (Sql Server), the selected year is passed into a stored procedure to pull out
> > > a list of available, which in turn is used to pull back available days.
> > >
> > > I'm sure I've simply over looked something in the data source setup.
> > >
> > > I'm using a custom data source, with credentials stored on the server. The
> > > account details are for a domain admin account, with full rights on all boxes.
> > >
> > > I have checked "Use as Windows credentials when connecting to the data source"
> > >
> > > Any ideas?
> > >
> > > TIA
> > >
> > > Joe
> > >|||Was this issue resolved? As I seem to be having the same problem. I have just
upgraded to the SP2 CTP and when I use cascading parameters I get the same
error "One or more data sources is missing credentials". I have tried
various options on my data source including windows integrated and sql logins
but problem remains.
Regards,
David S

Cascading Parameters, Drop down lists & the use of "ALL"

I have a report I'm working on (data dictionary) where the team wants to be
able to do the following:
1) Choose "All Tables" and get a report listing all tables with each tables
column names & properties underneath the proper table (ie, the columnName
param would be greyed out if this is chosen).
OR
2) Choose a single table by name, then choose "All Columns" and get a report
with ONLY that table and only the columns belonging to that table underneath
it with their properties.
OR
3) Choose a single table by name, then choose a single column by name and
only get that single column's properites.
Obviously, 3 is the easiest and what I was able to get done first. I have a
seperate report (my first report) where I've got a list of all tables with
drilldown enabled that shows each table's columns (all of them) when
expanded. Of course, once I got that report done, I was asked to add this
additional functionality.
Can this be done in one report or will I need to resort to multiple,
seperate reports to accomplish this?
I've got the code written for "All Tables" and "All Columns", but when I try
to pick a single table, I still get a ColumnList that lists all columns in
the entire DB, not the columns specific for that table.
Can someone give me advice on how to accomplish what I need? Thanks!!!
CatadminWe do something similar (although no cascading parameters). For "All", we
pass in the value zero, and the query is written like this:
Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
If you're doing actual database objects and are using the sysobjects and
syscolumns tables, I would imagine your syscolumns query would include a
similar clause:
Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid =@.ColumnID)
I don't use cascading parameters, so I may be completely missing the point,
and if so, I apologize...
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
>I have a report I'm working on (data dictionary) where the team wants to be
> able to do the following:
> 1) Choose "All Tables" and get a report listing all tables with each
> tables
> column names & properties underneath the proper table (ie, the columnName
> param would be greyed out if this is chosen).
> OR
> 2) Choose a single table by name, then choose "All Columns" and get a
> report
> with ONLY that table and only the columns belonging to that table
> underneath
> it with their properties.
> OR
> 3) Choose a single table by name, then choose a single column by name and
> only get that single column's properites.
> Obviously, 3 is the easiest and what I was able to get done first. I have
> a
> seperate report (my first report) where I've got a list of all tables with
> drilldown enabled that shows each table's columns (all of them) when
> expanded. Of course, once I got that report done, I was asked to add this
> additional functionality.
> Can this be done in one report or will I need to resort to multiple,
> seperate reports to accomplish this?
> I've got the code written for "All Tables" and "All Columns", but when I
> try
> to pick a single table, I still get a ColumnList that lists all columns in
> the entire DB, not the columns specific for that table.
> Can someone give me advice on how to accomplish what I need? Thanks!!!
> Catadmin
>|||I appreciate the suggestion, but I'm not sure it will work.
I want to be able to grey out the column parameter if "All Tables" is
chosen. Make it not accessible. Then, if a single table is chosen, not
print the other table on the report. My first problem is, that even when I
choose a single table, it prints out ALL the tables with the single column I
chose & all associated properties underneath all tables, even the tables that
have no such column. My second problem is creating the second parameter's
drop down list based only the columns associated with a single table picked
in the first parameter. My third problem is forcing all columns to
automatically be chosen, blocking out the second parameter, if "All Tables"
is chosen, so that all tables print, in order, with all of the columns
associated with that table and only that table.
Thank you for your time, though.
Catadmin
"DJM" wrote:
> We do something similar (although no cascading parameters). For "All", we
> pass in the value zero, and the query is written like this:
> Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
> If you're doing actual database objects and are using the sysobjects and
> syscolumns tables, I would imagine your syscolumns query would include a
> similar clause:
> Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid => @.ColumnID)
> I don't use cascading parameters, so I may be completely missing the point,
> and if so, I apologize...
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
> >I have a report I'm working on (data dictionary) where the team wants to be
> > able to do the following:
> >
> > 1) Choose "All Tables" and get a report listing all tables with each
> > tables
> > column names & properties underneath the proper table (ie, the columnName
> > param would be greyed out if this is chosen).
> >
> > OR
> >
> > 2) Choose a single table by name, then choose "All Columns" and get a
> > report
> > with ONLY that table and only the columns belonging to that table
> > underneath
> > it with their properties.
> >
> > OR
> >
> > 3) Choose a single table by name, then choose a single column by name and
> > only get that single column's properites.
> >
> > Obviously, 3 is the easiest and what I was able to get done first. I have
> > a
> > seperate report (my first report) where I've got a list of all tables with
> > drilldown enabled that shows each table's columns (all of them) when
> > expanded. Of course, once I got that report done, I was asked to add this
> > additional functionality.
> >
> > Can this be done in one report or will I need to resort to multiple,
> > seperate reports to accomplish this?
> >
> > I've got the code written for "All Tables" and "All Columns", but when I
> > try
> > to pick a single table, I still get a ColumnList that lists all columns in
> > the entire DB, not the columns specific for that table.
> >
> > Can someone give me advice on how to accomplish what I need? Thanks!!!
> >
> > Catadmin
> >
>
>

cascading parameters with multi-select

In SQL 2005 report server. When viewing a report that uses cascading
parameters with multi-select and one of the lower level cascading
parameters only populates with one item (that's all the query brought
back), the scroll left and right bar (for the parmeter box) covers up
the item when the item is longer in length than the parameter box.
Short parameter names are no problem. Is there any way to make the
parameter box wider or the drop down longer. This seems to be a bug as
it doesn't add any length when displaying the scroll bar.All of that business is out of our control... unfortunately... There are
many times when adjusting the parameter layout and size would be useful...
When it is really necessary, I put an HTML page in front of the report which
gathers the parameters... then calls the report...
--
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.
"Jim" wrote:
> In SQL 2005 report server. When viewing a report that uses cascading
> parameters with multi-select and one of the lower level cascading
> parameters only populates with one item (that's all the query brought
> back), the scroll left and right bar (for the parmeter box) covers up
> the item when the item is longer in length than the parameter box.
> Short parameter names are no problem. Is there any way to make the
> parameter box wider or the drop down longer. This seems to be a bug as
> it doesn't add any length when displaying the scroll bar.
>

Cascading Parameters ValidValues via the Webservices?

I'd like to access validvalues in a report through the webservices. Anyone
know a way to get validvalues of a parameter that is dependent on a chosen
value of another param? Thanks.If the valid values query for the second parameter (B) references the value of the first parameter
(A), the valid values list of B should dynamically change based on A.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Landis" <landiss@.bus.oregonstate.edu> wrote in message
news:um8bvJUWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> I'd like to access validvalues in a report through the webservices. Anyone
> know a way to get validvalues of a parameter that is dependent on a chosen
> value of another param? Thanks.
>|||Can I do this via the webservices? I'm still not clear on how to do it.
The query for the second parameter (B) is parameterized using the first
parameter (A) and works fine using the report manager to render the report.
However, if I do
ReportParameter[] parameters = rs.GetReportParameters(myReportPath, null,
false, null, null);
then parameters[0] will have valid values, but parameters[1] won't yet.
This makes sense, because it doesn't have a value from the first parameter
to query with. Is there a way for me to now send a value picked out of the
first parameter's valid values back to the server and get the valid values
for the second parameter? I've tried recalling GetReportParameters with a
ParameterValue[], but that doesn't seem to work.
Thanks.
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:OqkzusUWEHA.1888@.TK2MSFTNGP11.phx.gbl...
> If the valid values query for the second parameter (B) references the
value of the first parameter
> (A), the valid values list of B should dynamically change based on A.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Steve Landis" <landiss@.bus.oregonstate.edu> wrote in message
> news:um8bvJUWEHA.3472@.TK2MSFTNGP09.phx.gbl...
> > I'd like to access validvalues in a report through the webservices.
Anyone
> > know a way to get validvalues of a parameter that is dependent on a
chosen
> > value of another param? Thanks.
> >
> >
>|||Hi Steve:
I have an example here: http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
On Wed, 23 Jun 2004 09:52:12 -0700, "Steve Landis"
<landiss@.bus.oregonstate.edu> wrote:
>I'd like to access validvalues in a report through the webservices. Anyone
>know a way to get validvalues of a parameter that is dependent on a chosen
>value of another param? Thanks.
>|||Thanks!
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:p3njd0t8smjh6kun5dnu1vocpu7qe24vui@.4ax.com...
> Hi Steve:
> I have an example here: http://odetocode.com/Articles/123.aspx
> HTH,
> --
> Scott
> http://www.OdeToCode.com
> On Wed, 23 Jun 2004 09:52:12 -0700, "Steve Landis"
> <landiss@.bus.oregonstate.edu> wrote:
> >I'd like to access validvalues in a report through the webservices.
Anyone
> >know a way to get validvalues of a parameter that is dependent on a
chosen
> >value of another param? Thanks.
> >
>

Cascading Parameters Unreliable

I find that cascading parameters work intermittently. In addition, a
report with a drop down list will sometimes attempt to execute before
the user has made a selection.make sure you have the parameters in the proper order for cascading to work
properly. i.e. country parm first in your parameter list, state parameter
second, city parameter third... all the cascading parameters have to be
ahead of your other non-cascading parameters. to ensure the report will not
execute before a user selects parms... just leave 1 parm without default
value.
"mjhillman@.msn.com" wrote:
> I find that cascading parameters work intermittently. In addition, a
> report with a drop down list will sometimes attempt to execute before
> the user has made a selection.
>|||I am calling two stored procedures that take the same parameter. I
only have one parameter in the parameter list. Does this suggestion
apply to this scenario as well? Do I have to modify the SP to use a
different parameter name? (I did not write the SPs I am just executing
reports against an existing WMS.). Thanks.

Cascading parameters problem : First parameter is textbox

Hi,
I am using cascading parameters in my report. The first paramter is a
servername which user types in a TextBox. The second parameter is
populated based on the first parameter.
The problem is secord paramter drop down list doesn't get populated
unless I press "View Report" button in the preview mode.
Is there any way to populate second parameter (drop downlist) as user
finished typing the first parameter (TextBox)
please help,
regards,
SAchinUser needs to press "Tab" key to populate and need not press "view report"
Amarnath
"sachin laddha" wrote:
> Hi,
> I am using cascading parameters in my report. The first paramter is a
> servername which user types in a TextBox. The second parameter is
> populated based on the first parameter.
> The problem is secord paramter drop down list doesn't get populated
> unless I press "View Report" button in the preview mode.
> Is there any way to populate second parameter (drop downlist) as user
> finished typing the first parameter (TextBox)
> please help,
> regards,
> SAchin
>

Cascading parameters problem

Hi,

I have a problem with cascading parameters. I have 10 parameters in my report.

Parameters 1, 2, 9 and 10 are independant. Parameters 3 to 5 are cascading parameters as well as parameters 6 to 8.

Parameters 3 to 5 have no relationship with 6 to 8. But when I change the value for parameter 3 the values for parameters 4 to 10 are reset ?! I don't understand why, only parameters 4 to 5 should be reset.

Can someone please tell me how to avoid parameters 6 to 10 being reset ?

Are you sure that parameters 4 to 10 don't reference the value in parameter 3? With referencing I mean as well by queryparameters as by default values.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Parameter 3 is not referenced in query for parameters 6 to 10. Parameters 6 to 10 have default values but the default values are not related to the values selected in parameters 3 to 5|||

Can you sent me the RDL file than I will take quick look at it.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Cascading Parameters problem

I am using two parameters in my report. DepartmentID and EmployeeID. The employeeID is cascaded and it expects a DepartmentID as the query parameter in stored proc. The sp works fine in SQL and even from the datasets created in report designer. But when I preview the report and select a department to get the employee list populated, I get the following weirdo error:

An error occured during local report processing

The value provided for the report parameter 'EmployeeID' is not valid for its type.

I have checked and rechecked, and had some one else take a look at the report params, data types, and stored procs, but for the life of it, I can't figure out why I am getting this error? It seems the report designer is goofed up or something. Any ideas?

Never mind,, I resolved it. Somehow the allow blank values was highlighted even though the parameter data type was selected as integer.

Cascading Parameters problem

I am using two parameters in my report. DepartmentID and EmployeeID. The employeeID is cascaded and it expects a DepartmentID as the query parameter in stored proc. The sp works fine in SQL and even from the datasets created in report designer. But when I preview the report and select a department to get the employee list populated, I get the following weirdo error:

An error occured during local report processing

The value provided for the report parameter 'EmployeeID' is not valid for its type.

I have checked and rechecked, and had some one else take a look at the report params, data types, and stored procs, but for the life of it, I can't figure out why I am getting this error? It seems the report designer is goofed up or something. Any ideas?

Never mind,, I resolved it. Somehow the allow blank values was highlighted even though the parameter data type was selected as integer.

Cascading parameters problem

I have the following conundrum:
I have a report that utilises 3 cascading report parameters: region, area manager and business manager. They are in the correct order in the list of report parameters i.e. region then area manager then business manager.
When I select the region, then an area manager within that region, after having made the selection the area manager dropdown list changes to some arbitrary choice.
I also have a textbox equal to the label value of the area manager report parameter and this label displays something entirely different from my choice and the one that seems to be the "default".
Please note that I don't have any actual defaults for the parameters.

My question is why does the area manager dropdown change after the postback from my choice to something other?Found out what the problem was.
When using a dataset to populate a report parameter it would appear that the Value has to be unique / distinct, otherwise it looks like on the postback, it selects the first or whatever label it comes across.
Oh well, live and learn I guess

cascading parameters in Webpart

Hi,
In a Webpart, I have a report with 2 parameters.. the second parameter is
dependent on the first parameter.. I have noticed that when I changed the
first parameter, the values of the second parameter doesn't changed.., so I
got an error..
Can anybody help me?Do you get the same behavior when not in the webpart?
--
| Thread-Topic: cascading parameters in Webpart
| thread-index: AcWCyilGfXMDE2rkQn6X17V43/TYaw==| X-WBNR-Posting-Host: 195.6.59.36
| From: "=?Utf-8?B?cjM4ODA0Mg==?=" <r388042@.discussions.microsoft.com>
| Subject: cascading parameters in Webpart
| Date: Thu, 7 Jul 2005 01:02:03 -0700
| Lines: 7
| Message-ID: <9C86DC82-335A-4E95-93FB-4D3DE94B72A4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47555
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
| In a Webpart, I have a report with 2 parameters.. the second parameter is
| dependent on the first parameter.. I have noticed that when I changed the
| first parameter, the values of the second parameter doesn't changed.., so
I
| got an error..
| Can anybody help me?
|
||||No. The application runs correctly in Visual Studio.net or in the reporting
services server.
I thinks that the problem is due to the parameters or a property of the
Webpart. But I don't know exactly.
""Brad Syputa - MS"" wrote:
> Do you get the same behavior when not in the webpart?
> --
> | Thread-Topic: cascading parameters in Webpart
> | thread-index: AcWCyilGfXMDE2rkQn6X17V43/TYaw==> | X-WBNR-Posting-Host: 195.6.59.36
> | From: "=?Utf-8?B?cjM4ODA0Mg==?=" <r388042@.discussions.microsoft.com>
> | Subject: cascading parameters in Webpart
> | Date: Thu, 7 Jul 2005 01:02:03 -0700
> | Lines: 7
> | Message-ID: <9C86DC82-335A-4E95-93FB-4D3DE94B72A4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47555
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi,
> | In a Webpart, I have a report with 2 parameters.. the second parameter is
> | dependent on the first parameter.. I have noticed that when I changed the
> | first parameter, the values of the second parameter doesn't changed.., so
> I
> | got an error..
> | Can anybody help me?
> |
> |
>

Cascading Parameters in Report Builder Reports!

Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.

(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).

Thanks in advance

I need to know if it is possible. I have checked Report Builder and didn't find a way for it.

I am curios if i have missed something or if Report Builder doesn't support cascadign parameters.

cascading parameters in a Webpart

In a webpart, I have a report with 2 parameters..
the second parameter is dependent on the first parameter..
I have noticed that when I changed the first parameter, the values of the second parameter doesn't changed.., so I got an error..
Can you help me?Hi,
I am having the exact same problem. It seems that the default values set initially in the report aren't being carried over to all of the parameters in the web parts.
I have not been able to find a solution yet. Please let me know if you have an answer.
Thanks!|||Hi
I got something similar did you get an answer?

Cascading Parameters error

I am new to SQL Reporting services - (3 weeks in fact)
I have a report that uses cascading parameters down 2 levels
I select from a dropdown which populates the second dropdown.
I then select from the second drop down list.
There are three more parameters to be entered in by the user - (these
are not drop down lists)
type of test
start date
end date
This runs fine in the test environment
I deploy
then select from my first dropdown.
It crashes out because start date and end date are not entered
It appears that the page is refreshing with all the parameters even
though they are not entered.
Any help is greatly appreciated.
ThanksTry changing the order of your parameters. Put the start and end date
at the beginning.
Or put in defaults. Start and end date of yesterday and today for
example.|||How are you implementing cascading parameters. I have been needing to do
something like this, but do not see how it would be done.
Thanks
John
"Ches Weldishofer" wrote:
> Try changing the order of your parameters. Put the start and end date
> at the beginning.
> Or put in defaults. Start and end date of yesterday and today for
> example.
>|||I am not experienced enough to explain it to you correctly - RS books
online explains it quite well|||Changing the order etc as you say works fine
Thank you very much
Can you recommend a good book for a beginner?

Cascading Parameters

I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?

Thanks,

The Rook

I'd be very interested to see how you got one parameter's available values to be dependant on another's. Every way I've tried, I've had a forward dependency error.

Do you mean all available values, or the selected values?

|||I meant the "selected values." MSDN has a walkthrough that's very helpful and it's URL is: http://msdn2.microsoft.com/en-us/library/aa337426(d=printer).aspx - good luck!|||

Thanks for the link.

As for displaying the selected values on the report, that depends. If you are using just single-valued parameters, you can just drop a textbox for each parameter on the page, and set its value to the parameter. If you have a multi-valued parameter, you can try =Join(Parameters!Foo, ", ") for a comma-separated list of selected values. If you wanted something fancier, the only way I can think of is to use that same join syntax to pass the selected parameters to a query that returns the rowset that can populate, say, a table.

Cascading Parameters

All of a sudden I am having problems with cascading parameters. This report,
under development, was working fine earlier in the week. I've started over
and each time I get the same problem. I've been working with cascding
parameters for some time and nver seen this. I've tried everything but can't
get it back working correctly.
I have a cascading parameter that rlies on a date parameter. When I key in
the parameter, the cascading parameter populates the dropdown on the report
but when I click in the next parameter text box to set focus, Visual studio
has a seizure and starts flashing and by cpu maxes out. When I run the same
report in reporting services, I get an error that the next parameter has an
inconsistent data type.
I'm confused. Can anyone help."Mardy" wrote:
> All of a sudden I am having problems with cascading parameters. This report,
> under development, was working fine earlier in the week. I've started over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the report
> but when I click in the next parameter text box to set focus, Visual studio
> has a seizure and starts flashing and by cpu maxes out. When I run the same
> report in reporting services, I get an error that the next parameter has an
> inconsistent data type.
> I'm confused. Can anyone help.|||More information. If I tab between the parameter text boxes, I can set the
dependent parameter and the report runs. Any subsequent click leads to Visual
Studion convulsions. WEIRD
"Mardy" wrote:
> All of a sudden I am having problems with cascading parameters. This report,
> under development, was working fine earlier in the week. I've started over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the report
> but when I click in the next parameter text box to set focus, Visual studio
> has a seizure and starts flashing and by cpu maxes out. When I run the same
> report in reporting services, I get an error that the next parameter has an
> inconsistent data type.
> I'm confused. Can anyone help.|||Tested further on my other laptop. It's Reporting Services sp1 versus sp2 on
the problem machine. The SP1 machine is more stable. No Visual Studio
convusling.
SH**
"Mardy" wrote:
> More information. If I tab between the parameter text boxes, I can set the
> dependent parameter and the report runs. Any subsequent click leads to Visual
> Studion convulsions. WEIRD
> "Mardy" wrote:
> > All of a sudden I am having problems with cascading parameters. This report,
> > under development, was working fine earlier in the week. I've started over
> > and each time I get the same problem. I've been working with cascding
> > parameters for some time and nver seen this. I've tried everything but can't
> > get it back working correctly.
> >
> > I have a cascading parameter that rlies on a date parameter. When I key in
> > the parameter, the cascading parameter populates the dropdown on the report
> > but when I click in the next parameter text box to set focus, Visual studio
> > has a seizure and starts flashing and by cpu maxes out. When I run the same
> > report in reporting services, I get an error that the next parameter has an
> > inconsistent data type.
> >
> > I'm confused. Can anyone help.|||I have found that it depends on my cascading parameters whether this
happens. It doesn't always happened. I haven't been developing on a report
with this problem in SP2 so I can't say if it is better or worse. What I can
say is that it only occurs when developing. If you deploy it, it will work
just fine. So, for a report that does this I would just deploy it for
testing. A bit of a pain but it is a workaround and the report will work
fine in production.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:938D29C2-34FA-4843-B74F-D0C95B09BFC2@.microsoft.com...
> Tested further on my other laptop. It's Reporting Services sp1 versus sp2
> on
> the problem machine. The SP1 machine is more stable. No Visual Studio
> convusling.
> SH**
> "Mardy" wrote:
>> More information. If I tab between the parameter text boxes, I can set
>> the
>> dependent parameter and the report runs. Any subsequent click leads to
>> Visual
>> Studion convulsions. WEIRD
>> "Mardy" wrote:
>> > All of a sudden I am having problems with cascading parameters. This
>> > report,
>> > under development, was working fine earlier in the week. I've started
>> > over
>> > and each time I get the same problem. I've been working with cascding
>> > parameters for some time and nver seen this. I've tried everything but
>> > can't
>> > get it back working correctly.
>> >
>> > I have a cascading parameter that rlies on a date parameter. When I key
>> > in
>> > the parameter, the cascading parameter populates the dropdown on the
>> > report
>> > but when I click in the next parameter text box to set focus, Visual
>> > studio
>> > has a seizure and starts flashing and by cpu maxes out. When I run the
>> > same
>> > report in reporting services, I get an error that the next parameter
>> > has an
>> > inconsistent data type.
>> >
>> > I'm confused. Can anyone help.|||I found this issue occurs after applying MS patch 05-014 to my XP desktop
client and using IE browser. I have not found the solution yet, but we are
running Report Services SP1 as well.
"Mardy" wrote:
> All of a sudden I am having problems with cascading parameters. This report,
> under development, was working fine earlier in the week. I've started over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the report
> but when I click in the next parameter text box to set focus, Visual studio
> has a seizure and starts flashing and by cpu maxes out. When I run the same
> report in reporting services, I get an error that the next parameter has an
> inconsistent data type.
> I'm confused. Can anyone help.|||I am having the same issue. I found by putting in a default value for the
parameter, I can bypass the dropdown selection. I have this issue both in
SP1 and SP2.
Looking forward to a patch,
andy
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:1A67CB60-5CB8-4AD7-9B2E-F42530880DD2@.microsoft.com...
> All of a sudden I am having problems with cascading parameters. This
> report,
> under development, was working fine earlier in the week. I've started
> over
> and each time I get the same problem. I've been working with cascding
> parameters for some time and nver seen this. I've tried everything but
> can't
> get it back working correctly.
> I have a cascading parameter that rlies on a date parameter. When I key in
> the parameter, the cascading parameter populates the dropdown on the
> report
> but when I click in the next parameter text box to set focus, Visual
> studio
> has a seizure and starts flashing and by cpu maxes out. When I run the
> same
> report in reporting services, I get an error that the next parameter has
> an
> inconsistent data type.
> I'm confused. Can anyone help.

cascading parameters

Hello,
I have a total of 5 parameters in my report. Year, season, list type,
startdate, enddate.
Year and season are simple queries.
List type depends on year and season
Startdate depends on year,season and list type. it has a default value
(dataset) to pull the min(date) given the year, season and list type
Enddate depends on year,season and list type. it has a default value
(dataset) to pull the max(date) given the year, season and list type
The report parameters are ok when the user selects the values for the
first time. but then when they try to change just the season parameter,
the startdate and enddate do not default to the correct values. Is
there a way I can force the startdate and enddate parameters to
re-query or re populate?
thanks in advanceActually the refresh depends on the first parameter, when it is changed then
it refreshes all since it is cascading. before changing 2nd param you need to
refresh and do the changes, because since season is dependent on year so if
you change the year then season will get changed.
Amarnath
"clemlau@.yahoo.com" wrote:
> Hello,
> I have a total of 5 parameters in my report. Year, season, list type,
> startdate, enddate.
> Year and season are simple queries.
> List type depends on year and season
> Startdate depends on year,season and list type. it has a default value
> (dataset) to pull the min(date) given the year, season and list type
> Enddate depends on year,season and list type. it has a default value
> (dataset) to pull the max(date) given the year, season and list type
> The report parameters are ok when the user selects the values for the
> first time. but then when they try to change just the season parameter,
> the startdate and enddate do not default to the correct values. Is
> there a way I can force the startdate and enddate parameters to
> re-query or re populate?
> thanks in advance
>

Cascading Parameters

Hi,

I am using SQL Server 2005 reporting services. I am having a problem with Cascading Parameters. In my report there are 4 parameter . (Product Class, Product Type, Product SubType and Activites). when user open the report page he/she will have to select a value for product class depending on the selection Product Type and Sub type will be populated. So far its good, but Activities also depends on the Product Class. My question is can we have 2 parameters depend on same parameter. Here in this case Product Type and Activities both depend on the Product Class. I want only those activies in the dropdown which belongs to the Product Class that is selected.

Can anyone please give an ideas?

Thanks

Ashwini

Hi Ashwini,

I am assuming that you are using datasets or stored procedures to fill the parameters. If that is the case, This link -MIGHT- get you in the right direction.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=67984&SiteID=1

It is concerning about which dataset is being executed first, and with this, you might be able to execute certain datasets to get what you need and then based the next dataset on the previously executed dataset.

Hope this helps!

Bernard

|||

Hi Bernard,

Yes I have created 4 datasets with stored procedures to fill these report parameters.

SP1 : SelectProductClassAll -->which brings in all the product class

SP2: RptSelectCascadedProductType --> which has a parameter @.selected_product_class and will bring in all the product types for the given product class.

SP3: RptSelectCascadedProductSubType --> which has a parameter @.selected_product_type and will bring in all the product sub-types for the given product types.

SP4: RptSelectCascadedProductSubType --> which has a parameter @.selected_product_class and will bring in all the Activities for the given product class.

Problem is 2nd and 4th SP expects the same parameter. After adding these datasets if you go to Report -->Report Parameters you will only have 2 parameters (which would act as a drop down filter) @.selected_product_class , @.selected_product_type and I added one for Subtypes these 3 drop downs are fine. But I am not able to get the dropdown for activites since my SP4 expects the same parameter as SP1 and I cant use the same name parameter name twice?

I hope I am clear...

I looked at the link which you send it did not give me enough information so I am still confused...

Thanks

Ashwini

|||

HI Ashwini.

Actually you should be able to use the same parameter name.

Have you tried using only 3 parameters instead of 4 parameters ?

I mean, logically speaking, since two SPs are using ONE parameter, I don't see a reason why you would try and use another Report Parameter. Just make sure that the 2 stored procedures are pointing to one Parameter.

The link I posted tells of how datasets can be executed in order depending on what you need. In this case, you would want to execute SP1, before executing Sp2 and Sp3 right ?

Of course unless, I am getting it all confused myself which is not a good thing.

Bernard

|||

Without reading in detail too much, I'd say yes to you can re-use same parameter (same name) for different parameters

e.g. our reports have performance venue dropdown based on the date range selected (@.start_date to @.end_date)

then performance type, based on the venue AND date range

then say seating zones of the venue, based on type AND venue AND date range

|||

Ok may be I am understanding the concept of report parameter wrong...If I only have 3 parameters it will only show 3 dropdown menu when I actually run the report right? for ex: Product class, type and subtype. I need to have another dropdown menu for Activities as well how would I get this if I dont have a report parameter for it?

As per my understanding SRS will create these dropdown menus for you based on the report parameter. Please correct me if I am wrong...

|||In your case, you only have 3 params, isn't it. The #2 and #4 sp share the same parameter which is your Selected_Product_Class. So when user makes a selection for ProductClass both sp should run based on that Product Class. If you want to pass different values of Selected_Product_Class to these 2 sp, then you need to define 2 separate parameters and fill them both with your #1 sp. But in that case you will have 2 Selected_Product_Class, not sure if that's what you intend to do.|||

Ashwini Ramaiah wrote:

I need to have another dropdown menu for Activities as well how would I get this if I dont have a report parameter for it?

Define another parameter and fill it with your #1 sp, just liek you did earlier. And pass the value of this param to your #4 sp. But this way, user will be able to select 2 different values of your ProductClass. If you want them to be same, then just fill this new param with the value of your already selected product class parameter.

|||

Thanks Everyone!!!

Its working now....I am not sure what was the problem. All I did was delete all my parameter and datasets and re do it. I created a 4th parameter and hooked it up with Activities Data which in turn is looking fo SP4 which expects @.selected_Product_Class parameter. Trust me I did try this like 100 times it didnt work that time when I deleted the whole thing and recreated all the parameters its all good now.

I have 4 dropdowns in my Report when user comes in He/She would only be able to select a value from Product Class rest 3 will be grayed once product class is selected Product type and Activities dropdown's data gets populated and the user can select Product Sub type which is 4th dropdown. This is exactly what I wanted.

Once again thanks to everyone!!!

Ashwini