Turning Three Reports Into One Report

We recently added the Operating System (OS) report set to Enhansoft Reporting. This report set gave the Enhansoft Team some challenges when it came time to designing it, so that’s why I decided to share some tips we learned from that experience. In this post, I show you how we consolidated data and turned three reports into one.

First – How Nine Reports Became Five Reports

Normally, when designing reports, we follow what’s outlined in this post, “What Are the Best Report Styles for SCCM?” In this case, however, if we followed that report style guide to the letter, we would have created a total of nine reports. Here’s the original report set break-down:

a) Count of OS

b) List of Computers by OS

c) Computer OS Details

d) Count of OS Rings

e) List of Computers by OS Ring

f) Computer OS Ring Details

g) Count of OS States

h) List of Computers by OS State

i) Computer OS State Details

We knew off the top that the data from the three count reports (a, d, and g) made a great dashboard. Here is your one-stop-shop for high-level OS details. You may recall that the Operating System Dashboard was December 2019’s free report giveaway (hopefully you had a chance to download it). The Operating System Dashboard shows you the total number of computers by system role within a collection. It further breaks down the percentage of these computers by OS, ring (branch version), and state. You can quickly see, all in one place, the number of computers using a specific OS and ring, and the number that are supported, unsupported or nearing end-of-support.

Next, we combined the three computer detail reports (c, f, and i) into one report.

Simply by organizing the data, we went from nine reports down to five reports.

1. Operating System Dashboard

2. List of Computers by OS

3. List of Computers by OS Ring

4. List of Computers by OS State

5. Computer Operating System Details

How did we then get from five reports down to three reports? You guessed it! We combined the three list reports into one report.

How to Turn Three Reports into One Report

If we had kept each list report as a separate entity, each one (List of Computers by OS, List of Computers by OS Ring and List of Computers by OS State) would have had the exact same columns, so we wondered, “Do you really need to have three reports that display the exact same details BUT have different prompts for each OS setting?” The answer was, “NO!”

Instead we created one report with prompts for OS, OS ring and OS state which allows admins to compare results more easily.

Individual List Reports

Below is what each of the drill through list reports would have looked like if we didn’t combine them. It is possible to compare details between each report, especially when you only have a handful of computers in a collection, but how realistic is that?

List of Computers by OS

List of Computers by Ring

List of Computers by State

Curiously, you can see in the reports above, that the computer “Zotac” has an OS that is both Windows 10 Enterprise and End of Life. Wouldn’t it be nice to find all computers based on those two values? After we combined these reports, you can do exactly that!

End Result

We ended-up creating one report with three prompts: OS, OS Ring and OS State, so when you drill through from any of the dashboard’s three sections, the details you want are passed along to the list report.

Next, we set the default values for each prompt to an “All” option. We did this in order to save the end user time. They don’t have to select a value for each prompt; only the ones they want. In the near future, I am publishing a blog post about how to create a prompt to have the “All” option, so keep an eye out for it.

You might be wondering how we handled the “All” option when drilling through from a dashboard to a list report, so I put together the steps below.

“All” Option Steps

First, I renamed the original List of Computers by OS report to List of Computers by OS Setting. Next, I added the two prompts for Ring and State because I am starting with the OS name. Since there are no real tricks to these steps I have not shown them.

Next, I went back to the dashboard report. Starting left-to-right with the OS name section first, on each column with a drill through, I right-clicked on it and selected Text Box Properties…

On the Action node, I adjusted the drill through prompt values to the appropriate variables. Again, there are no real tricks to this step. When I got to the Ring and State prompts, however, I hard-coded the values to – All Rings – and – All States –. Then I repeated the process for the Ring and State drill through columns on the dashboard report.

That’s it! The appropriate prompt options are now set for the drill through reports. With this simple design tip, you can consolidate the information from three reports into one report.

Mix and Match Prompt Options

The bonus of having three prompts is that you can now mix and match the prompt options. For example, in the above screenshot, I selected all Windows 10 Enterprise computers with the state, “End of Life,” while “All Rings” was left alone. Remember what I said earlier about the computer “Zotac?” I no longer have to run two or three reports in order to compare the results. I can do it all in one shot. This saves me a lot of time tracking down computers that need work.

Can you believe that this set went from starting off with nine reports, down to five reports and then to three? It only took a little bit of work, but believe me, it’s not as much work as creating all NINE reports in the set! As I mentioned earlier, in one of my upcoming blog posts, I show you how to create the “All” option, so stay tuned. Please feel free to touch base with me @GarthMJ if you have any questions about how to turn three reports into one report.