locked
VS 2005 - Report Matrix - force columns to show for data not in current selection RRS feed

  • Question

  • Here's my data:
    SQL Dataset (select * from RMA) contains 2 key items, "Network" & "Status". Network is a list of about 15 "Networks" and "Status" is either "open" or "closed". the data is list of various RMA's for equipment, ATM some 600+ entries in my test data.
    So here's generic example of what I'm working with
    OBJID 1 // Network_A // Open
    OBJID 2 // Network_B // Closed
    OBJID 3 // Network_B // Open

    My issue:
    When I build a VS 2k5 report template and and add a matrix and set "Networks to the rows, Count of "status" to data and "Status" to column it works fine, i get a table
    Network // Open // Closed
    Network_A // 1 // 0
    Network_B // 1 // 1
    Exactly what i want.
    But if report only on network_A, it doesn't build the Closed column.

    My Question:
    Assuming the reverse of the above example is true as well, there could be networks where all item status's are "closed" and the "open" column is not building.
    I need a way to force the matrix to show the count of "open" items column and count of "closed" items columns. and I want to display  in a single view [ Network, count of Open, Count of closed].
    This is my first report I've tried to build using the built in reporting service so I'm a little lost and brain is turning into go from all the "solutions" I've tried. I could of course go through and build the whole reporting by hand, I just want to use the reporting capabilities for.

    Thanks - James

    The beatings will continue until morale improves.
    Wednesday, September 23, 2009 7:05 PM

Answers

  • *Sigh* Admitted defeat too soon. Figured it out...
    In the data section dragged another instance of status to it, this created 1 group and 2 static column names.
    I deleted the group edited the static columns to Open and Closed and then put these formulas in the two data section...
    <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} span.EmailStyle15 {mso-style-type:personal; mso-style-noshow:yes; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; font-family:Arial; mso-ascii-font-family:Arial; mso-hansi-font-family:Arial; mso-bidi-font-family:Arial; color:windowtext;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    =count(iif(fields!status.Value="Open","True",nothing))

    =count(iif(fields!status.Value="Closed","True",nothing))

     

    So my matrix now has two static columns called "Open" and "Closed", rows that build dynamically and accurate counts of the open and closed RMA's.   Now onward to figuring out how to pass parameters to the report viewer.


    The beatings will continue until morale improves.
    • Marked as answer by James Burt Wednesday, September 23, 2009 10:15 PM
    Wednesday, September 23, 2009 10:15 PM