Brio Reporting Broadcasting A New Query Each Day

[Tony Tillyer]

(You may have seen this question in one guise, or another but I really *do* need a good example to get this Query going!):

I'm using Brio 6.2.3;

I am trying to get a Report Generation Assistant Query *only* saved onto our Broadcast Server, so that it reports the previous day's data, as an Excel document. Following various instructions, I have added a Field to my Query, called Variable Date, with GETDATE() as the Function. I've tried to add this Field to my Query Limits and attempted to add it in as a Variable but, no matter what I try, I'm being told that I am using the wrong syntax.

I do have other Date Fields that I could use as variable, but they are all based on date *and* time. This would mean that only one entry would be returned if I used one of those Fields as my Variable, which is the reason why I need to set up a new Date Field to base my Query on.

Does anyone have a full example of how I can set up my Query on the Broadcast Server, so that it reports on the previous day's data, "automagically", every day?

TIA!

Tony Tillyer

[tony]

Hey - Found this and noticed I didn't post the answer when I finally got it:

Apply the following as a Computed Item in the Query:

For SQL, use;

CASE WHEN T_450_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0 END

For Oracle, use:

DECODE(T_450_Time_Dim.Date_Yyyymmdd, TO_CHAR(SYSDATE-1,'YYYYMMDD'),1,0)

Where T_450 is the T_ Table relevant to your Brio Query.

- Then drag this in as a Selection on the Query and enter the digit 1 as the constraint if you use the SQL version.  This will filter out only entires which match yesterdays date.

Quite simple really - no idea why it took me 23 months to post this answer though.  Must be losing my marbles!

Tony

[mark]

Thread resurrection!!

Running DM on a SQL db, wanting to pull yesterdays stats automatically.

Any suggestions on what custom SQL should be used?

[tony]

Hello Mark 

I think I gave the answer..? Not sure what you mean by "DM"..?

"To include the following custom stat in your Query:

CASE WHEN T_XXX_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()[b]-1[/b],12) THEN 1 ELSE 0 END

Then drag this in as a Selection on the Query and enter the digit 1 as the constraint if you use the SQL version.  This will filter out only entries which match yesterdays date."

Is this it..

T

[mark]

Sorry Tony, where are you creating the custom stat to?

Normally I would put the custom SQL into the 'Query Date' box when dragging it into 'Limits'

Mark

[tony]

My fault Mark - I wasn't being clear...

You need to add in a [i]Computed Item [/i] to the [b]Request [/b] section, first.  Call it [i][b]Previous_Day [/b] [/i] and use the T_Table reference of the Query within the context of the SQL:

CASE WHEN T_[i]XXX[/i]_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()[b]-1[/b],12) THEN 1 ELSE 0

After that, drag [i][b]Previous_Day [/b] [/i] into the [i]Limits [/i] Section - when you do that, you need to apply a Limit criteria of "=1" for it to return the previous days data only...

Sorry - no pretty pictures this time..

T

[mark]

Cool cheers.


[edit]

Doing that gives me

CONVERT(Previous_Day)= in the request bar.
Dragged to Limits with criteria =1

Processing gives the error:
"Incorrect syntax near the keyword 'FROM'"


Does it matter that I am using a normal T table rather than one of the T_xxx_Time_Dim tables? (that kinda leads to a question of what is the importance of the time_dim tables!)


Gotta be me doing something wrong!

[tony]

Yep - the date/time format is specific to a Time Dimension Table...

OK - you would ordinarily have 3 Tables to work from: Object (O_xxx), Stat (R_xxx) and Time (T_xxx).  The basis of your original Query should show these Tables in the Structure View/Pane, with connectors such as;

O > R = [b]ObjectID[/b]
R > T = [b]Time Key[/b]

The T_xxx Table contains different variations of the Time Key itself, such as:

Date Yymmdd
Date Yyyymmdd

The concept of applying a Limit to the T_xxx Table means you need to get the Syntax of the Date/Time correct, for the SQL Statement to parse correctly.

In my examples, the Format of the Date_Yyyymmdd is CONVERTed to character set 12 - this is defined as: yymmdd

So, the SQL takes the format Date_Yyyymmdd and converts it to yymmdd and then minuses "1" from that figure, giving you something like yymmdd minus 1 = yesterday, irrespective of the actual date.

I don't know which Field in your Time Table is comparable to my Date_Yyyymmdd but there must be a Field reference you can use, to determine the date (i.e. yesterday) of the data you want to extract...?

Not sure if all that is clear - but it is in my head (don't go there..! )

T

[mark]

yeah, I'm using R, O and T tables, just that there are lots of T_xxx_Time_Dim ones that I'm completely unsure about.

T_1_Time_Dim has Date Yymmdd, but still errors. I'll try some more to see if i can find one that works!

[tony]

Maybe I need to clear something up - the O, R and T tables need to be produced under the Report Generation Assistant.  It's either that or you need to find out which O, R and T tables were actually built with the same Objects and Aggregation Levels, as defined in the ETL Layout... But that depends on the naming conventions used within the Layouts... 

OK - we could make the [i]assumption [/i] that your Tables (or Views) have the same naming conventions applied to them and use (for example):

T_415_Day contains the Date criteria for your Query.  This would be based on DAILY Aggregations only, as the name suggests.
O_415_Day contains the Object criteria for your Query, used in DAILY Aggregation.  This may be Route Points, Agents, Agents Groups, DN Gropus but it's not usually a mix of Object Types.
R_415_Day contains the Statistical data criteria of your Query, based on the Objects and DAILY Aggregations.

Next, all 3 tables need to be JOINed, in some way.  In the Structure View, drag a JOIN between:

R_415_Day.ObjectID and O_415_Day.ObjectID
O_415_Day.TimeKey and T_415_Day.TimeKey

After that, you can drag the Object Fields, Statistics Fields and Time Fields you need into your Request Selection.

After that, you can apply the SQL to the Request Section and apply "=1" within the Limits of the Query... along with any other Limits for Objects, etc...

I think that should do it...

T

[mark]

Building the reports to get them running isnt the problem, that part is fine. Pulling results back for all sorts is fine :]

the only issue comes when trying to run the report to auto select yesterdays date.

I have used commonly named T, O and R tables but get the same error
eg: R_A_PERF_DAY, T_A_PERF_DAY and O_A_PERF_DAY (obviously changing the SQL to reference T_A_PERF_DAY).

screenshot attached

[tony]

Sorry Mark - I don't have SQL... but it may be that you need to set the Datatype when you create/define the Computed Item.  On the Computed Item selection box, there is an Options button - you might want to try some different Datatypes and see if that helps...

[b]EDIT:

You can copy out the Query Syntax... go to the File Menu>View>Query Log and leave that window at the front as you hit Process on the Query.  De-sensitize the Query if you need to, then copy/paste it here...

[/b]

[mark]

Yeah tried a few different ones that might be applicable. No change.
Will run through the rest to try them, even if its just to see whether a different error is given!

[mark]

Dropped some of the query to clean it up, just got Tt_Login now.

SELECT AL1.PRESENTATION_NAME, AL2.TT_LOGIN, AL3.QUERY_DATE, AL3.DATE_YYMMDD, CASE WHEN AL3.DATE_YYMMDD = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0 FROM dbo.O_A_PERF_DAY AL1, dbo.R_A_PERF_DAY AL2, dbo.T_A_PERF_DAY AL3 WHERE (AL2.OBJECT_ID=AL1.OBJECT_ID AND AL3.TIME_KEY=AL2.TIME_KEY)  AND (CASE WHEN AL3.DATE_YYMMDD = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0=N'1')

[tony]

Not sure but this don't look right...

(CASE WHEN AL3.DATE_YYMMDD = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0=[color=red][b]N[/b][/color]'1')

You sure you only entered the digit "1" in the box...?

T