Today some coworkers and myself were standing around the kurig joking about how you couldn’t actually sit at the table in our office. The chairs (while totally cute, thanks Ikea) are made to be perfectly fitted beneath the tall table, leaving mere inches for thigh comfort.

As we explored this table conundrum other questions of logic and functionality arose.

We began to discuss design methodologies and patterns that presented themselves as opportunities for automation. As we each chose to ignore the time briefly (it isn’t easy with 5 clocks on the wall) the conversation evolved into an incredibly valuable brainstorm.

These creative processes are often stifled when we are busy jumping from one meeting to the next. Try scheduling some “do nothing” time this week and see what problems seem to solve themselves.

Time is a created thing. To say “I don’t have time,” is like saying, “I don’t want to.”

~ Lao Tzu

I recently needed to create a SSRS report that allowed my client to group his data by choosing the group field from a drop down list. It’s actually quite simple to accomplish this, although not intuitive straight away, so I thought I would share the steps I took.

First create a report with a valid dataset. Include the fields you will be grouping on. I am using the Adventure Works 2012 Data warehouse. If you would like to follow along my query is:

SELECT top 3000
[TotalProductCost]
,[SalesAmount]
,[MonthNumberOfYear]
,[CalendarQuarter]
,[CalendarYear]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] fs
JOIN [dbo].[DimDate] dd
ON fs.OrderDateKey=dd.DateKey

Next create a tabular design with your detail fields and a few blank columns.

Next Add a group by right clicking on the details group:

Click on the expression builder:

Paste this code into your expression:

=iif (Parameters!GroupBy.Value is Nothing,1,Fields(iif(Parameters!GroupBy.Value is Nothing, “CalendarYear”,Parameters!GroupBy.Value)).Value)

This handles the scenario where we don’t want to choose a grouping.

Now we need to actually create the parameter by right clicking Parameters and selecting Add Parameter:

Allow nulls in your parameter and assign available values:

The names in the Value field need to match the names in your dataset.

Go back to your table and delete the extra group columns that were added when you created your groups:

Now right click the field and paste this expression:

=Fields(iif(Parameters!GroupBy.Value is Nothing, “CalendarYear”, Parameters!GroupBy.Value)).Value

Next you will create two identical parameters with the below names:

Now right click on your first group, select add group, select child group:

The child group settings will be identical to the parent one except you will update the code to read your second parameter:

You can add multiple sub groups this way to really empower your users!

Next I will show you why we deleted the group column and kept the regular tablix column. We want to be able to hide the entire column if the user doesn’t choose it to group on.

A “grouped column” doesn’t have the visibility property available:

But you can right click on your regular column and select Column Visibility and then click on the expression builder:

In your expression paste the below code:

=iif (Parameters!GroupBy.Value is Nothing,True,False)

This will effectively hide your column if you don’t choose it to group by.

Now right click on your blank field and choose expression:

This expression will be:

=iif (Parameters!GroupBySub2.Value is Nothing,1,Fields(iif(Parameters!GroupBySub2.Value is Nothing, “CalendarYear”,Parameters!GroupBySub2.Value)).Value)

Your column heading will be the parameter label (not the parameter value):

And now the fun part. 🙂 You get to preview your report:

No Group Parameters Selected:

One Group parameter selected:

Two Group Parameters selected:

All three Group Parameters selected:

And that’s it! Now I just need to clean up the formatting to make everything pretty.

I hope this is helpful. Please let me know if you find an easier way to accomplish this, or any feedback you might have.

Thanks for reading and have a great day!

The Creative Genius

The value of creativity cannot be overlooked in any field, especially business intelligence.  For many of us BI folks our days consist of continual problem solving. As we’re eating our morning Cherrios we are often contemplating that one silly problem that just hasn’t been solved yet… and suddenly all of those O’s spell Eureka! Solutions often come in ways that our current knowledge doesn’t support. It takes a creative brain to think diagonally and find solutions that don’t yet exist in textbooks, manuals, or blogs.

The Eternal Optimist

When times get tough and there are no solutions in sight you need that person who recognizes that there is ALWAYS a solution, you just haven’t found it yet. A positive thought process allows for creative solutions. There is nothing more stifling than to hear someone say “It can’t be done” or “I don’t think this is possible”. An intelligent optimist will recognize that to every logical problem there is a logical solution. This type of person doesn’t need the proof of specific past solutions, he is willing to take a chance and find an entirely new solution.

The Technical genius (AKA Nerd)

Wishful hoping and wishful thinking never accomplished anything until they met the technical genius that could bring visions into reality. Think of the relationship between an interior designer and an architect here. The decorator has no idea HOW that pool is going to get on the third floor balcony that overlooks the city she just knows it is an amazing idea and that it can be done. The architect will then build all of the required support systems to make the roof hold and he will even go one step farther to design a methodology to migrate all of those heavy building materials onto the balcony. Your technical genius will build the foundation for your BI system and will make sure that your silly creative people use construction materials that are extensible and durable.

The Organizer
Are we on track? Those artsy folks might not care and those nerds might not be aware. For this task you need that A type personality that sets and respects deadlines. A promise is meaningless if you don’t keep your word. When you promise other departments that you will have that new set of reporting capabilities ready by the end of the month your organizer will help you to earn a solid reputation by keeping your team on track.

The Politician

Oh how I wish this wasn’t the case (but I’m just a creative artsy nerd) but yes, you need someone to represent the greatness of your BI team to the masses. This person will know how to present himself to the public and field those not so friendly questions in a politically sensitive way. Your technical staff may inadvertently throw the public into the deep end and your artsy staff may appear too aloof, but your BI politician will exert the confidence and high level thinking that your board members can relate to.

The Communicator

Ah yes, the rare IT communicator. If you get your hands on someone who is efficient in this area I suggest you not let go! This person will translate your geek-speak into understandable English for your business users. She will also be a great resource to gather requirements for your technical staff. Your communicator will be a great lead for informal meetings as she can help to generate excitement and ensure that your staff clearly understands your business users.

Jack of all Some Trades

Many people on your BI team will possess several of these qualities. This is great! A collaborative organization requires that people be flexible and versatile. You shouldn’t attempt to hire one person for each role and place that person in a mold. Let your team experiment and find out their strengths while also engaging and offering development opportunities where they may be lacking. With that being said, it is also important to recognize if your team is missing one of these key strengths and to make an effort to fill the empty hole.

Good luck and happy developing!

 

 

 

 

If you have multiple files that you need to move in SharePoint don’t worry, you won’t be forced to select and download each one individually to accomplish this task.

Navigate to the library section of your library tools and select “Open with Explorer”. This will open an explorer window that includes all of the files in your selected library.

Now navigate to the location that you want to upload the files to and mimic the same steps. Now you will have two explorer windows open, one that contains the source documents, and another that points to the destination.

From here you can simply drag and drop the files into the destination folder. If you have hundreds of items keep in mind that this could take several minutes to complete.

Thanks for reading and have a great day!

If you have a development SharePoint site and you need to transfer a file to your production site this is a relatively easy task, however, you will follow different steps if you need to move multiple items at the same time. Please read my post “How to Easily Move Multiple Files Across SharePoint Sites”.

First you will navigate to the report location and select the check box next to the title.

Now select “Download a Copy” from your documents task ribbon.

When asked if you would like to save the file select “Save”. 

Name your file and save as type “BISM File” then select “Save”.

Now navigate to your production site where you want to upload the file. Under your Power View library select “Add document”.

Now browse to the location where you saved your bism file and select “OK”.

If the process worked correctly you will see a message displayed that indicates your document was uploaded successfully. Name your file and select “Save”.

Now you will see your uploaded file appear in your Power View library! Notice that the file Name is displayed for selection, not the file Title that you may have typed in the properties window.

I hope this has been helpful. Please let me know if there are any topics that you think would be beneficial to our BI community.

Thanks for reading and have a great day!

I’m going to make a broad assumption that if you are a fellow nerd (who grins after coding for eight hours straight) you may not be the type of person who works on your own car. Have you ever taken your car to the garage and experienced the mechanic speaking a hundred miles an hour about A-Brackets,  A-Pillars, Alternators, and Ammeters? Yes, No? Well, I have, and sometimes I wish they would consider that I might not speak their language. I really won’t be offended if you simply tell me there’s a hose that has a small leak in it and it should be replaced.

I personally feel that it is more admirable to be able to speak intelligently about your profession in a manner that anyone can understand, regardless of your industry. When preparing for an upcoming board presentation I came across a 40 page presentation designed to explain BI to non-technical people. Ahhhhh!

Remember…It Doesn’t Have to Be Complex to Be Cool! What is BI? Well, if you search this question you will find hundreds of complex explanations, however, don’t forget to remember your audience. Don’t make the mistake of boring them with long drawn out ramblings of ETL processes, data cleansing, and server maintenance plans.

Really, BI can be explained very simply; it’s the process of gathering data to present a single version of the truth so that data driven decisions can be made more easily.

Simple, right? Here are a few more: Data warehouse – a place to store summarized data from several end user systems. KPIs – a way to measure the progress of company goals. Scorecard – a collection of KPIs. I know, it may sound silly to simplify concepts to this degree, but it often accomplishes what is really needed in business conversations – solid communication.

It’s still easier said than done, so here are some things to consider before presenting your technical joys to non-technical people.

1. An intelligent co-worker of mine suggested considering how you might explain the technicality to a child. This is not with the intent of sounding condescending, but more to make us more aware of what we are saying and how we are saying it.
2. Remember, don’t try to impress others by using technical lingo, instead, impress them by demonstrating that you can live in their world as well as yours. This means being mindful and considerate of your audience.
3. Practice! After a long time of fumbling through my explanation of “what I do for a living” I finally have my elevator speech nearly perfected – ha-ha, but not really. Sometimes I find that I still accidently insult people by speaking too elementary or I frustrate people by speaking too technically. The important thing is that I learn more each time about how to gauge people and the approach I should take when speaking about my nerdy life.

One last thought to consider…From my experience “less is more”. My first goal in meetings is to accomplish solid communication. I do this by presenting condensed and non-wordy explanations. I have found that if the other party wants to know more they generally won’t hesitate to ask, especially when I have successfully presented the topic in a non-threatening manner.

Your communication success can be judged by paying attention to your audience. Are they staring at you blankly with little to no interaction? You may be speaking a foreign language to them. Are they interacting and actively engaged with the conversation? If so, congratulations! You have empowered them and potentially created a strong business ally.