SMI How to add a new expression to the layout page

Replies to This Discussion

Permalink Reply by Keith Govender on December 29, 2011 at 4:11pm

Hi Clark,

 

I am looking into this one for you.

 

Thanks!

• ▶ Reply

Permalink Reply by Clark Walliser on December 29, 2011 at 7:52pm

Ok, I think I figured it out. Let me know if I am heading the right direction, or off a cliff... :)

 

 Step 1. How to create the new expression fields

In the connector app, double click on the connector I am using (in this case the SOTAMAS silent).

This will list the data groups, ie. Financials, Consolidated, etc.

If you double click on a data group, i.e. financials, all the Fields / Expressions will display.

I copied the PYActivityPer01 expression and changed the name to PY2AcitivityPer01.

Then I changed the SQL expression from select FISCALYEAR -1, to FISCALYEAR -2.

 

Step 2. To get it to be available on your report layout.

I Opened Report manager and clicked on my report.

I clicked on the Columns Tab (on the right side of the screen)

This shows all the data fields available to the report.

I clicked on ADD, and the new PY2 fields I had created were avialable. So I added them to the list.

 

Step 3.

I ran my report, then clicked on BI tools to edit a layout.

Now I can see the PY2 fields I created and add them to the report layout.

 

I am wading through various SQL error messages, so I have some cleanup.

But I think I got the concept.

 

 

 

• ▶ Reply

Permalink Reply by Keith Govender on December 30, 2011 at 4:07pm

You are on the right track!!

• ▶ Reply

Permalink Reply by Clark Walliser on February 2, 2012 at 9:48pm

I'm just in the process of figuring this out myself. This is what I have found...

1. In the connector, drill down to the container you are using (for me it was Financial Reports).

2. Create the new expressions in the container.

3. For SMI to pull more than 2 years of history, you must modify the Filter in the Container.
With the container highlighted (Financial Reports) click on Show Advanced.
Note the Hints for Universal Query Engine. Check the SQL statement.
By default it has the selection fn CONVERT('@FISYEAR@', SQL_INTEGER)}-1
You can change the -1 to however years you want to go back. I did -5 to get a 5 year trend.

4. Open the Report Manager and click on the template you are using.
Click on the columns tab, then click ADD. The list of expresssions displays and you check the ones you want to add.

5. Open your layout. These expressions should now appear at the bottom of the list of Green Expressions.

To create YTD fields for prior years I had to create new Formulas (Blue) to add PY2Per01 to PY2Per12.

 

Hope that helps.

Clark

• ▶ Reply

Permalink Reply by Debbie Long on March 1, 2012 at 1:50am

Clark,

I have modified the SQL statement in the Container, under Properties, Hints as follows:

<hints>
                    <workertables>
                        <table name="GL_PeriodPostingHistory">
                            <addfilter>
                            (FiscalYear = fn CONVERT({fn CONVERT('@FISYEAR@', SQL_INTEGER)}-3,SQL_CHAR)} OR FiscalYear = '@FISYEAR@')  AND (BeginningBalance &lt;&gt; 0 OR CreditAmount &lt;&gt; 0 OR DebitAmount &lt;&gt; 0 )
                            </addfilter>
                        </table>
                        <table name="GL_PeriodBudgetDetail">
                            <addfilter>
                            (FiscalYear = fn CONVERT({fn CONVERT('@FISYEAR@', SQL_INTEGER)}-3,SQL_CHAR)} OR FiscalYear = '@FISYEAR@') AND (CreditAmount &lt;&gt; 0 OR DebitAmount &lt;&gt; 0 )
                            </addfilter>
                        </table>
                    </workertables>
                </hints>

The result is I get the current period and the 3rd prior year.  If I change the statement to:  fn CONVERT({fn CONVERT('@FISYEAR@', SQL_INTEGER)}-2,SQL_CHAR)}, I get current period and 2nd prior year.  How can I get a report that shows the current period, prior year, 2nd prior year, 3rd prior year, 4th prior year and 5th prior year? 

• ▶ Reply

Permalink Reply by Debbie Long on April 13, 2012 at 6:56pm

Clark,

I have followed your steps for a 5 year trend and I only ever get the last year; never get data in PY1, PY2, PY3 or PY4; only PY5.  What am I doing wrong? 

Debbie Long

• ▶ Reply

Permalink Reply by Clark Walliser on April 17, 2012 at 5:42pm

I wish I knew Debbie,

I thought I was on the right track, but never got it to actually show up in the excel output.

Most of what I came up with was by trial and error, so I don't know if there is an official way to do it.

And I haven't connected with anyone else yet that can produce a 5 year trend in SMI.

I finally gave up...

 

• ▶ Reply