Cascading Parameters Based Off Analysis Services
Jun 6, 2007
Hello,
I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?
For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.
When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.
Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].
Thank you.
-Gumbatman
View 4 Replies
ADVERTISEMENT
Jun 26, 2015
I am using SSRS 2008 with a SQL Server 2008 R2. It's been many years since i have used SSRS but i have used this and some other reporting tools in the past.I have created a cascading parameters where upon selecting 'ALL' or one of the other values in parameter A , the other values gets displayed in paramter B which is working in the Report builder. i am handling this in the dataset query where i handle that through a case statement ( something like WHERE 1 = (CASE WHEN @Prm_A = 'ALL' THEN 1 END) OR column_X= ( CASE WHEN @Prm_A <>'ALL' THEN @Prm_A END ).Also tried WHERE @Prm_A = 'ALL' OR column_X= @Prm_A
This seems to work in the report builder on my machine or when i run it by editing the report on the web, but when i publish it on the report server, i don't see that behavior, meaning it does not cascade. I also tried to simplify it by making the query simple(WHERE column_X= @Prm_A ), but it still would not cascade on the web.
View 2 Replies
View Related
Aug 27, 2007
Hello,
I'm using a SSIS cube as source for reporting services. In my DataSet I have 3 parameters: Year, ProductGroup, Product. By default all parameters are cascading when defined on the DataSet. This is great for ProductGroup -> Product, but I don't want it for Year -> ProductGroup (I want to avoid the roundtrip). I removed the link in the generated MDX, I removed the parameters on the DataSet, but when I run the report, ProductGroup is still grayed out until I choose a year.
Suggestions are very welcome...
View 4 Replies
View Related
May 29, 2007
I have a Reporting Services 2005 report which uses cascading parameter multi-select drop-down boxes. The report runs perfect after making initial selections. However, each subsequent parameter selection causes the page to refresh to a blank page until the View Report button is clicked. What I would like to happen (which should be the norm) is for the user to be able to make all necessary selections without the page auto-refreshing after each selection. The user would then click the View Report button to regenerate the report. We are currently using reports which contain up to 5 cascading parameters. This becomes very time-consuming when the page refreshes after each selection.
Has anyone come across a solution or code alternative to this problem??
Any help would be most appreciated.
Thank you.
Tony
View 3 Replies
View Related
Apr 23, 2015
I have created two report parameters and want them as Cascading. District Parameter depends on Region Parameter which should allow Multi selection. When I select single value in Region it works perfectly. But when I choose multiple values, District turns out to be a blank text box. I have used the In clause in my code :
SELECT
DISTINCT
SalesDistrict
FROMDistrict(NOLOCK)
WHERESalesRegion
IN(@SalesRegion)
View 2 Replies
View Related
Sep 2, 2015
After adding cascading parameters my report which was running earlier is failing. I'm using Report builder 3.0, windows 2008r2, am running the report from the server
Error message "An error has occurred during report processing.(rsProcessingAborted)
Query execution failed for dataset 'LastName' (rsErrorExecutinGcOMMAND)
Incorrect Syntax near ','."
Is there a way where i can look up the code in xml using report builder or some other way so that i can delete the extra '','.
View 6 Replies
View Related
Nov 10, 2015
I have two reports.
1. Main Report
2. Sub Report.
I have cascading parameters in 'Main Report'. When i try to pass cascading parameters in to sub-report, I could pass only 'first parameter' value to sub-report.Is there a way that i can pass cascading parameters to the child report?
View 4 Replies
View Related
Oct 22, 2015
I am creating a report where there are 3 tables in one report which have visibility set as an expression so that when a certain criteria is selected in the "Report" parameter, a different table is shown below. This works as expected.
When table 3 is shown, I want a new parameter to be visible to the user called "Ward" where the user will select the ward they are interested in and the table will display data below just for the ward selected. However, I don't want the ward parameter visible when either of the other 2 options are selected. Is this possible and how to do this?
View 3 Replies
View Related
Sep 16, 2007
Hi All,
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
Regards,
Joseph
View 1 Replies
View Related
Nov 29, 2007
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
In sql2000 the script looks like this...
="with " &
"member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " &
"Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName' " &
"Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name' " &
"SELECT " &
"{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns, " &
"filter({descendants(" & Parameters!CustomerClass.value & ", [Customers by Class].[" & Parameters!CustomersLevel.value & "], SELF)}, " &
" [Measures].[Ex Price] > 0) on rows " &
"from Sales "
i've changed the script in sql2005 but not working yet..
with
member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)'
Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName'
Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name'
SELECT
{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns,
filter({descendants( STRTOMEMBER(@CustomerClass), "[Customer Class]." + STRTOMEMBER(@CustomersLevel), SELF)},
[Measures].[Ex Price] > 0) on rows
from Sales
Thanks in advance..
Vivek
View 4 Replies
View Related
Feb 7, 2008
Mr. Pearson,
I am currently reading your article titled, "MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist". This article is directly applicable to a problem we are currently trying to solve regarding ragged hierarchies as input parameters.
I have not read the entire article through but am in the process of doing so. Also, I will be trying to implement your solution.
I have one question, will SSRS support multi selection when using a hierarchical picklist? Thanks in advance for any assistance.
View 1 Replies
View Related
Jul 18, 2007
I'm looking for a new way to publish reports based on OLAP data and was very disappointed to find that Reporting Services does not natively support displaying MDX query results in a matrix. While it is possible to assign an MDX query to the matrix control in Reporting Services, the two main problems are that the columns of the query must be measures (not dimensions), and it does not support display of server based formatting (fore color, back color, and font flags).
Does anyone know of any custom control that properly deals with Analysis Services data?
Thanks for any info you have!
View 13 Replies
View Related
Jun 7, 2007
We have been a Crystal shop for ages; we are currently doing a proof-of-concept for a conversion to MS Reporting Services. As such, we are developing some Analysis Services 2005 cubes to drive some new SSRS reports, which our users will access through Report Manager. Unfortunately, we are all MDX noobs here, so we are making heavy use of the Wizards until we can come up to speed.
The problem we are running into is when we develop a report with Date Parameters. When we deploy this report, the date parameter box is a dropdown box instead of a date picker. I've seen a couple of other posts on this topic, but when I try to apply the fixes mentioned in them, I throw errors.
I have two quick questions:
Why does this happen? Is it a limitation in the MDX language, in SSAS, or SSRS? Are there any planned fixes?
Can someone please show me how to fix this on my actual query string for one of our basic reports? I've highlighted the date parameters.
Code Snippet
SELECT NON EMPTY { [Measures].[Lead] } ON COLUMNS, NON EMPTY { ([Store].[Store ID].[Store ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StoreStoreID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromLeadCreationDateCalendarDate, CONSTRAINED) : STRTOMEMBER(@ToLeadCreationDateCalendarDate, CONSTRAINED) ) ON COLUMNS FROM [Referral Leads]))) WHERE ( IIF( STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED).Count = 1, STRTOSET(@LeadSourceTypeLeadSourceType, CONSTRAINED), [Lead Source Type].[Lead Source Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I'm afraid, given my user community, that if I can't get the date picker to work properly, it could be a deal breaker.
Thanks very much in advance for your help.
Regards,
Steve
View 7 Replies
View Related
Aug 21, 2015
I am trying to use SSRS to allow users to select any one or several columns from a set of cascading parameters which will then do a "data dump" of the contents of the selected columns for "export".I know how to do cascading parameters, but I am having problems coming up with a way of using the parameters to dynamically build a matrix which has as its columns the list selected in the parameters. I need the user to choose WHICH columns will be displayed. There could be 1, 2, 3, up to 50 columns.
View 2 Replies
View Related
Oct 2, 2015
I am producing a report in Report Builder 2 containing a chart and table. The user is able to select single or multiple choices on a parameter called Specialty. My present chart is only suitable when one Specialty is selected. Therefore I only want this to be visible when the user has selected one Specialty. I am planning to create a second chart which is suitable for than more than one selection. So if the user selects more than one Specialty the existing chart will not be visible and instead will be replaced by the second.I think this should be done using Chart Properties>Visibility but cannot develop the logic of the expression.
View 5 Replies
View Related
Jun 9, 2015
I need to send a report to various email id based on parameters.
I have a report which has a dropdown which shows the list of parameters. I need to send report to 3 different email ids with different parameters. How to do that?
View 6 Replies
View Related
Dec 15, 2005
Hi
I have three params p1 , p2 and p3.
All 3 are non queried with values Yes and NO .
if p1 is yes only i have to enable the remaining twp params otherwise disable them.
Can some one suggest as to how this can be achieved.
Thanks
View 1 Replies
View Related
Mar 28, 2006
Hi All
I am using RS 2000. I used 2 input parameters for one of the report. The 2nd parameter is based on the 1st parameter. i.e. First Parameter is Country and 2nd Parameter is State. Based on the Country selection the list of states for the selected country will get displayed. This is working fine in Reporting services as well as report server. When deploying into the application it doesn't work.
If you have solution for this I would like to share.
Regards
Venkataraman M
ramanmahalingam@hotmail.com
View 6 Replies
View Related
Apr 15, 2008
Hi
I am doing report development against cube (OLAP) and I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do we achieve this in a cube environment? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Hope this helps. If you have questions, please let me know.
View 1 Replies
View Related
Mar 21, 2007
hi all,
In my report I want to make certain parameters to depend on the previous parameters. I think we can use those as cascading parameters. How to make a parameter a cascading parameter? and how to use those cascading parameter ? It would be nice of you if you can help me.
Thanking you,
Lekshmi
View 4 Replies
View Related
Jun 1, 2007
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
View 3 Replies
View Related
Sep 24, 2007
I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.
Thanks!
View 6 Replies
View Related
Apr 25, 2007
Hi,
I have a listbox which selects distinct brands from the products table.
Then another list box which lists all the orders with order description. Each order has a unique system generated ORDERID and the user provides the orderdescription which could be duplicate.
Depending on the Brand selected by the user in the earlier list box, only those orders containing the the brands in order details files should be available for selection in the list box.
e.g. BRands Lisbox shows: Cream A, Cream B and Cream C
If the user selects 'Cream A', then the next list box shows orderdescription as 'Cream A order for regular sale','Cream A order for exhinition sale', 'Cream A order for exhibition sale'
The problem here is that if the user selects a description which is duplicate (could be the case), then the system brings back the wrong order details.
How is it possible to allow a user to select a order description but search on the OrderID?
Thanks for the help
regards
josh
View 1 Replies
View Related
Mar 20, 2007
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
View 10 Replies
View Related
Jul 7, 2015
I have a detailed report in ssrs in which data can come from start date and end date parameters.but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and so on to sep month data in new tab when i export to Excel.
View 3 Replies
View Related
Apr 1, 2008
Hi,
I have the folowing problem with RS2005.
I have 2 parameters in my report. Firs one is a list of projects which is a single select. The second one allows to select multiple employees.
When I select a project from the first parameter, the list in second parameter gets narrowed to list of employees that are assigned to this project.
The unexpected behaviour:
1. I select "Proj1" from first parameter
2. The second parameter gets narrowed. I select "Select All" employees
3. I generate my report. OK
4. I change the selection in first param to "Proj2"
5. The second parameter gets narrowed, but employees that are common for "Proj1" and "Proj2" are still selected, which is unwanted. ERR
The users of this report gets confused as they generate the report just after point 4. and they think all employees are still selected.
What I want to do is uncheck all employees from second parameter every time the first one is changed. That would cause the user to get a prompt for selecting second parameter.
Is there a way I can do this? Any clue would be nice.
Best Regards.
View 5 Replies
View Related
Jan 17, 2007
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 ?
View 3 Replies
View Related
Jul 9, 2007
Hi,
I have 3 parameters in a report related with each other, but using them makes the report go extremly slow... is this a known bug? I have been reading about the problem with postbacks and stuff is this the problem?
Any workaround or fix for this?
Best Regards,
View 3 Replies
View Related
Mar 7, 2007
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?
View 1 Replies
View Related
May 19, 2008
Hi all,
did any of you had the same issue ? how can I add cascading parameters to my report from the SAME hierarchy?
If I have 4 levels in hierarchy- according to level 1 - filter level2 (param2), then according to level 2 - filter level 3 (param3).
any ideas ?
Thanks in advance
View 4 Replies
View Related
Nov 17, 2007
I need the ability to choose between quite a few cascading olap time query parameters for a report without my creating a report or subreport for every combination of these parameters. If the recordset were smaller perhaps I could filter it when it's passed back to the report but it's a few million. Will switching to expression-based command text and building the mdx queries in code work? I would like to be able to tell rs to remove the parameter from the query if no selections are made.
View 2 Replies
View Related
Feb 1, 2008
Hi,
I am currently working on a report with 3 cascading parameters. These three parameters depend on the datasets whose data are retrieved from a large table with SELECT DISTINCT. As the table grows larger, selecting values for these parameters cause postback, and slow in performance from user perspective. I am looking for a way to reduce postback. Can anyone suggest the way to retrieve the dataset all once and filter the dataset without causing postback? Or any other way to improve the performance will be greatly appreciated.
Thanks,
NL
View 7 Replies
View Related
Dec 4, 2006
In the standard report manager I'm running a report with 5 parameter choices. The first 3 are determinants of the 4th, and the combined 1-4 determine the 5th.
Although this works fine in my VS BIDS (sp1) with SQL 2005 sp2 CTP, when I deploy it to my sp2 CTP Report server, it throws an error as soon as I select the 3rd parameter.
One or more data sources is missing credentials
I found this answer from MS in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923684&SiteID=1) and since it deals with my problem, wanted to pass it along to anyone else that is dealing with this problem.
" This is a known regression with CTP2 that will be fixed before SP2 is released. The problem is not specific to multivalue parameters. It has to do with dependencies between parameters.
In some cases, you can work around the issue if you give the dependent parameter a default value. You can also work around the issue by removing the valid values dependency between the parameters, though this may not be possible for a given report."
The suggested workaround does work! I can get beyond the 3rd parameter now because I applied a default value!
View 2 Replies
View Related
