Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. IanWaterman

    Hierarchy with indeterminate number of level

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

    Hierarchy with indeterminate number of level

    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...
  3. IanWaterman

    Hierarchy with indeterminate number of level

    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
  4. IanWaterman

    Hierarchy with indeterminate number of level

    ...--WHERE ACCOUNT_IQID = 'EFD7FF92A1CD48B29626FC3A77D12238'-- WHERE ACCOUNT_IQID = '44BF4D2D7518466AAE6B2A942C93075E' -- AND MANAGED_ENTITY_NAME like '%SVF%' AND Layer < 8 ) select * from LEVEL2 where INVESTEE_FUND_IQID is not null OR INVESTEE_COMPANY_IQID is not null
  5. IanWaterman

    Hierarchy with indeterminate number of level

    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...
  6. IanWaterman

    db name and server name

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

    db name and server name

    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...
  8. IanWaterman

    Variable reset in a cross tab report

    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
  9. IanWaterman

    Counting records per page- counter skipping numbers

    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
  10. IanWaterman

    I am using cr2016 I have a param

    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
  11. IanWaterman

    Unable to display certain punctuation characters from a memo field

    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
  12. IanWaterman

    Dynamic sorting of details within group

    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...
  13. IanWaterman

    Placing different data fields into the same location at the Details section

    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'...
  14. IanWaterman

    Calculate Qty depending on another field

    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
  15. IanWaterman

    Display the current week of data - Crystal Reports 2016

    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"...
  16. IanWaterman

    Sum on Formula That's Summed?

    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...
  17. IanWaterman

    Sum on Formula That's Summed?

    Misread your first post should be in Group Employee Name Ian
  18. IanWaterman

    Sum on Formula That's Summed?

    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...
  19. IanWaterman

    Sum on Formula That's Summed?

    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...
  20. IanWaterman

    Target Value that increases throughout the year

    ...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 * day(@Testdate) + (6820) -- AUGUST WHEN...

Part and Inventory Search

Back
Top