Search results for query: *

Solved problem by using two temp tables and looping through data until no records were found. Thanks for your input Ian

Hi John Sorry my statement was perhaps a bit ambiguous. When I say manually create. I have strung together a series of CTEs which drill down through data and then UNION all the CTEs. I want to avoid this as I do not know how deep data can go. So either a recursive CTE or your Cursor suggestion...

Please note I have other CTEs above which create the table Level1. The recursion occurs from level 2 down. My plan is to union the level above to the recursive data. Works OK when I manually create the levels. Hence no WITH in above code. Ian

Tamar Thank you for your guides, I have tried what you suggest, here is my code. However, it will not execute as it will not allow left outer joins. I have to use left outer joins in the investee company and fund tables Msg 462, Level 16, State 1, Line 107 Outer join is not allowed in the...

Trying to build an investment query where I drill down from a fund or a company and find all underlying funds of investee companies. In reality this is unlikely to go down more than 3-4 levels but could go further. A company can invest in another company or fund and a fund can invest in another...

If you are using a command just add above select to your main query. Ian

Depends on database this works for MS SQL. In Crystal Reports, open the report based off a MS SQL Server database, on which you want to display the database name. In the Field Explorer pane, right click on "SQL Expression Field", and select "New..." Type a name for the SQL Expression Field...

Can you add a test to top of your formula something like If Not onfirstrecord and CutomerID <> Previous(customer) then yourvar:=0 else yourvar:=YOurvar onfirstrecord is a crystal function and should turn blue Ian

Difficult to say without any knowledge of report layout/structure. Make the above formula visible on report so you can see it incrementing. When you know which record is being skipped that might give you some clues as to why. Ian

If your Parameter is filtering data then surely dataset is what you want. Add a Cross tab to report header Alternatively Add a Group which encompasses data and then insert Crosstab in either group header or footer. Ian

Not seen this either. Can you try another connection type. I never use ODBC for Oracle, Native Oracle is best. Though have used OLEDB occasionally. Ian

Create a formula @sort If Status = 'Active' then DateDiff("d",dateadd('d',-365,currentdate),StatusDate) // gives date diff from date in past increases as dates get older else DateDiff("d",StatusDate,dateadd('d',365,currentdate))// gives date diff from date in in future increases as dates get...

Create a formula use your database fields and excel locations to replace my text This assumes there is a one to one link from DB to Excel records @Misc If {CrimeType} = 'Burgulary' then {burgularyExcel} else If {CrimeType} = 'Robbery' then {RobberyExcel} else If {CrimeType} = 'Stolen Vehicle'...

Create a Formula @Total Qty If {TransactionCode} = 'SUNREC' then {QTY}*-1 else else {QTY} Yu might need to expand this if other codes affect qty differently Replace QTY with this formula and then perform sum summary as usual Ian

I assume you have two date groups by Month and by week. Then placed your charts in week and Month group header? You could create another group for a two week band. The only way I can think of doing this is to use week number DatePart ("ww", {table.date}) @TwoWeekBand If DatePart ("ww"...

NumberVar Q1Tax; NumberVar Q2Tax; NumberVar Q3Tax; NumberVar Q4Tax; Are variables created to capture your tax amounts and effectively produce a running total in the background. If you do not suppress the formula you will see the Q4Tax value increment as you run through data. You can call them...

Misread your first post should be in Group Employee Name Ian

Why do you want to Group on Q1Taxable? I assumed this formula returned a number If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name}) Did you follow my syntax exactly? The ; and : are important Whileprintingrecords; global NumberVar Q1Tax; Q1Tax:= Q1Tax + (If Sum...

You will have to use a Variable. Assuming you want to sum on Employee Create 3 formula @reset // place this in Employee group header and suppress Whileprintingrecords; global NumberVar Q1Tax:=0; @Eval// place this in Q1Taxable group header and suppress Whileprintingrecords; global NumberVar...

Silly mistake on my part, Olaf's solution works. Not sure what he means by will not roll up? Declare @Testdate as date = '2019-02-01' Select Case WHEN DATEDIFF(month,'2018-07-01',@Testdate)=0 THEN 220 * day(@Testdate) -- JULY WHEN DATEDIFF(month,'2018-07-01',@Testdate)= 1 THEN 148 *...