.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MDX Query in SSRS Dataset taking more time

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server

Hi All,

I have  created report based on below mention MDX query ,but it will take more time say of 10 mins to execute.while other report having bulky data in their datset working fine.

Kindly suggest how do i optimise the Query so it will take less time



member [measures].[A]as

member [measures].[B]as
iif([Measures].[Avg After Call Work]=0,0,[Measures].[Avg After Call Work])

member [measures].[C]as
iif([Measures].[Staff Time]=0,0,[Measures].[CALLSHANDLED]/[Measures].[Staff Time])

member [measures].[AA] as ((STRTOMEMBER(@EmployeeEMPLOYEENAME)), [Measures].[A] )
member [measures].[BB] as ((STRTOMEMBER(@EmployeeEMPLOYEENAME)), [Measures].[B] )
member [measures].[CC] as ((STRTOMEMBER(@EmployeeEMPLOYEENAME)), [Measures].[C] )

 member [measures].[Rank_Avg_Handle]as
(filter ([Employee].[EMPLOYEE NAME].[EMPLOYEE NAME].members,

 member [measures].[Rank_Aft_Avg_Call]as
(filter ([Employee].[EMPLOYEE NAME].[EMPLOYEE NAME].members,
iif([Measures].[Avg After Call Work]=0,0,[Measures].[Avg After Call Work])>[measures].[

View Complete Post

More Related Resource Links

SSRS taking a long time to run an MDX query



I have an MDX query which runs fine in the MDX Sample Application. It runs against an SSAS 2000 cube. In my query there is a recursion kind of a logic. It takes about 1-2 sec to run in the MDX Sample Application. My sample application is in the same server as my AS 2000 cube is. However when I run the same MDX from another server through BIDS (2008) it takes an unusually long amount of time to run. Most of the time it times out. :(

As a workaround I created 4 calculated members in my Cube itself (Initially these were used as members in my MDX). I thought that maybe the recursion logic is the culprit so I modified my MDX, removed the recursion part. But still from my SSRS it is taking a long time.

Any pointers to where I am missing the trick ?

Thanks in advance,


Why the Same sql query taking much more time in other server.

Hi All,   I have a sql query.. which is running absoulutely fine in the production environment. I restored the Prod DB to another server.. same hardware and software configuration as Prod server. But in that server the same query taking ____ lot of time.. What could be the reason ??SD

SQL Query is taking long time to run


Hi All,
SQL Query is taking long time to run in my report. It is using Clustered index seek when I am querying for 2 days on invoice_itemized table. It is returning 99 thousand records. It is returning  2524094 records and it is using clustered index scan when I am querying for one month. For one month It is taking 7 minutes.
Basically it is not using the index defined for the store_Id, status and datetime columns in the invoice_totals table. Can I force the sql query to use the specified index?

Please suggest me to improve the performance....

SQL Query

,Invoice_Totals.DateTime InvoiceDate
,Invoice_Totals.Discount InvDiscPercent
,Invoice_Itemized.LineDisc LineDiscPercent
WHEN Invoice_Totals.Total_Price <> 0 AND Inventory.ItemType=7 AND Invoice_Totals.Discount = 0 THEN ((Invoice_Itemized.PricePer * Invoice_Itemized.Quantity) * -1) / ((Invoice_Totals.Total_Price) + ((Invoice_Itemized.PricePer * Invoice_Itemized.Quantity) * -1))
WHEN Invoice_Totals.Total_Price <> 0 AND Inventory.ItemType=7 THEN ((Invoice_Itemized.PricePer * I

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Slow page load during a list query one time during the day


We have a monitoring tool set to check to see if the home pages for our 3 web apps load in under 60 seconds every 10 minutes.  All 3 web apps load in under 3 seconds on every 10 minute check except for one exception.  One check every day one of the web app's home page takes longer than 60 seconds to load.  This happens at 11:45PM when there is very little user usage on the environment.  The characteristics of this page are as follows: The only thing on the page is a list view web part which was added by the browser.  The page has not been customized with SPD or code at all.  The list that it pulls is a simple links list that has 281 items on it.  The view pulls all 281 items and displays them in sets of 100.  I cannot find any associated event in the server events and/or SharePoint ULS logs nor are there any daily sharepoint timer jobs running at that time.  Our full index happens at midnight with incremental happening hourly.  Our enviroment is 2 WFEs, 1 App/indexer and we have a separate SQL cluster backend.  

Could someone lead me possibly in the direction I should take next in my troubleshooting?   

Shared dataset in SSRS 2008 R2 running on SSAS 2005

I am trying to create a report in SSRS 2008 R2 using SSAS 2005 as a datasource. My dataset has a date parameter. When I create an embedded dataset in my report everything works fine, but when I create a shared dataset with the exact same mdx query, I get the following error: An error occurred during local report processing. The definition of the report 'xxxx' is invalid. The Value expression for the report parameter 'DateCalendarHierarchy' contains an error: [BC30034] Bracketed identifier is missing closing ']'. Does anybody have any ideas? Is this a bug? Thanks, Louis

Query Time out error?

Iam getting the querey time out error, when processing dimension in SSAS? The error Details is: OLE DB or ODBC error: Query timeout expired; HYT00; Unknown token received from SQL Server; HY000.\ can any one help me on this....how to resolve this?

SQLDataAdapter/SQLDataReader takes lot of time for executing a query

All, I have a webpage with 6 drop down lists. User can start with selecting any drop down list first, and then proceed in any order.  1. Assume when the 1st dropdown is selected first, rest 5 drop downs (except 1st) are filtered/updated based on the selected value in the 1st dropdown. 2. Assume user selects the 4th dropdown second, rest 5 drop downs (except 4th) will be filtered/updated but the condition would be based on both 1st drop down and 4th drop down. But while filtering the first drop down, only the 4th drop down is used as filter (self-filter will be ignored). The code works fine but I see a very bad performance hit in time when the user selects the fourth dropdown after selecting three drop downs. I tried using both SQLDataAdapter and SQLDataReader but neither used to solve the issue. The code takes a lot of time on one line in either of the code (which is highlighted below). When I run the query in SQLServer Mgmt Studio, the query completes in a few seconds.Using SQLDataReader: SqlConnection conn = new SqlConnection(CONNECTION); SqlCommand ObjCmd = new SqlCommand(query, conn); ObjCmd.CommandTimeout = 600; conn.Open(); SqlDataReader daSelTools = ObjCmd.ExecuteReader(); //This line takes a lot of time ddl.Items.Clear(); // Call Read before accessing data. while (daSelTools.Read()) { ddl.Items.Add(new ListItem(String.Format("{0}",daSelTools[0])));

SSRS 2008 R2 - Query builder freezes and needs to be shut down Report Builder 3 when attempting quie

Hi, In SSRS, using Report Builder 3.0 (SSRS R2), after choosing a Report Model as the report's embedded data source, launched "Query Deisinger…" from GUI to build data source in report.  First 1-2 attempts at querying underlying data source (Oracle back-end), are successful, but later attempts freeze application to the point where application freezes and overall Report Builder application needs to be forcibly shut-down. This system freeze has occurred when referencing tables containing a very small number of records as well as larger data sets.  Suspect that application freezing may be caused by timing-out of connection to back-end database. but I am not sure. Any suggestion to solve this issue? Thanks   Environment - Distributed deployment Server1(report_srv) --Windows server 2003R2 64bit, Installed sql server 2008R2 reporting service, configuration tools Server2 (report_db_srv) --windows server 2003r2 64bit, sql server 2005 for report server database

How can i show Transpose Query In SSRS ?

Hi, Any one help me for following Output. please tell immidiatly.  How I use this expression as follow: I have Database Table Like ----------------------------------------------------------- Date                 ShiftName                    Tons 7/21/2009               Day                          (Ans by Formula) 3 7/21/2009               Night                          (Ans by Formula) 4 7/22/2009               Day                           (Ans by Formula) 8 7/22/2009               Night       &

Get Query execution time

I need to find out the query execution time from the front end .Where should I insert the code for that.Please help.. I am using the bleow query: OracleConnection con = new OracleConnection(ConnStr); con.Open();  OracleCommand cmd = new OracleCommand("Stored_Proc",con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(); .................... ................ OracleDataAdapter oda = new OracleDataAdapter (cmd);  

Parameter Help in my Dataset query

I have a qurey in a data set in reporting services. What im trying to do is use the shipdateyear field as a parameter but i keep getting an error when im running it. I think its because how im getting the name shipdate year its coming from a field where im formatting the data to just show year.heres my query. its erroring in the where Clause i believe. any help would be awesome.   I have underlinded where i think my isues are.  Thanks.select bol.loadnumber, bol.ldhshipdate,bol.shiptocode,bol.destination,bol.shiptoname,bol.itemdesc,bol.abbrev,Datepart(week,bol.ldhshipdate) as 'weeknum',Datepart(weekday,bol.ldhshipdate) as 'weekdaynum',--case statement--Case when Datepart(weekday,bol.ldhshipdate) = 1 then 'Sunday'else case when Datepart(weekday,bol.ldhshipdate) = 2 then 'monday'else case when Datepart(weekday,bol.ldhshipdate) = 3 then 'tuesday'else case when Datepart(weekday,bol.ldhshipdate) = 4 then 'wednesday'else case when Datepart(weekday,bol.ldhshipdate) = 5 then 'thursday'else case when Datepart(weekday,bol.ldhshipdate) = 6 then 'Friday'else case when Datepart(weekday,bol.ldhshipdate) = 7 then 'saturday'else 'none'end end endendendendend as 'dayofweek',--case statement--ofstm.ostabr, ofstm.ostnam,ofstm.ostccd,Datepart(year,bol.ldhshipdate) as shipdateyear,--caseCase when d

MDX Query Measure with time period

Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

Expression for 24 hours time and date in SSRS

Hi Guys, What is the expression for  displaying 24 hours time and date in SSRS? Is this the format: =Format(now, "MM/dd/yyyy HH:mm:ss")?Thanks Shiven:)

MDX Query for Defualt Value in SSRS Parameter

Hello everybody, I am new to MDX and struggling to come up with a query that works.  I have a  SSRS report  that filters by week. I need to write a query that will run for last week as default. The query i have cooked up is: WITH MEMBER Measures.Lastweek as [Time Dimension].[Report Week].currentmember.lag(1) Select Measures.lastweek on 0, [Time Dimension].[Report Week] on 1 from [sales] This returns a null value. I have searched and searched the internet for the answer but i have found nothing. I would really appreciate some help.   Thanks.

How to use Multi DataSet in ssrs

Hi All , Am using ssrs 2005. In my Report (rdl) am using Stored Procedure to return multiple Results. For Example , While Executing Stored Procedure , it' s returning 3 select Statements .SELECT FROM Table1SELECT FROM Table2 SELECT FROM Table3 .. While using this Stored Procedure in Reports , Getting First Table Result alone ..There is no relationship with these Tables , so i can't able to use JOIN QUery also .. What's the Solution ? I want to write separate Procedure for all select Statements ??? Please any body have idea about this , help me...Thanks & Regards,Chitra.

SSRS 2008 R2 Dataset Field Value Property Data Type not numeric

We're building SSRS 2008 R2 reports using SSAS as data sources.  There are several occaissions where the data types of certain dataset field values that represent cube measures are not numeric.  As such, when these fields are used in an aggregate function they throw an error.  Is there a way to remedy this in the dataset? Thanks for your help.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend