SSRS reports BAQ report designer and setting filters / sorts

Epicor ERP 10
, ,
1

Are there any good videos, training materials on applying filters & sorts in SSRS that are established in the Report Designer in Epicor 10.2.400.14?

I have 4 different sorts established in Report Designer, but they do not carry through to the SSRS report
Also, I need to be able to select 2 or more departments in an option field since filters are not available as the Report is using an External BAQ.
I added a BAQ combo box listing the departments (is is possible to select more than one from a drop down list?)
Very much a learning curve for me :slight_smile:

Please and thanks!
Carol

2

It would be better if you created the report through a Report Data Definition.

  • Create a BAQ
  • Create a RDD
  • Add the BAQ to the RDD
  • Use the Report Criteria Sets to add in the prompts and filters that you want.
3

From a previous case with Epicor they told us that the functionality was not working yet. Adding a BAQ to the RDD,

4

The Sorts you add in “BAQ Report Designer” can either affect the data set sent to SSRS (by pre-sorting them), or merely add parameters that are passed to SSRS.

In the following I’ve added 5 sorts to the BAQ Report.

The first one appears as “Invoice Num ASC” in the dropdown, and has a “pre-sort” on InvoiceNum (ascending) applied to the dataset, as specified in the lower pane.

image

The third displays as “Date Newest first” in the dropdown and pre-sorts on InvoiceDate

image

And the last one has no presorts.
image

Why would you use the last one? It does no pre-sorting to the dataset, but it is passed as a parameter, which could be used for complex sorting (or filtering) done in SSRS.

5

Thank you … I will need that.

6

I am using an external BAQ so there are no filters to select from. Is there another way I can add a filter in Report Designer?

7

You can create a Sort entries without any sort fields.

Then in the report, duplicate the tablix and manually set the sort order in the tablix’s properties. Then use the parameter value to show/hide the tablixes that match your desired sort.

For example:

  1. Setup the tablix to be how you want it laid out. We’ll call this Tablix1.
  2. Set a default sort order in Tablix1, using the tablix’s properties,
  3. Duplicate Tablix1, creating Tablix2.
  4. Place Tablix2 directly below Tablix1.
  5. Set an alternate sort order for Tablix2 (like in step 2)
  6. Set visibility of Tablix1 based on an expression using the dataset parameter.SortBy
  7. Do the same on Tablix2 (but so that it’s shown instead of hidden).

Need more details?

8

In theory, you could also just add an order by statement at the end of your Dataset, if you identify the sort via a parameter.

9

What would be the SQL syntax for that?

10

Thank you both for the input. I will be trying both of these suggestions today. If I run into a snag, I will let you know.

Thanks again!!

Carol

11

Well the SQL Call is Dynamic, so technically you could put your table output into a temp table and then build out the final select with as needed. For example:

Declare @SQL as varchar(2000)
Declare @OrderBy as Varchar(100)

Set @SQL = ‘’
Set @OrderBy = ‘’

SELECT T1.BLAH BLAH
INTO #TEMP
FROM BAQRESULT

SELECT @OrderBy = CASE @ORDERBYPARAM WHEN = X THEN ‘ORDER BY 1,2,3’
WHEN = Y THEN ‘ORDER BY 1,3,2’
ELSE ‘ORDER BY 3 DESC, 2, 1’
END
SET @SQL = 'SELECT * FROM #TEMP ’ + @OrderBy

12

My guess would be that at the end of SQL statement I could put — order by EmpBasic.JCDept, EmpBasic.EmpID asc

But now thinking about that, it may not work. In the Report Designer, I have 5 different sort options. My thought is I will have to have to add 5 parameters/sorts
on the SSRS side to match those of the Report Designer.

Keep in mind this is an Report using an External BAQ. The reason for creating an external query is because I need to reach into the FileStore (Ice Table) which is not available in a regular BAQ. I mapped the Image ID to the FileStore SysRow ID to get to image
content which is a binary field. I saved this as a view and pulled it into the external BAQ

Below are my 5 sort options

Dept - EmpID

Dept - Last Name

Dept - Shift

EmpID

Last Name

I hope the way I am wording all of this makes sense.

Thanks again.

Carol

13

Hmmmm …. I am not that SQL savvy … this can be done in a view or would it have to be a SP?

14

Even if the SortBy value was in a different table? In BAQ Reports, the SortBy is in BAQReportParameter.

Here’s the query expression for a very simple BAQ Report’s BAQReportResult dataset

="SELECT [InvcHead_InvoiceNum],[InvcHead_InvoiceSuffix],[InvcHead_InvoiceType],[InvcHead_InvoiceDate],[InvcHead_GroupID],[InvcHead_OrderNum] FROM dbo.[BAQReportResult_" + Parameters!TableGuid.Value + "]"

Assume valid values for BAQReportParameter.SortBy are:

  • value: Invoice Number, desired sort: InvcHead_InvoiceNum asc
  • value: Invoice Date, desired sort: InvcHead_InvoiceDate desc
  • value: Invoice Group, desired sort: InvcHead_GroupID asc
15

  1. Make a Sort entry for each of those, with the “Sort Field” pane blank for each
    image

  2. In the SSRS Builder, setup the tablix (Tablix1) to show the info.

  3. Add any grouping and group sorting for how it should appear for Dept - EmpID

  4. Set Tablix1’s visibility to use “Show or Hide based on the expression”, and enter the expression like

=iif(First(Fields!SortBy.Value, "BAQReportParameter") <> "Dept - EmpID", True, False)

Tablix1 will now only be visible when the user selected the sort Dept - EmpID

  1. Copy and paste Tablix1, making Tablix2.
  2. Edit the sorts and grouping of Tablix2 for the desired output for Dept - Last Name
  3. Edit Tablix2’s visibility expression to:
=iif(First(Fields!SortBy.Value, "BAQReportParameter") <> "Dept - Last Name", True, False)

repeat steps 5-7 for Dept - Shift, EmpID, and Last Name

For debugging, I usually set the background color of the tablix different for each one, so you instantly know which is being displayed. Then remove the background color before deploying.

1 Like
16

Thank you…. That sounds like it will work wonderfully.

17

Calvin … could you insert a screen shot of where in SSRS report builder you set up the Tablix. Which area do I click in to select this? Sorry but my experience
in SSRS is very limited … just starting to use it.

Thank you.

Carol

18

So here is what I was talking about, I created a very basic BAQ just hitting the Erp.InvcHead Table:

image
image881×670 44.2 KB

I then created a BAQ Report and added 2 Sort Options to it…
image
image
image800×503 16.8 KB

When I test the BAQ I get the following Objects created in the Reporting DB
image
taking this one step further, i can see my sort options in the BAQReportParameter table
image
Now go grab the RDL and take a look at the Dynamic SQL Query
image
image1103×831 59.4 KB

It should be something similar to the following (YES I FORMAT MY SQL FROM ONE LINE TO MULTILINE) :
=“SELECT [InvcHead_Company]
,[InvcHead_InvoiceSuffix]
,[InvcHead_GroupID]
,[InvcHead_InvoiceNum]
,[InvcHead_InvoiceType]
,[InvcHead_OrderNum]
,[InvcHead_CustNum]
,[InvcHead_InvoiceDate]
,[InvcHead_DueDate]
,[InvcHead_InvoiceAmt]
,[InvcHead_SalesRepList]
FROM dbo.[BAQReportResult_” + Parameters!TableGuid.Value + “]”
I recommend changing your SQL statement to something like this:
="
DECLARE @SQL VARCHAR(2000)
DECLARE @SORT VARCHAR(200)

SELECT @SQL = ‘’, @SORT = ‘’

SELECT @SORT = SortFields from [dbo].[BAQReportParameter_" + Parameters!TableGuid.Value + “]
SET @SQL = ’
SELECT [InvcHead_Company]
,[InvcHead_InvoiceSuffix]
,[InvcHead_GroupID]
,[InvcHead_InvoiceNum]
,[InvcHead_InvoiceType]
,[InvcHead_OrderNum]
,[InvcHead_CustNum]
,[InvcHead_InvoiceDate]
,[InvcHead_DueDate]
,[InvcHead_InvoiceAmt]
,[InvcHead_SalesRepList]
FROM dbo.[BAQReportResult_” + Parameters!TableGuid.Value + "] ’
IF LEN(@SORT) > 0
BEGIN
SET @SORT = ’ ORDER BY '+ @SORT
EXEC (@SQL + @SORT)
END
ELSE
BEGIN
EXEC (@SQL)
END "

Although all of our solutions will work for you, I feel like the more processing you can do on the SQL side the more optimized your reports will be, if you have to populate multiple tablix… (how the heck do we make this plural?) controls and then suppress them on parameters, well SSRS still has to do all that work, this way you get one result set regardless of what sort options you choose.

2 Likes
19

You found the report RDL created by BAQ Report Designer, correct? It should be in the CustomReports folder.

image
image847×503 18.9 KB

Click those three dots to pop-up a menu, and select “Edit in Report Builder”

When Report Builder opens up you’ll see a fairly blank report. Only info in the Header section.

select the Insert tab, then select Table -> Insert Table

image

Click somewhere in the body of the report like:

image

You’ll then see:
image

Over on the Properties pane, click the Properties button

image
image952×389 17.3 KB

If Tablix1 isn’t selected, click in the tablix (1), then click the upper left box(2).
image

This changes the focus from that cell that was clicked, to the whole tablix. Now you can select the properties icon from the properties pane.

In the Properties window select the BAQReportResult as the dataset.
image

While you’re here, yo can set the visibility of this tablix (described in an earlier post)

Click ok after setting the visibility expression and close the tablix’s properties. Go ahead and add the columns and grouping

Now you’ve got a tablix that will only show when the user selected the sort named “Dept - EmpID” when running the report.

Try this out so far.

If you think you’ve got it, copy that tablix (select it the way I descrivbed above), and paste a copy into the body of the report RDL.

20

Wow … thanks.

I’m trying both solutions; yours and Alex’s. I’ll see which one I get working.

Thanks both of you for your help.

Carol