lasenzait
asked on
Exporting an SSRS Report with Custom Code shows 0
Hey All,
I've checked else where on google and can't seem to find anything on this topic so I figured I'd ask it here. I have an SSRS report with some custom code to calculate the Top N sum (needed to use custom code since the SUM function sums the entire dataset and not the filtered dataset). The code is below for reference but I know it's working, as on the preview tab in Visual Studios 2008's ssrs report builder it displays the total fine. The problem is when I export the report (to any format, but looking specifically for excel), the value in the total shows 0. I was under the impression that the rendered version is what is exported, but it seems I was wrong, but even so, shouldn't the export save the same result that the preview shows?
Has anyone seen this issue, and figured out a solution? Thankfully I tested the export before doing my sums for each column of the report (there are roughly 25 columns which need to be summed, and only finding out this after would have been terrible news).
The data is coming from an Analysis Services cube.
Unfortunately I cannot attach a sample of the report as it contains confidential information.
Thank you in advance!
I've checked else where on google and can't seem to find anything on this topic so I figured I'd ask it here. I have an SSRS report with some custom code to calculate the Top N sum (needed to use custom code since the SUM function sums the entire dataset and not the filtered dataset). The code is below for reference but I know it's working, as on the preview tab in Visual Studios 2008's ssrs report builder it displays the total fine. The problem is when I export the report (to any format, but looking specifically for excel), the value in the total shows 0. I was under the impression that the rendered version is what is exported, but it seems I was wrong, but even so, shouldn't the export save the same result that the preview shows?
Has anyone seen this issue, and figured out a solution? Thankfully I tested the export before doing my sums for each column of the report (there are roughly 25 columns which need to be summed, and only finding out this after would have been terrible news).
The data is coming from an Analysis Services cube.
Unfortunately I cannot attach a sample of the report as it contains confidential information.
Thank you in advance!
Public total as Integer
Public totalField as new System.Collections.HashTable
Public function AddTotal(ByVal product as String,Byval value as Integer)
if (totalField(product) is nothing) then
totalField.Add(product,value)
else
total = totalField(product)
total = total + value
totalField.Remove(product)
totalField.Add(product,total)
end if
End function
Public function GetTotal(Byval product As String) As Integer
return totalField(product)
End function
When you use VB code in 2008 you are better to use code like below....
Public total as Integer =0
Public totalField as new System.Collections.HashTab le
Public function AddTotal(ByVal product as String,Byval value as Integer) as Object
if IsNothing(totalField(produ ct)) then
totalField.Add(product,val ue)
else
total = totalField(product)
total += value
totalField.Remove(product)
totalField.Add(product,tot al)
return total
end if
return Nothing
End function
Public function GetTotal(Byval product As String) As Integer
return totalField(product)
End function
Public total as Integer =0
Public totalField as new System.Collections.HashTab
Public function AddTotal(ByVal product as String,Byval value as Integer) as Object
if IsNothing(totalField(produ
totalField.Add(product,val
else
total = totalField(product)
total += value
totalField.Remove(product)
totalField.Add(product,tot
return total
end if
return Nothing
End function
Public function GetTotal(Byval product As String) As Integer
return totalField(product)
End function
ASKER
Hello,
The reason I'm not doing it directly in MDX, is because the TOP N is a parameter value that the user can select from a drop down. I had originally made it so that that parameter goes to the dataset for the MDX, but it kept giving me errors, and eventually I was told to use the filter property on the table.
This works fine for displaying in the SSRS viewer, it's only on export that it disappears, and the users here generally export to excel.
I'll change the code to add the return nothing, but I don't see that as being the source of the issue.
Thank you, and if you have any other ideas, please let me know.
The reason I'm not doing it directly in MDX, is because the TOP N is a parameter value that the user can select from a drop down. I had originally made it so that that parameter goes to the dataset for the MDX, but it kept giving me errors, and eventually I was told to use the filter property on the table.
This works fine for displaying in the SSRS viewer, it's only on export that it disappears, and the users here generally export to excel.
I'll change the code to add the return nothing, but I don't see that as being the source of the issue.
Thank you, and if you have any other ideas, please let me know.
Hi
If you decide to go MDX route post the code which gives you errors and I might be able to help.
Regards
Emil
If you decide to go MDX route post the code which gives you errors and I might be able to help.
Regards
Emil
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I haven't come across this issue before but if you use SSAS as your source it might be easier to write MDX which will give you the answer instead of using code in SSRS. MDX usually handles complexity very well! Would that work for you?
Regards
Emil