Report problem - How to?

[Colnocode]

I am trying to produce a report that is 101 with a spreadsheet but can’t figure out how to do it with Caspio. 

Very simplified so I can explain my difficulty. I have two tables  <Income> and <Cost>. Each table contain the following fields:-  ID; Project#;  and Amount.

My desired report something like this

Project#  ~ Income_Amount ~ Cost_Amount

1                           $200     

1                           $250

1                                                       $150

1                                                       $250

Total Project      $450            $400     Profit $50

I tried to use a View to get the <Income> & <Cost> by project , However, I  wound up  hundreds of records - seemed to combine every <Income> with every<Cost>. And the report was meaningless.

Simple task but how?

[cheonsa]

 Hi @Colnocode,

 

May I ask why do you need to have a separate tables for Income and Cost? I tried to create a View that joined two tables, however, the result is not like what you want.

So I just thought of creating a third table that contains project#,  income and cost fields. I also have a triggered action for the two tables that will insert a record to the third table.

And I used the third table as the Data Source of my report. 

 

Please see screenshots below.

 

 

Here is the output.

 

Hope this help. 

 

-kristina

[TsiBiRu]

FHi @Colnocode

You can actually just add a calculated field in your report DataPage and used the sample SQL code to get the value of the 'Cost_Amount' field from your 'Cost' table.

SELECT Cost_Amount
FROM Cost
WHERE cost_ID =target.[@field:cost_ID]

For this solution to work, you will need to ensure that you have set up a relationship between your  'Income' and 'Cost' table. There should be a common field between this two tables, and in my example, I've added the 'cost_ID' (which is the primary key of my cost table) to my   'Income' table in which will serve as a common field between two tables (foreign Key) to link them.

Here is the view of the relationship that I've to establish for this solution to work 

For more details about  table relationships, you can review this link 

For more details about the calculated field, you can review this link  

I hope this helps.

Regards,

TsiBiRu

[Colnocode]

Hi Kristina,

 

Thanks for your input. As I mentioned the example the <income> and <cost> are super simplified, actually they are part of a quite complex live system that I want to expand. The issue I see with your solution it does not readily allow for the dynamic selection of a project or projects to report on??

Thanks though it is a possible solution

[Colnocode]

Hi TsiBiRu

You solution has merit. Both Tables have the project number so I will kick it around and see if it will work for me

Idea much appreciated