Issue With MDX Query Builder In Producing Results For Measures That Reference To Levels In Dimension
Feb 20, 2008
Folks,
I have an issue in producing output for the following query in MDX Query builder avaialable in BIDS. the error message I get is:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)
Code Snippet
WITH MEMBER [Measures].[Annualized Vs Target] AS
'[Measures].[Revenue]-[Measures].[Target Revenue]'
SELECT
NON EMPTY {
([Measures].[Balance],[Year].[Year - Year].&[2006]) ,
([Measures].[Balance], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2006]),
([Measures].[Target Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Annualized Vs Target], [Year].[Year - Year].&[2007])
}
ON COLUMNS ,
NON EMPTY { ([Client - Coverage Region].[Region Name].[Region Name].ALLMEMBERS *
[Client].[Client Name].[Client Name].ALLMEMBERS)} ON ROWS
FROM [MIS POC FINAL]
Do Anybody has a solution for this? I require the output to be in the following format :
Region_Name(RegDim) Client_Name(ClientDim) Balance_2006 Balance_2007 Revenue_2007 TargetRevenue_2007
USA A 20000 30000000 40000000 5000000000
B 60000 70000000 80000000 9000000000
Note: We can execute the above query in SSMS-MDX Query window without any issues.
Many Thanks in Advance for any assistance.
Subhash Subramanyam, Suraj Magdum
I have created a cube in Analysis Services with a time dimension named Time. The data is only needed at the month level, so the fields in the table that the dimension is based on are [DTE MM] and [DTE YR]. In Visual Studio 2005 or in SQL Server Mgmt Studio I can browse the dimension and I see that Time has the following attributes: Year and Month both Regular attributes and Time (Key attribute). There is also a hierarchy named [Year - Month] and if I browse that it looks good dril down from All to Year to Month.
However, when I point my Reporting Services Datasource to the cube, and start Query Builder, I see two dimensions, [DTE MM] and [DTE YR] and no Time dimension.
This is causing me huge problems in my report. I need to use a date range in the query. To do this, I created Query Parameters and refer to Report Parameters that will be passed in. For example, I use ="[DTE YR].[Year - Month].[Year].&[" + Parameters!StartYear.Value + "].&[" + Parameters!StartMonth.Value +"]" (thanks to Simon Philips) as the query parameter for the Start Year Month, but if I use the [DTE YR].[Year - Month] in my SELECT, the data is not sliced in my query results. That is to say that the Year and Month show correctly, according to the date range, but the Measure is equal for each month and is equal to the total for the cube. To slice the data, I have to use [DTE YR].[Year].[Year] and [DTE MM].[Month].[Month] in my select, but then the data is shown for every month, i.e. the date range is ignored.
What am I doing wrong, or is this a quirk of RS that I can work around?
hello at every body.. well i have a problem. i want to create a report which will present two measures vued with the same dimsneion but with different values.for example i want to present sales bu country and i want to present at the report the sales for the US and the sales for UK at the same time
indicator1=([Measures].[sales],[country].[UK]),
indicator2=([Measures].[sales],[country].[US]).
Further more i want to create two parameters which take the same values in order to change the values of indicators..
i have creted the datasets for the parameters and the parameters but i don't know how to code the main data set in order to change the values of indicator 1 with the values of parameter 1 and the values of indicator 2 with the values of parameter 2
I have a Star schema based dimension called Customer which has these levels:
ALL Customers Level1: Customer Type Level2: Customer Sub Type Level3: Customer Name
When a user is browsing the cube, is it possible to hide the the 1st level (and all it's sub-levels)? For example, If the Customer Type = "Low Ranked" then I do not want it to be displayed to the user while (s)he is selecting from the dimension. HOWEVER I only want it to be hidden from being displayed but it's effect should always reflect e.g. Suppose: [list=1] Sales (measure count) for Customers with Type "High Ranked" = 100 Sales (measure count) for Customers with Type "Medium Ranked" = 50 Sales (measure count) for Customers with Type "Low Ranked" = 10 [/list=1] Now if the user selects 'ALL Customer Type' in the dimension he/she should get a total Sale (measure count) of 160 (i.e. 100+50+10).
However when the user expands the Customers Dimension (i.e. ALL Customers), the resulting child nodes should only list 2 nodes i.e. High Ranked and Medium Ranked.
I went to the cube editor --> Advanced Properties and looked at the 'Hide Member If' property but amongst the 5 options there is none which allows me to specify the criteria.
Maybe the solution already is in one of those 5 options and thus please help me.
I am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:
Gross Amount applies to all clients, whether Type A or Type B. I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither. I would like to return blank/null, not $0.00, for those values that should not be displayed.
Here's the basic query.
SELECT { [Measures].[Gross Amount], [Measures].[Type A Net Amount], [Measures].[Type B Net Amount] } ON COLUMNS, NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] * [Dim Client].[Child Client Code].[Child Client Code] * [Dim Client].[Is Type A].CHILDREN * [Dim Client].[Is Type B].CHILDREN } ON ROWS FROM ClientInfo
I've 2 measures that I would like to swap according to which member in a dimension are selected by the user:Ex.
[Measure].[A] [Measure].[B]
Dimension called TEMP with 2 members (HOT and COLD).So if the user choose HOT it should show [Measure].[A] and if the user choose COLD it should show [Measure].[B]If the dimension is not included in the analysis by the user or if no dimension attribute are choosed then it should default to [Measure].[A].How to do that as Calculated Script in the SSAS cube ?
The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.
Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).
Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.
Tested on SQL 2000 only. Please post any errors found.
Edit 2006/10/10: Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.
This script finds table references and ranks them by level in order to be able to load tables with FK references in the correct order. Tables can then be loaded one level at a time from lower to higher. This script also shows all the relationships for each table by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except themselves, and are only referenced by higher level tables or themselves.
Levels 2 and above are tables which reference lower levels and may be referenced by higher levels or themselves.
declare @table table ( TABLE_NAME nvarchar(200) not null primary key clustered ) set nocount off
print 'Load tables for database '+db_name()
insert into @table select TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME from INFORMATION_SCHEMA.TABLES a where a.TABLE_TYPE = 'BASE TABLE'and a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties' order by 1
print 'Load PK/FK references' insert into @r selectdistinct PK_TABLE = b.TABLE_SCHEMA+'.'+b.TABLE_NAME, FK_TABLE = c.TABLE_SCHEMA+'.'+c.TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and a.CONSTRAINT_NAME = c.CONSTRAINT_NAME order by 1,2
print 'Make copy of PK/FK references' insert into @rs select * from @r order by 1,2
print 'Load un-referenced tables as level 0' insert into @t select REF_LEVEL = 0, a.TABLE_NAME from @table a where a.TABLE_NAME not in ( select PK_TABLE from @r union all select FK_TABLE from @r ) order by 1
-- select * from @r print 'Remove self references' delete from @r where PK_TABLE = FK_TABLE
declare @level int set @level = 0
while @level < 100 begin set @level = @level + 1
print 'Delete lower level references' delete from @r where PK_TABLE in ( select TABLE_NAME from @t ) or FK_TABLE in ( select TABLE_NAME from @t )
insert into @t select REF_LEVEL =@level, a.TABLE_NAME from @table a where a.TABLE_NAME not in ( select FK_TABLE from @r ) and a.TABLE_NAME not in ( select TABLE_NAME from @t ) order by 1
if not exists (select * from @r ) begin print 'Done loading table levels' print '' break end
end
print 'Count of Tables by level' print ''
select REF_LEVEL, TABLE_COUNT = count(*) from @t group by REF_LEVEL order by REF_LEVEL
print 'Tables in order by level and table name' print 'Note: Null REF_LEVEL nay indicate possible circular reference' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME order by b.REF_LEVEL, a.TABLE_NAME
print 'Tables and Referencing Tables' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME), REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME left join @rs c on a.TABLE_NAME = c.PK_TABLE order by a.TABLE_NAME, c.FK_TABLE
print 'Tables and Tables Referenced' print '' select b.REF_LEVEL, TABLE_NAME = convert(varchar(40),a.TABLE_NAME), TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE) from @table a left join @t b on a.TABLE_NAME = b.TABLE_NAME left join @rs c on a.TABLE_NAME = c.FK_TABLE order by a.TABLE_NAME, c.PK_TABLE
I ran my SP in QA and it brought back some data from my live database but I had my test DB selected from the DDL at the top of the screen. So i ran a Select statement on the whole table in a different query window (with the same parameters)and it gave me the correct info. Any idea y? They both should be bringing back information from the same table not one from my live DB and one from my TEST DB. Please help me--I'm so lost!min max rate <----This is what my stored procedure brought back(data from my live DB) ---it has parameters 90 100 .4080 89 .3070 79 .2559 69 .1549 58 .10The stored procedure is as follows: CREATE PROCEDURE dbo.sp_WageMatrix_GetRate( @CompanyID nvarchar(2), @FacilityID nvarchar(2))AS SET NOCOUNT ON;SELECT [Min], [Max], Rate FROM Performance.dbo.WageMatrix WHERE (CompanyID = @CompanyID) AND (FacilityID = @FacilityID) AND PeriodID = dbo.fn_GetCurrentPeriod(CompanyID, FacilityID)ORDER BY RangeIDmy select statement brought back the following:min max rate 90 100 .4080 89 .3070 79 .2560 69 .15and I ran the following select statement: SELECT [Min], [Max], [Rate] FROM [PerformanceDEV].[dbo].[WageMatrix] where companyid = '21' and facilityid = '01' and periodid = 2order by rangeid
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID LEFT JOIN Customers ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
How do I get my data to show starting at the first row instead of skipping down?
Refer to the attachment.
Code: CREATE PROCEDURE [dbo].[uspReportData] -- Add the parameters for the stored procedure here @Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL, @Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
I would like to have a parameter on my report to Select by Order_Taken_By. My attempt at that is here:
Dataset called OrderTaken:
select distinct SO_Header.Order_Taken_By from SO_Header Under the report Parameter Properties named @Order, under Available Values Selected "Get Values from a query"
I have DataSet: OrderTaken
Value Field: Order_Taken_By Label Field: Order_Taken_By
The values of the field Order_Taken_By is all text characters, no integer values.
Running the inital Query by itself yields results. When I add the parameter, I can make a selection, but now I get no results, even though there should be values for the choice I've chosen.
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field. Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below: SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2], Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2], TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName FROM Ticket INNER JOIN TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID WHERE (Ticket.CreatedDate >= @StartDate) ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.
In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.
I'm using the Bush family in this example, these are the relationships: - George SR --- George JR ------ Jenna ------ Barbara --- Jeb ------ Jeb JR ------ Noelle
These statements will create and populate the tables for the example with the above relationships:
SET NOCOUNT ON DROP TABLE KK_Kids, DD_Dads, GG_Grandpas CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20)) CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20)) CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))
INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR') INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR') INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB') INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' ) INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' ) INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' ) INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )
So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads. I am getting this, which is not what I want:
- George SR --- George JR --- Jeb ------ Jenna ------ Barbara ------ Jeb JR ------ Noelle
SELECT 1 as Tag, NULL as Parent, GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName], GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id], NULL as [DD_Dads!2!DD_DadName], NULL as [DD_Dads!2!DD_Dad_Key!id], NULL as [DD_Dads!2!DD_Grandpa_Key!idref], NULL as [KK_Kids!3!KK_KidName], NULL as [KK_Kids!3!KK_Dad_Key!idref] FROM GG_Grandpas UNION ALL SELECT 2 , 1 , NULL , GG_Grandpa_Key , DD_DadName , DD_Dad_Key , DD_Grandpa_Key , NULL , NULL FROM GG_Grandpas, DD_Dads WHERE GG_Grandpa_Key = DD_Grandpa_Key UNION ALL SELECT 3 , 2 , NULL , GG_Grandpa_Key , NULL , DD_Dad_Key , NULL , KK_KidName , KK_Dad_Key FROM GG_Grandpas, DD_Dads , KK_Kids WHERE GG_Grandpa_Key = DD_Grandpa_Key AND DD_Dad_Key = KK_Dad_Key
FOR XML EXPLICIT
I've tried it all different ways, but no luck so far. Any ideas?
I keep running into a problem with the debugger freezing up. I'm trying to pull results from a query into an array (not more than 10 rows)
cmd.ExecuteReader();
while (rdr.Read() == true) {
letter = rdr[0].ToString();
i += 1;
}
if I comment out the "letter = rdr[0].ToString();" portion of code, the project runs fine. With that code there, I can not step into the proc from the SQL test script. No errors are raised. VS just freezes.
When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.
When I try and run Report Builder Reports i get this error message "Object reference not set to an instance of an object. "
I can run reports locally but not from Report manager
here is the stack trace info
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] Microsoft.Reporting.WebForms.WebRequestHelper.GetExceptionForMoreInformationNode(XmlNode moreInfo, XmlNamespaceManager namespaces) +18 Microsoft.Reporting.WebForms.WebRequestHelper.ExceptionFromWebResponse(Exception e) +358 Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +482 Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +958 Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +84 Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +143 Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
Facing a strange problem, but obviously not expected earlier on, I am trying to execute a stored procedure via ADO which refrences a linked server table and I get an error specifying " OLEDB provider SQLOLEDB does not contain table "<DatabaseName>"."<owner>"."<tableName>" "
Has any one else encountered this problem before ? regards Govind
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
I am new to Analysis services. and I have figure out the following things:
I have a cube with multiple dimensions. I want to display all the options in cube along with the count of the rows for that combination. I am unable to understand how to count the dimensions and apply to column.
For example, I have say dim1, dim2, dim3 dim4, name as dimensions and cnt as count of the rows. Now I want to write an mdx expression that returns me dim1, dim2, dim3, dim4 with count(name fields) for above combination so the output will be as follows:
name field1 name field2 dim1 dim2 dim3 dim4 200 100
how do I do it?
Also how do we accept input parameters for mdx expression. for example in the above thing, if I have to accept an input for dim1 and display the output values for dim1 how do I do it?
I am not sure the SQL code to use...I am to produce a list that shows total sales by customer for the first two months of the year. I am to show the customer name, customer number, and total sales in that order. The current format I have it in list every invoice separately and what I need to do is find a way (the code) to combine the invoice totals into one total amount and only have the company name listed once. I also do not know how to set a date range so it includes invoices only from 1/1/2005 to 2/28/2005 Right now this is the current list and SQL code I have:
NameCustomer NumberDateSales Ace Construction Co.100023/18/2005$62.25 Ace Construction Co.100022/13/2005$62.25 Ace Construction Co.100022/13/2005$97.50 Ace Construction Co.100022/13/2005$89.00 Ace Construction Co.100022/13/2005$194.25 Ace Construction Co.100021/29/2005$24.90 Ace Construction Co.100023/18/2005$67.80 Ace Construction Co.100023/18/2005$304.15 Ace Construction Co.100021/29/2005$695.00 Ace Construction Co.100021/29/2005$19.50 Ace Construction Co.100023/18/2005$299.00 Aho Electrical Services100233/20/2005$449.55 Aho Electrical Services100233/20/2005$149.55 Aho Electrical Services100233/20/2005$994.70 Aho Electrical Services100233/20/2005$276.45 Barren Construction100501/30/2005$298.41 Barren Construction100502/25/2005$62.85
SELECT DISTINCTROW tblCustomer.CustomerName, tblSales.CustomerNumber, tblSales.InvoiceDate, [SInvPrice]*[SInvQuantity] AS Sales FROM (tblCustomer INNER JOIN tblSales ON tblCustomer.CustomerNumber = tblSales.CustomerNumber) INNER JOIN (tblInventory INNER JOIN [tblSales-Inventory] ON tblInventory.InventoryItemCode = [tblSales-Inventory].InventoryItemCode) ON tblSales.InvoiceNumber = [tblSales-Inventory].InvoiceNumber ORDER BY tblSales.InvoiceNumber, [tblSales-Inventory].InventoryItemCode;
trying to make a script to view data in a excell spreadsheet.I can get all the data I need except one.select ordernumber, itemcode, quantity, rate, totalfrom dbo.chargeswhere ordernumber = '45676'the problem is there are more than one itemcodeexample: run resultsordernumber, itemcode, quantity, rate, total45676 fuel 123 .10 12.3045676 stops 3 50 150how do I get this data on one line?to look more like this: run resultsordernumber, itemcode, quantity, rate, total itemcode, quantity, rate, total45676 fuel 123 .10 12.30 stops 3 50 150now just one ordernumber appears and its all on one line.hope you can help, thanks for your time...Mike
I have created a form with box in MS Access. Later I added a query into MS Access. Now I want to make this query dynamic so that it can run with the value available in that box.
I tried several reference in "where" cluase of query but did not work.
I would like to know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?
I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?
Hi people!I'm looking for an SQL query builder application that helps me constructSELECT queries with full respect of underlaying relational model. It wouldbe nice if the app is database independant because my work covers MSSQL,mySQL and Oracle DB.OpenSource and/or comercial suggestions are welcome!*** thanx ***
HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.
When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas? i'm using sql server express. thanks
Hi All, For one of our projects, we are supposed to write a query builder to display reports in tabular format. For the filter criteria ( i.e where clause ) user can select from a list of predefined columns and assign conditions to it. For example, user can select 'CustomerName' as the column and specify condition where name begines with 'A'. Similarly he can also select ouput columns Problem The columns that user selects can come from different tables. We are planning to store mapping between tables and columns and also their primary and foreign keys in an xml file. But I would want to understand how to build the join clause. For example, say there are 4 tables customer, customer address, order, and items. Say user wants the number of items purchased by each customer who stays in 'Delhi'. 1. In output column he selects customer.name and count(items.id) 2. In filter criteria he selects address.city = 'delhi' How would the query builder know that to join customer and items, order also has to be joined. Any pointers would be helpful.
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.
I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.
Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.
