Let’s make it real simple for the end-user to get the ‘best’ behavior by default. Let’s sort by our ‘Revenue’ column in DEscending order first.
You ever have a paginated report, i.e. a standard old-fashioned report with a big table (tablix, matrix), where you want the users to be able to sort by any column they want? And let’s say one such column is ‘Revenue’ — some money amount where the “biggest is the most important”. Like your top customer, for example.
Now, you know about ‘Interactive Sorting’ right? No?!? Oh it’s awesome! Let me remind you. It’s super easy to configure: just get to the Text Box Properties of the header box of whatever column you want to use, & go to the ‘Interactive Sorting’ tab. Like so:
Make sure you pick the appropriate Field in the Dataset to “Sort by”. Hint: it’s usually the one you’re displaying in that column!
But you know what’s kinda annoying? You can’t dictate a “first sort direction” — it just assumes that the first time you click the sort-arrows, you want ‘Ascending’ (lowest first). Then you can switch to ‘DEscending’ (highest first). This makes perfect sense for alpha values (strings), but not always for numeric values — at least not when you’re dealing with money, when generally the highest dollar amount is the most important!
So let’s make it real simple for the end-user to get the ‘best’ behavior by default. Let’s try to make it sort by our ‘Revenue’ column in DEscending order first. Ready?
It’s so small in that picture you might miss it. I’m doing this, but I’ll add whitespace below for easier reading:
= - Fields!Revenue.Value
..^look, there's the minus sign!
(assuming your viewing device hasn't mangled the fixed-width text)
You could also get more pedantic/readable and spell-it-out like so:
Right? Beautiful. So what does it look like on the report?
Notice my silly hyphenated column names, like ‘Custom-er No’ and ‘Rev-enue’. That’s because those sort-icons take up space within the textbox, and you can’t control it, so the text wraps in a non-obvious manner. Thus, I purposefully added a hyphen and a line-break to the text, so I could control how they looked, instead of leaving it to the wiles of the SSRS render-er.
While there’s no native control like it, with a few simple tricks and visual slight-of-hand, we can fake it.
Today I present to you, a hopefully less confusing and more intuitive explanation of how to make a “multi-column list” control on your SSRS report. And if the term is confusing by itself, I’ll illustrate in a second.
Three links that helped me get going in the right direction:
They all roughly boil down to the same concept: placing redundant or repetitive controls (like a set of 2 or 3 tablixes), and using a RowNumber() expression with some modulo arithmetic to set “visibility” properties of the dataset rows shown in each.
We don’t quite get to “dynamic # of columns and rows based on the total # of records” nirvana (e.g. make it a 3×5 grid if you have somewhere around 15 records, but only make it 2×4 if you have 8 or less records, etc.), but I’d estimate that this approach would suffice in 90% of use-cases. And whoever at Microsoft decided to not build a control like this into the toolset, curse you!!
Yes, we’re not even past the intro section and I’ve already cursed someone at MSFT. Doesn’t change my passion for the platform in general; it’s just a minor curse. =)
SSRS uses a dataset to fill one or more controls with data; controls such as the tablix. In my regular implementations, a dataset is usually a stored-proc. The tablix shows the results of the proc in tabular form. The problem with the tablix control is that you can’t turn it into a multi-column layout, as you might see with some more advanced UI controls. You might think that the matrix control would be suitable for this, but no; that is just a table-like structure that can support hierarchical column-groups and row-groups.
For example, let’s say you have a table of Users, with columns ID, Name, and Role. Like so:
ID | Name | Role
1 | Bob | Admin
2 | Alice | User
3 | Jack | User
4 | Jill | User
5 | Joe | Admin
But with many more records. We want to display this on a report, which is typically viewed on a PC screen, i.e. landscape orientation (wider than tall). So wouldn’t it be nice if we could display it like so?
ID | Name | Role || ID | Name | Role || ID | Name | Role
1 | Bob | Admin || 2 | Alice | User || 3 | Jack | User
4 | Jill | User || 5 | Joe | Admin ||
etc. Right? Right. So essentially what we want is 3 “sections” of data — 3 “columns” each with its own “part” of the table.
Bonus: Yes, this works even when the # of rows you have is not evenly divisible by the # of sections you want to break into!
The actual work is fairly simple. We add 3 tablixes to the report and place them side-by-side. We connect them all the same dataset, i.e. stored-proc — which will only be executed once, conveniently! On the data row (as opposed to the header row) of each tablix, right click to Row Visibility. Then use an expression like so:
=IIf(RowNumber(Nothing) Mod N = X, False, True) — where N is the # of sections (tablixes) you want, and X is going to vary from 1 to (# of columns) - 1, and finally to 0 for the last (right-most) tablix.
Remember, the expression defines the row’s Hidden property, not “Visible”. I know, it seems backwards, but that’s how it is.
In plain English, and if we just want 3 sections (tablixes), we could say the following. For Section 1, left-most: “If the row is the 1st row, show it, else hide it”. Likewise, for the Section 2, the middle, we’d say: “if the row is the 2nd row, show it.” And finally, for Section 3, the left-most: “If the row is the 3rd row, show it.” And so on.
So those Hidden expressions would look like this:
=IIf(RowNumber(Nothing) Mod 3 = 1, False, True) //section 1
=IIf(RowNumber(Nothing) Mod 3 = 2, False, True) //section 2
=IIf(RowNumber(Nothing) Mod 3 = 0, False, True) //section 3
Not bad, right?
It’s certainly odd that there’s no native way of building such a report. But with a few simple tricks and visual slight-of-hand, we can fake it.
I now present to you, a recent adventure in building & configuring a SSRS 2016 / VS2015 project to replace our aging SSRS 2008R2 / VS2008 setup. In trying to make things ‘better’, I wanted to centralize the storage of the Data Sources. I found a tip on StackOverflow with a neat (albeit hacky) idea on how to do this. So I started diving into it. Here’s how that went.
I have a bunch of published reports on a ‘real’ report server (SSRS instance), which were created off-the-cuff using Report Builder 3.0 (no source-control connection or anything). So the first thing I had to do was go and download each RDL (thanks, Microsoft… ugh!), to my local VS2015 project folder, so I could bring them into my SSRS project. I didn’t bother trying to download/copy the Data Sources (they’re basically just connection-strings, and anyway you can’t download them from Report Manager), so I settled for re-creating those in VS.
Then it was time to set up the solution & projects. I prefer to organize my reports in context-relevant folders, like “Operations”, “Marketing”, etc. In order to do this sensibly, within the VS solution, you need to create a Project for each major fold you’ll have. Makes sense; this is how the old solution was constructed too. Here’s where I tried to improve things: I created a “Datasources” project, which would house just the shared Data Sources. Here’s where I created my new data-sources, setting them up with saved SQL auth (login & pwd). Sure, maybe not the “best practice” but it keeps things simple — my SSRS user only ever has read-only db access, and the pwd is managed well enough to keep auditors happy.
Thus, my plan (in following the SO tip) was to have all the other projects’ Data Sources be pointers to these shared Data Source files (RDS’s). Sounds good in theory, right?
Starting to Implement
Well. I started small with just one project, one report, and one data source. I went into my Nate test project, did “add existing item” into the Reports folder, and browsed to pick the rdl that I had just downloaded from my live SSRS server. I then went to my Datasources project, copied the rds that I needed, pasted it into Nate test project’s Shared Data Sources folder.
Now at this point there are 2 copies of this file in my solution folder. We don’t want that. So I opened up trusty ol’ Notepad++ on the Nate test.rptproj file, and edited the XML node under <DataSources><ProjectItem><Name>My-Source.rds</Name><FullPath>My-Source.rds</FullPath></ProjectItem></DataSources> , changing the FullPath node value to <FullPath>..\Datasources\My-Source.rds</FullPath>. I then deleted the physical copy of the file at \MySolution\Nate test\My-Source.rds , so that only the shared one physically remains in the filesystem (\MySolution\Datasources\My-Source.rds).
Another way to accomplish this, you may read, is to right-click Shared Data Sources within your project, e.g. Nate test, and say “Add existing item..”, and go select the global shared RDL from your master Datasources project-folder. However, this still results in a physical copy of the file within Nate test folder! So in either case you’ll end up doing some editing & deletion.
With me so far? Okay, here’s where it gets interesting. I could successfully build and deploy this report to my SSRS server, verifying that it showed my “new thing” (I added a text-box that simply said “This is the new hotness!”). But I could not preview the report in VS! Sad panda. And of course, the most vague generic error message in the world:
An error occurred during local report processing
An error occurred during report processing
More Googling led to a somewhat random tip in the vein of “Go to the report properties, Open the report’s Data Source, and re-select the Shared Data Source from the dropdown”. Combine with “Edit the Shared Data Source, ensure your saved login/pwd is filled in, and Save it”. Presto! Previewing now worked.
But why is this? Well, as it turns out, it’s not all that surprising. The RDLs are stored without the saved login/pwd, because of course they’re just plain-text XML files. So to “protect you”, MSFT doesn’t store your SQL auth credentials, even if (and now matter how many times) you click “Save my password” on the connection-properties window (connection-string builder).
Great. Well, I did that once, for Nate test project. Let’s see what happens with another project (aka report folder), say Test again. So I add an existing RDL, I copy-paste the Data Source from the global shared Datasources project, manually edit the rptproj in Notepad++, delete the copied rdl, etc. I then try to preview the report… and behold, the same maddeningly vague error message!
“Well duh!” you may think to yourself, “the credentials still aren’t stored anywhere un-encrypted!”. Yes, dear read, you are correct. But do I want to right-click on this project’s Data Sources, edit it, fill in the credentials, save, rinse, repeat, for every single project?! A resounding NO!
So what should I do? I could go type in the credentials to the actual connection-strings contained within the ‘master’ rds files. Of course then they’re in plain-text for all to see… but wait, do I care? Another no. Let’s go edit some connection-strings!
Why did MSFT choose to name the XML node ConnectString in their rds schema instead of ConnectionString? Don’t ask me!
Here’s the first gotcha: My reports (RDLs) need to be re-pointed at the correct Shared Datasource, because currently, as they were downloaded from my ‘real’ SSRS server — which had my desired top-level folder layout of “Datasources”, “Nate test”, “Marketing”, etc. — their Data Sources xml node points at \Datasources\MyDatasource.rdl. This was the correct path on the SSRS server and still will be the correct path when all is said & done. BUT, to support local debugging/previewing, the build output has to make a copy of the rds in the Project’s bin\Debug folder, which it will happily do, and then just as happily proclaim Could not find a part of the path 'C:\Users\Nate\Documents\Visual Studio 2015\Projects\MyReportSolution\Nate test\bin\Debug\Datasources\MyDatasource.rdl'. — because DUH, the build system didn’t make you a ‘Datasources’ sub-folder under the Debug folder, why would it!? So by either manually editing the rdl file to remove the \Datasources\ path from the node, or by clicking on the Report’s Data Sources node in the Report Data pane and re-pointing it at the local Shard Data Source (which again, is itself a pointer to the globally shared Datasources!), you can fix this issue.
In the rdl XML, that looks like this (using strikethru to show what you remove):
And there’s another gotcha: After we do all this, if we open the rds from within VS solution-explorer, it still won’t “think” it saved the authentication! But the connection-string will be clearly visible with the plain password. Here’s a couple pictures of what that looks like.
Oh, by the way, did I mention how many times VS crashed on me while doing these gymnastics? At least a few.
So, what have we learned? Well, for one, this is a crappy situation born of poor in-product support. I should be able to configure Solution-level shared Data Sources, use them in as many Projects (within said Solution) as I want, and have VS configuration management support them; bonus points for doing so with saved & encrypted credentials. Ideally, when we check this into source-control, we’d check in the “DEV” environment flavor connection-configs. Then, when the reports get deployed to the “PROD” SSRS server, the same globally shared Data Sources are already present (and they don’t get over-written, thankfully by default!), configured by the DBA with prod credentials, and nobody in the development pipeline needs to know said credentials. Yay?
But alas. We live in an imperfect world. I also live in a world where I’m both the Report Developer, the Report Manager, and the Production DBA. So guess what I get to do? That’s right, not care about the connection-string security!
Okay, yes, I care a little. Depending on my mood. But not enough to turn my whole dang world upside-down trying to design and implement a robust multi-tiered-environment solution for one measly SSRS repository. Kudos to you if you do — you’re a better man than I.
Or “better woman”. But I’m not a woman. So I guess the correct phrasing would be “You’re a better woman than I am a man.” No, that’s not right. “You’re a better person than I am?” Sure, I guess that works.
Create the solution & projects
Create the global shared Datasources project, and create your Data Sources (RDS‘s)
Import your Reports (RDL‘s) to each requisite report-project
Manually edit your rptproj to point the data-sources at the central shared path
Manually edit your RDL‘s to remove previously established paths to the data-sources
Configure your rptproj‘s to output to the correct folders on the report server — this should be done by default, i.e. VS has the “intelligence” to guess these for you, but just double-check.
And now to go automate all this with PowerShell… right after this beer.
Anyway. Until next time, folks! If I’ve made any mistakes, missteps, or otherwise offending your technical sensibilities, and/or if you know of a better way to accomplish what I’m after, I’d love to hear from you in the comments! =)
a quick tip for passing default values to an SSRS multi-value parameter during report drill-thru.
Inspired by some StackOverflow-ing and this particular answer.
Aka: “How do I pass/assign a default set of values to a multi-value parameter in SSRS?”
We often have an SSRS report that needs to “drill thru” to another report, usually to go from a “high level view” to a “lower level” or more detailed view, or sometimes just to lead the user down the path we’ve chosen as the head analyst / BI architect. And part of that report navigation involves initializing (set to defaults) the value(s) of the sub-report’s parameters, including multi-value parameters (which I’ll now refer to as mvp, even though, I know, it’s a ridiculously overused acronym). These are basically arrays, but are often represented as simply comma-delimited strings, so it’s easy to forget their true nature.
Let’s fabricate an example. In our Sales Summary report, we have an element (a textbox, image, placeholder, or whatnot) that, when clicked, should drill-thru to Sales by Person. Said next report requires a multi-select parameter (another term for the mvp) to have 1 or more values selected, otherwise it doesn’t render. We’ll call this parameter SelectedNames, with value-label pairings 1=Bob, 2=Alice, 3=Mary. When we drill-thru to this by-Person report, we want it to initially show (have selected by default) all available people.
So how do we do this? In the properties of the “clickable” element on Sales Summary, say it’s a text-box named GoToDetails, we go to the Action tab. We choose the “Go to report” action, select/specify the target report, Sales by Person, and then add the parameters we want to pass to it. For comparison, I’m going to pass a “regular” (single value) parameter called @ReportDate, as well as the mvpSelectedNames. Here’s what that all looks like, in picture form.
The single parameter pass-thru is, as you’d expect, very simple. But for our mvp, we need to use the expression-builder, that little fx button stylized to look like your old high school math class days. Here’s what that function looks like:
And presto!, we have converted a comma-delimited list into an array to pass into our Sales by Person report’s SelectedNames multi-value parameter. Now it will initially render with all 3 selected people as desired.
So there you have it, a quick tip for passing default values to an SSRS multi-value parameter during report drill-thru. But what if you wanted to pass the selected values of one mvp down to another? Or do some clever on-the-fly mapping (conversion) from one to the next? Well, stay tuned! I’ll write about that next time. =)
Thanks for reading! For a lot more on SSRS and multi-value parameters, check out these articles: @sqlchick, @mssqltips, and @msdn.
MDX is not SQL. It may look like it has SELECT/FROM/WHERE clauses, but god help you if you start drawing parallels to your standard TSQL query.
This is an exercise I had to go through recently, because A) the reports in question were deployed in SSRS but used an SSAS backing, i.e. cubes, and the source queries (MDX) were not stored in source-control, and B) I don’t write MDX queries.
Run Profiler or XEvents against the SSAS server
set to capture “Query Begin” events only, with “Event Subtype = 0” (for MDX query)
optionally, set filter on NTUserName to the dedicated SSRS account (if you have it set up that way)
Run the SSRS report(s) that you want to dive into
For each event in the Trace, copy-paste the MDX query to a new MDX editor window
SSRS parameter substitution happens via some XML at the bottom; but in MDX, the parameters are standard @params like in T-SQL. So we need to manually substitute our parameter values.
2 blocks of XML: the “Parameters”, and the “PropertyList” — delete the latter.
In the former, text-replace & for simply & .
Side-bar: You’ll notice that the MDX parameters are usually inside STRTOMEMBER() or STRTOSET(), which are built-in MDX functions that do exactly what they sound like — parse a string into a dimension’s attribute’s member or set of members. That’s why they’ll usually have at least 3 .‘s (dots) — Dimension.Attribute.&MemberValue, for example. I’m grossly oversimplifying that because it’s beyond the scope of this post, but read the docs if you need more gritty details.
For each parameter node:
Copy/cut the <Value> node content (I like to ‘cut’ because it helps me keep track of which ones I’ve done already)
Find-and-Replace @ParameterName with that Value node’s content, surrounded in single-quotes
Example: we have parameter @ReportDate (in MDX), corresponding to <Parameter><Name>ReportDate</Name> in XML, with <Value xsi:type="xsd:string">[Some Dimension].[Some Attribute].[Some sub-attribute].&[2017-05-01T00:00:00]</Value> — where that last bit is a standard SQL datetime literal.
So you replace @ReportDate with '[Some Dimension].[Some Attribute].[Some sub-attribute].&[2017-05-01T00:00:00]' .
Delete the XML block.
Boom, now you have a valid MDX query that you can run and view results.
Why do this? Well, it can help you learn MDX from a working example, instead of from super-basic dummy examples. That’s not always a good learning style — you should still learn the fundamentals of MDX and why it’s so very different from SQL. Especially if you’ll be responsible for writing and maintaining more than a few MDX queries. But, in a pinch, if you need to start somewhere, and possibly all that the MDX / overlaying report needs is a slight tweak, this may be enough to get you going.
Profiler can still be a useful tool, despite some people’s attempts to kill it.
MDX is not SQL. It may look like it has select, from, and where clauses, but god help you if you start drawing parallels to your standard TSQL query.
SSRS does parameter-passing in an odd way.
SSAS & MDX are fascinating and I need to learn more about them!