Attention to detail is a key in creating SSRS reports/dashboards that look like a work of a professional; To that end, here’s a tip: How to capitalize the first letter in your string? In other words, how to Camel Case the Text?
Here’s the function that you can use in your SSRS Expressions:
You have a Fact Sales and Fact Target in your data mart. Fact Sales stores values are product sub category level and fact target stores values at product category level because business sets “sales targets” at a higher (rolled up) level. How do you connect it to a single dimension at different granularity?
Solution:
Here’s the table structure, I just made this up for the demo purpose:
1. Fact Sales
2. Fact Target
3. Dim product sub category
so, you went ahead and tried testing by creating relationship’s to single dimension at different granularity in the cube:
Note how the relationship was specified between Fact Target and Product Sub Category Dimension – it’s joined at a different granularity compared to fact sales. it would be help you from a performance standpoint if the fields that you are using to join the fact and dimension is an int.
So, you browse the cube and here’s what you get:
Note the problem: the target values are being repeated for sub categories but that shouldn’t be happening, right? that’s misleading to business users…ok, to recap what we need to do here: hide target values for subcategories since targets are not set at that granularity. but we do need to show them if the business users pulls in product category.
So here’s a measure group property that comes to the rescue!
Go to Fact Target Measure group’s property > Set IgnoreUnRelatedDimension to False
deploy and browse your cube again, here’s what you will see now:
That’s it! you have successfully joined facts at different granularity to a single dimension.
How to use Execute SQL Task in SSIS to assign value to a variable?
Solution:
This is a beginner level post so I’ll show you how you can use Execute SQL Task to assign a value to a variable. Note that variables can also be given full result set. With that said, here are the steps:
1. Create the query against the source system
Example: ((Note the column name, this will be handy later!)
2. Open SSIS Project > Create the variable
Example
3. Now, drag a Execute SQL Task to Control Flow. Rename it. And go to Edit. Configure SQL Statement Section
4. Now, since we want to store a value to the variable, change the Result Set property to Single Row
5. One last step, go to result set section and map Result Name (remember the column name from #1?!) with Variable Name:
How to sort the dimension attribute by something other than the key and name column? How do you set the “OrderBy” property?
Example: You have created an Inventory age buckets 1-50,51-100,101-150 and so if a business user uses this dimension attribute then the sorting won’t be logical. It would be 1-50, 101-150,51-100 – so how to show the buckets in the logical order?
Solution:
1. make sure that the table/view that you are bringing in has the sort key.
Example:
2. Now, switch to SSAS and open your dimension. I am assuming that you’ve already configured your data source views and you are already bringing in these columns in the dimension:
3. Let’s start with hiding Aging Bucket Sort key so that it’s not visible to user. Change the AttributeHierarchyVisible to False
4. Now, switch to Attribute Relationships – Right Click on Aging Bucket and click on New Attribute Relationship. And set the attribute relanship between Aging bucket and Aging Bucket Sort Key
And you should see something like this in your attribute relationship section:
5. Now, one more thing to configure. Go back to dimension structure section. Open the properties section for the Aging Bucket Attribute and change the OrderBy property to AttributeKey. Also, change the orderByAttribute property to Aging Bucket Sort Key (in your case, choose the sort key that you have)
That’s it, after you process the model then you should see the attribute being sorted based on the sort key that you had.
Conclusion:
In this post, you saw how to configure sort/order property of a dimension attribute.
Note: if you do not have integers to divide and you allow data conversion to double, it would show on SSRS as “infinity” instead of “#Error”.
Solution:
Let’s understand why does it do it?
So even though you are using IIF condition, the vb IIF condition evaluates every function in every expression before it renders and that’s why the “False” condition that I have in my condition gets run which results in #Error.
Ok, armed with that knowledge, let’s solve the problem.
So here’s a modified version of the expression, have a look:
See what we did there! We added one more IIF condition in the “false” condition of the parent IIF.
PASS Business Analytics VC presents “The Accidental Report Designer: Data Visualization Best Practices in SSRS” by Meagan Longoria. Here are the meeting details:
Date & Time: Thu, June 19 2014 12:00 Eastern Daylight Time
Session Abstract: Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!
Session Level: Intermediate
Speaker BIO: Meagan Longoria is a BI consultant with Valorem Consulting in Kansas City, Missouri. She has over 6 years of experience with the SQL Server BI stack and enjoys sharing her knowledge and experiences at SQL Saturdays. She is also one of the coordinators for SQL Saturday in Kansas City. Contact URL: http://datasavvy.wordpress.com
In MDX, How to search for a sub-string using something similar to LIKE operator in SQL?
Solution:
here’s an example using Contoso Sales Cube, please use this to get started for your scenario:
Note the use of INSTR function.
select [Measures].[Sales Amount] on 0, Filter( [Geography].[Region Country Name].[Region Country Name] ,instr([Geography].[Region Country Name].CURRENTMEMBER.MEMBER_CAPTION,'United')>0) on 1 from [Sales]
Also, if you are looking for NOT LIKE then replace >0 with =0 and you should get the desired results.
Conclusion: In this post, you saw how to write a MDX query that does sub-string search for you similar to LIKE and NOT LIKE operators in SQL.
How do you customize the Legend Text on a chart? by default, it’s going to show you the name that you have in the Data Set, but sometimes that’s not what you want to display so how do you change the name?
It’s really useful when you don’t want to change the name of field in the dataset or when you are using an expression as an item on the chart and you need to display a business user friendly name on the report:
Solution:
1. Go to Chart Data Properties:
2. To change the Legend Text of “Sales Amount CY” – Navigate to series properties of the “Sales Amount CY” series:
3. Go to “Legend” and enter the Text in the “Custom Legend Text“:
4. You can customize text for all legends on your report by navigating to the series properties.
Conclusion:
In this post, I walked you through how you can customize the legend name/text in SQL Server Reporting Services.
Join PASS Business Analytics VC on Thu, Mar 27 2014 12:00 (GMT-05:00) Eastern Time (US & Canada) for “Analyzing Road Traffic Accident Data in Power BI” by Chris Webb. In this one hour session, Chris will spend an hour going over the details on how he developed his amazing demo for the Power BI contest (see here & here).
