I’m working on an SSRS report for sales commissions. I’m no expert in SSRS, so I probably set something up wrong in the report, but I’m at a loss for how to get totals (subtotals of each category, and a grand total of each sales rep) working, along with how to hide each category when the detail is empty. I seem to distinctly remember something about adding a header to a group, but I wasn’t sure how to go back and do it after the fact. Also, every time I try uploading the report into Epicor after adding totals to a group, I get textbox scope errors.
Willing to share anything that would help. I’ve uploaded the rdl for reference. CommPayable.rdl (229.5 KB)
I don’t know the exact details…I’m just making the report the way I was told. I believe there is extra information we want reported that is not on the default report, plus since we’ve been doing this report manually for the longest time (moving data into excel/access), there’s a design aspect we’d like to stick with as this is a public facing report.
have you tried on doing a BAQ first and then convert it into a SSRS report?
to hide data in SSRS you will need to use the “hide” functionality at the needed level. Select the item level you want to hide (group, row, header, etc) and over the properties are scroll to the bottom and under the Visibility section, for Hidden select Expression:
depending the report needs will be the IIF statement you will need to use.
I’m not sure what you mean by this. The dataset is off a BAQ report, I created a data definition and a report style to make the SSRS report.
I only want to hide the headers if the detail row is empty, and the issue with using an expression is the detail row is using expressions and also its own filters for visibility. If I reference the group ex:
=IIF((Fields!Category.Value=0,True,False), “GroupName”)
I start getting scope level errors.
That textbox error you are getting is a pain. to resolve it we have to find the textbox that is calling for a missing field, or has an invalid expression. Since you didn’t make this report it is hard to know where to start. But the job is what it is, and you have to do it. So lets start by getting some details on that error. What is the error text? If it points to a specific textbox, then that gives you something to go on. If the error is on Textbox7, then open the report builder and find that text box. This isn’t always easy. Right click on the various text box areas in the report and look for the Select > menu. From there you can see the list of overlapping controls. Click around till you find the textbox that is the culprit. From there, the issue is probably with the expression in the text box, so give us that expression. It could also be that the BAQ or data source feeding the report is missing a field. That is a little trickier to track down, but the process is similar.
Good Idea…probably should’ve done this originally. So, lets say I take the last group, which is named Production (and is the last detail row, under the Production label), and add totals after the group (which are highlighted yellow).
For example we can use the middle item (under the comm $ header)…which has an expression of =Sum(Fields!Calculated_OrderTotalComm.Value, "Production") - Sum(Fields!Calculated_InvoiceTotalComm.Value, "Production")
Production is the name of the group, and I have the group grouped by OrderNum, along with some filters. This expression is taking the order commission less any invoice commissions. The totals expression given to me for this is the exact same expression.
When trying to upload to a report style I get this error
With the textbox19 being the totals expression. All I really want is to grab all totals of that textbox for the current rep.
For now, just take the formula out of the textbox19. Set the expression to 1, or something static just so we can get the report to upload and run. I downloaded your RDL but, I do not see any Textbox19, so you may have made edits after you uploaded, or there is something else going on here. Post the BAQ too.
