Results 1 to 10 of 10

Thread: [RESOLVED] SSRS report that uses a pivot table with dynamic columns

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [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.

  2. #2
    Addicted Member thetimmer's Avatar
    Join Date
    Jan 2014
    Location
    Plano, Texas
    Posts
    243

    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 thread Resolved.


    reference links

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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.

    -tg


    edit - see if this is of any help. http://stackoverflow.com/questions/1...to-ssrs-report
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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).
    Attached Images Attached Images  
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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:
    Code:
    =iif(Fields!TheType.Value.IndexOf("Blank") >= 0, " ", Fields!TheType.Value)
    and
    Code:
    =iif(Fields!TheType.Value.IndexOf("Blank") >= 0, " ", Str(Sum(Fields!TheValue.Value)))
    Which I believe is a pretty good way to do it.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width