How Do You Set Parameters On A Cube?
Oct 30, 2006
I had no problem setting parameters with a relational database. But with a cube its so much more difficult. When i check the parameter box in the dataset it gives me way too many options..when i only want 1999, 2000, 2001, 2002, 2003, 2004, 2005 and 2006. Instead, its displaying all the quarters in each of those years, so i get this long list. Is there any way i can fix that. And actually get the parameter to work. Because when i click one, it doesnt look like it queries the report. Whats the process for putting a parameter in the report? Am i missing something?
View 4 Replies
ADVERTISEMENT
Mar 10, 2008
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in the following scenario:
I have a dimension "Period" as Paremeter.
The Period dimension has a hierarchy as year- quarter- month.
So, I would like to create a drilldown parameter for period as year - quarter - month.
Please let me know, how to acheive this.
thanks,
Naveen.
View 1 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
Mar 11, 2008
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in creating a cascading parameter.
The following scenario:
I have a dimension "Period" as Paremeter.
The Period dimension is cascaded to year- quarter- month.
So, I would like to create a cascading parameter for period as year - quarter - month.
Please let me know, how to acheive this.
thanks,
Naveen.
View 5 Replies
View Related
Jan 21, 2008
I am using a 2005 SSAS cube as a datasource for 2005 SSRS report with multivalue parameters. I use the design mode query builder to generate my MDX. I then add my parameter to my parameter field in the filters section. The name and value of the parameter in the dataset editor is as follows:
Name: FacilityFacilityNumber
Parameter: Parameters!FacilityFacilityNumber.Value
I then proceed to layout mode and then open up report parameters. I then check the multivalue box and select non-queried available values. I choose non-queried radio button because I want my asp page to be passing in the parameters, not a dropdown list.
The problem is that when I try and preview the report and I type in just one parameter I get the following:
"Query (1, 437) The restrictions imposed by the CONTRAINED flag in the STROSET function were violoated"
After looking through a couple of forums I found that adding the following Parameter value in the data tab and dataset editor would help me out a bit:
"[Facility].[Facility Number].&["+Parameters!FacilityFacilityNumber.Value+"]"
Ok so now (in layout mode) I type in one parameter and I get a report back, BUT when I try to type in a second or more parameter I get the same error as before:
"Query (1, 437) The restrictions imposed by the CONTRAINED flag in the STROSET function were violoated"
Here is the MDX query that I am using for this report:
SELECT NON EMPTY { [Measures].[Claim Count], [Measures].[Item Reasons Count], [Measures].[Charged Amount] } ON COLUMNS, NON EMPTY { ([Facility].[Facility Number].[Facility Number].ALLMEMBERS * [Claim Status Codes].[CLaim Status Code].[CLaim Status Code].ALLMEMBERS * [Claim Status Codes].[Claim Status Description].[Claim Status Description].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@FacilityFacilityNumber, CONSTRAINED) ) ON COLUMNS FROM [Claims Remittance]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Please, if anybody know's what I am doing wrong I would greatly appreciate it.......I have to have my report accept non-queried multivalued parameters. Thanks
View 1 Replies
View Related
Oct 15, 2007
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
View 7 Replies
View Related
Aug 23, 2015
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
View 5 Replies
View Related
May 19, 2015
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
View 2 Replies
View Related
May 27, 2008
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Oct 20, 2006
Can any body help me to know about cube in dataminig?
How is the cube represented in database?
How to querry the cube?
View 4 Replies
View Related
Feb 4, 2005
unable to browse the cube - unspecified error - ms sql server
--- even the sps are up to date..
what is the missing link...
thank u
View 1 Replies
View Related
Mar 3, 2005
I have an archieve cube as .cab file and i have restored my cube into analysis server , but i am not able to browse the datain the cube. My doubt is- Does the cube contains data with it to browse or not? or we have to explicitly enter data at the sql query analyser.
Thanks in advance.
View 3 Replies
View Related
Sep 8, 2005
Hi ALL,
Just started researching Analysis Services product, and am playing w/ foodmart db (located on my C: drive) and creating cubes and dimensions, etc.
My problem is that I cannot browse any dimensions once they are created. For exampe, for a dimension called product I am getting an error message, "Unable to browse the dimension 'product'. Unspecified error"
Can anyone offer help?
thanks so much,
Alan
View 2 Replies
View Related
Jan 26, 2004
1.Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.
2.Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements:
a.IS NULL-
b.GROUPING-
c.AS-
3.Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.
4.Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows.
a.A CUBE query with Grouping used to distinguish Null values.
b.A CUBE query showing a multidimensional cube.
c.A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.
5.Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.
View 3 Replies
View Related
Feb 24, 2004
I have just started working the 2047 OLAP and came arcross the Analysis Service Limits. It states that the levels in a cube has a limit of 256 and the leves per dimension is 64. I am confused.
What is the definition of (levels in a cube)! and how are the levels in a cube different from (levels per dimension)
View 1 Replies
View Related
Mar 25, 2004
I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office.
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.
Any inputs will be highly appreciated!
Thanks.
View 3 Replies
View Related
Aug 21, 2007
I have installed SQL Server 2005 on a server which runs Windows Server 2003 with SP2. And I have two client machines, both running Windows XP with SP2. Machine A has a full version of SQL server 2005 installed, including database engine, analysis service server, reporting service server. Machine B only has workstation components installed, BI development studio, SQL Server management studio.
I have developed a couple of analysis service projects and deployed to the server. On machine A, I can connect to the server and browse cubes no problem. On manchine B, I still can connect to the server and see the projects I deployed, even browse dimensions data. But when I browse cubes, management studio will be hanging there, not doing anything. Eventually I will have to kill it. I also opened the projects on machine B from BI management studio and try to browse data from there, it shows 'loading data' and doesn't do anything either.
Does anyone experience this type of issue? It's really frustrating as it doesn't show me any error message.
Thanks for any help.
View 1 Replies
View Related
Sep 11, 2007
We are using SQL 2005, Visual Studio 2005, SSIS, and SSAS. We have built our Dimensional model in SQL 2005, we have build our packages to complete full refresh of Dimensional model using SSIS. We have built SSAS cube using VS 2005. We built source, data source, cube and dimensions using auto build. We processed cube in VS 2005 by right clicking on solution and click process. Cube was built in Analysis Services. We made some schema changes to model then data changes in SSIS packages. We then pulled up cube in VS 2005 right click on solution and process. Cube is being re-built. After completion we check cube using Proclarity and Excel 2007 and notice the schema changes and data changes did not take. We dropped cube, then deleted data source, dimensions, and cube then re-created data source view and cube auto build then process and now have new schema changes and data changes.
Why is process not working to re-build schema and data changes when we have process FULL selected, Changes only. We even tried rebuild, deploy, and process. What is it we are missing or not doing correctly?????????
Topic
View 7 Replies
View Related
Feb 19, 2008
Can anyone suggest a case-study/reference links/video demos for creating a cube using Analysis Services and generation of reports using Reporting Services.
Thank You
View 2 Replies
View Related
Jul 20, 2005
Hi All,I want to create a cube using MSSQL2000 i dont know any thing how tocreate and what is parameter required so if any body help me i will bethankfull to him/her.For information : i have 4 tablescustdetailtable which contain all about a customer as per examplecustomer ID,name,add,CUSTOMERID IS PRIMARYKEY,Proddetail table which contain all about a product meanproductcode,productname,productvalue,dman (dateof manufacture),dexpdate of expire,etc etc PRODUCTCODE IS PRIMARY.Sales this table contain data aboutsale mean sales detailcusomerid,productcode,noofpieces,priceperunit,prod uct name,date ofsale. etc etc SALEID IS PRIMARYKEY,PRODUCTCODE AND CUSTOMERID ISFOREIGNKEY AND PRODUCTCODE IS PRIMARYKEY,and atlast i have a table name stok detail which contain productid,DTOFman and DTofexpire and amount of producthere product id,DTOFman and DTofexpire are foreign key.
View 3 Replies
View Related
Oct 25, 2007
Is it possible for RS to connect to a Cognos cube?
If so, where can I find information on it especially how security within cognos is evaluated/verified.
View 2 Replies
View Related
Sep 16, 2007
Hi,
Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use???
Are they usefull in any point of view from ADO.NET???
View 5 Replies
View Related
Sep 17, 2007
The GUI in RS does not let me hand edit the underlining query, so how do I define MDX functions like TOPCOUNT?
View 5 Replies
View Related
Nov 5, 2005
I right click on my AS cube and click "Process". It tells me the server content is out of date would I like to build and deploy first. i say yes.
View 31 Replies
View Related
Jan 11, 2007
Trying to drillthrough one report to another. But im not getting the correct values. I dont know why its not working. I created the parameters and linked them in the properties of the clickable field. What am i missing? or is it more complex in a cube to drillthrough then relational tables?
View 3 Replies
View Related
Oct 8, 2007
Hello,
I've gotton both the sprocs in these tutorials to work in the C# app:
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4503.aspx
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/2271.aspx
But when I try to call them in the Managment Studio I get the following error:
"No cube specified. A cube must be specified for this command to work."
What am I missing here?
Thanks in advance,
Adam
View 8 Replies
View Related
May 22, 2008
Hi All,
I have 3 cubes in a single SSAS database and these cubes should be processed using the following schedule
Cube 1 - Every Day
Cube 2 - Every Week
Cube 3 - Every Month
Cube 4 - Every Day
The issue that I face is that these cubes share the dimensions and so I cant do a FULL process of these SHARED Dimensions as it will affec other cubes.
I can expect additions and deletions to my dimension data , but the structure remains the same. It would be great if someone can suggest how to go about processing the dimensions. I am confused with the number of options(Process Incremental, Process Update etc.,) available for processing the dimensions.
I will creating a SSIS package to automate the processing. One more question is say, if Cube 2 fails during a day and Cube 1 has succesfully processed on the same day earlier, how do I revert back to the old state of Cube 2? Does this mean that I need to do a back up of the SSAS database before processing each cube?
Apologies if I had missed anything.
Thanks!
View 1 Replies
View Related
May 24, 2007
can i create a cube by VB.net?
I wanna a dynamic Cube, so I can not define it in the Analysis Services Project.
So the problems come.
I must deploy and process and compile the cube.
That can be done?
thank you.
View 4 Replies
View Related
Dec 5, 2007
Hi All,
i got a errors while creating a cubes. error comes like
Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Fact_Trng, Column: fct_trng_per_from_key, Value: 5/25/2006. 0 0
could u please solve thisa problem
View 1 Replies
View Related
