[RESOLVED] SSRS report that uses a pivot table with dynamic columns
I have a task to take an existing stored procedure and make it an SSRS report. Note that I did not write the sp and don't understand it fully. It is making a pivot table. I think where the difficulty lies is the columns are dynamic and there can be a lot of them. The columns are a list of store numbers and if a particular store has no data, it is skipped. So let's say store numbers 1 through 10 exist in our database. If I run the sproc with one set of input, I may get a dataset back with columns for store number 1 and 2. Another set of input gives me data back for stores 1, 2 8 and 10. So the columns are always variable. (In reality, there are hundreds of stores, not a handful).
When I try creating the report in SSRS, by default it knows I have a field name ID0000, source name 0000 and fieldname ID9999, source name 9999. When I run the sp in SSMS, I get a boatload of dynamic columns and the first one is column name 0000 and the last is column name 9999. So what SSRS is doing kind of makes sense. I can manually add columns to the report, like 0001, 0002, and as I do, my report grows and looks more like the dataset returned by the sproc. But it is forcing me to explicitly list the columns. And with different input, maybe column 0002 won't have any data to report.
Is my explanation clear? Is my problem solvable? My boss is thinking that the dynamic nature of the sproc and pivot table perhaps can't be handled by SSRS.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Re: SSRS report that uses a pivot table with dynamic columns
why not manually add the columns like you mentioned and use a if statement in the Hidden ( or is it visibility ) property of the column? or will the columns that return be an undefinable set?
_____________
Tim
If anyone's answer has helped you, please show your appreciation by rating that answer.
When you get a solution to your issue remember to mark the threadResolved.
Re: SSRS report that uses a pivot table with dynamic columns
It can be... I've done it. It was a few years ago, and I don't have access to that report any more. I do remember that the pivot was in the SQL itself, but on the SSRS side, I used a TextMatrix object to display the data. There ended up being multiple datasources on the report, one for the main data, then subsequent ones for the different sections of the report. The TextMArtix is then tied to a specific stored procedure as its datasource, and simply displays the data in a table (col/row) format. Best part is that you don't need to specify the cols ahead of time. I think. Like I said, it's been a while, but I do remember we solved that very issue.
Re: SSRS report that uses a pivot table with dynamic columns
Thanks to both of you for your replies. I used the link tg posted and it was so incredibly helpful I don't know how to thank you (and the person who answered the stackoverflow question)!!! My boss had said: I think I mentioned Matrix to you. Let me know how it turns out. I personally have never done a Matrix, but if it works I may stop doing pivots in SQL. so it looks like you have turned us onto something "new". Enjoy your day (and holiday, if you are in the U.S. celebrating Thanksgiving! And if in the northeast as am I, good luck in the snowstorm!)
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Re: [RESOLVED] SSRS report that uses a pivot table with dynamic columns
If I may ask one more question. In the sproc, I am doing my SELECT...UNION statements in the order in which I want to see my rows. So I have rows named Cartons Received, Total Units Received, and Pct Missing:
Code:
SELECT StoreNumber, 'Cartons Received' AS TheType, CartonsReceived AS TheValue FROM mytable
UNION ALL
SELECT StoreNumber, 'Total Units Received' AS TheType, TotalUnitsReceived AS TheValue FROM mytable
UNION ALL
SELECT StoreNumber, 'Pct Missing' AS TheType, PctMissing AS TheValue FROM mytable
In my SSRS report, however, they are listed alphabetically: Cartons Received, Pct Missing, Total Units Received. I can't see how to turn off this alphabetization. Is it possible?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Re: [RESOLVED] SSRS report that uses a pivot table with dynamic columns
Never mind. Looked some more and found it: remove the sorting from the group properties. The default was that this had been created so all I had to do was delete it. Thanks. (Sometimes it isn't until I phrase the question that it comes to me what my search string should be: "tablix turn off alphabetization").
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Re: [RESOLVED] SSRS report that uses a pivot table with dynamic columns
Truly a follow-up question I don't think I will be able to find help for - See in Post #5 I have a row called Pct Missing? I want these values to be followed by a percent sign, such as 19.2%. The sproc returns 19.2. Is there a way I can do that? Because it's taking a sum, I have to return the pure numeric. That is, I can't append the % in the sproc (which I spent an hour trying).
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Re: [RESOLVED] SSRS report that uses a pivot table with dynamic columns
Never mind that previous question; that is done. This is my last question because it is the last thing I need to do and something I am not sure I can figure out. I need a couple of blank lines in between my rows. So lets say from what the sproc returns me, I get six rows. I want the report to have one rows, a blank row or line, then rows 2-4, then a blank, then the remaining rows. Is this possible? Since I've closed this thread and pretty much veered from the original topic (or drilled it down a lot), I will open a new thread Monday if no one is listening anymore.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Re: [RESOLVED] SSRS report that uses a pivot table with dynamic columns
Glad the link worked. I think it's what I found originally too that got me onto TextMatrix as well. I probably should bookmark it for safe keeping. Based on scuttlebutt at the office, I might need it again some day.
Hmmm... not sure if there is a way to manipulate it from the SSRS side... I'd probably cheat, add a "row sequence" to the source data, then dummy up empty rows and sort everything by the sequence so the blank lines appear where they need to. There's probably a more programmy way to do it, I'm just not fluent enough with SSRS to know off hand.
Re: [RESOLVED] SSRS report that uses a pivot table with dynamic columns
Good morning! While I did not solve it exactly as you suggested, your suggestion made me look more closely at the union statements of my final select in the sproc. I am selecting the rows in the order I want to see them, and selecting a blank line where one is desired. Such as:
Code:
SELECT StoreNumber, 'Cartons Received' AS TheType, CartonsReceived AS TheValue FROM thetable
UNION ALL
SELECT StoreNumber, 'Blank 1' AS TheType, 0 AS TheValue FROM thetable
UNION ALL...
Then in SSRS, I made the expressions conditional like this:
All I need to add is formatting (separate into thousands by commas and one of the rows needs percent signs - I'm assuming that'll be trivial) and I'm done with this report. Thanks again for all the help.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.