How To Create Top 10 In The Dimension Or Drilldown
May 26, 2008
Hi,
some of our Drill down has many values and we would like to display only top 10 ...
I know how to create CALCULATEDMEMBER for the cube ..but that becomes a measure and will NOT be in the drill down so,
how to create Top 10 for Raw Url
Drill Down
Groups
Top 10 Urls
( this has 10000 values€¦€¦.. can we just display top 10 here? Right now the report never comes back because of the count€¦ or if there is a better way let me know
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.
Analysis manager won’t let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
I have a fact table which contains the transaction date, ProductID, QTySold, TotalSaleAmount, etc...
Since I am new to OLAP therefore I need help to now create the table for TIME on which I will be basing my time dimension... I have read a few articles and have gathered that at the end of the exercise my fact table should have a 'timeid' column which will be linked to the same column in the table being used for the time dimension...
I have gone through the tutorial of MS-Analysis Service and FoodMart example have some idea about what he structure of this table will be.
My questions are:
1. I need guidance on how to create the table for time. One option is to just copy the table used in the Foodmart example but thought that might work but my concept will not be clear
2. The structure of the table to be used for time dimension is quite clear (i think this part is easy). What I want to understand is that how do i POPULATE this table which data? Can some one provide me with scripts, SPs, or whatever to do this.... This is the area where I am lost...
3. How will I enter the "TheDate" column in my fact table and link it with my table for time dimension...
Looking forward to someone's help.
BTW, I would like to share a very good article which i recently found in one of the newsgroups. Some of you might like appreciate it too: http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk
I can manipulate this in a pivot table for reporting however I need this to be a table for reporting by filterable searchable information. What can I do short of defining each field with case logic, or union queries.
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name] Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name] Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Just found that I am not able to ignore the name column property for role-palying dimension even though I only select one of the key columns for this dimension in the mining structure where the role-playing dimension is used? E.g I have a fact table (which is the case table for the mining structure) which is related to the role-playing date dimension. The schema is as following:
Dim_event_day_time_key
Dim_carrier_day_time_key
Dim_domain_day_time_key
.....................................
and other attibutes in the fact table.
When I dragged the 'hours' attribute from the dim_day_time table to the mining structure, if I left the key columns of 'Hours' as the above 3 columns, then I have to select a name column, by then I dont have a name column in the dimension table yet. Therefore I want to jsut select one key column as the key of that role-playing dimension in the mining structure, but then I am not able to ignore the 'name column' property as it still always asks me to select the 'name column' for that.
Therefore my question is: we are not allowed to select only one key columns in the mining structure for the role-playing dimension? And we will have to always go to the data source view to create a named calculation as the new column for the role-playing dimension?
Hope my question is clear for your advices and I am looking forward to hearing from you shortly.
I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like
01/01/2004 00:01:00
01/01/2004 00:02:00
01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00
I will use this time dimension in bussiness objects.
The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g
1/1/2000 1/2/2000 1/3/2000 etc..
I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.Â
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on. Â
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.Â
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
In my data modell I have defined the 2 tables "Person" and "Category":
Table "Person" ---------------- [PersonID] [int] IDENTITY(1,1) NOT NULL [CategoryID] [int] NOT NULL [FirstName] [nvarchar](50) [LastName] [nvarchar](50)
Table "Category" ---------------- [CategoryID] [int] IDENTITY(1,1) NOT NULL [CategoryName] [nvarchar](50)
Now I like to read my first row from the source and lookup a value for the CategoryID "sailing". As my data tables are empty right now, the lookup is not able to read a value for "sailing". Now I like to insert a new row in the table "Category" for the value "sailing" and receive the new "CategoryID" to insert my values in the table "Person" INCLUDING the new "CategoryID".
I think this is a normal way of reading data from a source and performing some lookups. In my "real world" scenario I have to lookup about 20 foreign keys before I'm able to insert the row read from the flat file source.
I really can't belief that this is a "special" case and I also can't belief that there is no easy and simple way to solve this with SSIS. Ok, the solution from Thomas is working but it is a very complex solution for this small problem. So, any help would be appreciated...
I've been playing around with SSRS 2005 to see if it might be the solution for some reporting requirements I have been given, but I still haven't found a way to do exactly what I need. Basically I've been asked to provide a report which allows drilling down multiple levels of a client hierarchy to show some sales figures, sounds pretty simple right? The report will look like this (not showing sales figues here just client hierarchy)...
+ Client A - Client B - Client C - Client D + Client E - Client F - Client G - Client H - Client I - Client J + Client K + Client L
As you can see they want to be able to expand & collapse rows to drill down through their client hierarchy. The hierarchy will be 3 or 4 levels deep.
I have seen that RS2005 supports a simple way to drill-down via grouping, but I don't want to do it this way because it means that I will have to have a query which retrieves all of the data right away which would take several minutes.
What I'd really like to do is to lazily load the hierarchy so that the data is loaded as the tree is expanded so that it won't take too long to render the report initially. I.e. when they click on "Client B" I'll hot the DB and get the sales figures for all children of Client B, in the case above thats Clients C & D.
Can this be achieved using sub reports or report linking? If anyone knows of some tutorials or examples that might be able to help me with this I would really appreciate it.
I am creating report which has group and I want to show the [+] in front of each grouped item and when user click this the group item will get displayed.
I'm new to SQL Reporting Service. Now only I've started studying that. My question is,
I wanna list out my product sales for each month for each keywords. For example, I have 4 products, some 15 keywords (its an ecommerce site. Visitor coming from a particluar keyword and buying some products. Wanna list out sales for each keywords and product for Every month. To know the quarter sales wanna see the sales and product name for particular keyword). I'm having reports for each month sales. like,
Keywords Product A Product B Product C Product D
Keyword 1 125 24 275 12
Keyword 2 456 245 67 1223
For quarter sales, I want to see the sales and keywords list for each month and for each products in the same report. I don't want to create one another report for each and every product and month. If I want to see the sales and keywords for a particular product I want a report like,For Product 1
Keywords Month1 Month2 Month3 Month4
Keyword 1 125 24 275 12
Keyword 2 456 245 67 1223
and For Month 1
Keywords Product A Product B Product C Product D
Keyword 1 125 24 275 12
Keyword 2 456 245 67 1223
Just by using drilldown I want this report. If any other method is possible, please tell me. It's very urgent. Please Help me. I'm very new to SQL Reporting Service. Started studying before 2 days. Thanks.............
When i am designing a report using BIDS how do i control the Drilldown on the Matrix Totals. At present when i created a drill down report it works fine for all non total Values but chooses a random Value when it drills on Totals (Usually the first but not always)
Eg if i have a Matix that looks like this
Name, Value1, Value2, Value3 John, 1,1,1 = 3 Mark 2,2,2 = 6 Fred 3,3,3 = 9
Total 6,6,6
If i want to Drill on either the totals at the bottom or the totals on the Right or the bottom, it will choose a random Value eg if i want ot see the totals for John the Column Parameter that it passes to my drill through report will be that for Value 1 which is incorrect.
I am writing a report that has two levels of groupings like this and the visibity is controlled by Title and Staff Grouping (Partner, Employee) for drilling down.
WTD Hours *Staff
*Manager
Joe 8 Max 8 Totals 16 *Senior Manager
Bob 7 Totals 7 Totals 23 *Partner
*Partner
Ed 20 Total 20 Totals 43 * = Toggle Item
Now, all this is fine except that when everything is collapsed, I want to show the totals on the group header and hide the group footer, like this:
I have the functionality working with the toggle states and visibility, but whenever the group is collapsed, all the formatting is lost and it is just whitespace not matching the rest of the row.
Is there any way to control this by some expression in the value, etc?
I'm basically learning SSRS (and SQL for that matter) so bear with me. I'm building some reports and discovered that apparently one has to have the Enterprise edition to have the drilldown capability. We really don't need the Enterprise edition for anything else, being a small company. Is there absolutely no way to do drilldown in the Standard edition, is there any way to add just that capability, and are there any sort of workarounds? Thanks.
I have a report that drills down into 2 different levels like this:- Main Report -> Report -> Detail Report.
Everything works fine for the first 2 reports but when I try to drill down to the 3rd report (Detail Report in my example above) I keep gettting this error:
The path of the item '(null)' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath) Has somebody got a solution or a workaround?
I have two servers running the same report server project with the same webapplication front end.
Both instances of SQL are on SP2.
When I drilldown in the report viewer on server A, and click next page it returns to the parent report. (which obviously it should go to the next page of the current report) Sometimes this odd behavior happens and sometimes it doesn't.
However on server B, everything works fine.
Note: the installation on server A went a littly crazy. Am I missing a service pack that would have fixed this?
When runing a report in Report Manager I can click on an item and SQL Server provides the logic to drill down into the underlying tables. I can find no button to go back. The only way I've found to go back is to click a different tab at the top of the screen (History or something) then click View again and you get the original report. In designer there's a back button. Is there not such a button when running a saved report????
I have a drilldown matrix report similar to the one below...state - drilldowns to sales person which lists the sales for a particular product...in each state there are usually more than 10 names, however i would only like to see the TOP 10 name appear for the sale off that product
My SQL is as follows
SELECT Sales,Consultant, ProductNo, State, Sum(Value) As SumOfValue,
From tblSales where productNo = 2000
ORDER BY SumOfValue Desc;
If I put in the select TOP 10 it returns only the top 10 for all the states however i need the top 10 off EACH state, is there a way to filter this..or better yet a sort button which will show the top 10 only.
Hi I€™m having trouble again with a matrix report. I wish to make a matrix report with 5 columns. The right part of course will be filled depending on how many moths the user has chosen. The left part will be field with country, city and then the person names and some other info.
What I want to do is to have the first 2 columns, country and city, with drilldown. That is pushing the plus sign to open the corresponding group. How do I do this?
Im having a silly question, but im not able to find a solution for this. Im having a simple tabular report in which i have 3 groups defined and a detailed row. It is a drill down report wherein we need click on each group to get the next group and so on. When i went thro the wizard i was able to get that, so i tried myself without the wizard by adding the table and groups manually. However i found for the details grouping i was not able to set the "Visibility can be toggled by another report Item" because it was disabled, but in the report which i generated thro. the wizard it was selected and the last group item was given in that. Im not able to find why the checkbox is disabled when i create the groups manually. Please Help.
Hi, I have a table with three group headers. I want the header row in the third section to be toggled by a textbox in the second header. This works as expected. However, there are times when there is no data in the third section. When this happens, the third section header row shows even though there is no data. I can also create an expression for the third section header's hidden property that looks for the existence of data, but then the toggle is deactivated. I need some way to say, "here's the toggle item. Show the header if there is data for it."
I have 2 matrix in my report and I have the drilldown colunmns year, quarter, month and day. But when I click in year of matrix1 I want to expand the quarters for matrix 1 and 2... and not only for the matrix 1... How can I share the columns with other matrix?
I trued to use textboxes, but in textbox I only see one item... Q1, January... etc... in spite of have all the childs...
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
I have a drilldown report includes three groups. I add the last group for pagination. But the details in each page doesn't accord to I specified and the detail record number is different in different page.
Another issue is the interactive sort always sort in the first page scope. I set the data region or grouping to the table, and evaluate expression scrope to Detail scope.
I have a report that (for purposes of this example) displays States and the Cities in a state.
Now the State is the first group which I want to drill down into by expanding and then show the Cities as the detail line.
What I would expect to happen (and what does happen if I use the wizard instead of creating the report manually) is that I would click on a State and then the page would expand to make room for all the cities listed below.
(That is to say if I am showing only states, not expanded, then all 50 states fit on a single page. Then if I expand California all the states below that shift onto other pages to make room for the 400+ cities in California)
However, what happens is that when I preview the report instead of being 1 page long it is many pages in length with empty white space between the States (white space that would be occupied by the cities if I were to expand the state).
Can someone tell me what setting I need to adjust to get the report to dynamically resize when I expand/collapse groups?
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Hi,Basically I have a Matrix report with two column drilldown parameters, named "result" and "detail of the result". Then When I click on the detail cell I am passing two parameters to filter the linked report.The first one is quite direct I pass it and it's allright.The second one has me crazy... If I pass it while the second column is collapsed, the first value is passed.... When I am not choosing anyone and the sum of all the columns is shown, I need (and surely all of us) That when a drilldown column is collapsed, the respective value of the column that I pass to the parameter is passed as null or better, That I am able to detect when a column is collapsed or not, for just not passing the parameter (faster).Well any help or guidance would help as I am lost on this.Thanks for the help,Jose
The only workaround (partially) I have found is, on the TextBox properties, Navigation tab, Parameters details is to assign the following expression to the Omit column: =IIf(IsNothing(Fields!Det_Result.Value) Or (Trim(Fields!Det_Resul.Value)="") , True, False)
But that only works when the parameter is text and is a null or empty value... not nice...
