So today’s blog is going to show you how easy it is in XLCubed to have a slicer driving a grid and a SQL table at the same time. There may be occasions when some of the information you require for your report is held not in an analysis services cube but a SQL table. So you’ve created a grid report with a slicer like below:
This is a simple report with Geography on headers and Product Model Categories on rows showing Reseller Sales Amount with the Country slicer driving the grid. The slicer is set to update cell B9 with the slicer choice.
So I show this to my manager and he asks for some more detail – he wants to know what type of businesses there are in each country, their names and the number of employees. That’s when I realise that all of this extra information is not in my cube but on a completely separate SQL table.
Not a problem for XLCubed! I can quickly create a report that includes all this data from the SQL table. Using the SQL option within Grids & Tables I can create a report that connects to a relational SQL data source.
Create my connection to my data source – I am selecting the AdventureWorksSDW database:
Let’s build up my SQL query – I’m using the DimReseller and DimGeography tables to return the required fields.
My SQL statement is:
Select DimReseller.BusinessType, DimReseller.ResellerName, DimReseller.NumberEmployees, DimGeography.EnglishCountryRegionName From DimReseller Inner Join DimGeography On DimGeography.GeographyKey = DimReseller.GeographyKey
This is great but it returns data for all the countries and I only want to see data for the country chosen through the slicer. So let’s add a parameter to our SQL query.
If you look at the corner of the SQL query window you will see the parameters area – with a very helpful tip on adding a named parameter.
Let’s add the following to the end of our SQL query:
where DimGeography.EnglishCountryRegionName = @parm1
Now we can define where the Excel range is for our parameter – in our example it is cell B9. You remember that this is the cell that the slicer has been set up to output the slicer choice.
So now when we select a country from our slicer eg United States the grid refreshes as well as the table.
