Trying to Filter a Table has Joined Query

[HEPennyPacker]

The report I'm working on has a first table with Revenues, the second table is Expenditures. There is a summary for both in each table. Then, I created a 3rd table at the bottom, joined the Revenues and Expenditure queries in order to show the total for both and the difference. Well, the problem is that the value is way too high because I can't get it to be filtered by Fiscal Year and Fund like the first two tables.

[MFGF]

The report I'm working on has a first table with Revenues, the second table is Expenditures. There is a summary for both in each table. Then, I created a 3rd table at the bottom, joined the Revenues and Expenditure queries in order to show the total for both and the difference. Well, the problem is that the value is way too high because I can't get it to be filtered by Fiscal Year and Fund like the first two tables.

Hi,

When you refer to "tables", do you mean list containers on the page, or are you referring to separate database tables in your database?

I'm assuming from the question it is probably the first option? If so, are the queries for each list fed from the same package/data module? If so, you may not need to perform a join in your report - just add a new list containing the relevant data from the package/data module. You could use calculations for the totals - eg total([Revenue] for report). Then just make sure you are using the same filters for Fiscal Year and Fund.

Cheers!

MF.

[HEPennyPacker]

Here is the 3rd table - https://imgur.io/a/ESJuyLN

[HEPennyPacker]

I'm assuming from the question it is probably the first option? If so, are the queries for each list fed from the same package/data module? If so, you may not need to perform a join in your report - just add a new list containing the relevant data from the package/data module. You could use calculations for the totals - eg total([Revenue] for report). Then just make sure you are using the same filters for Fiscal Year and Fund.

Okay, so I see how I can just do two lists at the bottom with the totals, but then how do I do the calculation difference? It should look like this:

Projected Revenues - $10
Projected Expenditures - $9
Difference - $1

[MFGF]

Here is the 3rd table - https://imgur.io/a/ESJuyLN

Ah, gotcha. Tables on the page. How are you populating the Revenues and Expenditures values - are you using singletons?

Okay, so I see how I can just do two lists at the bottom with the totals, but then how do I do the calculation difference? It should look like this:

Projected Revenues - $10
Projected Expenditures - $9
Difference - $1

Are all the values filtered in the same way? If so, and if you want to stick with singletons, you could base them all off the same query in your report. Then the differences are just query calculations subtracting one value in the query from the other?

Cheers!

MF.

[HEPennyPacker]

Okay, thanks. I was able to do it using Singleton's but now I can't do the math of the "difference:

So, I have this now:

Projected Revenues - $10 (this is correct)
Projected Expenditures - $9 (this is correct)
Difference - (How do I do this? when Revenue and and Expenditures are separate queries?)

[MFGF]

Okay, thanks. I was able to do it using Singleton's but now I can't do the math of the "difference:

So, I have this now:

Projected Revenues - $10 (this is correct)
Projected Expenditures - $9 (this is correct)
Difference - (How do I do this? when Revenue and and Expenditures are separate queries?)

Is it possible to pull in those values in the same query, and display them as two separate items in two separate singletons? Although a singleton will default to having its own query, it can also use a "shared" query with other containers. If so, you can just add a query calculation to the query to calculate the difference.

Cheers!

MF.

[HEPennyPacker]

Tried that, got this error:

RQP-DEF-0103 Cross joins (between query subjects: [Tables].[expledgr], [Tables].[revledgr], [Tables].[expledgr account], [Tables].[expledgr orgn], [Tables].[Expledger Fund], [Tables].[Expbudgt Fund]) are not permitted for the user who has the identity '*'.

[MFGF]

Tried that, got this error:

RQP-DEF-0103 Cross joins (between query subjects: [Tables].[expledgr], [Tables].[revledgr], [Tables].[expledgr account], [Tables].[expledgr orgn], [Tables].[Expledger Fund], [Tables].[Expbudgt Fund]) are not permitted for the user who has the identity '*'.

That sounds like the underlying database tables are not joined in your model? That's unfortunate.

In that case, you are going to have to join them in your report, which brings you right back to the beginning - having a separate query for each, and joining them into a new query. Are there common items you can use to join them?

Cheers!

MF.

[dougp]

This question would be easier to understand if we could see the problem.  Can you reproduce the problem using the GOSales (query) package and post the report spec to pastebin?

[bus_pass_man]

You do not state what you are using. I think from the syntax of the object references that it is a FM package.

You are going down a rabbit hole and I don't know why, and you don't seem to recognize that you are. 

You should not need your queries.  I do not know why you think you needed to approach the problem this way.

A report of the variance of one fact from another, usually actuals versus plan, is one of the fundamental reporting requirements. 

If your model has been properly built, then it will have all the dimensions which you need and all the facts.  Cognos is designed to recognize when you are working with multiple fact tables and generate the proper query.

With a properly designed model, you should be able to use the objects that you want right out of the model and the exercise for you would only be to simply drag and drop them into the report.  If your modeler has been diligent then your variance calculation would have been already defined in the model so you would just need to drag it into your report too.  If he hasn't then it is a matter of creating the calculation in your report, which should not be difficult.

Also your unexpectedly high variance values could be due to double counting.  What are the grains of detail for the two facts?   Are you trying to project a query below its grain?

[HEPennyPacker]

I figured it out. One of the first queries was referencing Periods which was a completely separate table. Had to join them, then join the joins and voila! Works great, thanks for all your help!