Cascading parameters.

how to set a cascading parameter in the sql reporting(mdx)?

Use the StrToMember method. Using AdventureWorks, if you wanted to limit the list of Product Subcategories from the selected category, @.Category.

Code Snippet

WITH MEMBER [Measures].[ParameterLabel] AS

[Product].[Subcategory].CurrentMember.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS

[Product].[Subcategory].CurrentMember.UniqueName

SELECT {

[Measures].[ParameterLabel],

[Measures].[ParameterValue]

} ON COLUMNS,

NON EMPTY

{ [Product].[Subcategory].[Subcategory].Members }

ON ROWS

FROM [Adventure Works]

WHERE (

StrToMember(@.Category, CONSTRAINED)

)

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

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

Capturing ISS Log data

I am quite new to Reporting Services and need some help figuring out why i can't correctly capture the referrrer information in the IIS Logs.

As far as i know when you run the data extraction tool provided with RS it copies the logs into the staging db, from there it then copies all the data into the WSSFact table in the actual db.

I thought that if i entered a URL into the referrer table that when it populates the WSSFact table it would check to see if the referrer URL matches any of the entries in the referrer table and if so enter its id, otherwise enter the id for No referrer.

This seems to work fine for all existing entires in the referrer table but it ignores the new one i have added. Does anyone know if there is something else that i have to do or check?It sounds like you are using IIS tools. I am not aware of the WSSFact table and such. Can you provide a link to the tools you are using?|||

I am using the data extraction tool provided with Reporting Services. The problem is you can't see what this tool does, therefore its difficult to trace the process of how it reads the logs and places the information in the database.

Like i said i am pretty sure it places the log information into a staging database and then from there into the actual one used by to generate the reports.

What i need to know is why it keeps showing as not having any referrer information in the database when you can clearly see there is info in the logs.

Sorry if i wasn't clear the first time.

Capabilities Question

I am very new to SQL Reporting Services and trying to determine it's
capabilities. I developed a data-entry application. This application has
extensive business logic that summarizies and formats the data from the
database (including many calculations that I would rather not repeat in
another location).
My question is this... Can I tie a Report created with MS Reporting Services
to one of functions in my business logic. Or can I send the data from my
business logic to a reports?
If either is possible what would be the best way to go about implementing
thisREporting Services can consume a web service, data set or XML file as its
data source.
Also an RS report can be called from a URL directly...
I suspect you have or can create a web service to provide the data, and you
will be good to go.
--
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.
"matchbx" wrote:
> I am very new to SQL Reporting Services and trying to determine it's
> capabilities. I developed a data-entry application. This application has
> extensive business logic that summarizies and formats the data from the
> database (including many calculations that I would rather not repeat in
> another location).
> My question is this... Can I tie a Report created with MS Reporting Services
> to one of functions in my business logic. Or can I send the data from my
> business logic to a reports?
> If either is possible what would be the best way to go about implementing
> this
>|||Thank you for such a fast response.
Just one more quick question. I'm sorry I did refer to a version. Will
this method work for both 2000 and 2005 or just for 2005.
"Wayne Snyder" wrote:
> REporting Services can consume a web service, data set or XML file as its
> data source.
> Also an RS report can be called from a URL directly...
> I suspect you have or can create a web service to provide the data, and you
> will be good to go.
> --
> 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.
>
> "matchbx" wrote:
> > I am very new to SQL Reporting Services and trying to determine it's
> > capabilities. I developed a data-entry application. This application has
> > extensive business logic that summarizies and formats the data from the
> > database (including many calculations that I would rather not repeat in
> > another location).
> >
> > My question is this... Can I tie a Report created with MS Reporting Services
> > to one of functions in my business logic. Or can I send the data from my
> > business logic to a reports?
> >
> > If either is possible what would be the best way to go about implementing
> > this
> >

Can't use VS 2003 for reports now that VS 2005 and SQL 2005 are installed?

I've been using Visual Studio 2003 successfully for several months to
develop a web-based reporting solution. I've also had SQL Server 2000
installed, including the evaluation version of Reporting Services. I
just recently installed Visual Studio 2005 (but left Visual Studio 2003
installed as well) and updated to SQL Server 2005, including the new
version of Reporting Services. I made a copy of one of the report
projects from my VS 2003 solution and I can successfully open it in VS
2005 and convert the reports to the new RS 2005 format. However, when
I try to open my original solution (not the converted copy) in VS 2003,
the non-report projects open fine, but I get the follow message as it
tries to open each of the report projects: "The application for
C:\MyReportProject.rptproj is not installed. Make sure the application
for the project type (.rptproj) is installed." After that, the word
"unavailable" appears next to each of the report projects in Solution
Explorer, but the non-report projects are fine.
Any ideas?I am having the same issue.
--
John
"Lee" wrote:
> I've been using Visual Studio 2003 successfully for several months to
> develop a web-based reporting solution. I've also had SQL Server 2000
> installed, including the evaluation version of Reporting Services. I
> just recently installed Visual Studio 2005 (but left Visual Studio 2003
> installed as well) and updated to SQL Server 2005, including the new
> version of Reporting Services. I made a copy of one of the report
> projects from my VS 2003 solution and I can successfully open it in VS
> 2005 and convert the reports to the new RS 2005 format. However, when
> I try to open my original solution (not the converted copy) in VS 2003,
> the non-report projects open fine, but I get the follow message as it
> tries to open each of the report projects: "The application for
> C:\MyReportProject.rptproj is not installed. Make sure the application
> for the project type (.rptproj) is installed." After that, the word
> "unavailable" appears next to each of the report projects in Solution
> Explorer, but the non-report projects are fine.
> Any ideas?
>|||Dear All,
solution :
--
install the Client Components from SQL SERVER 2005.
SQL SERVER 2005 -> Client Components -> Business Intelligence
with regards,
Murali
"John K" wrote:
> I am having the same issue.
> --
> John
>
> "Lee" wrote:
> > I've been using Visual Studio 2003 successfully for several months to
> > develop a web-based reporting solution. I've also had SQL Server 2000
> > installed, including the evaluation version of Reporting Services. I
> > just recently installed Visual Studio 2005 (but left Visual Studio 2003
> > installed as well) and updated to SQL Server 2005, including the new
> > version of Reporting Services. I made a copy of one of the report
> > projects from my VS 2003 solution and I can successfully open it in VS
> > 2005 and convert the reports to the new RS 2005 format. However, when
> > I try to open my original solution (not the converted copy) in VS 2003,
> > the non-report projects open fine, but I get the follow message as it
> > tries to open each of the report projects: "The application for
> > C:\MyReportProject.rptproj is not installed. Make sure the application
> > for the project type (.rptproj) is installed." After that, the word
> > "unavailable" appears next to each of the report projects in Solution
> > Explorer, but the non-report projects are fine.
> >
> > Any ideas?
> >
> >|||That will install 2005 components...
What I need back is the 2003 projects templates that work will SQL 2000 !
Has anybody managed to fix this issue ?
> install the Client Components from SQL SERVER 2005.
> SQL SERVER 2005 -> Client Components -> Business Intelligence
> with regards,
> Murali
>
> "John K" wrote:
> > I am having the same issue.
> >
> > --
> > John
> >
> >
> > "Lee" wrote:
> >
> > > I've been using Visual Studio 2003 successfully for several months to
> > > develop a web-based reporting solution. I've also had SQL Server 2000
> > > installed, including the evaluation version of Reporting Services. I
> > > just recently installed Visual Studio 2005 (but left Visual Studio 2003
> > > installed as well) and updated to SQL Server 2005, including the new
> > > version of Reporting Services. I made a copy of one of the report
> > > projects from my VS 2003 solution and I can successfully open it in VS
> > > 2005 and convert the reports to the new RS 2005 format. However, when
> > > I try to open my original solution (not the converted copy) in VS 2003,
> > > the non-report projects open fine, but I get the follow message as it
> > > tries to open each of the report projects: "The application for
> > > C:\MyReportProject.rptproj is not installed. Make sure the application
> > > for the project type (.rptproj) is installed." After that, the word
> > > "unavailable" appears next to each of the report projects in Solution
> > > Explorer, but the non-report projects are fine.
> > >
> > > Any ideas?
> > >
> > >|||Did you try re-installing the RS 2000 report designer?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mikey" <mikey@.discussions.microsoft.com> wrote in message
news:C4164015-D773-40D7-BABB-4E0AE343CC1E@.microsoft.com...
> That will install 2005 components...
> What I need back is the 2003 projects templates that work will SQL 2000 !
> Has anybody managed to fix this issue ?
>
>> install the Client Components from SQL SERVER 2005.
>> SQL SERVER 2005 -> Client Components -> Business Intelligence
>> with regards,
>> Murali
>>
>> "John K" wrote:
>> > I am having the same issue.
>> >
>> > --
>> > John
>> >
>> >
>> > "Lee" wrote:
>> >
>> > > I've been using Visual Studio 2003 successfully for several months to
>> > > develop a web-based reporting solution. I've also had SQL Server
>> > > 2000
>> > > installed, including the evaluation version of Reporting Services. I
>> > > just recently installed Visual Studio 2005 (but left Visual Studio
>> > > 2003
>> > > installed as well) and updated to SQL Server 2005, including the new
>> > > version of Reporting Services. I made a copy of one of the report
>> > > projects from my VS 2003 solution and I can successfully open it in
>> > > VS
>> > > 2005 and convert the reports to the new RS 2005 format. However,
>> > > when
>> > > I try to open my original solution (not the converted copy) in VS
>> > > 2003,
>> > > the non-report projects open fine, but I get the follow message as it
>> > > tries to open each of the report projects: "The application for
>> > > C:\MyReportProject.rptproj is not installed. Make sure the
>> > > application
>> > > for the project type (.rptproj) is installed." After that, the word
>> > > "unavailable" appears next to each of the report projects in Solution
>> > > Explorer, but the non-report projects are fine.
>> > >
>> > > Any ideas?
>> > >
>> > >|||Hi Bruce,
I did this morning.. and it has worked by installing the client designer of
SQL 2000 reporting back onto my machine.
Thanks for the reply. Just hadn't had chance to post my solution back.

Can't use TAB key in SQL Pane

Hello folks,
When working in Visual Studio 2005 Reporting Services, I have been
cursed with a small problem that I have run out of remedy ideas for.
On the data tab of a report I am no longer able to use the TAB key on
the keyboard to format my SQL. I say no longer because i was using
Visual Studio 2003 until recently and this problem did not exist. If
I hit TAB when writing code in the SQL pane the cursor just moves to
the next action object (button) as if on a form. The only band-aid
solution has been to write all my code in a SQL Management Studio
query window and than copy/paste it into the report SQL pane but this
is a nuisance, especially for short,easy report queries and when
returning to existing reports for upgrades or bug fixes. Another
workaround has been to copy a single TAB from Notepad or any other app
and paste the tab in the SQL pane when needed, but than i have to re-
copy it each time if i copy something else. Any ideas or
sympathizers, I would love to hear from you.
Thanks, and here is an example of what I mean.
/* this is what I am stuck with */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.foo
/* this is what I want */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.fooNo way t o use tab, you need to use spacebar with space, I understand if it
is a small query you can do it, but if it is a big query will be very
tedious.
what otherway you can do is, just click "generic query builder" and again
click what it does is, it indends automatically.
Amarnath
"Skilliam" wrote:
> Hello folks,
> When working in Visual Studio 2005 Reporting Services, I have been
> cursed with a small problem that I have run out of remedy ideas for.
> On the data tab of a report I am no longer able to use the TAB key on
> the keyboard to format my SQL. I say no longer because i was using
> Visual Studio 2003 until recently and this problem did not exist. If
> I hit TAB when writing code in the SQL pane the cursor just moves to
> the next action object (button) as if on a form. The only band-aid
> solution has been to write all my code in a SQL Management Studio
> query window and than copy/paste it into the report SQL pane but this
> is a nuisance, especially for short,easy report queries and when
> returning to existing reports for upgrades or bug fixes. Another
> workaround has been to copy a single TAB from Notepad or any other app
> and paste the tab in the SQL pane when needed, but than i have to re-
> copy it each time if i copy something else. Any ideas or
> sympathizers, I would love to hear from you.
> Thanks, and here is an example of what I mean.
> /* this is what I am stuck with */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
> /* this is what I want */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
>