How to display all tablix columns, including zero?
I am developing an SSRS 2008 tablix. The problem that I have though is that I need to display all 12 months of the year. Currently, it only displays columns with values > 0. Even though I entered this expression for that field: =IIF(Sum(Fields!NumActionPlanRemainOverdue.Value)=0, 0, Sum(Fields!NumActionPlanRemainOverdue.Value)) What else can I do?Ryan D
July 25th, 2011 4:22pm

Hello ironryan77, try: =IIF(Sum(Fields!NumActionPlanRemainOverdue.Value) is nothing, 0, Sum(Fields!NumActionPlanRemainOverdue.Value)) It will work if you are getting in Null. =IIF(Sum(Fields!NumActionPlanRemainOverdue.Value)<0, 0, Sum(Fields!NumActionPlanRemainOverdue.Value)) it will work if is less than '0' as you have stated in your question. Hope this helps. Thank you, SammieRS.Sammie
Free Windows Admin Tool Kit Click here and download it now
July 25th, 2011 5:55pm

Thanks Sammie. Neither of those worked though. Reason is probably that my SQL dataset does not have records for every month. What I tried doing was creating a temp table with all 12 months and then unioning this table with my dataset. This resulted in all 12 months. But since I just unioned on this one column, this gave me an error since I dummied up the other columns in this temp table. This is a tablix; not a chart. Data type of month is varchar. Any other ideas?Ryan D
July 25th, 2011 6:09pm

Hello Ryan, I had the same issue. You can write a stored procedure. You can write a case statement: when the value is null then 0. It helped me before. Hope it helps you. Thank you, SammieRS.Sammie
Free Windows Admin Tool Kit Click here and download it now
July 25th, 2011 6:17pm

Hi ironryan77, Based on your description, if you need to display 12 months in a report, you can try to join a month-list table in the query of the dataset. For example: SELECT table_A.MonthName, table_B.ProductCategoryName, FROM table_A CROSS JOIN table_B If the field value is null, you can use IsNothging() function to deal with this issue. Based on your requirement, please refer to the expression below: =IIF(IsNothing(Sum(Fields!NumActionPlanRemainOverdue.Value)), 0, Sum(Fields!NumActionPlanRemainOverdue.Value)) For more information about Expression Examples (Reporting Services), please refer to the article below: http://msdn.microsoft.com/en-us/library/ms157328(v=SQL.100).aspx Thanks, Bin Long Forum Support If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.comPlease remember to mark the replies as answers if they help and unmark them if they provide no help.
July 26th, 2011 12:58am

Thanks, but this didn't fix it either. Now I can't get all 12 months to display in TSQL. I just posted a new questoin regarding this code. Can you look at that? URL: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b23de9a8-6560-4459-976e-e217dc2b10a5 (In T-SQL forum)Ryan D
Free Windows Admin Tool Kit Click here and download it now
July 28th, 2011 10:34am

Hi Ryan, Sorry for my delay response. In the above link, you solved the problem that joins a month-list table via T-SQL code. How about replace the “NULL” value with “0”? If all of the issue has been solved, please share the solution here. It will benefit other community members who have the similar issue with you. If you have any more questions, please feel free to let me know. Thanks, Bin Long Forum Support If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.comPlease remember to mark the replies as answers if they help and unmark them if they provide no help.
August 1st, 2011 9:28pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics