Ragged Parent Child Hierarchies SSRS
Awesome Post explaining how to best setup an SSAS Parent Child hierarchy in SSRS,
http://blog.davyknuysen.be/2010/04/16/parent-child-hierarchies-in-reporting-services/
However if you have a ragged hierarchy you wind up with repetitive members, to address this issue you can use the following visibility expression in the Row Group properties
In the ‘Row Groups’ Pane click the down arrow on the grouping and select group properties.
- Select Visibility
- Select Show or Hide Based on Expression
- insert the following expression ‘=iif(Instr(fields!fieldName.UniqueName, “DATAMEMBER”), true, false)
SQL 2012 Release Candidate 0
Just installed SQL 2012 RC0 specifically to check out the new stuff in Power View, have a look at http://blogs.msdn.com/b/robertbruckner/archive/2011/11/17/what-s-new-in-power-view.aspx to see all cool new features, however there are few things that I would have loved to have seen included.
- Support for Hierarchies
- Cards or Tile region slicers, the addition of grid filters is cool, but if you are filtering cards within a region that contains a grid with related measures it does not get filtered.
- Font control within grids and cards
- Image size control
- Dimension attribute aliasing
- The abilty to publish documents that cannot be edited.
SQL – Converting an integer to datetime
Sometimes in life you come across integers that represent a datetime value and you want to convert it to make it more appealing . Here is a handy little helper that will convert integers represented as ‘YYYYMMDDHHMM’, ie 200805051010;
select CAST(STUFF(STUFF(STUFF(STUFF(CAST(200805051010 as varchar(20) ), 5, 0, ‘-’), 8, 0, ‘-’),11,0,’ ‘), 14, 0, ‘:’) as datetime)
as dt.
(You could also use the convert function if you wanted a different date format, I know I could have cast the int to a varchar(8) and gotten a valid date without all the stuffing, but I needed to keep the time part of the datetime)
As with just about everything that I do, I am sure there is a much better more efficient way to do it that involves less typing, but it works and that is my ultimate goal
Power View aka ‘Project Crescent’ Installed and Working
Well it took the better part of the weekend, but I got finally got SQL Denali and Power View working side by side with SQL 2008 R2. Warning this was not a pleasant experience and should not be attempted unless you enjoy being frustrated and swearing uncontrollably at your computer.
Now the fun begins.
PPS Cascading Filters Save the Day
The Scenario:
Time Intelligence filter based on a typical date dimension filtering scorecard and Analytical reports, needed to add an Excel Services Report with a Parameter based on the Year attribute of the time dimension. I tried connecting the TI filter to year parameter available in the Excel Services report with a connection formula like: EXISTS([Date].[Years].members, <<UniqueName>>,”MeasureGroup”) expecting it to work, problem was that excel did not like it. Cascading filters to the rescue.
The Solution:
Created a Member Selection filter from the descendants of the Years Attributes, connected the TI filter to the Member Selection filter with the above connection formula, passed the member selection filter to the report, excel is happy again
The Gotchas:
- Cascading Filters filter Nulls, so if you need to return members that are in fact null you need to create a plug measures and set that as your filter measure
- Member Selection filters not based on a Function like Children or Descendants that you pass a connection formula into get created with a ‘With Set’ Expression and not the expected ‘strtoset’ expression and do not work very well (my experience anyway)
Will post a how to shortly.
Migrating Custom SharePoint Column nightmare
Well I had to migrate custom columns from one SharePoint 2010 environment to another, thanks to the following post, I was able to script it and migrate.
Easy right, not so fast, went to check my columns out and got this great error when accessing site columns under site settings
Field type is not installed properly. Go to the list settings page to delete this field.
Turns out I made a typo and left a column type blank. after scouring the internet, i came across somebody brave enough to delete from the content database.
So I looked in the contenttypes table in the Content DB, found the offending record, deleted it and SharePoint started to work again.
I hope this helps.
Share this Blog
