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