Recent content by bcking

Then how could you be expected to know which records are on a Monday or which are on Tuesday? You will only know the month and year for each record, not each day. It would then be impossible to only show records on Monday.

You will need to make sure that your COGNOS joins are outer joins. You can check them by going to CATALOG / JOINS and selecting the relevant join, there are tick boxes to make them outer joins.

So your filter will have; Year = YEARPROMPT and Month = MONTHPROMPT and dayofweek(DATE) = 1 Where the DATE fieled in the dayofweek function is the date of the record. Do you have a date field for each record?

Just add a filter in the report that will return only mondays. add this to your existing filter. dayofweek(DATE) = 1 Where dayofweek is a function. And then sort the DATE field A - Z.

I have a cube that I need to create a 6 Week financial period in December. I know that Transformer has the ability to add an extra week, but if Dec is is already a 5 week period then the Cognos help tells me that it will make Nov a 5 week period, not Dec a 6 week period. I have created a...

Give this a go. UPDATE MyTable SET MyBitColumn = MyNumericColumn1 + MyNumericColumn2 WHERE MyNumericColumn1 + MyNumericColumn2 <> 0 and etc.... Brett

Why would you like to know how much it costs if you don't even know what it is??? Try www.microsoft.com/sql

I found the solution. To overcome the problem I needed to have 2 if-then statements. If (Total(Qty) <> 0 then Total(Sales) else 1) / (If Total(Qty) <> 0 then Total(Qty) else 0) The reason for the error in the first place was that the DB actually can perform the Sales/Qty calc. before the...

I have created a report that shows Sales, Quantity and Margin. Margin is created by Sales / Qty. I have totals at a couple of groupings, so Margin is the Total(Sales) / Total(Qty). In each Margin calculation I have used an if then statement. If Total(Qty <> 0) then Total(Sales) / Total(Qty)...

Have you tried to read the manual to get info on Prompts and Filters. It's very helpfull.

I'm not sure if I understand but would this work. Update Production set [Transaction Price] = (select [Costing Sum 1] from pricelist where [pricelist].[Company]=[production].[Company] and [pricelist].[Facility]=[production].[Facility] and [pricelist].[Item number]=[production].[Component...

Couple of basic things to check. -You need to have a copy of the catalog saved in the same directory as the reports. - The catalog needs to have the database login info saved. (Catalog / User Profiles / Database from Impromptu.)

Thanks guys, I had already started to work on the alias and this works fine. I'll have a go at overiding the SQL as well, though this is pretty restrictive if I then want to add any further data.

I have 1 catalog setup against our DataWarehouse and it is working well. I have recently been creating a few new reports and I have issues with the joins. With some reports I want to remove (or not use) a join that is needed for other reports. Apart from creating a copy of the catalog is there...

If T2 holds all of the new data then how about deleting from T1 where a record exists and then insert them from T2. Steps would be Delete from T1 where custnr exists in T2 Insert into T1 (Select * from T2) If T1 joins to other tables using a unique id and not the custnr then you would first...