SSRS reports BAQ report designer and setting filters / sorts

cpelowski · March 17, 2020, 3:07pm

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

Please and thanks!
Carol

jkane · March 17, 2020, 3:11pm

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.

cpelowski · March 17, 2020, 4:11pm

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

ckrusen · March 17, 2020, 4:34pm

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.

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

And the last one has no presorts.

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.

cpelowski · March 17, 2020, 7:32pm

Thank you … I will need that.

cpelowski · March 17, 2020, 7:33pm

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?

ckrusen · March 17, 2020, 7:49pm

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:

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

Need more details?

AlexAverbukh · March 18, 2020, 1:23pm

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.

ckrusen · March 18, 2020, 1:25pm

What would be the SQL syntax for that?

cpelowski · March 18, 2020, 1:27pm

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

AlexAverbukh · March 18, 2020, 1:39pm

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

cpelowski · March 18, 2020, 1:38pm

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

cpelowski · March 18, 2020, 1:47pm

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

ckrusen · March 18, 2020, 1:56pm

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

ckrusen · March 18, 2020, 2:16pm

Make a Sort entry for each of those, with the “Sort Field” pane blank for each
In the SSRS Builder, setup the tablix (Tablix1) to show the info.
Add any grouping and group sorting for how it should appear for Dept - EmpID
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

Copy and paste Tablix1, making Tablix2.
Edit the sorts and grouping of Tablix2 for the desired output for Dept - Last Name
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.

cpelowski · March 18, 2020, 2:41pm

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

cpelowski · March 18, 2020, 5:07pm

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

AlexAverbukh · March 18, 2020, 5:53pm

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

I then created a BAQ Report and added 2 Sort Options to it…

When I test the BAQ I get the following Objects created in the Reporting DB

taking this one step further, i can see my sort options in the BAQReportParameter table

Now go grab the RDL and take a look at the Dynamic SQL Query

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.

ckrusen · March 18, 2020, 6:05pm

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

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

Click somewhere in the body of the report like:

You’ll then see:

Over on the Properties pane, click the Properties button

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

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.

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.

cpelowski · March 18, 2020, 6:15pm

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