Can you "get at" the SQL Statement in a Report?

[nlk10010]

I have need of building/dynamically modifying the SQL statement used in one of my reports, to customize the filter. Now if this were a grid filter then I could access the statement as a property in an event and modify it on the fly. I can't find a way to do that from within a report (the report is launched from a button in a Tabbed UI).

The reason I need to do this is, for example, I have a report that asks the user if they want to include ALL bills or ONLY those bills that were invoiced. The field that indicates whether a bill has been invoiced is the bit (logical) field "LINVOICED" in my SQL Server table. The checkbox on the screen is labeled "Only Invoiced?" so that if a user checks it the filter should include something like "AND LINVOICED = true" while if it is not checked then the clause shouldn't be present at all. Now I realize there are a multitude of clauses that would work but I can't figure out a way, without getting at the SQL statement (or at least its filter clause) directly to have the filter built appropriately.

I looked at the video about dynamic filtering with reports embedded in a UX component but this is too restrictive to be useful (i.e. still can't get at the SQL statement or filter clause directly).

Thanks for any ideas.

[Catalin]

Re: Can you "get at" the SQL Statement in a Report?

I don't know if it possible to do that but I think you can pass a parameter :IsInvoiced to the sql query. In this way you can customize the report as you like.

[nlk10010]

Re: Can you "get at" the SQL Statement in a Report?

Passing the argument is not the problem. The problem is that if the user wants to see only invoiced bills I pass "true" and the WHERE clause reads LInvoiced = true, it works, but if the user wants to see ALL bills and I pass "false" and then the WHERE clause reads LInvoiced = false and I see only those bills that are not invoiced, not all bills.

Alpha apparently doesn't let you get at the SQL statement directly, at least for reports, but I DID find a thread here: http://msgboard.alphasoftware.com/al...t=Local+Filter that might let me do what I want.

Thanks regardless for posting and trying to help me out.

[GGandhi]

Re: Can you "get at" the SQL Statement in a Report?

Yes you can pass arguments to the report
I print my reports for dates or ids
You need to design straight report, the on the button when you call the report you set the filter.
I can show what I have or I may be able to work out a sample little later.

[nlk10010]

Re: Can you "get at" the SQL Statement in a Report?

Yes you can pass arguments to the report
I print my reports for dates or ids
You need to design straight report, the on the button when you call the report you set the filter.
I can show what I have or I may be able to work out a sample little later.

OK, so you're saying that in the OnClick event of the button in the TabbedUI that calls my report I can set the filter for the SQL Statement in Javascript? I guess I could see this except I can't find the "hook"; i.e. the pointer to the SQL Statement (or its filter clause). It might be in one of those Javascript code tabs, I'll look, but I'm reviewing the thread I pointed to and also a video it referenced.

Obviously, an example would be great but even if that isn't forthcoming, I appreciate you chiming in and confirming something like this can be done (whether it's from a TabbedUI or a UX Dialog).

[Catalin]

Re: Can you "get at" the SQL Statement in a Report?

Norman you ca use 2 parameters:

select * from bills where LINVOICED = :param1 or LINVOICED = :param2

1) param1 = true param2 = true - only invoiced
2) param1 = true param2 = false - all bills
3) param1 = false param2 = false - only not invoiced

[nlk10010]

Re: Can you "get at" the SQL Statement in a Report?

Norman you ca use 2 parameters:

select * from bills where LINVOICED = :param1 or LINVOICED = :param2

1) param1 = true param2 = true - only invoiced
2) param1 = true param2 = false - all bills
3) param1 = false param2 = false - only not invoiced

Catalin:

Ahh, yes, I guess my Boolean Algebra skills have become a bit rusty. However, this still begs the question: How do I set this up, say in the Tabbed UI? I can display a dropdown to the user with the choices "All", "Only Invoiced" and "Only Not Invoiced", but then how do I intercept the execution of the report to set the arguments appropriately? Javascript? XBasic function on the server? THAT'S the issue. One of the things I more or less like about Alpha5 (and why I'm investing some funds to evaluate it) is the surplus of "hooks". Now there may be one here and it might be obvious to many experienced developers but I'm still finding my way.

Appreciate the "lesson". :)

[Catalin]

Re: Can you "get at" the SQL Statement in a Report?

You could have a dropdown or radio group buttons and 2 hidden controls. When you select a value in dropdown through javascript set the selected value to this hidden control.

dropdown values
1. all
2. only invoiced
3. only not invoiced

var selected = {dialog.object}.getValue('MYDROPDOWN')

if (selected==1)
{
{dialog.object}.setValue('PARAM1', 1, false) (See the syntax for this functions)
{dialog.object}.setValue('PARAM2', 0, false)
}
else if (selected==2)
{
{dialog.object}.setValue('PARAM1', 1, false) (See the syntax for this functions)
{dialog.object}.setValue('PARAM2', 1, false)
}
else if (selected==2)
{
{dialog.object}.setValue('PARAM1', 0, false) (See the syntax for this functions)
{dialog.object}.setValue('PARAM2', 0, false)
}

When you click the button to open the report you should link the parameters in "Argument bindings" with

param1 = {param1}
param2 = {param2}

1.png

[Peter.Greulich]

Re: Can you "get at" the SQL Statement in a Report?

I don't think you can use the tabbed_ui to set up complex dynamic report filtering. However, what I do is create a UX with radio button choices or whatever and pass that into the report.

[Peter.Greulich]

Re: Can you "get at" the SQL Statement in a Report?

I see Catalin gave you a nice example. Remember you will still have to set the filter.

[nlk10010]

Re: Can you "get at" the SQL Statement in a Report?

Peter:

Yea, that's what I'm thinking, thanks to the video pointed to in the above-mentioned thread. In a UX with controls I've been able to modify the sql.filter in the text version of the javascript. That may be the way to go.

Catalin:

The hidden controls idea is clever, I can see how that would be doable with javascript. The devil is in details of your last statement, I think: "When you click the button to open the report you should link the parameters in "Argument bindings" with ...". I'm not sure I have that ability from within the OnClick event of the TabbedUI. But I WILL investigate.

Thanks to both of you.


Norman

[nlk10010]

Re: Can you "get at" the SQL Statement in a Report?

I see Catalin gave you a nice example. Remember you will still have to set the filter.

Well, from my perspective, it's not clear that I can have hidden controls when I'm activating a report from a TabbedUI, although I suppose something can be set via HTML in the header. Then there's the setting of the arguments, I need to investigate the options offered by the arguments dialog.

Thanks.

[Peter.Greulich]

Re: Can you "get at" the SQL Statement in a Report?

No, not from a tabbed_ui. Use a UX. Call the UX from the tabbed_ui.

[GGandhi]

Re: Can you "get at" the SQL Statement in a Report?

http://screencast.com/t/ZxPJQEnO

while I am recording interesting stuff going on, I agree with peter. you design a dialog (ux) then call the report

[nlk10010]

Re: Can you "get at" the SQL Statement in a Report?

Wow, that's what I call service, thanks much.

Yes, your approach in the Screencast is the approach used in the video I mentioned: Set up multiple actions, one for each possible filter, then call them in an OnClick event depending on the desired report. Selwyn used a dropdown to choose his grid, you simply click different buttons. Nicely done, but I'm going to have too many possible filters to do it this way. You DID answer my original question, which was a simplification.

Again, appreciate you trying to help me out here.